Re: Estimating index sizes

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/12/19
Message-ID: <349be453.47528722@inet16>#1/1

On Fri, 12 Dec 1997 14:29:15 +0100, Brian Rasmusson <br_at_belle.dk> wrote:

>Hi,
>
>I would like to see the average record size of my indexes - is this
>possible?
>
>When I use the ANALYZE command, i can see the average length of table
>rows in dba_tables, is simething similar available for indexes?
>
>Regards,
>Brian

You can get this from the index_stats view. This view is populated only immediately after the analyze index <name> validate structure. It only contains at most 1 row at a time. You should copy it to another table if you are going to analyze lots of indexes and compare them. The following demonstrates how to populate the index stats view and then whats in it....

SQL> analyze index emp_pk validate structure;

Index analyzed.

SQL> desc sys.index_stats;

 Name                            Null?    Type
 ------------------------------- -------- ----
 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

SQL> select height, blocks, lf_rows_len from index_stats;

HEIGHT BLOCKS LF_ROWS_LEN
---------- ---------- -----------

1 5 209

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Dec 19 1997 - 00:00:00 CST