Oracle - how to imitate bit columns and boolean AND/OR?-CodePudding

I come from MS SQL and My SQL. These DBMS provide a bit data type where the two boolean values are represented by 0 and 1.

I am now in a project with Oracle that is new to me. There is no bit type. It (somewhere) advises to use NUMBER(1) as bit - so values 0 and 1 would go, but it supports also values -9 to -1 and 2 to 9.

And we have to make an OR between two such columns. I read also this article here. But what is best for OR (and AND) functionality, few boiler plate, readable for programmers (and performant)?

Here some test code to check it:

--drop table test_bool_number;
create table test_bool_number (
    test_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    abool NUMBER(1),
    bbool NUMBER(1),
    cbool NUMBER(1)
    PRIMARY KEY(test_id)
);

INSERT INTO test_bool_number (abool, bbool, cbool)
            select 0, 0, null from dual
  union all select 0, 1, null from dual
  union all select 1, 0, null from dual
  union all select 1, 1, null from dual;
 
SELECT * FROM test_bool_number ORDER BY test_id; -- to query the results

A team member proposed to use:

-- option A   for OR, * for AND
UPDATE test_bool_number
  SET
    cbool = abool   bbool;

I found this version works most probably, if cbool is deserialized in Java class boolean property, but it won't work always:

abool and bbool have to be only either 0 or 1
cbool as result does not fulfill above condition and is true if cbool > 0
and if 10 bool columns like that are summed, it results in 10 and with a number(1) leads to ORA-01438 error

So I found then this:

-- option B greatest for OR, least for AND
UPDATE test_bool_number
  SET
    cbool = greatest(abool, bbool);

Above works great, because the result is always either 0 or 1, but only:

abool and bbool have to be only either 0 or 1
And this works also for 10 and more columns OR'ed. For AND the method least instead of greatest could be taken.

But if someone calculates intermediate boolean results in the first manner or different - the value in abool and bbool could be -9 ... -1 or 2 ... 9 too! Assuming all values not being 0 are representation of true (like in e. g. C programming language) What is the simplest code to get a boolean OR?

-- bool input values are not always only 1 in case of true!
UPDATE test_bool_number
  SET abool = abool * -5, bbool = bbool *  5;

-- option C standard SQL always correct   
UPDATE test_bool_number
  SET cbool = case when ((abool <> 0) or (bbool <> 0)) then 1 else 0 end;

Above statement is fully correct, but a lot of boiler plate. So I found another option:

-- option D ABS and SIGN
UPDATE test_bool_number
  SET
    cbool = SIGN(ABS(abool)   ABS(bbool));

Is shorter, always correct. It even works with abool = 9 and bbool = 7 and the intermediate result being SIGN(16) where 16 is outside of range of NUMBER(1), but performant?

Is there a simpler one, perhaps even performant one to deal with boolean values? Or is it better to represent boolean values in an other fashion on Oracle table columns than NUMBER(1) with values 0 = false and 1 (or other) = true?

-- option E ABS   > 0
UPDATE test_bool_number
  SET
    cbool = case when ((ABS(abool)   ABS(bbool)) > 0) then 1 else 0 end;

So what about using binary or bitwise OR?

-- option F BITOR for OR
UPDATE test_bool_number
  SET
    cbool = BITOR(abool, bbool) <> 0 then 1 else 0 end;

Above only works for OR, BITAND cannot be used for AND functionality. Only some magic idea, not at all easy to understand by the next code reader:

-- option G via text
UPDATE test_bool_number
  SET
    cbool = ABS(SIGN(TO_NUMBER(abool || bbool)));

The most inner could be read as boolean or like it is in a lot of programming languages (C#, Java, ...) but in Oracle it is a string concatenation operator! So we end up with '00', '10' etc. Finally it returns only 0 or 1, but only if (abool and) bbool are positive values or 0! And I doubt the performance (number to text and text to number conversion).

So how are you working with boolean values in Oracle, and what is best suitable to be able to build an understandable OR (and AND) code for 2 and more other boolean columns, that is quite performant as well? Do you use another column type to represent boolean values? char? '0' and '1' or 'y' and 'n'? How do you make OR (and AND) with these for 2... n columns?

CodePudding user response：

Use the functions BITAND (from at least Oracle 11) and BITOR (from Oracle 21, although undocumented) and put a CHECK constraint on your columns:

SELECT abool,
       bbool,
       BITAND(abool, bbool),
       BITOR(abool, bbool)
FROM   test_bool_number
ORDER BY test_id;

Which, for the sample data:

create table test_bool_number (
    test_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    abool NUMBER(1) CHECK (abool IN (0, 1)),
    bbool NUMBER(1) CHECK (bbool IN (0, 1))
    PRIMARY KEY(test_id)
);

INSERT INTO test_bool_number (abool, bbool)
            select 0, 0 from dual
  union all select 0, 1 from dual
  union all select 1, 0 from dual
  union all select 1, 1 from dual;

Outputs:

ABOOL BBOOL BITAND(ABOOL,BBOOL) BITOR(ABOOL,BBOOL)

0 0 0 0

0 1 0 1

1 0 0 1

1 1 1 1

ABOOL	BBOOL	BITAND(ABOOL,BBOOL)	BITOR(ABOOL,BBOOL)
0	0	0	0
0	1	0	1
1	0	0	1
1	1	1	1

Prior to Oracle 21, you can create a user-defined function for BITOR.

db<>fiddle here