Re: Index access much slower than expected.

Could you put up an explain plan (from dbms_xplan)

On Thu, May 19, 2011 at 5:28 PM, Kenneth Naim <kennethnaim@xxxxxxxxx> wrote:

> 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
>



-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

Other related posts: