Re: Index on status field?

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.

• Warning! Story problem! --- Two cities, A & B, are 150 miles apart. A train leaves city A for city B at 35 miles per hour just as another train leaves city B for city A at 40 miles per hour - on parallel tracks. Assume a bird that could turn instantaneously at its speed of 60 miles per hour left city A at the same time as the train, flew until it met the train from city B, turned (instantaneously - at full speed) and flew the other way until it met the first train again, turned again, .. and repeated this until the trains met. When the two trains met.how far will the bird have flown? (Total distance traveled - back and forth. The answer is scalar, not vector.)

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)

• Original Message ----- From: "Martic Zoran" <zoran_martic_at_yahoo.com> To: "Riyaj Shamsudeen" <rshamsud_at_jcpenney.com>; <lex.de.haan_at_naturaljoin.nl>; <Oracle-L_at_freelists.org> Sent: Friday, April 08, 2005 2:50 PM Subject: RE: Index on status field?

> 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

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