Re: Index management

Mike,

Can you show us some concrete examples of how rebuilding an index changes the clustering factor?

> The idea that you should optimize an index for inserts and updates is
> totally odd to me as indexes are used for optimization of select
> processing.

So an index can't be used for UPDATE statements? What if I issued the following:

UPDATE emp
SET salary=salary*1.03
WHERE empid=1001;

Surely an index on EMPID would be beneficial in this case if the table is sufficiently large (making the normal assumption in this case that EMPID is unique).

You didn't mention DELETE statements, so I'm just adding it here....an index can also help certain DELETE statements as well. Taking my above example....

DELETE FROM emp WHERE empid=1001;

If the table is sufficiently large, then an index can help here too.

I strongly disagree with your statement "indexes are used for optimization of select processing." As I've stated above, an UPDATE and a DELETE statement can use indexes to speed processing. Here is an example of an EXPLAIN PLAN on a simple DELETE statement:

ORA9I SQL> set autotrace on explain
ORA9I SQL> delete from emp where empno=1001;

1 row deleted.

Execution Plan

   0 DELETE STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=13)    1 0 DELETE OF 'EMP'

   2    1     INDEX (UNIQUE SCAN) OF 'EMP_PK' (UNIQUE) (Cost=1 Card=1
          Bytes=13)

If an index where, as you say "used for optimization of select processing" then why use the index in the DELETE statement?

As for INSERT statements...an index won't *help* a statement similar to the following:

INSERT INTO emp (empid,ename) VALUES (1099,'Bob');

However, rebuilding an index and losing any white space can slow down the above insert should a block split need to take effect. Instead of an index on EMPID (which is a bad example for this case), an index on ENAME would show the point. If my index on ENAME had leaf values similar to:

.... Bill, Brad, Brian, ....

And I delete 'Brad', then when I insert 'Bob', it will take the slot previously occupied by 'Brad'. If I rebuild the index and remove that slot, then an insert of 'Bob' can require block splits.

> I have no magic formula for clustering factor, Oracle calculates
> that readily enough and provides it in the dba_indexes view. The
> number of dirty, used, occupied or whatever euphemism you wish to use,
> blocks is also readily available in dba_tables. A ratio is determined
> by the division of two values, so when I say the ratio of clustering
> factor (a single, easy to obtain number) to dirty blocks (another easy
> to obtain number) then I have stated my formula.

Thanks for providing that. I now know that your clustering factor is the same as Oracle's definition, since you are using the CLUSTERING_FACTOR column of DBA_INDEXES. And I now fully understand your ratio you have been talking about.

> I don't say, rebuild all indexes, which would be as bad as never
> rebuild indexes. Both of these can lead to problems. Analyze (as in
> look at various statistics), rebuild when indicated. Think for
> yourself.

On this last point, you and I agree, sort of. I don't say rebuild indexes just because of some magic number. And I don't say to never rebuild indexes. There are times to rebuild indexes and times not to. But for me, those times are more dictated by the expected flow of data, not by some magic number. If an index has a tendancy to get one-sided (normally due to the use of a monotic sequence as the value to the indexed column followed by deletions of "older" values) then rebuilding periodically can help. And if I delete massive amounts of data and I don't expect to add much more data in the future, then rebuilding can help. And sometimes rebuilding an index can help in one area and hurt in others, but the one area that I am fixing is more important to me than the other areas. But in none of these circumstances can I arrive at any ratio which defines the need to rebuild or not. There is simply no query you can apply the index and table metrics to arrive at these conclusions. A DBA has to know about the data flows in, and out of, their database.

Cheers,
Brian

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"