Re: Tunning Indexes

"Tanel Poder" <tanel_at__at_peldik.com> wrote in message news:3f1acb3f_1_at_news.estpak.ee...

Hi!

 

> Clustering factor indicates how well the data is physically organised within
> the index. IF the clustering actor approaches the number of leaf_blocks in
> the index, then you are OK, but if it approaches the number of rows in the
> table, then it means that more I/O needs to be performed to fetch the index
> blocks ,unnecessarily and the index becomes a good choice for rebuild.

 

No, clustering factor shows how much are matching rows for index key values spread out in data blocks. No index rebuild will reduce clustering factor. You could of course rebuild the table and put rows with same key values to adjacent blocks, but normally tables have more than one index anyway, so you'll always have indexes with bad clustering factor. For monotonically increasing columns such often are primary keys or dates the clustering factor is usually better, but that also depends on whether rows are inserted to the end of the table or are spread in (somewhat) empty blocks inside the table.

 

"Oops. I meant Clustering factor indicates how well the data(ie  matching rows) is physically organized within TABLE,relative to the index . And again, table rebuilds could improve the clustering factor of indexes,if they have single indexes ...not index rebuilds as I mentioned earlier,my bad "

 

A little (and very easy to do) demonstration:

 

C:\>sqlplus admin/admin

 

SQL*Plus: Release 9.2.0.1.0 - Production on P Jul 20 17:34:28 2003

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

 

SQL> create table t as select * from sys.obj$;

 

Table created.

 

SQL> create index i on t(obj#);

 

Index created.

 

SQL> create index j on t(name);

 

Index created.

 

SQL> analyze index i compute statistics;

 

Index analyzed.

 

SQL> analyze index j compute statistics;

 

Index analyzed.

 

SQL> select index_name, leaf_blocks, num_rows, clustering_factor from user_indexes;

 

INDEX_NAME                     LEAF_BLOCKS   NUM_ROWS CLUSTERING_FACTOR
------------------------------ ----------- ---------- -----------------
I                                       65      29764               398
J                                      145      29764             13749

 

2 rows selected.

 

We see that clustering factor is quite low for index on obj#. The obj# column value comes from sequence and in this fresh test database not much objects are deleted & created, that's why most inserts to this table go to the end of segment and are in same "order" than key inserts to index. But the CF for index on name column is quite high, since object names are quite random from string comparision point of view.

 

Now, let's see another example, where name column would be kind of monotonically increasing column.

 

SQL> create table t2 as select * from t order by name;

 

Table created.

 

SQL> create index i2 on t2(obj#);

 

Index created.

 

SQL> create index j2 on t2(name);

 

Index created.

 

SQL> analyze index i2 compute statistics;

 

Index analyzed.

 

SQL> analyze index j2 compute statistics;

 

Index analyzed.

 

SQL> select index_name, leaf_blocks, num_rows, clustering_factor from user_indexes;

 

INDEX_NAME                     LEAF_BLOCKS   NUM_ROWS CLUSTERING_FACTOR
------------------------------ ----------- ---------- -----------------
I                                       65      29764               398
I2                                      65      29764             14810
J                                      145      29764             13749
J2                                     145      29764               710

 

4 rows selected.

 

SQL>

 

As you see, now the index on obj# has quite bad clustering factor. Since this index fas just built, it is in perfect shape and no rebuild can make it better.

 

Thus, clustering factor should not affect decision for rebuilding indexes.

 

In theory, it might be useful to order data in table according to index which is most queried with *range scans*. Unique scans don't matter, because for accessing table data only one block is read anyway (if not migrated or chained row). But for that, it might be reasonable to use IOT's instead and forget about clustering factors..

> Blevel for most of the indexes,including large ones lies in the range 2-3
> which indicates the number of reads to be performed to get to the key value.
> Also look for the ratio of number of deleted rows to the total number of
> rows. If you find that this is a considerable percentage and does not find
> that this is not getting reused by additional inserts,you may need to
> rebuild . In most cases,Oracle will reuse the space freed up the deletes.

This is usually a rare case where index level is reduced by rebuild. The most common reason would be a table with monotonically increasing column where lots of data is inserted and 99% of data is deleted, leaving few entries in every leaf block. That way Oracle can't use leaf blocks for other values. But in this case you should use alter index coalesce, which can be done completely online (online rebuild actually still has to acquire exclusive lock on index AFAIK, but for short time, when doing final sync of index with the temporarily created materialized view log). Index coalesce does just take two blocks with adjacent key values and merges them, leaving one free for other key values (happens only if merged block will contain less data than PCTFREE allows).

Coalescing is much faster, since it only traverses index and merges blocks, rebuild requires an expensive sort operation.

 

>
> If you do decide to rebuild the index,then you have the option of rebuilding
> it online (ie doesnt lock simultaneous DML) and NOLOGGING to speedup the
> index creation.  Also you may want to increase session SORT_AREA_SIZE to
> speed up the sorts during the rebuild.

Yep, plus using PARALLEL if you got fast IO (and multiple CPUs).

 

Also, don't crosspost, c.d.o.server is right place for this kind of questions.

 

" Who was doing crosspost ? " 

 

Cheers,

Tanel.

 

>
> Hope this helps.
> Thiru
>
>
> "Javier Villegas" <mask_at_impsat1.com.ar> wrote in message
> news:YT1Sa.5756$Uc5.4404_at_fe06.atl2.webusenet.com...
> > HI.
> >
> > I have tables that have a couple of indexes
> >
> > The table are growing
> >
> >  I don´t know how can I improve the performance on the indexes (Defrag ,
> > rebuild, etc)
> >
> > Could you tell me how can I do that ?
> >
> > thanks in advance
> >
> >
> >
>
>