Jonathan Lewis wrote:
>
> 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
> >
Agreed but one elemtent of the VPD versus Partitioning issue is the
license fee (nil versus lots)
--
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com
"Semper in excremento, sole profundum qui variat."
------------------------------------------------------------
Received on Tue Jul 18 2006 - 08:14:04 CDT
