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
--- Inder.Singh_at_gecits.ge.com 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: Inder.Singh_at_gecits.ge.com
>
> 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: ListGuru_at_fatcity.com (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: rajan_sakthi_at_yahoo.com
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: ListGuru_at_fatcity.com (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).
Received on Fri Jun 21 2002 - 12:11:13 CDT
