Path: news.f.de.plusline.net!news-fra1.dfn.de!newsfeed.hanau.net!news.tiscali.de!tiscali!newsfeed1.ip.tiscali.net!newsfeed00.sul.t-online.de!newsmm00.sul.t-online.de!t-online.de!news.t-online.com!not-for-mail From: Maxim Demenko Newsgroups: comp.databases.oracle.server Subject: Re: SELECT statement efficiency question Date: Sun, 08 Apr 2007 20:15:42 +0200 Organization: T-Online Lines: 54 Message-ID: <4619314E.4000008@gmail.com> References: <1176039457.597640.64660@q75g2000hsh.googlegroups.com> Reply-To: mdemenko@gmail.com Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Trace: news.t-online.com 1176056164 01 26050 UY+C6rqrb-lx-pN 070408 18:16:04 X-Complaints-To: usenet-abuse@t-online.de To: Mladen Gogala X-ID: GDPQrcZvwej3T5UuuQ39cXjwk1P4PSpp6C0tk4e4yYcw3PM6weBQU0 User-Agent: Thunderbird 1.5.0.10 (Windows/20070221) In-Reply-To: Xref: news.f.de.plusline.net comp.databases.oracle.server:195983 Mladen Gogala schrieb: > On Sun, 08 Apr 2007 06:37:37 -0700, Charles Hooper wrote: > >> Good advice given so far. I would caution against creating too many >> indexes, as this will likely negatively impact performance of other >> parts of the system > > Charles, this is the line I frequently find in many books, CBT and > manuals and yet I have never seen insert or delete slowed down to the > unacceptable levels because of too many indexes. The only method to > diagnose that this is indeed happening would be to observe significant > increase in average I/O time on the underlying data file. > Again, I've never even seen this happening. I believe that this thing > with too many indexes is dangerous only in the extreme situations and it > is very hard to diagnose because the process that waits for writing the > index blocks is DBWR so the users never wait for the blocks to be > written. Users may wait for checkpoints or log file sync but not for the > index > I think, the increased IO activity is negligible compared to increased latch contention in this case. Here was an interesting blog entry about this http://esemrick.blogspot.com/2006/03/unused-indexes-and-scalability.html One can simply run this test to see the difference in 'cache buffers chains' latches: create table d as select * from dba_objects where 1=2; create table d1 as select * from dba_objects where 1=2; spool d.sql select 'CREATE INDEX D_'||COLUMN_ID||' ON D('||COLUMN_NAME||');' from dba_tab_columns where table_name = 'DBA_OBJECTS' spool off @d exec runstats_pkg.rs_start insert into d1 select * from dba_objects; exec runstats_pkg.rs_middle insert into d select * from dba_objects; exec runstats_pkg.rs_stop To be consequent, one should run 10046 trace for the indexed case and compare time spent due to IO related wait events and latch related wait events... Some years ago i've to do a big data load into OLTP Siebel system over weekend - some of very important tables (like s_customer) here often have about 40-50 indexes per table (they are just illdesigned so, to be generic) . The only possibility to get it done was to drop all indexes not required for load and recreate them after it. I suppose, many ERP systems don't differ much in this regard from Siebel. Best regards Maxim