Skip to Main Content
  • Questions
  • Similary Query Plan for in and exists query ..Is it possible ?

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Sutaria.

Asked: July 06, 2010 - 3:23 am UTC

Answered by: Tom Kyte - Last updated: July 08, 2010 - 11:47 am UTC

Category: Database - Version: 9.2.0.7.0

Viewed 100+ times

You Asked

I posted this question in a review earlier ( http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:442029737684#2657519000346087952 )

However.. i am taking liberty to post it again.. just in case you want to treat it as a new question

Hi Tom,
Oracle Database Version 11G

We had a bug in our application where in the original query was like this..

select bank_branch_name, bank_name, bank_branch_id
from xx_bank_branches
WHERE BANK_BRANCH_ID IN
(SELECT BANK_BRANCH_ID
from xx_bank_accounts a
WHERE a.ACCOUNT_TYPE <> 'EXTERNAL' )
order by bank_branch_name, bank_name ;

I thought of re-writing the query as

select bank_branch_name, bank_name, bank_branch_id
from xx_bank_branches b
where EXISTS ( SELECT 'x' FROM
xx_BANK_ACCOUNTS A
WHERE A.ACCOUNT_TYPE <> 'EXTERNAL'
and a.bank_branch_id = b.bank_branch_id )
order by bank_branch_name, bank_name;

However when I compared the explain plan.. the plan generated by both the queries was exactly the
same..

My question is did CBO treat both the queries as similar ?

and we said...

http://www.oracle.com/technology/oramag/oracle/06-may/o36asktom.html

in and exists are known by the optimizer to be semantically equivalent.

You said "we had a bug", but fail to say "what the bug was" at all. :( So sad.. We might have been able to help you....

I'll presume you mean "it wasn't as fast as we'd like". If so, an equivalent query might be:

select *
  from (
select bank_branch_name, bank_name, bank_branch_id,
       max(case when account_type<>'EXTERNAL' then 1 end) over (partition by bank_branch_id) flag
  from xx_bank_branches
       )
 where flag = 1
 order by bank_branch_name, bank_name;

and you rated our response

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

That Helps a lot as always

July 06, 2010 - 8:19 am UTC

Reviewer: A reader

I do have a bug which I am currently working on..
However I am yet to get the count of rows in each table
xx_bank_branches and xx_bank_accounts from the customer so did not post the data..

I reviewed the analytic query you suggested.. However it's missing the reference to the xx_bank_accounts table
The account_type column is from the xx_bank_accounts table..

Tom Kyte

Followup  

July 06, 2010 - 4:19 pm UTC

ah, I see - sorry, missed that, I thought it was in the same table.

then:

select bank_branch_name, bank_name, bank_branch_id
  from xx_bank_branches
WHERE BANK_BRANCH_ID IN
(SELECT BANK_BRANCH_ID
from  xx_bank_accounts a
WHERE  a.ACCOUNT_TYPE <> 'EXTERNAL' )
order by bank_branch_name, bank_name ;


is the way to go.

July 06, 2010 - 9:32 am UTC

Reviewer: A reader

I did try to re-write the query .. using the below sql statements.. both of them were more expensive on my test database..

alternate 1
===========
select distinct b.bank_branch_name, b.bank_name, b.bank_branch_id
from xx_bank_branches b, xx_bank_accounts_all a
where a.bank_branch_id = b.bank_branch_id
and a.account_type != 'EXTERNAL' ;

alternate 2
===========
select * from (
select b.bank_branch_name, b.bank_name, b.bank_branch_id, row_number() over ( partition by a.bank_branch_id order by a.bank_branch_id) rn
from xx_bank_branches b, xx_bank_accounts_all a
where a.bank_branch_id = b.bank_branch_id
and a.account_type != 'EXTERNAL'
)
where rn = 1 ;

Tom Kyte

Followup  

July 06, 2010 - 4:30 pm UTC

your original in query is the right way to approach this, you are done

in exist

July 07, 2010 - 5:29 am UTC

Reviewer: Snehasish Das from Kolkata, India.

Hi Tom,

I was reading your earlier post

http://asktom.oracle.com/pls/asktom/f?p=100:11:2072516818027715::::P11_QUESTION_ID:953229842074

here you said that both in and exist are evaluated differently. Can you please enlighten us.

Regards,
Snehasish Das.
Tom Kyte

Followup  

July 08, 2010 - 11:47 am UTC

read the entire page. The original answer was based on the RBO (rule based opimizer). If you read the page, you'll see how things evolved over time - the CBO was used and now it is "the same"

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.