Re: FGAC predicate - problem with plan/execution time

Jonathan

I didn't think that there was any other way than using the exists stmt if I wanted to do a join to another table in the predicate. I believe I can only add a where clause, I don't think I can add more tables to the inline view I now know it creates.

I know the outer join is bad news - I'm limited by the fact that the query is being used in a Business Objects Universe and apparently the 2 tables can only be joined once and the children are optional so it needs the outer join. It runs fine without the outer join, no perf problems.

thanks

   nigel

--
~~~~~~~~~~~~~~~~~~~~~~~
Remove backward spam to reply!
~~~~~~~~~~~~~~~~~~~~~~~

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:b97upa$9j8$1$8302bc10_at_news.demon.co.uk...


>

> Richard's description is correct.

> Your existence clause is appended

> to the table, and in inline view created,

> as in:

>

>

> select event.part_number

> from

>     (    select * from event

>          where exists (select 1 from event_security

>                               where

> event.event_security.system_event_number =

>                               event.system_event_number)

>     )    even

>     (    select * from action

>          where   exists (select 1 from event_security

>                           where

> action.event_security.system_event_number =

>                             evnt.system_event_number)

>     )    action

> where event.system_event_number = action.system_event_number(+)

> and   event.part_number = 'l'

> and  action.part_number = 'p'

>

>

> Using an existence clause as an FGAC predicate

> is bad news as it is - it can easily result in very

> complex statements which give the optimizer a

> lot of work to deal with.

>

> Moreover, your query now has an outer join to a

> table with an existence subquery - and in 8.1 Oracle

> will almost certainly have to instantiate the EVENT

> view as a temporary table before join to the action

> view.  (Oracle 9 is a little better at pushing predicates

> to minimise the impact of this type of issue).

>

>

>

> --

> Regards

>

> Jonathan Lewis

> http://www.jlcomp.demon.co.uk