Forum Stats

  • 3,874,091 Users
  • 2,266,678 Discussions
  • 7,911,723 Comments

Discussions

Disadvantages of Views?

user12083970
user12083970 Member Posts: 19
edited Apr 3, 2013 10:05PM in SQL & PL/SQL
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
Tagged:
«1

Answers

  • BrendanP
    BrendanP Member Posts: 383 Bronze Badge
    The view solution looks similar to how oracle itself implemented multi-org in its Applications up to R11. I think they may have moved to RLS for R12. So you are probably thinking on the right lines. Views can be problematic if they are used as a way of anticipating possible future table join requirements, but are good for implementing small, well-defined (and known) requirements like this.
  • Unknown
    edited Apr 2, 2013 6:52PM
    >
    1) Apart from way the underlying view query is written, are there any other factors that the view would introduce which will impact performance.
    >
    Huh? A view IS an underlying query - nothing more.
    >
    2) Do joins, subqueries on multiple views behave differently than on the base tables?
    >
    They can or they cannot. Often Oracle will just incorporate the view query but it could also wind up separating out that new 'id' filter and cause a performance issue. You will need to examine the execution plans during testing (actual plans not 'explain' plans).
    >
    3) Does the mere introduction of view cause concurrency, deadlock issues?
    >
    No
    >
    4) Any known issues of using Views along with triggers on base tables?
    >
    Please explain what you mean by this.
    >
    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.
    >
    Provide your analysis and reasons for the 'more than a year' and what other solutions were considered.

    What type of application is it, OLTP or OLAP? What are the data volumes involved for this table: tot rows, inserts per day, updates per day, deletes per day. Are any outage windows available?

    Have you considered the possibility of just partitioning the table? What about materialized views? What is it that determines what 'company' a given record belongs to?

    If you can provide more information about the details involved and the solutions that have been considered and rejected we may be able to offer other alternatives.
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,745 Red Diamond
    edited Apr 2, 2013 7:40PM
    Hi,

    If you're using Oracle Enterprise Edition, then you can use row-levl security with dbms_rls, also know as "Virtual Private Database" or VPD.
    How this works is you create security policies on tables of whih certain people are only allowed to see certain rows. This includes a PL/SQL function that returns a string such as 'company_id = 123'. (The part lke '123' can come from a package variable or a SYS_CONTEXT variable.) When this policy is applied to table_x, then whenever anybody uses a SQL statement, such as
    SELECT  *
    FROM    table_x;
    then what actually gets executed is
    SELECT  *
    FROM    table_x
    WHERE   company_id = 123;
    For more, see the 2 Day + Data Warehousing Guide
  • user12083970
    user12083970 Member Posts: 19
    edited Apr 3, 2013 4:56PM
    Hi Brendan, Frank,
    Thanks for the comments.
    We have done a small POC and understand that the RLS can work inplace of the view solution. However, we are not sure if the RLS is a proven technology?
    Does it require additional Oracle license when using Enterprise Edition?

    Hi rp0428,
    Thanks for the comments.
    The more than year estimate comes from application teams. They estimate it would take more than 1 man-year to include the filter clause inside their sql statements.
    This is a OLTP enterprise appln with around 1200 tables (potential to grow to 1600 since new applns are being planned) running on 3-node RAC (may become 4-node soon) with 25 commits/sec on a 1TB database.
    The application needs to support multi-company (currently it is just one company). The new additional companies will use the same application (schema), insert their specific data into same common schema. One company has nothing to do with other company's data.
    One option considered was to have seperate Oracle instances for every company. This means multi RAC instances for every company. This was not accepted since it would introduce complications in all other layers of the physical architecture. Hence, ruled out.
    How would partitioning help in this case? Will one company be not able to view other company's data after partitioning? Does application need to change their code?Also, partitioning needs additional license. The requirement needs enforcement of data partitioning at the logical level.

    Edited by: user12083970 on Apr 3, 2013 1:55 PM
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,745 Red Diamond
    edited Apr 3, 2013 5:08PM
    Hi,
    user12083970 wrote:
    Hi Brendan, Frank,
    Thanks for the comments.
    We have done a small POC and understand that the RLS can work inplace of the view solution. However, we are not sure if the RLS is a proven technology?
    Yes, it's been around since Oracle 8.1 (at least).
    I've used it for over 10 years, and never encountered any bugs.
    Does it require additional Oracle license when using Enterprise Edition?
    As far as I know, it requires Enterprise Edition, but nothing more. As always, check with your Oracle rep to be sure; don't rely on what you hear in a forum like this.
  • >
    The more than year estimate comes from application teams. They estimate it would take more than 1 man-year to include the filter clause inside their sql statements.
    >
    Not going to argue with you but '1 man-year' means one person could do it in a year. Presumably you have more than one person available? ;)
    >
    This is a OLTP enterprise appln with around 1200 tables (potential to grow to 1600 since new applns are being planned) running on 3-node RAC (may become 4-node soon) with 25 commits/sec on a 1TB database.
    The application needs to support multi-company (currently it is just one company). The new additional companies will use the same application (schema), insert their specific data into same common schema. One company has nothing to do with other company's data.
    One option considered was to have seperate Oracle instances for every company. This means multi RAC instances for every company. This was not accepted since it would introduce complications in all other layers of the physical architecture. Hence, ruled out.
    >
    I agree that separate instances for each company is not a viable solution.

    Sounds like you are trying to implement a 'home-grown' multi-tenancy solution.
    >
    How would partitioning help in this case? Will one company be not able to view other company's data after partitioning? Does application need to change their code?Also, partitioning needs additional license. The requirement needs enforcement of data partitioning at the logical level.
    >
    The combination of partitioning and RLS can be a very effective solution for that.

    The partitioning creates the 'physical' separation of data by 'tenant' while the RLS enforces both the 'logical' and the 'physical' separation of data.

    One table: each tenant only has access to ONE partition containing their data.

    The key to any solution is your answer to this question that I asked earlier
    >
    What is it that determines what 'company' a given record belongs to?
  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    If you are in 11.2.0.3 you can use Edition Based Redefinition to make this quite simple.

    You will, of course, need to add a column identifying the customer but this can be hidden from your existing application by means of an editioning view.

    Then you can use Cross-Edition Triggers and DBMS_PARALLEL_EXECUTE to populate that new column in real-time without severely impacting the system while changes take place.

    If you script the column add the challenge will not be trivial but it shouldn't be a show-stopper.
  • user12083970
    user12083970 Member Posts: 19
    edited Apr 3, 2013 7:18PM
    Hi Frank, rp0428,
    I will rephrase the estimate from appln...it's a huge effort which would take a year to implement (without knowing how many developers would be working on it). The PMO is convinced that it's a huge effort too.

    The current debate is over which logical solution should be picked (Views versus RLS). We heard about some performance issues with views(predicate pushing/non-mergeable views, hard parsing, etc), but are not sure in what way our design could be impacted. Hence, this post to understand if there is something we are missing or have not considered.

    We plan to conduct performance tests using Views and RLS.
    In the future, we could be forced to use Oracle's data partioning if we hit performance issues.

    What is it that determines what 'company' a given record belongs to?
    The company_id on every table will determine which company that record belongs to. Hope this is what you were asking.

    Thanks again for all your comments.
  • >
    What is it that determines what 'company' a given record belongs to?
    The company_id on every table will determine which company that record belongs to. Hope this is what you were asking.
    >
    No - it isn't.

    You have data now. You don't have company_id in that data now.

    What is it that determines, for a record that you have now, what company it belongs to?
    SELECT * FROM myTable where rownum = 1;
    How do you determine what 'company' that one row belongs to? What information in that table/row can be used to determine what company the row belongs to?
  • user12083970
    user12083970 Member Posts: 19
    OK, now I understand your question.
    The database currenly belong to only one company. The application will be open to other companies once the application is "multi-company" ready. Thus, we may call current data as company_id =1. While loading other company data, the company_id will be 2 and so on.

    rgds
This discussion has been closed.