Ah, yes, I was bitten by one of those persistent myths some weeks ago, involving this very subject.
Never stop learning... Jraed On Friday 06 December 2002 01:04, Connor McDonald wrote: > 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: Jared Still 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).
