Disadvantages of Views?
Hi,
We have an in-house developed application developed on Oracle 11.2.0.3 and is in production. Recently, there was a requirement from top mgmt to support a change in the application. After analysis, this change translated down to a data partitioning requirement based on which company the database record belonged to. This meant:
1) Adding a new column to every table to identify the company within the oragnization. Let's call it company_id
2) Modifying all SQL queries to now include the company id filter clause
Application impact to accomodate this change has been estimated to more than a year, which was not favoured by mgmt.
A quick solution has been proposed which would make use of database views. This view will carry the company_id filter clause and will look like:
'select * from basetable where company_id = x'.
x will be passed as a session variable (like a packaged variable).
The current schema of basetable --> synonym will be modified to
(basetable + company_id column) --> (view with company_id filter) --> (synonym with same name as basetable name).
With this change, the impact to application is expected to be very negligible and would not affect the functionality too. However, we have to evaluate the performance impact due to the introduction of the view on every table. Understanding that the performance of the view is related to the underlying query, here are my questions:
1) Apart from way the underlying view query is written, are there any other factors that the view would introduce which will impact performance.
2) Do joins, subqueries on multiple views behave differently than on the base tables?
3) Does the mere introduction of view cause concurrency, deadlock issues?
4) Any known issues of using Views along with triggers on base tables?
5) Any other disadvantages of using views?
Your expert inputs will be highly appreciated.
We have started looking at the Oracle's RLS(row level security) option as well which seems to fit the bill too.
rgds,
Edited by: user12083970 on Apr 2, 2013 3:00 PM
Edited by: user12083970 on Apr 2, 2013 3:07 PM