Boolean Field in Oracle

Oracle itself uses Y/N for Boolean values. For completeness it should be noted that pl/sql has a boolean type, it is only tables that do not.

If you are using the field to indicate whether the record needs to be processed or not you might consider using Y and NULL as the values. This makes for a very small (read fast) index that takes very little space.

To use the least amount of space you should use a CHAR field constrained to 'Y' or 'N'. Oracle doesn't support BOOLEAN, BIT, or TINYINT data types, so CHAR's one byte is as small as you can get.

I found this link useful.

Here is the paragraph highlighting some of the pros/cons of each approach.

The most commonly seen design is to imitate the many Boolean-like flags that Oracle's data dictionary views use, selecting 'Y' for true and 'N' for false. However, to interact correctly with host environments, such as JDBC, OCCI, and other programming environments, it's better to select 0 for false and 1 for true so it can work correctly with the getBoolean and setBoolean functions.

Basically they advocate method number 2, for efficiency's sake, using

• values of 0/1 (because of interoperability with JDBC's getBoolean() etc.) with a check constraint
• a type of CHAR (because it uses less space than NUMBER).

Their example:

create table tbool (bool char check (bool in (0,1));
insert into tbool values(0);
insert into tbool values(1);`