Oracle has finally introduced support for the Boolean data type in the release 23ai. Many thanks to the Engineers at Oracle for implementing this data type for an optimal performance. PL/SQL had BOOLEAN for decades, but developers were not able to declare native boolean type for columns of tables. For this reason, developers returned VARCHAR2/NUMBER from functions instead of BOOLEAN. Interestingly, PostgreSQL, an open-source relational database that has been widely used for many years and a migration target for Oracle, has had support for Boolean data for more than the past two decades. In this article, we will discuss about the workarounds used by developers before Oracle adopted boolean, and how it works in PostgreSQL.
The Hidden Cost of Simulating True and False
For database engineers and architects designing schemas, it required workarounds to represent "true" or "false" values as ‘Y’/’N’, ‘T’/’F’, or 1/0. While these may function adequately on the surface, they may hinder performance. The lack of a native Boolean data type can be a limitation in database design and impact storage efficiency.
The Workarounds We Lived With
Before Oracle’s recent version 23ai introduced BOOLEAN as the supported datatypes, following were some of the options.
Each of them may add redundant conversions and conditions in application code and PL/SQL functions, leading to more CPU work and larger indexes.
While Oracle took 2 decades to bring this up, PostgreSQL had it forever. Postgres stores Boolean values efficiently (internally as a single byte) and allows direct logical operations as follows.
SELECT * FROM employees WHERE is_active;
UPDATE orders SET is_verified = TRUE WHERE id = 1001;
... continue reading