Re: Index access much slower than expected.
From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Thu, 19 May 2011 18:43:00 +0200
Message-ID: <4DD54894.5090902_at_roughsea.com>
On 05/19/2011 06:28 PM, Kenneth Naim 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 <http://www.avg.com>
> Version: 10.0.1375 / Virus Database: 1509/3647 - Release Date: 05/19/11
>
Date: Thu, 19 May 2011 18:43:00 +0200
Message-ID: <4DD54894.5090902_at_roughsea.com>
On 05/19/2011 06:28 PM, Kenneth Naim 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 <http://www.avg.com>
> Version: 10.0.1375 / Virus Database: 1509/3647 - Release Date: 05/19/11
>
Ken,
The query might be more useful than the plan to understand what's going on. And for a start, 309-column tables always leave me with some vague feeling of uneasiness.
-- Stephane Faroult RoughSea Ltd <http://www.roughsea.com> Konagora <http://www.konagora.com> RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd> -- http://www.freelists.org/webpage/oracle-lReceived on Thu May 19 2011 - 11:43:00 CDT