Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Index on status field?

Re: Index on status field?

From: Don Granaman <>
Date: Sat, 9 Apr 2005 01:36:20 -0700
Message-ID: <009e01c53cdf$34e89b00$6401a8c0@dilbert>

I haven't seen this thread since last night. It has been quite interesting. However, for practical situations, I'll stick with (at least most of) the original assertion - but with one additional caveat: "Your mileage may vary".

It's currently working very well for one table of > 300,000,000 records and millions of new records per day - coming in around the clock and being processed as they arrive. At any given time there are usually a few dozen - or fewer - records with an "unprocessed" status (the rest being NULL). Sometimes it's a few hundred, at worst (duhvelopers aside) a few tens of thousands, and often none. A big part of the reason that there are typically so few is that programs looking for rows to process can find them easily. Records usually get processed within seconds, and the status set to NULL. I don't have a connection, or the ambition, now to get any index stats, but think practically...

For one thing, how expensive is it to frequently online rebuild this index? In spite of the near-dogma that index rebuilds are intrinsically bad (they often are), this is an ideal case for them. [This often works for batch-oriented model also - rebuild the index immediately afterward, when there isn't much there.]

Also, lets see... Index blocks are put back on the freelist when they are completely empty... Records are being appended to a large ever-expanding table... Records are processed almost immediately and the indexed status column is set to NULL. Under normal conditions, the index is entirely empty at least once every few minutes - or hours at worst. And this relatively tiny index provides extremely fast access to the exactly the 0.00000n% of the rows that are of interest.

Mathematician: Thrilled by the prospect... Immediately starts solving an infinite series. Six minutes and a backboard full of equations later, arrives at an elegant solution.

Physicist: Hmm... The trains are converging at 75 miles per hour, so they will met in 2 hours. In that time, the bird will have flown 120 miles. Next problem?

-Don Granaman (OraSaurus, nee: QuantumMechanic)

> Riyaj,
> Thanks a lot.
> Lex, thanks to you too.
> This finally explains everything.
> Of course that there is nothing else Oracle can use
> that is smart enough to populate the missing index
> slots.
> It looks very consistent that whetever is deleted in
> the table should soon be populated and so the index
> entry will be reused.
> Smart at the end. Maybe the easiest method anyway,
> because rowid needs to be there.
> I believe I am not the only one not knowing this ;)
> I basically never needed to think about this till now.
> But again this is not excuse for not knowing it :)
> Regards,
> Zoran

Received on Sat Apr 09 2005 - 02:38:48 CDT

Original text of this message