Re: [oracle-l] Re: query plan is bad when it is run inside a pl/sql stored procedure

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 28 Jan 2004 18:08:45 -0000
Message-ID: <006101c3e5c9$e2bf2230$6702a8c0@Primary>

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_at_adelphia.net> To: <ssarkar97_at_yahoo.com> Cc: "oracle-l" <oracle-l_at_freelists.org> 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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Jan 28 2004 - 12:08:45 CST