Neil Chandler's DB Blog

I wrote this blog post a while ago, and I’ve just spotted it in the backlog drafts, so I though it might be time to publish it. This may be old news to many of you, but I sometimes work on systems where consideration for how to store STATUS columns (or more accurately, columns with low cardinality / few values) at the design stage would have saved an awful lot of problems.

In the scenario where you are recording the status of an action within an Oracle table, there are many ways to accomplish it. However, some ways are better than others.

Let us say that we have 2 statuses; “NOT YET PROCESSED” and “COMPLETE”.

Let us also assume a 1,000,000,000 rows table for sizing purposes, with 1,000 rows “NOT YET PROCESSED” and all of the rest set to “COMPLETE”.

We could store and index these in several different ways:

Storing a descriptive status

CREATE TABLE STATUS_TAB
(prim_key NUMBER,
 otherdata VARCHAR2, etc...
 STATUS VARCHAR2(20) NOT NULL );

We could store the values as they are represented above. Very descriptive, but it will waste a lot of space.
Storing the word “COMPLETE” one billion times will take about 7.5 GB.

There’s also no guarantee that you will have good data integrity – the data is not being validated against a STATUS_DESCRIPTION, so we should also have CHECK constraint to ensure only the values we want to have in the table are there.

Using a number to represent the staus

CREATE TABLE STATUS_TAB 
(prim_key number,
 otherdata VARCHAR2, etc...
 STATUS number NOT NULL);

In this scenario, a 0 would represent “NOT YET PROCESSED” and a 1 would represent “COMPLETE”. Much better, but changing status from 0 to 1 will increase the size of your row by 1 byte.

“0” takes 1 byte to store in Oracle.
“1” takes 2 bytes to store.

You could cause some possible row migrations, and increase your storage requirement from 1GB to 2 GB for this column.

We would need a small second table STATUS_DESCRIPTION with a foreign key relationship, to explain the status and keep everything nice and relational with good data integrity.

Using a single character for the status

CREATE TABLE STATUS_TAB 
(prim_key number,
 otherdata VARCHAR2, etc...
 STATUS varchar2(1) NOT NULL);

Now, an “N” would represent “NOT YET PROCESSED” and a “C” would represent “COMPLETE”. Even better, now it’s just 1 byte to store your character, regardless of status. You still need 1 GB to store this data though.

We would need a small second table STATUS_DESCRIPTION as before.

Histograms: assuming you wish to query via the status, we need to allow Oracle to create a frequency Histogram on the indexed column in all scenario’s above, the optimizer would understand that there are very few values for the “not yet processed” status and many for “complete”, meaning that the index could be used when querying for the rare values. Without the histogram, the index is unlikely to be used for any value as Oracle would assume a 50/50 split between the 2 values and 500,000,000 single block lookups from the index isn’t going to be a selected access path.

Using NULL to represent COMPLETE

CREATE TABLE STATUS_TAB 
(prim_key number, 
 otherdata VARCHAR2, etc...
 STATUS varchar2(1) ) ;

Now, an “O” would represent “NOT YET PROCESSED” and “NULL” would represent “COMPLETE”. Now it’s just 1 byte to store your character before you process the data and NO bytes to store it when you are complete, assuming there are no columns in the table following the STATUs column. Oracle does not store NULLs at the end of a table if they are not there.

Better still, because NULL’s are not stored within B-Tree indexes within Oracle, and I suspect the query “show me what I need to process” is a lot more common than “show me what I have processed”, putting an index on a 1,000,000,000 row table with 1,000 unprocessed rows will result in an index containing 1000 bytes (plus 10 byte rowid per entry, plus block overhead).

That’s not a very big index – a couple of blocks for a potentially huge table and would be extremely quick to read. When querying for a value which is not NULL, the index would be very useful.

We would need a small second table STATUS_DESCRIPTION as before.

Using BOOLEAN type

CREATE TABLE STATUS_TAB 
(prim_key number, 
 otherdata VARCHAR2, etc...
 STATUS BOOLEAN ) ;

If you ony have 2 status levels, and will definitely not have more than 2, you could use the BOOLEAN type to store the status. This gives a TRUE or FALSE option but, and this is important in this context, a BOOLEAN takles 1 bytes to store and still has a length byte so it’s no more efficient from a storage perspective, and somewhat less flexible.

Caveat: All sizes are very approximate and don’t account for block overhead, length byte per column and – in an index – the storage of the ROWID, which takes 10 bytes per entry.