unbalanced indexes

[jchamp02155]

Does anyone have a script to check for unbalanced indexes for all indexes in a given schema?

Thanks.
Joe

[jkoopmann]

this is a great question!
i have really never thought about researching this, but you have sparked my interest look for a follow up after this initial reply.
the question you pose is that Oracle's B-Tree index consists of a root block, any number of branch blocks, and associated leaf blocks that reference data blocks. The whole scheme of a B-Tree index is to keep itself balanced so that all requests for a leaf block take about the same amount of time. The problem stems from having an ever increasing key for your index. When the first column of an index key constantly increases in value, applications delete or modify that key column, forcing the index to spread out and leaving wasted blocks on the back end. These wasted blocks may have only a few entries compared to the blocks on the front of the index, and when future inserts are made, they are done on the front end of the index.

i am not aware of a quick answer on this one and am not aware of any method to date to determine the order of branch blocks in an index and the number of index entries in them (i will investigate this). i believe the questions really is a determination of when to rebuild an index. there are three methods that are on the forefront of my brain at this moment, those being:
1. when del_lf_rows is increasing
2. when clustering gets out of wack (if the index is supposed to follow the table data order)
3. when there is wasted space in the index (btree_space > used_space)
for more background and some scripts, please see an article i have published at http://www.dbdoctor.net/articles/dbd...lthy_index.pdf

regards,
James F. Koopmann
Co-Founder & Chief Architect
Oracle Certified Professional DBA
dbDoctor Inc.
www.dbdoctor.net

[jchamp02155]

James,

I found a doc on metalink which talks about the issue of when to rebuild an index - 77574.1.

Basically, it talks about 3 criteria to determine if a rebuild is necessary:

1. the height of the index (greater than 4 is bad)
2. the percentage of deleted leaf rows to total rows. (greater then 20% is bad)
3. distinctiveness, i.e. "how often a value for the column(s) of the index is repeated on average". It's referred to as a good or bad distribution of values. Not sure if this is what you meant by clustering.

Thee article gives the following script to analyze item 2. I was looking for a way to make it work for all indexes in a given schema. Because my pl/sql skills are poor I thought I'd see if someone else had done it. Here's the script:

SQL> select DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS) PCT_DELETED, (LF_ROWS-DISTINCT_KEYS)*100/ decode(LF_ROWS,0,1,LF_ROWS) DISTINCTIVENESS from index_stats where NAME='&index_name';

Joe

[jkoopmann]

joe,
ok, just beware this check does not validate nor answer the question you posed. indexes in oracle do get unbalanced when faced with ever increasing key values with deletes. the criteria you mention will not determine this problem.

for scripting the sql statement you pose, unfortunately the validate index command needs to be issued before the query and it wipes out the information in the index_stats table and only has information in it for the last index that you validated.
in order to over come this, if you want to keep all the information from past validate commands, you need to create a table that looks just like the index_stats table, validate for the index, insert these values in the clone table for each index.

here is some code, while not pl/sql, to
1. create a clone table (get rid of the --#'s and check the column names for your version)
2. truncates the clone table.
3. spools a validate and insert command for each index.
4. executes the command listing.

now all you have to do is change the sql you gave to go against the clone table. i have added an OWNER and a CHECK_DATE to keep track of the owner for the index since it is not in the index_stats table and the date that i got the statistics in case you want to keep some history. you may want to change the sql you have to display owner and filter on the check_date.
hope this helps.

if you need a pl/sql application to do this, it isn't that complicated and will give you a good start at learning pl/sql which is a good skill to have.

the script:
--#CREATE TABLE sys.INDEX_STATS_CHECK (
--# HEIGHT NUMBER,
--# BLOCKS NUMBER,
--# NAME VARCHAR2(30),
--# PARTITION_NAME VARCHAR2(30),
--# LF_ROWS NUMBER,
--# LF_BLKS NUMBER,
--# LF_ROWS_LEN NUMBER,
--# LF_BLK_LEN NUMBER,
--# BR_ROWS NUMBER,
--# BR_BLKS NUMBER,
--# BR_ROWS_LEN NUMBER,
--# BR_BLK_LEN NUMBER,
--# DEL_LF_ROWS NUMBER,
--# DEL_LF_ROWS_LEN NUMBER,
--# DISTINCT_KEYS NUMBER,
--# MOST_REPEATED_KEY NUMBER,
--# BTREE_SPACE NUMBER,
--# USED_SPACE NUMBER,
--# PCT_USED NUMBER,
--# ROWS_PER_KEY NUMBER,
--# BLKS_GETS_PER_ACCESS NUMBER,
--# PRE_ROWS NUMBER,
--# PRE_ROWS_LEN NUMBER,
--# OPT_CMPR_COUNT NUMBER,
--# OPT_CMPR_PCTSAVE NUMBER)
--# tablespace tools;

set pagesize 0
set verify off
set echo off
set heading off
set linesize 100

truncate table sys.index_stats_check
/

spool valyixs.lst

select 'validate index '||owner||'.'||index_name||';',
'insert into sys.index_stats_check (select a.*,'''||owner||''',sysdate from index_stats a);'
from dba_indexes
where owner not in ('SYS','SYSTEM')
/
spool off

@valyixs.lst
set echo on
set verify on
set heading on

[jchamp02155]

James,

I'm not sure why you think that the sql won't tell you that an index is unbalanced. You said "indexes in oracle do get unbalanced when faced with ever increasing key values with deletes. the criteria you mention will not determine this problem."

But the sql (select DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS) PCT_DELETED) will show the percentage of deleted leaf rows to total rows. This sounds like a way to determine unbalanced indexes to me. What am I missing?

Thanks.
Joe

[jkoopmann]

joe,
del_lf_rows is just that, the number of leaf rows deleted. while the number is good to determine when to rebuild an index, it does not tell you where in the index the leaf rows are and thus does nothing for determining the lopsidedness (balance) of the index.

i am defining (and others have also) unbalance as to when you have a key value such as a date or a sequence that continually increases over time and at the same time transactions on the data delete old values of that key. since oracle does not delete old leaf blocks that would become empty with these deletes, you could have a very large index with empty blocks on one side of the index and thus the term unbalanced.