Re: Optimizer problems

if the explain shows hash joins being used with the full table scan (as oppose to merge joins) try disabling hash joins in the init file. I found hash joins did not work well in 7.3.2.x but when I went to 7.3.3, it made better decisions.

hash_join_enabled = false

Regards,
Richard Fuoco

In article ,
  BradMurray_at_SeeSigIfThere.com (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

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet