Oracle optimizer (9iR2), inline views and "count(*)" queries

Lets suppose you've a big query with an inline view (a subquery in the FROM list) in it and in the outer query you just want to use the count of rows from the inline view. The Oracle optimizer sees this and does not evaluate the expressions in the select-list of the inline view, which can result in a great amount of speed gain if the expressions of the inline view contain heavy processing.

Let's see the following example:

create table TEST_M(
  M_ID   number not null,
  M_DATA varchar2(100) not null
);
alter table TEST_M
  add constraint PK_TEST_M primary key (M_ID);



create table TEST_D(
  D_ID   number not null,
  M_ID   number not null,
  D_DATA varchar2(100) not null
);
alter table TEST_D
  add constraint PK_TEST_D primary key (D_ID);
alter table TEST_D
  add constraint FK_TEST_D_M_ID foreign key (M_ID)
  references TEST_M (M_ID) on delete cascade;



analyze table TEST_M compute statistics;
analyze table TEST_D compute statistics;

We have a master table "TEST_M" and a detail table "TEST_D". Now let us see the execution plan for the following query:

truncate table plan_table;
explain plan for select
  count(*)
from
  (
    select
      case
        when exists(
          select
            null
          from
            TEST_D d
          where 1 = 1
            and d.M_ID = m.M_ID
        )
        then 1
        else 0
      end as expression
    from
      TEST_M m
  )
/
commit;

The optimizer detects that we are using just the count(*) in the outer query and does not evaluate the case ... end expression inside the select-list of the inline view.
Lets query the execution plan:

select
  t.operation
    || decode(
      t.optimizer,
      null, decode(t.options, null, null, ' ' || t.options),
      decode(t.object_name, null, ', OPTIMIZER = ' || t.optimizer)
    )
    as operation,
  t.object_owner,
  t.object_name,
  t.cost,
  t.cardinality,
  t.bytes,
  t.io_cost
from
  plan_table t
where 1 = 1
connect by prior t.id = t.parent_id
start with t.parent_id is null
order by
  level,
  t.position

And the result confirms what I stated above:

OPERATION                             OBJECT_OWNER  OBJECT_NAME  COST  CARDINALITY  BYTES
SELECT STATEMENT, OPTIMIZER = CHOOSE                               1             1     13
SORT AGGREGATE                                                                   1     13
INDEX                                 SMART         PK_TEST_M                    1     13

The TEST_D table (used in the inline view's select-list) is not even mentioned by the execution plan.