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

Perhaps this is better :

SELECT top_id, decode(nvl(ct_id,
'NO' ),'NO','NO','YES')
FROM top , childoftop
WHERE top_id=ct_top_id(+)

"Gary" <gary_at_moneysuite.com> a écrit
dans le message news:
e7778a4a.0203141646.4754949a_at_posting.goo gle.com...
> 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 Fri Mar 15 2002 - 08:18:26 CST