Re: Optimizer problems

Be aware that the CBO uses "ALL_ROWS" by default unless you specify "FIRST_ROWS". Also, "ALL_ROWS" tends to favor full table scans while "FIRST_ROWS" tends to favor using indexes. If you want to force the use of indexes, use the "FIRST_ROWS" mode of the CBO. Also, be sure you have done an analyze on all your tables and indexes.

Brad Murray wrote:

> I am running Oracle 7.3.2.3.1 on NT. I am using FIRST_ROWS optimization.
> I have found that for our database, Oracle rarely, if ever, uses indexes
> unless the optimizer_mode is set to FIRST_ROWS. I have 2 SQL statements.
> One appears to be a subset of the other. All columns joined in the where
> clause are indexed. The first statement is using indexes on all but three
> tables. The second statement is not using any indexes. Network traffic
> and server resource utilization are not an issue. Both of these statements
> were executed many times with similar results. I could try using hints,
> but our SQL statements are generated with a dynamic report generator which
> would need to be overhauled to include the capability to hint, and if they
> are not necessary for the first statement, why would they be necessary for
> the second statement? Does anybody have any thoughts on why the second
> statement is not finding the indexes and how to get it to find them?
>
> Statement 1:
> (Parse,Execute, and Fetch Time = 4.44 seconds)
> SELECT DISTINCT PEOPLE_INJ_PARTY.LAST_NAME ,
> PEOPLE_INJ_PARTY.FIRST_NAME , PEOPLE_INJ_PARTY.MIDDLE_NAME ,
> PEOPLE_INJ_PARTY.PEOPLE_SUFFIX , PEOPLE_INJ_PARTY.SS_NUM ,
> PEOPLE_INJ_PARTY.BIRTH_DATE , PEOPLE_INJ_PARTY.DEATH_DATE ,
> PEOPLE_INJ_PARTY.PEOPLE_ID , CLM_INJURED_PARTY.IP_ID ,
> CLM_INJURED_PARTY.PEOPLE_ID , CLM_IP_CLAIM_POOL.CLAIM_ID ,
> CLM_IP_CLAIM_POOL.IP_ID , CLM_CLAIM.CLAIM_ID ,
> CLM_CLAIM.CLAIM_TYPE , CLM_CLAIM.CLAIM_STATUS ,
> CLAIM_EVENTS_OPEN.CLAIM_ID , CLAIM_EVENTS_OPEN.EVENT_ID ,
> CLAIM_EVENTS_OPEN.CLAIM_EVENT_TYPE ,
> CLAIM_EVENTS_OPEN.EVENT_START_DATE ,
> CLAIM_EVENTS_OPEN.USER_CODE , CLM_CLAIM.CLIENT_CLAIM_NUMBER
> , PEOPLE_INJ_PARTY.MAIDEN_NAME ,
> PEOPLE_INJ_PARTY.SEX , CLM_CLAIM.APPORTIONMENT ,
> PEOPLE_INJ_PARTY.MARITAL_STATUS , '', '',
> '', '', CLM_CLAIM.FIRST_EXPOSURE_DATE ,
> CLM_CLAIM.LAST_EXPOSURE_DATE FROM CLM_PEOPLE PEOPLE_INJ_PARTY,
> CLM_PEOPLE PEOPLE_PLNTF, CLM_LAWSUIT_PLAINTIFF
> LAWSUIT_PLAINTIFF_PLNTF, CLM_INJURED_PARTY, CLM_IP_CLAIM_POOL,
> CLM_CLAIM_EVENTS CLAIM_EVENTS_OPEN, CLM_CLAIM, CLM_LAWSUIT_CLAIM_POOL,
> CLM_LAWSUIT WHERE ( CLM_LAWSUIT.STATE IN ('AL', 'AZ')) AND
> (PEOPLE_INJ_PARTY.PEOPLE_ID = CLM_INJURED_PARTY.PEOPLE_ID) AND
> (PEOPLE_PLNTF.PEOPLE_ID = LAWSUIT_PLAINTIFF_PLNTF.PEOPLE_ID) AND
> (LAWSUIT_PLAINTIFF_PLNTF.LAWSUIT_ID = CLM_LAWSUIT.LAWSUIT_ID) AND
> (LAWSUIT_PLAINTIFF_PLNTF.CLAIM_ID = CLM_LAWSUIT_CLAIM_POOL.CLAIM_ID)
> AND (CLM_INJURED_PARTY.IP_ID = CLM_IP_CLAIM_POOL.IP_ID) AND
> (CLM_IP_CLAIM_POOL.CLAIM_ID = CLM_LAWSUIT_CLAIM_POOL.CLAIM_ID) AND
> (CLAIM_EVENTS_OPEN.CLAIM_ID (+) = CLM_LAWSUIT_CLAIM_POOL.CLAIM_ID) AND
> (CLAIM_EVENTS_OPEN.CLAIM_EVENT_TYPE (+) = 'OPEN') AND
> (CLM_CLAIM.CLAIM_ID = CLM_LAWSUIT_CLAIM_POOL.CLAIM_ID) AND
> (CLM_LAWSUIT.LAWSUIT_ID = CLM_LAWSUIT_CLAIM_POOL.LAWSUIT_ID)
>
> Statement2:
> (Parse,Execute, and Fetch Time = 147 seconds)
> SELECT DISTINCT CLM_LAWSUIT.STATE, PEOPLE_INJ_PARTY.LAST_NAME,
> PEOPLE_INJ_PARTY.FIRST_NAME,
> PEOPLE_INJ_PARTY.SS_NUM,
> CLM_LAWSUIT_CLAIM_POOL.CLAIM_ID
> FROM CLM_PEOPLE PEOPLE_INJ_PARTY,
> CLM_INJURED_PARTY, CLM_IP_CLAIM_POOL,
> CLM_LAWSUIT_CLAIM_POOL,
> CLM_LAWSUIT WHERE ( CLM_LAWSUIT.STATE IN ('AL', 'AZ')) AND
> (PEOPLE_INJ_PARTY.PEOPLE_ID = CLM_INJURED_PARTY.PEOPLE_ID)
> AND (CLM_INJURED_PARTY.IP_ID = CLM_IP_CLAIM_POOL.IP_ID) AND
> (CLM_IP_CLAIM_POOL.CLAIM_ID = CLM_LAWSUIT_CLAIM_POOL.CLAIM_ID) AND
> (CLM_LAWSUIT.LAWSUIT_ID = CLM_LAWSUIT_CLAIM_POOL.LAWSUIT_ID)
> _______________________________________________________________________
> Bradley S. Murray Princeton Computer Consulting (609) 730-9100
> BradMurray(-at-)usa.net Pennington, NJ 08534-3612 Fax: 275-5651
Received on Tue Dec 23 1997 - 00:00:00 CST