Re: size of an index

From: Tanel Poder <change_to_my_first_name_at_integrid.info>
Date: Fri, 15 Aug 2003 02:36:25 +0300
Message-ID: <3f3c1cfb_1@news.estpak.ee>

Hi!

I would put it that way:

If you want to know how much of space is allocated to an index, query blocks or bytes from dba_segments.
If you want to know, how many more blocks can an index use *inside allocated space* before growing, use validate index as Norman suggested, and calculate blocks - (lf_blks + br_blks) from index_stats to get number of free blocks. If you want to take a peak how much free blocks you got *inside* the index (below highwatermark), then use dbms_space package. If this is not enough, then do index treedump and dig into block contents ;)

A simple example follows:

SQL> create table t as select * from sys.obj$ where rownum <=1000;

Table created.

SQL> create index i on t(obj#);

Index created.

SQL> validate index i;

Index analyzed.

SQL> select lf_blks, br_blks from index_stats;

   LF_BLKS BR_BLKS

• ----------

         3 1

SQL> select blocks - (lf_blks + br_blks) from index_stats;

BLOCKS-(LF_BLKS+BR_BLKS)

---

                       4

SQL> var i number;

SQL> exec dbms_space.free_blocks('ADMIN', 'I', 'INDEX', 0, :i);

PL/SQL procedure successfully completed.

SQL> print i

         I

---

         0

SQL> delete from t;

1000 rows deleted.

• Note that blocks are put on master freelist after you have committed.

SQL> exec dbms_space.free_blocks('ADMIN', 'I', 'INDEX', 0, :i);

PL/SQL procedure successfully completed.

SQL> print i;

         I

---

         0

SQL> commit;

Commit complete.

SQL> exec dbms_space.free_blocks('ADMIN', 'I', 'INDEX', 0, :i);

PL/SQL procedure successfully completed.

SQL> print i

         I

---

         3

• But the HWM remains same even though all records are deleted.

SQL> validate index i;

Index analyzed.

SQL> select blocks - (lf_blks + br_blks) from index_stats;

BLOCKS-(LF_BLKS+BR_BLKS)

---

                       4

Cheers,
Tanel.

"Norman Dunbar" <Norman.Dunbar_at_lfs.co.uk> wrote in message news:E2F6A70FE45242488C865C3BC1245DA7041286D9_at_lnewton.leeds.lfs.co.uk...
> Analyze index <index_name> validate structure;
> select <stuff> from index_stats;
>
> Stuff can be BLOCKS (which is effectively what you want) or anything
> else like LF_ROWS (number of index entries) DEL_LF_ROWS (number of
> 'holes' waiting to be filled as new values get inserted), HEIGHT (how
> many levels in the index, each level = one block read when looking
> something up - then an extra for the data) etc.
>
> HTH
>
> Regards,
> Norm.
>
> PS. INDEX_STATS only ever holds one row - the last index you
> analy[sz]ed.
>
> -------------------------------------
> Norman Dunbar
> Database/Unix administrator
> Lynx Financial Systems Ltd.
> mailto:Norman.Dunbar_at_LFS.co.uk
> Tel: 0113 289 6265
> Fax: 0113 289 3146
> URL: http://www.Lynx-FS.com
> -------------------------------------
>
>
> -----Original Message-----
> From: Steven [mailto:SPAMBLOCKER_at_gmx.net]
> Posted At: Thursday, August 14, 2003 4:47 PM
> Posted To: server
> Conversation: size of an index
> Subject: size of an index
>
>
> Hi,
>
> Does anyone have a query whereby I can get the physical size of an index
> in
> bytes.
>
> Thanks,
> Steve.
>
>
> --
> -----------------------------------------------------------------
> Holidays in Berchtesgaden, Germany:
> http://www.sonnenkoepfl.de
> http://unterkunft-berchtesgaden.de
> http://pension-berchtesgaden.de
>
>
Received on Thu Aug 14 2003 - 18:36:25 CDT