Re: VPD vs Multiple Schemas

If you can afford the cash, then the optimum strategy is to forget the full implementation of VPD and use list-partitioned tables, with one partition per hospital.

create table XYZ as ....
partition by list (hospital_code) (
 partition hospital1 values ('code1'),

    ... etc...
)

Then have a logon trigger that sets up a sys_context variable that the user cannot change; and a pre-insert trigger on every table that supplies this context value as the hospital code.

Create a single view of each table:

    create view v_XYZ as
    select {everything but hospital_code} from XYZ     where hospital_code = sys_context('hospital_context','hospital_code')     with check option
    ;

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.

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


"Shailesh" <shailesh.saraff_at_gmail.com> wrote in message 
news:1160046794.284345.196060_at_e3g2000cwe.googlegroups.com...


> We are into healthcare software and now customers are requesting new

> feature from our application, they want to run application in their

> group of hospitals on single Oracle database. Available options are

> listed below. what do you suggest? if I am missing any point please let

> me know

>

> Single Schema (using Virtual Private Database)

> ----------------------------------------------

> Maintaining all database objects in the main schema and create user for

> each hospital to access data from main schema. Hospital id will be

> defined for each hospital and will part of PK of all tables. Security

> policy based on hospital id can be defined for each table, which allows

> other users to do DML (data-manipulation) as well as SELECT operations

> only on those records in a table, for which a user has access.

>

> Application Level

> -------------------

> Advantages: No changes are required in our application.

>

>

> Database Level

> -----------------

> Advantages: Easy to manage and maintain

> Easy to upgrade and patch (Oracle)

> SGA can be defined adequately

> Advantage of sharing Sqls

>

> Disadvantages: VPD concept may sound complicated (to Management)

> Downtime of All hospitals will be the same incase our application

> version needs to be upgraded

> Performance overhead (10%?) for SELECT while doing join operation or

> sub select

> Backup time will be more

>

>

> Multiple Schema's:

> ------------------

> Create Schema for each hospital and maintain all schemas in single

> database. Each User will access own schema objects.

>

> Application Level

> -----------------

> Disadvantages: All References to main schema needs to be removed from

> our application. (e.g. Schemaname.TableName)

>

>

> Database Level

> ---------------

> Advantages: Looks Simple and easy to understand (to Management)

> Downtime can be planed for each Hospital for upgrading our application

> version

> All hospitals can run on different versions of our application

> Schema level backup can be scheduled

>

> Disadvantages: Difficult to manage and maintain xx schema's

> Difficult to upgrade and patch (Oracle)

> SGA (Oracle Memory) to increase xx times (shared sql will not work)

> Consumes more disk space (data, archive logs)

> Accessing the Oracle data dictionary will have performance overhead

> latching on the shared pool will be more

>

>

> Please let me know.

>

> Thanks & Regards.

>