DBA_Index columns
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: DBA_Index columns

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    Cool

    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,

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    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.


    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.




    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width