Optimizer problems

From: Brad Murray <BradMurray_at_SeeSigIfThere.com>
Date: 1997/12/23
Message-ID: <MPG.f09aca578f8385798984d_at_news>#1/1

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 CET