Re: Index management

"Mike Ault" <mikerault_at_earthlink.net> wrote in message news:37fab3ab.0405051352.11555818_at_posting.google.com...

> Other than violating relational tenents, reordering table data will
> dramatically improve access times in many cases. Of course as the
> table ages it must be rebuilt over and over again, along with the
> index, to maintain this ordering. I hope those that follow this advice
> have lots of maintenance window time!

Dunno, Mike. I won't dwell on the relational tennets: no one seems to care about those nowadays...
IME what happens is tables are often initially loaded with data in random order regarding what the arrival rate will be in future.

So, you end up with a table that has initial data not clustered at all, then all future data in the so-called "right" sequence.

Simple example: where a "INVOICE_HEADER" table is loaded with data in sequence of invoice number, togetherr with an "INVOICE_LINE" table with data in sequence of a surrogate key. Not the sequence of the FK to "INVOICE_HEADER".

Now, in future all new rows in the "LINE" table will most likely arrive "clustered" on invoice number FK: apps often enter the lines of an invoice in one batch. But the initialy loaded ones will never be so.

The result: you get tremendous variance in efficiency of access via an index on the FK: some queries will run like lightning, others will cause huge I/O. Others yet will have crazy behaviour, sometimes taking a long time, others just running OK-ish. The bigger the delta between initial table volume and table load rates, the bigger this problem will be.

Now: re-order/re-cluster the table data ONCE, and Cod - er,sorry: Bob - will be your uncle.

> their major, multi-million row tables. I heard that first from Don
> Burleson when I reviewed one of his first Oracle books. It was a carry
> over from Mainframe tuning that is still valid. Nice of you to give
> the same advice as Don!

Actually, the first time it was mentioned in the specific Oracle context was long before Don wrote any books: in Compuserve, in the Oracle User's Group that Chris Wooldridge used to run there. And where many "original" ideas and insights were "pinched" along the years. Not just on this subject either, and by a great many writers.

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam