You have to tread softly with hash joins. They are not suitable for every scenario. Take a look at metalink and Steve adams site http://www.ixora.com.au/ about hash joins tuning and problems.
HTH RS --- [EMAIL PROTECTED] wrote: > Hi, > I have a report which was taking 48 minutes. So I > added rule hint in that > now it is taking 14-15 min but, still it's not > acceptable.. Is there any way > I can elimintae these nested loops or Can I replace > them with hash joins. or > is there any other way to optimize this query. I > don't have always_semi_join > to hash_joins in the init.ora file and I don't want > to set that in init.ora. > Is there any session level setting like any rule > which enforces the hash > join. > > Regards, > SELECT STATEMENT Optimizer=HINT: RULE > SORT (UNIQUE) > NESTED LOOPS > NESTED LOOPS > NESTED LOOPS > NESTED LOOPS > NESTED LOOPS > NESTED LOOPS > NESTED LOOPS > NESTED LOOPS > NESTED LOOPS > NESTED LOOPS > NESTED LOOPS > NESTED LOOPS > NESTED LOOPS > NESTED LOOPS > NESTED LOOPS > NESTED LOOPS > NESTED LOOPS > NESTED LOOPS > TABLE ACCESS > (FULL) OF > TABLE_EMP_CERT > TABLE ACCESS > (BY INDEX ROWID) OF > TABLE_CERTIFICATION > INDEX > (UNIQUE SCAN) OF > CERTIFICATION_OBJINDEX (UNIQUE) > TABLE ACCESS > (BY INDEX ROWID) OF > TABLE_PART_CLASS > INDEX > (UNIQUE SCAN) OF > PART_CLASS_NAME_INDEX (UNIQUE) > TABLE ACCESS (BY > INDEX ROWID) OF > TABLE_EMPLOYEE > INDEX (UNIQUE > SCAN) OF > EMPLOYEE_OBJINDEX (UNIQUE) > TABLE ACCESS (BY > INDEX ROWID) OF > TABLE_PART_NUM > INDEX (RANGE > SCAN) OF > IND_PART_NUM2PART_CLASS (NON-UNIQUE) > TABLE ACCESS (BY > INDEX ROWID) OF > TABLE_MOD_LEVEL > INDEX (RANGE SCAN) > OF > IND_PART_INFO2PART_NUM (NON-UNIQUE) > TABLE ACCESS (BY INDEX > ROWID) OF > TABLE_SITE_PART > INDEX (RANGE SCAN) > OF > IND_SITE_PART2PART_INFO (NON-UNIQUE) > TABLE ACCESS (BY INDEX > ROWID) OF TABLE_CASE > INDEX (RANGE SCAN) OF > IND_CASE_PROD2SITE_PART > (NON-UNIQUE) > TABLE ACCESS (BY INDEX > ROWID) OF TABLE_SITE > INDEX (UNIQUE SCAN) OF > SITE_OBJINDEX (UNIQUE) > TABLE ACCESS (BY INDEX > ROWID) OF TABLE_ADDRESS > INDEX (UNIQUE SCAN) OF > ADDRESS_OBJINDEX (UNIQUE) > TABLE ACCESS (BY INDEX ROWID) > OF > TABLE_X_CASE_INFORMATION > INDEX (RANGE SCAN) OF > CASE_OBJIDINDEX (NON-UNIQUE) > TABLE ACCESS (BY INDEX ROWID) OF > TABLE_DEMAND_HDR > INDEX (RANGE SCAN) OF > IND_DEMANDHDR_CASEINFO2CASE > (NON-UNIQUE) > TABLE ACCESS (BY INDEX ROWID) OF > TABLE_DEMAND_DTL > INDEX (RANGE SCAN) OF > IND_DEMAND_DTL2DEMAND_HDR > (NON-UNIQUE) > TABLE ACCESS (BY INDEX ROWID) OF > TABLE_CONDITION > INDEX (UNIQUE SCAN) OF > CONDITION_OBJINDEX (UNIQUE) > TABLE ACCESS (BY INDEX ROWID) OF > TABLE_GBST_ELM > INDEX (UNIQUE SCAN) OF > GBST_ELM_OBJINDEX (UNIQUE) > TABLE ACCESS (BY INDEX ROWID) OF > TABLE_MOD_LEVEL > INDEX (UNIQUE SCAN) OF > MOD_LEVEL_OBJINDEX (UNIQUE) > TABLE ACCESS (BY INDEX ROWID) OF > TABLE_PART_NUM > INDEX (UNIQUE SCAN) OF PART_NUM_OBJINDEX > (UNIQUE) > TABLE ACCESS (BY INDEX ROWID) OF TABLE_SITE > INDEX (UNIQUE SCAN) OF SITE_OBJINDEX > (UNIQUE) > TABLE ACCESS (BY INDEX ROWID) OF TABLE_SITE > INDEX (UNIQUE SCAN) OF SITE_OBJINDEX > (UNIQUE) > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California -- Public Internet > access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). __________________________________________________ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sakthi , Raj INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
