Re: VPD vs Multiple Schemas

<thomas.kyte_at_oracle.com> wrote in message news:1160226604.789784.267100_at_b28g2000cwb.googlegroups.com...
> Jonathan Lewis wrote:
> ...
>>
>> Do not let the user see the tables, only the views.
>> Every query the user write will automatically
>> include the hospital code predicate and do
>> partition elimination - and get the correct
>> statistics because it will be for a single known
>> partition.
>>
>
> Well, actually, the optimizer will hard parse the query once against
> the view - and (fortunately) since it cannot bind peek at the
> SYS_CONTEXT function - it will use global statistics to optimize the
> query.
>
> so, even though the optimizer knows "single partition" - it doesn't
> have any idea which one. So, it will use global statistics in all
> cases to optimize this query.
>
> If it used bind variables instead - it could/would bind peek during the
> first hard parse - but that would (in my opinion) be an even worse
> situation as the query would be optimized using SOME local partitions
> statistics, but that set of statistics would be dependent on who first
> hard parsed the query - meaning the plans for that query could change
> from day to day (hour to hour, how ever often that query happens to be
> hard parsed).
>
> So, this view solution is very nice (if you have ONLY ONE predicate for
> all users, Virtual private database is "overkill", views do the job
> very nicely) but the comment about the optimization would not be true.
>>
>>

True,

Thanks for the correction - that was a fairly staggering error on my part.

I think for this particular case I would pick up your line
> (if you have ONLY ONE predicate for
> all users,

and expand it to something like: if every hospital has the same quantity and pattern of data as every other hospital, then this will work well, as the global statistics will reflect the partition-level statistics. But the point remains: the optimizer knows that one partition will be used, but doesn't know which one until run-time. (I do hope that the optimizer team doesn't decide to make the code peek inside contexts in Oracle 11 - unless they also introduce a couple of hints like 'peeking', 'no_peeking')

If the hospitals have significantly different volumes and distributions of data, the OP could still use the partitioning approach, then create a context-sensitive security policy on each table that used a context variable introduced at logon time to create a "column = 'literal'" predicate. This would give you the one-known-partition optimisation, reduce the RLS overheads to a minimum - but would give you the overheads of N copies of every cursor, with the associated latching costs if you don't code to hold cursors.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html