[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) > > --
What you are posting here is hardly helpful (in terms of uselessness, it comes second to 'hallo, here are the contents of V$WAITSTAT and events plus my init.ora, please rewrite the query'). When you want to optimize a query, it's better to start with the query, vague hints (not in the Oracle acceptance) about volumes, indexes, number of rows expected and selectivity of criteria. -- HTH, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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).
