Re: Index ignored

Ender Wiggin wrote:
>
> Jim Poe wrote:
>
> > Hi,
> >
> > Looking at the sql and the plan, can anyone tell me why there is a TABLE
> > ACCESS FULL on FIT_DBA.QUOTE? QUOTE_ID is a unique primary key.
> >
> > SELECT A.ST_ID, NULL as CNTY_ID, NULL as CITY_ID, NULL AS ZIP_ID
> > FROM QUOTE Q, SUBM S, INSD I, OFFICE_ADDR O, ADDR A
> > WHERE Q.QUOTE_ID=10000
> > AND S.SUBM_ID = Q.SUBM_ID
> > AND I.INSD_ID = S.INSD_ID
> > AND O.OFFICE_ID = I.INSD_ID
> > AND A.ADDR_ID=O.ADDR_ID
> > and AMS_PROC.Val_Carr_State(10000, A.ST_ID, 'Q')='T'
> >
> > Description cost card bytes
> >
> > SELECT STATEMENT, GOAL = CHOOSE 5 1 54
> > HASH JOIN 5 1 54
> > NESTED LOOPS 3 6 276
> > NESTED LOOPS 2 1 38
> > NESTED LOOPS 2 1 34
> > TABLE ACCESS FULL FIT_DBA QUOTE 1 1 26
> > TABLE ACCESS BY INDEX ROWID FIT_DBA SUBM 1 5 40
> > INDEX UNIQUE SCAN FIT_DBA SUBM_PK 5
> > INDEX UNIQUE SCAN FIT_DBA INSD_PK 2 8
> > TABLE ACCESS FULL FIT_DBA ADDR 1 6 48
> > TABLE ACCESS FULL FIT_DBA OFFICE_ADDR 1 551 4408
> >
> >
> > Thanks
> >
>
> Probably a small table or you have somehow set oracle parms to believe that
> a full table scan is better than index scan for quote.

Hi Jim,

Or the current statistics are not accurate (or not there at all for the Quote table) in which case Oracle might be incorrectly treating the table as if it's very small.

Cheers

Richard