Index Internals – Rebuilding The Truth December 11, 2007

The issue of when to rebuild indexes crops up again and again and again. Unfortunately, so do the same incorrect, myth-filled uninspired responses which considering how important and “key” (no pun intended) indexes are to database design and performance generally, is very puzzling indeed.

In the coming days I’ll post why I believe these index related myths simply refuse to go away …

This presentation was originally written several years ago but is still as relevant today as it’s always been.

Share this:

Like this:

Related

I see a question about rebuilding indexes everyday in several forums and mailing lists. Even Oracle’s own people suggest rebuilding indexes based on some percentage numbers or based on the blevel of an index in the reports they prepare for the customers. I think what they say is also not helping to destroy these myths.

To automate this task , I created a simple and practical procedure , which meets all the all indices ( partitioned , sub_particionados or single) .
To use it , use the example below:
BEGIN DBA_PS.PS_UNUSABLEIDX_PROC ( ‘ OWNER ‘ DEGREE ) ; END;

where:
Owner = is the owner of the object, for example : HRITPRD . Required inform !
Degree = is the degree of parallelism to be used in the Rebuild . The largest valid value is 30 !
If they try to use more parallel , an error ” ORA- 20010 ” will be displayed .
This clause is therefore not required can be omitted.

/*
GRANT ACCESS VIEWS IN READING BELOW FOR THE OWNER OF THIS PROCEDURE
GRANT SELECT ON V_ $ SESSION TO ;
GRANT SELECT ON TO sys.dba_dml_locks ;
grant alter any index to ;
grant execute any procedure to ;
grant create any index to ;
*/

When we create a table has 1000 rows and contain 200 distinct values and also create a index on that column contain 1000 rows and 200 distinct values so does affect clustering factor?
If affect so clustering factor is bad or good?

But if the question you’re asking is what is the CF of an index with 200 distinct values on a 1000 row table, then it depends on the ordering of that column within the table.

If the column values are distributed randomly throughout the table, such that as you read through the index entries, they continually access different table blocks, then CF could be as high as 1000. The CF could be improved in this case if you have set table_cached_blocks table preference: