Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Hash semi join with IN subquery

Re: Hash semi join with IN subquery

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Wed, 18 May 2005 23:53:47 +0200
Message-ID: <020e01c55bf4$12874550$3c02a8c0@JARAWIN>


Hi Josef,

I guess the reason of the semi join is that the CBO doesn't get the right cardinality of the global temporary table (assuming there are much more records than 10 in your example).
Use cardinality hint to check this

UPDATE sessions
SET expired =3D 'T'
WHERE session_key IN
(SELECT /*+ cardinality(temp_session_ids 10) */ session_id FROM temp_session_ids)

See other possibilieties to set statistics for temporary table on http://asktom.oracle.com/pls/ask/f?p=4950:8:3276292286690697205::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:18734376046450

HTH Jaromir
----- Original Message -----
From: "Stalin" <stalinsk_at_gmail.com>
To: <Oracle-L_at_freelists.org>
Sent: Wednesday, May 18, 2005 9:31 PM
Subject: Hash semi join with IN subquery

--
http://www.freelists.org/webpage/oracle-l
Received on Wed May 18 2005 - 18:01:56 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US