VPDs or schemas

Hello,

the DBAs at our site are currently reviewing a design document for a new Web-Application. This application will be used by about 2.500 branch offices and around 10.000 users. Oracle will be 10.n. and estimated size of the DB is about 300G.

Development suggests to use a VPD for every branch office. Further on they want to partition every table with one partition per office. Since the application is web-based, I doubt whether it will be possible to use context sensitive security policies.

We (the DBAs) are not really happy with this idea, because we feel that this design wil have a negative impact on performance and scalability. We are especially afraid that the database will parse itself to death.

So we are thinking of suggesting a different design (or more of a sketch until now) that would use a schema for every branch and take care of data seperation by creating views (MVs where it makes sense) and granting appropiate permissions. Keeping the idea of partitioning the base tables on a per office basis sounds good to me. Of course this will cause more work for the developers :->.

Any hints, tips or comments on the merits of both 'designs' are very welcome.

Regards,
uwe    

--
http://www.freelists.org/webpage/oracle-l