DBA_Index columns

[samdba]

Hi,

I have table with 1182039 rows.

I created an index on this table on column xyz. After that I analyzed the index. It shows foll. values in DBA_INDEX for that index. New Index size is 15368192 from dba_segments.
xyz is a varchar2(150).

BLEVEL: 2
LEAF_BLOCKS: 1823
DISTINCT_KEYS: 16669
AVG_LEAF_BLOCKS_PER_KEY: 1
AVG_DATA_BLOCKS_PER_KEY: 12
CLUSTERING_FACTOR: 202428
NUM_ROWS: 477284

What is the significance of each of these values?

and How do I decide the INITIAL and NEXT extents values from these?

Thanks,

[samdba]

Hi,

Any advise plz....

Thanks,

Note: Moderators, this time I am not expecting quick answers but I am updating this thread to put it on a higher order.

[jmodic]

Ah, who could resist such a polite invitation!

Ok, lets see:

1. NUM_ROWS: 477284 (1182039 rows in a table)
This simply means that more than half of your rows have NULL in xyz column. Can't comment if this is bad or good...

2. BLEVEL: 2
Means that your index "height" is 3, all your index data is contained in three levels: level0 - root block; level1: branch blocks; level2: leaf blocks. With this amount of data you couldn't get better BLEVEL than 2!

3. LEAF_BLOCKS: 1823
The number of blocks where your indexed data (along with the corresponding ROWIDs) is stored. Can't say if this is good or bad, but probably is OK unless you have very strange setting of your PCTFREE for that index.

4. DISTINCT_KEYS: 16669
The number of distinct values in your XYZ column. If the distribution of your non-null values is very even then each of your distinct value is repeated in 29 rows.

5. AVG_LEAF_BLOCKS_PER_KEY: 1
The average number of leaf blocks needed to hold one all the entries for a single indexed value. All rows with each distinct value in XYZ can fit in a single index block, which is the optimum. Meaning that this index is excelent for the type of queries with "... WHERE xyz = something ...".

6. AVG_DATA_BLOCKS_PER_KEY: 12
The average number of blocks in a table where all the rows for a specific indexed entry. With the above figures (each indexed value repeated in 29 table rows) you can conclude that each key you have about 2 rows of the indexed key stored in the same database block. In other words, if you do "SELECT * FROM my_table WHERE xyz=:b", the engine will go to the index, and then fetch 29 rows with that indexed value from a table. In order to fetch those 29 rows from a table it will have to visit 12 table blocks. You probably can't do anything about that number (the lower the better), it simply reflects the distribution of your data over the table. If you should get better result for this statistics you would have to reload the table sorted on value in XYZ.

7. CLUSTERING_FACTOR: 202428
Means how your rows with the same indexed value are "scattered" across the table. The best number you can get here is the number of your table blocks, while the worst number would be similar to the number of table rows. Again you can influence this only by reloading the data in a sorted manner.

And BTW, none of this numbers tells you anything about how to size your INITIAL/NEXT extents for that index. The extent sizing has nothing to do with the statistics.