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 ?

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;