Re: Index ignored

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. Received on Tue Aug 06 2002 - 22:18:21 CDT