Re: Index access much slower than expected.
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: kennethnaim@xxxxxxxxx
- Date: Thu, 19 May 2011 18:43:00 +0200
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>
Other related posts:
