0

There is a requirement to have true or false column in table. Oracle does not allow boolean/bit datatypes for columns. So we can do this by two options char(1) or number datatype and store 1 and 0 as numbers or chars like '1' or '0'.

When doing a select on this table with where condition on this column with an index created on it which datatype is better to use. Char or number.

Or it does not matter. Please throw some light into this.

Bharathg
  • 13
  • 4
  • https://stackoverflow.com/questions/3726758/is-there-any-boolean-type-in-oracle-databases – jarlh May 13 '20 at 19:38
  • It's a shame that Oracle SQL still doesn't feature a boolean type. However, you won't see any difference in performance with `CHAR(1)` vs. `NUMBER(1)`. AFAIK, the former takes 1 byte (same as `VARCHAR2(1)`) and the latter two bytes, but I don't know whether Oracle stores them four-byte-aligned maybe. So, don't worry. If you want to store the boolean as a number 0/1, I'd use `NUMBER(1)`, because this is what one would expect. If you prefer a character, say Y/N, I'd use `CHAR(1)` or `VARCHAR2(1)`. – Thorsten Kettner May 13 '20 at 21:04

1 Answers1

0

If you store as NUMBER(1) as 0 and 1 you can do Binary operations of Boolean Truth values using BITAND

and the below implementation of bitor

CREATE OR REPLACE FUNCTION bitor(x NUMBER, y NUMBER) RETURN NUMBER 
DETERMINISTIC
IS
BEGIN
RETURN x - bitand(x, y) + y;
END;
psaraj12
  • 4,772
  • 2
  • 21
  • 30