Re: Index on status field?
- From: Martic Zoran <zoran_martic@xxxxxxxxx>
- To: granaman@xxxxxxx, Oracle-L@xxxxxxxxxxxxx
- Date: Fri, 8 Apr 2005 01:27:20 -0700 (PDT)
Don,
Thanks a lot.
That is exactly my option.
But again curious about how Oracle hadnling the
situtation in the index where your are making the old
values NULL and inserting the same value 0 again and
again.
My partitions are around 30M each.
One index on that stupid modified date is 70% bigger
then on the insert date suggesting that index may be
bigger then needed when you are updating to some
value. This is reasonable to suggest or to have,
because Oracle cannot make the B*tree the same with
different spread of values on the date field.
But now curious if I use only 0 and NULL (for
processed) how the index going to behave?
Just curious about what will be with the index blocks
where I making values NULL (removing entries from the
index). Which kind of discrepancy is going to happen.
So again, looking somebody to explain to me what is
Oracle doing with the same values coming to the index
while there are many blocks emptied with the UPDATE to
NULL value?
Anybody?
Regards,
Zoran
--- Don Granaman <granaman@xxxxxxx> wrote:
> 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)
__________________________________
Do you Yahoo!?
Yahoo! Personals - Better first dates. More second dates.
http://personals.yahoo.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
