Re: VPD vs Multiple Schemas

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

VPD is the way to go.
I don't really follow why backup time would be increased. It can be a little tricky, but your biggest task is to sell it to management.
The are a lot of HA solutions.

Tim Received on Thu Oct 05 2006 - 06:34:42 CDT