Re: size of an index

Thanks guys that is very helpful.

Regards,

Steve,

"Tanel Poder" <change_to_my_first_name_at_integrid.info> wrote in message news:3f3c1cfb_1_at_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 Mon Aug 18 2003 - 03:54:36 CDT