zhu chao,
Your numbers are impressive. But they're not ultimate gauge, which should only
be performance (well, in very special cases, taking less disk space becomes
more important). Jonathan and Tom's points are indexes should not be too
densely packed unless the DML's on them already make them so.
So show us some timing data.
Yong Huang
--- zhu chao <[EMAIL PROTECTED]> wrote:
> Hi,
> In some case, rebuild index can help, this is from my work log on my
> Datawarehouse project.
> The effect of rebuiding index:
> SQL> exec show_space('INX_BID_BIDTYPE',USER,'INDEX')
> Free Blocks.............................22
> Total Blocks............................4090
> Total Bytes.............................33505280
> Unused Blocks...........................823
> Unused Bytes............................6742016
> Last Used Ext FileId....................11
> Last Used Ext BlockId...................52851
> Last Used Block.........................547
>
> PL/SQL procedure successfully completed.
>
>
> SQL> select index_name,owner,blevel,index_type from dba_indexes where
> blevel>4;
>
> INDEX_NAME OWNER BLEVEL
> INDEX_TYPE
> ------------------------------ ------------------------------ ----------
> ---------------------------
> INX_BID_BIDTYPE DAILYLOAD 10
> BITMAP
> INX_TX_CC_STATUS_ID DAILYLOAD 6
> BITMAP
> INX_TX_BD_STATUS_ID DAILYLOAD 5
> BITMAP
>
> SQL> ALTER INDEX INX_BID_BIDTYPE REBUILD;
>
> Index altered.
>
> SQL> exec show_space('INX_BID_BIDTYPE',USER,'INDEX')
> Free Blocks.............................0
> Total Blocks............................5
> Total Bytes.............................40960
> Unused Blocks...........................3
> Unused Bytes............................24576
> Last Used Ext FileId....................15
> Last Used Ext BlockId...................39837
> Last Used Block.........................2
>
> PL/SQL procedure successfully completed.
>
> DAILYSOURCE INX_STD_ST_STATEMENTID
> 122910
>
> 149 rows selected.
>
> SQL> CONN SYSTEM/[EMAIL PROTECTED]
> Connected.
> SQL> ALTER INDEX DAILYSOURCE.INX_STD_ST_STATEMENTID REBUILD;
>
> Index altered.
>
> SQL> select 122910*16/1024 from dual;
>
>
> 122910*16/1024
> --------------
> 1920.46875
>
> SQL> select blocks from dba_segments where
> segment_name='INX_STD_ST_STATEMENTID';
>
>
> BLOCKS
> ----------
> 54642
>
> SQL> SELECT 54642*16/1024 FROM DUAL;
>
>
> 54642*16/1024
> -------------
> 853.78125
>
> SQL> SET PAUSE OFF
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Friday, November 07, 2003 6:59 AM
>
>
> > renu (and Jared),
> >
> > The reason I'm very interested in whether there's performance improvement
> is
> > that there's a thread on the newsgroup about index rebuild recently. See
> >
> >
>
http://groups.google.com/groups?threadm=pan.2003.11.03.08.09.24.330520%40RE-MO-VE.BountifulSolutions.co.uk
> >
> > Jonathan Lewis says there's almost no need to rebuild indexes, although his
> > "Practical Oracle8i" lists at least one case you may benefit by rebuilding.
> (I
> > don't have the book with me). Asktom.oracle.com has numerous messages
> advising
> > against rebuilding indexes. Let's set theory aside for a moment and do the
> > experiment. Please post your report of performance change. Thanks.
> >
> > Yong Huang
> >
> > --- renu r <[EMAIL PROTECTED]> wrote:
> > > Jared : I think it is fragmented based on scripts and knowing that there
> have
> > > been lot of deletes.
> > >
> > > One script uses the table index_stats and looks at field del_lf_rows
> which
> > > should be less at least in comparison to field lf_rows. I mean less is
> good.
> > > more bad. I am not sure about the script but I will post it here if
> someone
> > > says so or send it to anyone if they want.
> > >
> > > I am sure the experts here know about it and can clarify if it is any
> good
> > > to look at the index_stats table.
> > >
> > > One other simple useful script is :
> > >
> > > SELECT owner, index_name, blevel
> > > FROM all_indexes
> > > WHERE blevel > 2
> > >
> > > This can be bacause the size of table is big so it is not definitive. I
> will
> > > check the level after rebuild.
> > >
> > > Benefits expected : Space savings (definitely). performance (hopefully).
> I
> > > will let you and Yong know about the benefits if any. If I get some help.
> > >
> > >
> > > [EMAIL PROTECTED] wrote:
> > >
> > > I'm curious, how have you identified the fragmentation?
> > >
> > > What benefits do you expect from the rebuild of the indexes?
> > >
> > > Are you targeting certain indexes that have been identified as
> > > benefiting from a rebuild, or just planning to rebuild all indexes?
> > >
> > > Jared
> > >
> > >
> > >
> > >
> > > renu r <[EMAIL PROTECTED]>
> > > Sent by: [EMAIL PROTECTED]
> > > 11/05/2003 06:14 PM
> > > Please respond to ORACLE-L
> > >
> > >
> > > To: Multiple recipients of list ORACLE-L
> > > <[EMAIL PROTECTED]>
> > > cc:
> > > Subject: any problem rebuilding indexes used for
> replication
> > >
> > >
> > > Hello,
> > > I have to rebuild some primary key indexes due to excessive
> fragmentation.
> > > It is rebuild not drop and create. We have multi master replication
> running.
> > > Is there any problem to replication if I do that. Has anyone tried it?
> TIA.
> >
> > __________________________________
> > Do you Yahoo!?
> > Protect your identity with Yahoo! Mail AddressGuard
> > http://antispam.yahoo.com/whatsnewfree
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Yong Huang
> > INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: zhu chao
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
>
=== message truncated ===
__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Yong Huang
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
