Re: Index on status field?

I have used this technique occasionally since Oracle 6. It works best when the number of index entries is a very small percentage of the total rows. For example, we have a table now with about 300 million rows and a status column which can have one of a few distinct non-null values, each meaning some particular state of "unprocessed" and a null meaning "processed". The critical process using this is an analyzer that is continually looking for unprocessed rows to process. After it does its thing, it sets the column to null/ Thus there are typically only a few hundred or a few dozen records with a non-null value. It is an extremely efficient way to sift through the haystack to find the needles.

The index space will be reused since new records are always coming in with identical indexed values as what previously existed. It does not grow without bound - as would occur, for example, with an index on something like a datetime.

I have also used this in tables where the data is "processed" in batch mode - perhaps once per month - where it also worked well. In this case the number of "dead" index entries is large after the batch processing completes, but the space will be reused after a time.

The only drawbacks of which I have experienced are these. *This is a very special-purpose attribute of the data. You have to be vigilante that nobody gets "creative" and starts using it inappropriately. For example, I once saw a developer (definitely a duhveloper) take this construct and pervert it by recreating the check constraint so he could use a new value - to flag records as "never mind" after his (seriously flawed and untested) code started inserting duplicate records for everything. Rather than fix the core problem, the code, he decided that a "better solution" was to continue inserting duplicate records, then keep them from being processed by having another job running to find the duplicates and set this column to the "never mind" status in all but one in each set of duplicates. The index became useless and nothing but overhead. It was discovered rather quickly though since the processing essentially "stopped" (became so inefficient as to always fall further behind). [Of course, this same developer misdiagnosed the issue as a "locking problem" ;-). There was no DBA. I was called back in as a consultant to "fix the locking problem".] *The index is, of course, useless for finding "processed" records. If you need multiple "processed" status codes and determine that an index on the status would be useful for finding records with particular "processed" values, another approach is preferred.

Don Granaman (OraSaurus)

• Original Message ----- From: "Martic Zoran" <zoran_martic_at_yahoo.com> To: <Oracle-L_at_freelists.org> Sent: Thursday, April 07, 2005 6:26 AM Subject: Index on status field?

>
> The index is on the simple status field on the huge
> partitioned table per day.
> Initially it is populated with value 0.
> When the record is processed I am thinking to put the
> value to NULL so removing entries from the index and
> making the index more compact (in opposed to 1).
>
> The idea is simple, but not sure about the drawbacks?
>
> If you have 1M rows of status field with 0 and 400M
> with NULL. Then you insert another many millions into
> the table every day, then put them back to NULL after
> they are processed.
>
> How is Oracle choosing where to place the new 0 in the
> index?
> What is going to happened to the index in simple
> manner? Is it going to grow or as we all know old
> blocks will be reused?
>
> I should think of this myself or even try it, but too
> busy at the moment.
> Also, my brain is not working nicely at the moment.
>
> Thanks in advance.
>
> Regards,
> Zoran
>
>
>
>
> __________________________________
> Yahoo! Messenger
> Show us what our next emoticon should look like. Join the fun.
> http://www.advision.webevents.yahoo.com/emoticontest
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l