A few nasties still exist with compression on indexes
if they are going to be used as a unique or primary
key - you'll have to come to my session at UKOUG if
you want to see why :-)
But also jumping back to Mark's point about rebuilding
the index because it contains ever increasing values -
this is not necessarily a driver toward rebuild the
index. A simple example follows:
SQL> create table t1 ( x number, y number);
Table created.
SQL> create index t1x on t1 (x);
Index created.
SQL> insert into t1
2 select rownum,rownum
3 from sys.source$
4 where rownum < 100000;
99999 rows created.
SQL> analyze index t1x compute statistics;
Index analyzed.
SQL> select leaf_blocks from user_indexes
2 where index_name = 'T1X';
LEAF_BLOCKS
-----------
200
-- remove the "lower" half of the rows
SQL> delete from t1 where x < 50000;
49999 rows deleted.
SQL> commit;
Commit complete.
rem
rem and add to the top
rem
SQL> insert into t1
2 select rownum+100000,rownum+100000
3 from sys.source$
4 where rownum < 50000;
49999 rows created.
SQL> analyze index t1x compute statistics;
Index analyzed.
SQL> select leaf_blocks from user_indexes
2 where index_name = 'T1X';
LEAF_BLOCKS
-----------
202
-- and again
SQL> delete from t1 where x < 100000;
50000 rows deleted.
SQL> commit;
Commit complete.
SQL> insert into t1
2 select rownum+150000,rownum+150000
3 from sys.source$
4 where rownum < 50000;
49999 rows created.
SQL> analyze index t1x compute statistics;
Index analyzed.
SQL> select leaf_blocks from user_indexes
2 where index_name = 'T1X';
LEAF_BLOCKS
-----------
205
Cheers
Connor
--- Mogens_N�rgaard <[EMAIL PROTECTED]> wrote: >
Compressing indexes has been an option since 8i, and
> Jonathan Lewis has
> done some interesting presentations on this (I
> witnessed it at our
> Database Forum in Middelfart this year - very
> impresive).
>
> In short, it changes the way you should think of
> concatenated indexes,
> ie you should put the least selective column first,
> then compress it.
> That way you'll end up with very small indexes
> compared to the old days
> and ways.
>
> I don't see any drawbacks to this approach except
> that you of course has
> to unlearn what you have learned (Yoda?)...
>
> Other index things Jonathan adresses include: It is
> actually better to
> index small tables, even one-row tables.
>
> Mogens
>
> Rachel Carmichael wrote:
>
>
>http://www.tusc.com/oracle/download/author.html#loneyk
> >
> >
> >--- John Kanagaraj <[EMAIL PROTECTED]> wrote:
> >
> >
> >>Mark,
> >>
> >>
> >>
> >>>Also, I have heard about compressing indexes, but
> it is
> >>>something I have
> >>>never used before. Can anyone shed some light on
> the topic?
> >>>Are there any
> >>>drawbacks (ie: reduced IO but increased
> processing)?
> >>>
> >>>
> >>Kevin Loney presented a paper on this at IOUG 2002
> - should be in the
> >>archives at www.ioug.org.
> >>
> >>John Kanagaraj
> >>Oracle Applications DBA
> >>DB Soft Inc
> >>Work : (408) 970 7002
> >>
> >>Listen to great, commercial-free christian music
> 24x7x365 at
> >>http://www.klove.com
> >>
> >>** The opinions and facts contained in this
> message are entirely mine
> >>and do not reflect those of my employer or
> customers **
> >>--
> >>Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> >>--
> >>Author: John Kanagaraj
> >> 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).
> >>
> >>
> >>
> >
> >
> >__________________________________________________
> >Do you Yahoo!?
> >Yahoo! Mail Plus - Powerful. Affordable. Sign up
> now.
> >http://mailplus.yahoo.com
> >
> >
>
>
=====
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will
sit in a boat and drink beer all day"
__________________________________________________
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
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).
