Re: [oracle-l] Re: query plan is bad when it is run inside a pl/sql stored procedure
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 28 Jan 2004 18:08:45 -0000
Technically the two queries are not the same
unless particular condition (which is probably
true, if the column names means what they suggest).
If there are duplicate rows in emktg_members
(which is probably not the case, given the universal_id
column) then the MINUS option reduces multiple
occurrences to a single occurrence before the count
takes place.
It is also possible that there are many gcd_data_source_details
rows for each emktg_members rows (again the implication
of the name of the universal_id is that there are not),
in which case, the join in the second clause will
be more expensive than the initial not exists.
By the way, you asked about '%TATA.COM', did the
OP say anything about the '13' that appears in the correlated
subquery - is this a literal too, or a bind variable in the pl/sql ?
The thing that puzzles me is that either version does an
hash anti join unhinted. I have a test case that OUGHT to
do a hash anti join for NOT EXISTS in 9.2.0.4, but
doesn't even consider it. But (a) it does it with an UNNEST
hint, and (b) the equivalent NOT IN subquery is automatically
converted to a hash anti-join and, as it says in the manual,
the first step of processing a NOT IN is to convert it into the
equivalent NOT EXISTS and then optimise. (All columns
are not null, unique constraints in place to help etc.... and
the cost of the forced hash on NOT EXISTS is the same as
the unforced hash on the NOT IN, and the cost of the FILTER
that happens automatically as 12 times the cost of the hash).
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
Next public appearances:
Jan 29th 2004 UKOUG Unix SIG - v$ and x$
March 2004 Hotsos Symposium - The Burden of Proof
March 2004 Charlotte NC OUG - CBO Tutorial
April 2004 Iceland
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
----- Original Message -----
From: "Mladen Gogala" <mgogala@xxxxxxxxxxxx>
To: <ssarkar97@xxxxxxxxx>
Cc: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, January 28, 2004 3:45 AM
Subject: [oracle-l] Re: query plan is bad when it is run inside a pl/sql
stored procedure
Query
******************************************************
SELECT count(1)
FROM ats.emktg_members t1
WHERE NOT EXISTS ( SELECT 'x'
FROM gcd_data_source_details t2
WHERE t2.universal_id = t1.universal_id
AND t2.data_source_id = 13 )
AND upper(t1.email) NOT LIKE '%TATA.COM'
*****************************************************
can be re-written like this:
*****************************************************
select count(*) from (
select * from ats.emktg_members
minus
select * from ats.emktg_members t1,gcd_data_source_details t2
WHERE t2.universal_id = t1.universal_id
AND t2.data_source_id = 13
AND upper(t1.email) NOT LIKE '%TATA.COM')
*****************************************************
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
