How to manage constants within an Oracle Database

Question

This question touches on a few different parts of Oracle that I'm not particularly familiar with so bear with me:

I am trying to redesign some of the procedures and functions within the database I manage. One of the most annoying things is the use of integers to express the status of rows.

For example rows that need to be processed get the number 3, rows that have been processed get the number 0 and rows that are in the middle of processing get number 1. This is a simplified example.

What I was hoping to do was code those integers into constants so when the procedures are written the words would be self-documenting...

I've tried to use packages and functions to manage these constants. Everything comes out a bit messy. The cleanest I've found is:

CREATE OR REPLACE FUNCTION PROCESSED RETURN NUMBER AS BEGIN RETURN 0;    END;
/

This allows you to type the sql below which looks relatively neat.

SELECT rows
FROM table
WHERE status = PROCESSED;

The problem I've found is that these columns are indexed to return quickly but the functions mean the indexes aren't used.

With that background my question is: How should constants be managed in Oracle effectively? What solution has the best trade off for visual simplicity, logical organisation and database performance.

Answer 1

The reason your index isn't being used is that the two following SELECT are fundamentally different to the optimizer:

SELECT rows FROM table WHERE status = PROCESSED;

SELECT rows FROM table WHERE status = 0;

In the first case the database will interpret PROCESSED as a variable, and its value, even if constant won't be learned until execution time. An index on status will be used only if status has a strong selectivity for all values (ie there are many different values). Since you have only 3 values, Oracle makes a FULL SCAN since from its point of view any of the 3 values could be used.

In the second case, an index will be used if you have statistics on this column that show that the value 0 is very selective (ie there are few rows processed). Oracle knows at compile time that this value won't change thus an index scan will always be effective.

So, if you're sure that there are few rows processed, you will have to help the optimizer, for example with an hint like /*+ FIRST_ROWS*/.

Or you could use comments in your SQL: 0 -- PROCESSED.

Also remember that FULL SCAN are not evil.

Answer 2

This seems to be an open and shut case for placing the meaningful codes into another table with a foreign key to it from this column.

It restricts the values that can be used and provides a meaning for the numbers.

Answer 3

There is a good reason for those rows to have a integer value:

• Row size will be smaller then if you would use VARCHAR data types (for the constants you want to implement)
• The index created on an integer column will be much smaller (and can be read faster in the DB buffers) -> faster query response
• etc.

Also, I believe that the application that's using your database already knows what those values mean. So what is the real use case for your constants ?

If you really want to go with your approach, without changing the existing column type, you can create a function that will return the STRING value for each integer code:

CREATE OR REPLACE FUNCTION getStatus(statusID int) RETURN varchar2
IS statusName varchar2(20);
BEGIN
   IF statusID == 0 THEN statusName := 'PROCESSED';
   ELSEIF statusID == 1 THEN statusName := 'PROCESSING';
   ELSE statusID == 3 THEN statusName := 'PENDING';
   END IF;

   RETURN statusName;
END;

* I'm not an ORACLE Dev, but the function should look something like the one above...

Once you create the function, you will create a functional index that will use the function to look for the rows you need:

CREATE INDEX idx1_tablename ON table(getStatus(status));

And the queries for data retrieval will look like

SELECT * 
FROM tablename
WHERE getStatus(status) = 'PENDING';

Hope this helps.