Re: Oracle 9i VPD Predicate Function

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 6 Jun 2005 15:26:25 +0000 (UTC)
Message-ID: <d81pv1$p3e$1@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com>

"Chris McMahon" <NOSPAM_cochrane68_at_hotmail.com> wrote in message news:IDgoe.33776$Fv.11115_at_lakeread01...

> Thanks for the answer! I assume there aren't any significant negative
> performance implications to this "double" call to the predicate function
> that I need to worry about?

Of course there are significant negative performance implications - you are calling an anonymous pl/sql block and your predicate function twice per call to the underlying pl/sql statement. If your SQL is very light-weight, the cost of the VPD work could be greater than the cost of the basic query.

Other posters have mentioned the 'static' option. This doesn't work properly. Create a table, create a function that returns a predicate that is dependent on user id, then associate the predicate function with the table using the option:

static_policy => TRUE

Connect as the first user and execute a query and that user's predicate will be applied - connect as the second user and re-execute exactly the same query and the first user's predicate will still be applied. STATIC_POLICY is too static.

(Last tested 9.2.0.6)

-- 
Regards

Jonathan Lewis

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

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated April 5th 2005

Received on Mon Jun 06 2005 - 10:26:25 CDT