| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: FGAC predicate - problem with plan/execution time
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
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 The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html ____Denmark__May 21-23rd ____Sweden___June ____Finland__September ____Norway___September Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html ____UK_(Manchester)_May x 2 ____Estonia___June (provisional) ____Australia_June (provisional) ____USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html "dnh" <nigel.hurst_at_ds-s.com> wrote in message news:3eb1d68e$1_at_cpns1.saic.com...Received on Tue May 06 2003 - 04:21:48 CDT
>
> "Richard Kuhler" <noone_at_nowhere.com> wrote in message
> news:Yddsa.27546$Ye6.1718625_at_twister.socal.rr.com...
> > dnh wrote:
> > >
> > > Hi
> > >
> > > I am currently using the fgac functionality to limit users view
of data.
> I
> > > am having a problem in that in a particular case I am getting a
> different
> > > explain plan and much slower execution times on running it as a
user to
> whom
> > > the predicate is applied than if I manually run the same query
> (supposedly!)
> > > as a user for whom I build a null predicate.
> > >
> > > I am running oracle 8.1.7.4 on a SUN/Solaris platform.
> > >
> > > I have not posted the query as I am initially looking for any
ideas as
> to
> > > why I am getting different plans and run times for what is
essentially
> the
> > > same query. Has anybody else come across this problem?? I do get
the
> same
> > > results from both queries
> > >
> > > cheers
> > > nigel
> >
> > This has been an issue in all the VPD implementations I've worked
on.
> > The FGAC functions essentially created inline views in place of
the
> > table with the criteria added. This means Oracle now has a view
it can
> > merge or not, additional access paths to consider, predicates it
might
> > push, etc... That increases complexity and makes coming up with
the
> > optimal execution plan more difficult.
> >
> > I'm not sure what you mean exactly by "I manually run the same
query".
> > If you create the same inline views as the FGAC functions produce
and
> > run from the same schema you should get the same plan (barring
session
> > configuration changes). Are you claiming you aren't?
> >
> >
> > Richard
> >
>
> I thought it just added the predicate onto the query as an 'and'
set. Here's
> a simplified example of the query I'm having problems with
>
> select event.part_number
> from event,
> action
> where event.system_event_number = action.system_event_number(+)
> and event.part_number = 'l'
> and action.part_number = 'p'
>
> the predicates are
>
> for event table:
> exists (select 1 from event_security
> where event.event_security.system_event_number =
> event.system_event_number)
>
> for action table:
> exists (select 1 from event_security
> where action.event_security.system_event_number =
> event.system_event_number)
>
> I thought it added these to the end of the where clause thus
>
> select event.part_number
> from event,
> action
> where event.system_event_number = action.system_event_number(+)
> and event.part_number = 'l' and action.part_number = 'p'
> and exists (select 1 from event_security
> where event_security.system_event_number =
> event.system_event_number)
> and exists (select 1 from event_security
> where event_security.system_event_number =
> action.system_event_number)
>
> but it seems that they do this, is that what you're saying?
>
> select event.part_number
> from (select * from event
> where exists (select 1 from event_security
> where event_security.system_event_number =
> event.system_event_number)) event,
> (select * from action
> where exists (select 1 from event_security
> where event_security.system_event_number =
> action.system_event_number)) action
> where event.system_event_number = action.system_event_number(+)
> and event.part_number = 'l' and action.part_number = 'p'
>
> if I run it using the inline view method I do get the same plan,
> unfortunately it's the slow one.
>
> I think I understand now, I was mistaken in how oracle added the
predicate
> to the query. It adds it on a per table basis rather than a query
basis.
> Correct??
>
> cheers
> nigel
>
>
 |
 |