Re: VPD vs Multiple Schemas

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.
>
>
> --
> 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
>
>

Thomas Kyte
http://asktom.oracle.com/
http://tkyte.blogspot.com/ Received on Sat Oct 07 2006 - 08:10:04 CDT