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).
