Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Are you sure the indexes are not used? Maybe your table is not sufficiently large and optimizers decides to use full table scan over indexes? IIRC this is the case when the table is small enough, and full table scan time is comparable to that of index scan. I can see TABLE ACCESS BY INDEX ROWID and INDEX RANGE SCAN in my explain plan even though there's only a few records in the table, and my query does contain a function call as in your query. –  Yasir Arsanukaev May 30 '13 at 7:24
    
@Ewanw do you want to understand why index is not being used? –  Ste May 30 '13 at 8:00
2  
You need to specify deterministic on your function definition otherwise Oracle has no way of knowing that your function cannot return different results for each invocation -- eg if the body of the function depends on some other objects whose value could change. See docs.oracle.com/cd/E11882_01/appdev.112/e10472/… –  Colin 't Hart May 30 '13 at 9:04
    
Yasir, According the explain plan they are not. I will do some performance testing and rebuild statistics and confirm Ste, That was covered in the question below, I'm not so concerned with why they aren't as I am with having reasonable performance Colin, I did try adding deterministic to my functions, it didn't appear to change anything. I will re-test and confirm –  Ewanw May 30 '13 at 23:28
add comment

3 Answers

up vote 1 down vote accepted

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.

share|improve this answer
    
I think this approach makes the most sense. I've done a lot of investigation and performance testing. Full scans seem to be the way it works but for my purposes the performance is equal or really close and the readability of code is invariably better. –  Ewanw Oct 1 '13 at 23:34
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Just to clarify the PROCESSED in my code is a variable rather than a string (note I didn't use quotes - this is on purpose) –  Ewanw Jun 4 '13 at 4:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.