Re: help with index hint

In our last gripping episode gdas_at_my-deja.com wrote:
> Sorry here's the plan:
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=61)
> 1 0 SORT (AGGREGATE)
> 2 1 NESTED LOOPS (Cost=2 Card=2 Bytes=122)
> 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'RUN' (Cost=1 Card=2
> Bytes=72)
>
> 4 3 INDEX (RANGE SCAN) OF 'RUN_RUN_ID_IDX' (NON-UNIQ
> UE) (Cost=1 Card=2)
> 5 5 INDEX (UNIQUE SCAN) OF 'SYS_C001378' (UNIQUE)
>
> Thanks,
>
> In article <8ua2ac$aoi$1_at_nnrp1.deja.com>,
> David Fitzjarrell <oratune_at_aol.com> wrote:
> > In our lasat gripping episode gdas_at_my-deja.com wrote:
> > > I've done this many times before, but for some reason, it just
 won't
> > > work today and I can't understand it.
> > >
> > > I'm trying to specify an index hint to use a specific index in a
 table,
> > > and oracle is simply ignoring my hint and not using the index I'm
> > > telling it to.
> > >
> > > My looks like this:
> > >
> > > SELECT /*+ INDEX(RUN RUN_ACCT_ID_IDX) */
> > > COUNT(RUN_ID),MAX(RUN_ID),AVG(RUN_ID)
> > > FROM
> > > RUN A WHERE A.ACCOUNT_ID = 723 AND A.STATUS = 'OK';
> > >
> > > When I run an explain plan on this statement, it doesn show the
> > > specified index in the hint being used.
> > >
> > > Did I do incorrectly specify the hint? I'm stumped.
> > > By the way, this is on 8.1.5.
> > >
> > > Any ideas appreciated,
> > > Gavin
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> > >
> >
> > Please provide the query plan -- seeing both the query and the
> > execution plan Oracle is using will go a long way to help explain
 this
> > behaviour.
> >
> > --
> > David Fitzjarrell
> > Oracle Certified DBA
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Let me go 'out on a limb' here and presume that ACCOUNT_ID is part of the primary key on RUN; this would explain the unique index scan on SYS_C001378 (I imagine that the primary key was declared in the CREATE TABLE statement and thus is unnamed). Presuming this is true the scan of the primary key will be faster and more efficient than any other index created with ACCOUNT_ID. From there it is a simple jump to the index on RUN_ID since it is, again, more efficient than the RUN_ACT_ID_IDX index, especially since all operations in the select are acting upon the RUN_ID column.

It appears that Oracle is using a more efficient query plan than would be generated by using your index hint.

You've done nothing wrong in thought or deed; you've simply been outoptimized  by the optimizer.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.