And here's the answer . . .

Your answer inspired me to go further, and I found the following succeeded in performing what I needed in completely optimum fashion. This surprised me in that I've often encountered a perfectly well structured explain plan, using all indexes, that was destroyed and turned into a hash join or a full table scan when a join (among indexed values) was made outer.

CREATE VIEW top_child(tc_id, tc_anythere) AS SELECT top_id, decode(ct_top_id,null,'NO','YES') FROM top , childoftop
WHERE top_id=ct_top_id(+);

And the following query obtained the indicated explain plan, which is perfection:

select * from top_child where tc_id=123;

>SELECT STATEMENT Cost =

  1>NESTED LOOPS OUTER
    1>INDEX UNIQUE SCAN TOP_ID
    2>INDEX RANGE SCAN CT_TOP_ID Thanks to all.

"F. MAAREF" <fmaaref_at_hfp.fr> wrote in message news:<a6srgb$sto$1_at_reader1.imaginet.fr>...
> Perhaps this is better :
>
> SELECT top_id, decode(nvl(ct_id,
> 'NO' ),'NO','NO','YES')
> FROM top , childoftop
> WHERE top_id=ct_top_id(+)
>
>
>
>
Received on Tue Mar 26 2002 - 10:27:50 CST