Is there a better way than two "exists" and a union?

A routine need here:

I have a parent table and a child, say

CREATE TABLE top(top_id NUMBER(12), top_text VARCHAR2(30),

   CONSTRAINT top_id PRIMARY KEY (top_id)); CREATE TABLE childoftop(ct_id NUMBER(12), ct_top_id NUMBER(12),

   otherstuff VARCHAR2(30),
   CONSTRAINT ct_id PRIMARY KEY (ct_id)); CREATE INDEX ct_top_id ON childoftop(ct_top_id);

I want a view, for others to use, that returns top_id and "Yes" if there are any child entries, and "No" if there are none. What has been quite efficient is:

CREATE VIEW top_child(tc_id, tc_anythere) AS SELECT top_id, 'Yes' FROM top WHERE EXISTS (SELECT * FROM childoftop WHERE ct_top_id=top_id) UNION ALL
SELECT top_id, 'No' FROM top WHERE NOT EXISTS (SELECT * FROM childoftop WHERE ct_top_id=top_id);

Then, an explain plan for

SELECT * FROM top_child WHERE tc_id=123;

is

>SELECT STATEMENT Cost =

  1>VIEW TOP_CHILD
    1>UNION-ALL

      1>FILTER
        1>INDEX UNIQUE SCAN TOP_ID
        2>INDEX RANGE SCAN CT_TOP_ID
      2>FILTER
        1>INDEX UNIQUE SCAN TOP_ID
        2>INDEX RANGE SCAN CT_TOP_ID

which is nice. But it seems that it could be better. One range scan on ct_top_id tells Oracle immediately that there is or is not an entry. It seems to me that there should be a construct that avoids the second scan -- but I've never been able to figure one out.

Any ideas? And a DECODE on a COUNT(*) from a subquery doesn't work because the obligatory GROUP BY on the joining foreign key means there is no return -- not a return with a COUNT(*) value of zero -- when there are no child entries, and making that an outer join causes Oracle to abandon the indexes, no matter what.

That is,

select top_id, decode(countval,0,'No','Yes') from top, (select ct_top_id, count(*) countval from childoftop group by ct_top_id)
where ct_top_id=top_id and top_id=123;

gets no rows when the top row exists but not child rows -- not the requested top row id and a decode of 0 into No.

And

select top_id, decode(countval,null,'No','Yes') from top, (select ct_top_id, count(*) countval from childoftop group by ct_top_id)
where ct_top_id(+)=top_id and top_id=123;

gets a full table scan:

>SELECT STATEMENT Cost =

  1>MERGE JOIN OUTER
    1>INDEX UNIQUE SCAN TOP_ID
    2>FILTER

      1>VIEW
        1>SORT GROUP BY
          1>TABLE ACCESS FULL CHILDOFTOP

and no hint prevents that. Received on Thu Mar 14 2002 - 18:46:13 CST