Index access much slower than expected.

  • From: "Kenneth Naim" <kennethnaim@xxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 19 May 2011 12:28:26 -0400

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





Checked by AVG -
Version: 10.0.1375 / Virus Database: 1509/3647 - Release Date: 05/19/11

Other related posts: