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

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Sutaria.

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

Last updated: July 08, 2010 - 11:47 am UTC

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...

https://www.oracle.com/technetwork/issue-archive/2006/06-may/o36asktom-099107.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;

Rating

  (3 ratings)

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

Comments

That Helps a lot as always

A reader, July 06, 2010 - 8:19 am UTC

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
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.

A reader, July 06, 2010 - 9:32 am UTC

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
July 06, 2010 - 4:30 pm UTC

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

in exist

Snehasish Das, July 07, 2010 - 5:29 am UTC

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
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.