Re: Case and the execution plan

sweidanz_at_yahoo.com wrote:
>
> When i use a case in a query and the case is using another table(s), the
> execution plan does not show the tables in the case.
> for example,
>
> SELECT column1, column2,
> CASE
> WHEN NOT EXISTS ( SELECT null
> FROM table_case1
> c1
> WHERE c1.column1
> = table1.column1)
> THEN 'column does not exist in table_case1'
> WHEN EXISTS (SELECT null
> FROM table_case2
> c2
> WHERE c2.column1
> = table1.column1)
>
> THEN 'column does not exist in table_case2'
> END AS description
> FROM table1, table2
> where some conditions for table1, table2
>
> In the excution plan Oracle does not show any access to table_case1 and
> table_case2. It only shows the joins for table1 and table2.
> Is that a normal behavior?
>
> Thanks,
> ZS

Unfortunately the plan table does not seem to keep up with all the goodies... For example,

select (select col from tab where ...)
from tab2

will not show 'tab' anywhere. You could enable a 10046 trace at level 8 and check the waits to determine what blocks were read - not great I know.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."