VPD on Terabyte db

Anyone got any experience of performance issues when using virtual private database security on large (5+ Tb) databases?

I'm thinking of using a primary key indexed table containing a CLOB column for storing XML, with access to individual rows controlled via VPD policy. The policy simply refers to a cross reference table which contains the PK on the object table and user id, so any logged in user can only see the rows in the object table with corresponding rows in the fross reference access rights table.

For example the policy would add the exists predicate

Select * from document d1
where exists (select 1 from doc_access d2 where d1.pk = d2.fk and d2.user_id = user) ;

Obviously d1.pk and d2.fk would be indexed, as would d2.user_id.

I've tried similar efforts on smaller databases and experienced approx trebling of query time, but with the indexing this means 0.1 sec changes to 0.3 sec, a tolerable performance hit.

The VPD is there to simplify coding in a web front end, the application developers do not have to consider data access rules from their end. In addition there are various other considerations in play, primarily data security. The above is a big simplification of the access rights rules... Received on Tue Oct 19 2004 - 18:55:13 CDT