In a previous post, I was lamenting the lack of a Boolean Data Type in Oracle Database. As of Oracle Database 23c, Boolean Data Types are finally here! There are some caveats and documentation issues regarding Boolean Data Types, but they otherwise work as advertised. I’ll briefly discuss some of the storage implications as well.
What Did Booleans Look Like Before?
In the past, you had to either stay within PL/SQL or use some other way to represent True/False values in a column. One way is to have a single CHAR column contain a ‘Y’ or a ‘N’ value:
create table char_bool
(
id number,
bool1 char(1),
bool2 char(1),
bool3 char(1),
bool4 char(1),
bool5 char(1)
);
insert into char_bool
values(1,'Y','Y','N','Y','N'),
(2,'N','Y','Y','Y','Y'),
(3,'Y','N','Y','N','N');To test the value of these Boolean stand-ins, you must use the value in a WHERE clause comparing the column to ‘Y’ or ‘N’.
select * from char_bool where bool2 = 'Y';Declaring Booleans in a Table Column
As of Oracle Database 23c (Developer Edition Preview) the built-in Boolean Data Type can be declared with the BOOLEAN (or BOOL) keyword, like this:
create table real_bool
(
id number,
bool1 boolean,
bool2 boolean,
bool3 boolean,
bool4 boolean,
bool5 boolean
);Populating the table can be done with a number of different constants representing TRUE or FALSE. Here I’m using the 23c table values constructor to insert the first 3 rows, then insert another row.
insert into real_bool
values(1,'YES','yes','fALSe','on','off'),
(2,'NO','1',null,'yES','YES'),
(3,'no','no','Y','n','0');
insert into real_bool values(5,42,'no','Y','n','0');Note that you have a lot of options when you populate a Boolean column: you can use ‘1’, ‘yes’, ‘on’, or ‘true’ to mean TRUE, and the value is case-insensitive. A numeric value of 0 means FALSE, and any other value is TRUE.
When you query the table you will see only 0, 1, or NULL values.
select * from real_bool;
The documentation is currently incorrect that the output of the query will contain the strings TRUE and FALSE without an explicit conversion.
Querying for a particular row in a WHERE clause is now more straightforward and intuitive:
select * from real_bool where bool1 and bool3;
What About the Storage?
But is the storage allocated any smaller when using a real Boolean Data Type? At first, it looked like a BOOLEAN Data Type occupies one byte each, even though by definition you could store a BOOLEAN value with only one bit.
select dump(bool2) from real_bool;
DUMP(BOOL2)
----------------
Typ=252 Len=1: 1
Typ=252 Len=1: 1
Typ=252 Len=1: 0
Typ=252 Len=1: 0Inserting 10,000 more rows into both the CHAR-based table and the BOOLEAN-based table implies that BOOLEAN Data Types occupy less space in each row:
select table_name,num_rows,avg_row_len
from dba_tab_statistics
where table_name like '%BOOL';
TABLE_NAME NUM_ROWS AVG_ROW_LEN
------------ ---------- -----------
CHAR_BOOL 10004 14
REAL_BOOL 10004 9
However, the amount of storage allocated appears to be the same even if I insert 1,000,000 rows in each table.
The Exadata Angle
When you access tables with Booleans on Exadata, the big question for me is: as a new Data Type, will Booleans participate in offload operations? Can Booleans use storage indexes for even faster access and more offloading? I expect that is true, but I won’t be able to find out until Oracle Database 23c is available on Exadata (on premises or in OCI). Version 23.1 of the Exadata software is now available on X10M and that will be the subject of an upcoming post.
All Kinds of Exadata Stuff 