1. Member
Join Date
Apr 2001
Posts
103

## Index statistics

Hi Gurus,
Is there a way I can find out the efficiency of an Index.
That is I want to know the following details

Allocated blocks
Distinct keys
Times most repeated key is repeated
B-tree space (total allocated space)
Used space
Percent used
Average rows per distinct key
Average blocks gets per access
B-tree height
Leaf rows
Leaf blocks In Be
Size of all leaf rows
Size of all leaf blocks in B-tree
Deleted leaf rows
Size of all deleted leaf rows
Branch rows
Branch blocks in B-tree
Size of all branch rows
Size of all branch blocks in B-tree

Is there a view gives me the above details.

Thanks

2. Member
Join Date
Jul 2002
Location
California
Posts
128
Trick question?

Analyze the indexes or the underlying tables and check DBA_INDEXES.

P.S. Study...

3. Member
Join Date
Apr 2001
Posts
103
Yes your right, i have found out many values in DBA_INDEXES
But the following values I am unable to find out in that view!!!

Leaf rows
Leaf blocks In Be
Size of all leaf rows
Size of all leaf blocks in B-tree
Deleted leaf rows
Size of all deleted leaf rows
Branch rows
Branch blocks in B-tree
Size of all branch rows
Size of all branch blocks in B-tree

4. Member
Join Date
Apr 2003
Location
South Carolina
Posts
148

## Index Statistics

There is a table called INDEX_STATS that contains
what you are looking for; however it only contains
a single row of data at a time and you must populate it.

To populate it: validate index YOUR_INDEX_NAME;

To View: select * from INDEX_STATS;

Output:

-- Column Name Description
-- ----------------------- ----------------------------------------- HEIGHT height of the b-tree
-- BLOCKS blocks allocated to the segment
-- NAME name of the index
-- LF_ROWS number of leaf rows
-- LF_BLKS number of leaf blocks in the b-tree
-- LF_ROWS_LEN sum of the lengths of all the leaf rows
-- LF_BLK_LEN useable space in a leaf block
-- BR_ROWS number of branch rows
-- BR_BLKS number of branch blocks in the b-tree
-- BR_ROWS_LEN sum of the lengths of all the branch block rows
-- BR_BLK_LEN useable space in a branch block
-- DEL_LF_ROWS number of deleted leaf rows in the index
-- DEL_LF_ROWS_LEN total length of all deleted rows in the index
-- DISTINCT_KEYS number of distinct keys in the index
-- MOST_REPEATED_KEY how many times the most repeated key is repeated
-- BTREE_SPACE total space currently allocated in the b-tree
-- USED_SPACE total space that is currently being used
-- PCT_USED percent of space allocated that is being used
-- ROWS_PER_KEY average number of rows per distinct key
-- BLKS_GETS_PER_ACCESS Expected number of consistent mode block
-- gets per row. This assumes that a row
-- chosen at random from the table is being
-- searched for using the index

5. Member
Join Date
Apr 2001
Posts
103
Can you give me the syntax to validate index.
Because when I do
validate index INTCH1 ;
i have ORA-01418: Index does not exist

when i do
validate index 'INTCH1' ;

ORA-00953: Index name absent

Thanks

6. Member
Join Date
Apr 2003
Location
South Carolina
Posts
148
you can:

analyze index OWNER.INDEX_NAME validate structure;

OR

validate index OWNER.INTCH1 ;

select * from INDEX_STATS;

I usually look at the height, distinct_keys,
BLKS_GETS_PER_ACCESS, ROWS_PER_KEY etc...

7. Member
Join Date
Apr 2003
Location
South Carolina
Posts
148
Here is a little script I also like that will tell me
the density of columns within a table as well as a look
at the indexes... This is only usefull if you are using
the cost based optimizer. Save the script and run it
as :

sql> @c:\...\script_name

/********************************************************************/

set echo off
set scan on
set lines 132
set pages 66
set verify off
set feedback off
set termout off
set termout on
column uservar new_value Table_Owner noprint
column TABLE_NAME heading "Tables owned by &Table_Owner" format a30
select user uservar from dual;
undefine table_name
undefine owner
prompt
accept owner prompt 'Please enter Name of Table Owner (Null = &Table_Owner): '
select table_name from DBA_tables WHERE OWNER = '&OWNER' order by 1
/
accept table_name prompt 'Please enter Table Name to show Statistics for: '
column TABLE_NAME heading "Table|Name" format a15
column NUM_ROWS heading "Number|of Rows" format 9,999,990
column BLOCKS heading "Blocks" format 999,990
column EMPTY_BLOCKS heading "Empty|Blocks" format 999,990

column AVG_SPACE heading "Average|Space" format 9,990
column CHAIN_CNT heading "Chain|Count" format 999,990
column AVG_ROW_LEN heading "Average|Row Len" format 990
column COLUMN_NAME heading "Column|Name" format a25
column NULLABLE heading Null|able format a4
column NUM_DISTINCT heading "Distinct|Values" format 9,999,990
column DENSITY heading "Density" format 990
column INDEX_NAME heading "Index|Name" format a15
column UNIQUENESS heading "Unique" format a9
column BLEV heading "B|Tree|Level" format 90
column LEAF_BLOCKS heading "Leaf|Blks" format 9,999,990
column DISTINCT_KEYS heading "Distinct|Keys" format 9,999,990
column AVG_LEAF_BLOCKS_PER_KEY heading "Average|Leaf Blocks|Per Key" format 99,990
column AVG_DATA_BLOCKS_PER_KEY heading "Average|Data Blocks|Per Key" format 99,990
column CLUSTERING_FACTOR heading "Cluster|Factor" format 9,999,990
column COLUMN_POSITION heading "Col|Pos" format 990
column col heading "Column|Details" format a24
column COLUMN_LENGTH heading "Col|Len" format 990
select TABLE_NAME,
NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
AVG_SPACE,
CHAIN_CNT,
AVG_ROW_LEN
from dba_tables
where owner = upper(nvl('&&Owner',user))
and table_name = upper('&&Table_name')
/
select
COLUMN_NAME,
decode(t.DATA_TYPE,
'NUMBER',t.DATA_TYPE||'('||
decode(t.DATA_PRECISION,
null,t.DATA_LENGTH||')',
t.DATA_PRECISION||','||t.DATA_SCALE||')'),
'DATE',t.DATA_TYPE,
'LONG',t.DATA_TYPE,
'LONG RAW',t.DATA_TYPE,
'ROWID',t.DATA_TYPE,
'MLSLABEL',t.DATA_TYPE,
t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||
decode(t.nullable,
'N','NOT NULL',
'n','NOT NULL',
NULL) col,
NUM_DISTINCT,
DENSITY
from dba_tab_columns t
where table_name = upper('&Table_name')
and owner = upper(nvl('&Owner',user))
ORDER BY COLUMN_ID
/
select INDEX_NAME,
UNIQUENESS,
BLEVEL BLev,
LEAF_BLOCKS,
DISTINCT_KEYS,
AVG_LEAF_BLOCKS_PER_KEY,
AVG_DATA_BLOCKS_PER_KEY,
CLUSTERING_FACTOR
from dba_indexes
where table_name = upper('&Table_name')
and table_owner = upper(nvl('&Owner',user))
/
break on index_name
select
i.INDEX_NAME,
i.COLUMN_NAME,
i.COLUMN_POSITION,
decode(t.DATA_TYPE,
'NUMBER',t.DATA_TYPE||'('||
decode(t.DATA_PRECISION,
null,t.DATA_LENGTH||')',
t.DATA_PRECISION||','||t.DATA_SCALE||')'),
'DATE',t.DATA_TYPE,
'LONG',t.DATA_TYPE,
'LONG RAW',t.DATA_TYPE,
'ROWID',t.DATA_TYPE,
'MLSLABEL',t.DATA_TYPE,
t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||
decode(t.nullable,
'N','NOT NULL',
'n','NOT NULL',
NULL) col
from dba_ind_columns i,dba_tab_columns t
where i.table_name = upper('&Table_name')
and owner = upper(nvl('&Owner',user))
and i.table_name = t.table_name
and t.column_name = i.column_name
order by index_name,column_position
/
clear breaks
set echo on

8. Member
Join Date
Apr 2001
Posts
103
Thanks very much.
Thanks once again.

#### Posting Permissions

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