Home » RDBMS Server » Security » Performance Issue with Virtual Private Database
Performance Issue with Virtual Private Database [message #430708] Wed, 11 November 2009 21:33 Go to next message
rawat_me
Messages: 45
Registered: September 2005
Member
I am thinking to apply some archiving process in our database system and have two options in front of me:

1) Move Unwanted data to another schema and delete Unwanted Data from main schema and thus performace would be increased.
2) Add a new flag which tells me if the Data is wanted or Unwanted.
Then using Virtual Private Database Policy restrict Users_sesion to Retrieve Either wanted OR unwanted Data.
By biggest worry for this VPD approach is that Oracle Internally add a new Query Predicate : Select * from ( select * from user ) where userid="authorised" will slow down the overall performace of the System.
VPD would save me to MOVE data from one schema to another and worry about all constraints while moving data across Schema.

Does any body any such experience with performance issue with VPD and what is the best way to achieve my requirment

Thanks
Re: Performance Issue with Virtual Private Database [message #430710 is a reply to message #430708] Wed, 11 November 2009 22:25 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Move Unwanted data to another schema and delete Unwanted Data from main schema and thus performace would be increased.
I do not accept the premise that reducing the amount of data would necessarily increase performance measurably.

If the vast majority of table accesses were done via index, then
minimal change to performance might occur.

Archiving application data is much easier said than done when you have to handle PK/FK constraints. If regularly used "base" tables contain the parent record (& can't be archived), what is done with child records to be archived with FK back to parent record.
Re: Performance Issue with Virtual Private Database [message #430722 is a reply to message #430710] Thu, 12 November 2009 00:52 Go to previous message
rawat_me
Messages: 45
Registered: September 2005
Member
The Data would be consider as Unwanted ( eligible to Archive) if it past one particular Date and then all Subsequent Child records would also be moved.
We will make sure that all subsequent Child records are also moved Along with "Main" records.
Previous Topic: Privileges granted to role
Next Topic: Revoke privilege
Goto Forum:
  


Current Time: Thu Mar 28 06:25:31 CDT 2024