Re: Index access much slower than expected.
From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Thu, 19 May 2011 09:39:37 -0700 (PDT)
Message-ID: <488819.95357.qm_at_web65403.mail.ac4.yahoo.com>
Date: Thu, 19 May 2011 09:39:37 -0700 (PDT)
Message-ID: <488819.95357.qm_at_web65403.mail.ac4.yahoo.com>
Can you post the original plan? David Fitzjarrell ________________________________ From: Kenneth Naim <kennethnaim_at_gmail.com> To: oracle-l_at_freelists.org Sent: Thu, May 19, 2011 9:28:26 AM Subject: Index access much slower than expected. I have an application generated 4 table query that produced explain plan that has an index join of 3 indexes (hash join of 2 indexes hash joined to a third) among the other joins. The 3 indexes are on the same table mdp, it has 55k rows and is 88mb in size, 309 columns, 1200 chained rows. The query ran for 9+ minutes with the index joins. With a full hint it runs 1:40. I altered the session to disable index joins using the underscore parameter and got an identical plan to the original with just one of the indexes. I expected this to be perform better than the full scan as the predicate returns 2305 rows out of the 55k or 4% however it ran for over 10 minutes. Any ideas on what I should be looking at to figure out why this is happening? Thanks, Ken ________________________________ Checked by AVG - www.avg.com Version: 10.0.1375 / Virus Database: 1509/3647 - Release Date: 05/19/11
-- http://www.freelists.org/webpage/oracle-lReceived on Thu May 19 2011 - 11:39:37 CDT