Re: VPD vs Multi Schema vs Partitioning

VPD is probably overkill.

If you create the tables as list partitioned, one partition per branch, and then create a single view for each table:

    create view v_x
    as
    select * from t_x
    where branch_code = sys_context('my_erp','branch_code')     ;

Create a login trigger that sets the context for each user to the correct value, and a before insert trigger on the table that uses the context to set the branch code on each table

Then your general application code can reference view v_x, and every cross-branch report can access t_x.

With list partitioning, you don't need indexes on the branch code column to get separation of branch activity. The main trap is that the optimizer will use table-level stats for all queries against the table, not branch/partition-level; so if you have branches with extreme variation in volumes of data you may get some sub-optimal plans.

If you go VPD, make sure you check the overheads carefully - and try to work out the most cost-effective policy_type setting to minimise the number of times the security functions are called.

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


<xtanto_at_hotmail.com> wrote in message 
news:1152445866.735493.271720_at_p79g2000cwp.googlegroups.com...


> Hi Oracle Gurus,

>

> We will develope a web based ERP application for a company with multi

> branch (about 20), with the following requirement :

> - a normal user can only see and update data for his branch

> - some users from head quarters can see ALL data from ALL branch

> - reporting will be per branch, but there should be Consolidation

> reports

>

> Total user will be 200, but maximum concurrent is 60 users.

>

> We will use JSF(ADF Faces) + BC4J, one database server and one apps

> server.

>

> The question is :

> To meet the requirement above about which user can see which data, what

> Oracle feature should I use :

> is it VPD ?

> or should I just use different schema for each branch ?

> or use Partitioning ?

>

> Thank you for your recommendation,

> xtanto

>