Re: Hash semi join with IN subquery

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