Re: dynamic join view

Here is an example of what I would like to do.

   create or replace view V as
   select A.id, B.bb, C.cc
     from A, B, C
    where B.id (+) = A.id
      and C.id (+) = A.id;

   select id, bb
     from v;

I would like the execution plan for the select statement to _not_ involve table C because it is not needed in the results. However, now that I look at this, I realize that the database doesn't even know that this is the case ... unless there is at most ONE C.id for each A.id. As it turns out, this is the case in the query I am writing, so my question still stands ... I am just more doubtful now that it is possible.

I understand dynamic SQL, and *think* I understand variable cursors. But as far as I know, a variable cursor is accessed only through PL/SQL ... using FETCH INTO. Is there a way to incorporate a variable cursor directly into an SQL statement, treating it like an SQL view?

Thanks.

• jetlag --