This discussion is archived
13 Replies Latest reply: Apr 3, 2013 7:05 PM by rp0428 RSS

Disadvantages of Views?

user12083970 Newbie
Currently Being Moderated
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
  • 1. Re: Disadvantages of Views?
    BrendanP Journeyer
    Currently Being Moderated
    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.
  • 2. Re: Disadvantages of Views?
    rp0428 Guru
    Currently Being Moderated
    >
    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.
  • 3. Re: Disadvantages of Views?
    Frank Kulash Guru
    Currently Being Moderated
    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
  • 4. Re: Disadvantages of Views?
    user12083970 Newbie
    Currently Being Moderated
    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
  • 5. Re: Disadvantages of Views?
    Frank Kulash Guru
    Currently Being Moderated
    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.
  • 6. Re: Disadvantages of Views?
    rp0428 Guru
    Currently Being Moderated
    >
    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?
  • 7. Re: Disadvantages of Views?
    damorgan Oracle ACE Director
    Currently Being Moderated
    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.
  • 8. Re: Disadvantages of Views?
    user12083970 Newbie
    Currently Being Moderated
    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.
  • 9. Re: Disadvantages of Views?
    rp0428 Guru
    Currently Being Moderated
    >
    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?
  • 10. Re: Disadvantages of Views?
    user12083970 Newbie
    Currently Being Moderated
    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
  • 11. Re: Disadvantages of Views?
    rp0428 Guru
    Currently Being Moderated
    >
    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.
    >
    Ok - got it.

    That introduces one more, rather major, implication if you do NOT use partitioning as part of the solution: You will need to add 'company_id' to any indexes on the tables or a full-table scan will ALWAYS be required in order to filter the data.

    Consider just a single table with your new 'company_id' column added to it. If company_id is not in a USABLE index for EVERY QUERY then there is NO WAY to select only records for one company_id value without scanning the entire table. And even if you added 'company_id' to every index there is no guarantee that you could force Oracle to use the index.

    That is true even IF you use RLS or views. The only way around that is to partition the table by 'company_id' AND include company_id in the queries by using RLS or actually modifying the queries.

    So unless you or someone else comes up with a way around that your only choice may be to include partitioning as part of the solution.

    Unless you want to be on the 'bleeding edge' and wait for Oracle 12c. It is expected to support 'pluggable databases' and that is supposed to be targeted specifically at the multi-tenancy problem.

    Here is a link to the ComputerWorld article that discusses that
    http://www.computerworld.com/s/article/9232120/How_Oracle_s_pluggable_databases_will_work
  • 12. Re: Disadvantages of Views?
    user12083970 Newbie
    Currently Being Moderated
    Hi rp0428,
    Trying to understand the concern you raised.
    In our schema, we have a PK on every table. We do not plan to change this PK in the modified solution. Appln access is mostly through PK, and it will continue to do so. I have doubts whether it will ever use the company_id filter clause (and the standalone index on it which we will create anyways) since optmizer would pick access path through PK, no need to use company_id index.
    select * from table where PK = 'xxx' and company_id = 1;
    Parent child joins will also continue to work as today. The new filter clause may actually be used when users want to perform non-PK based access on their data, which is when the company_id index scan can be expected along with the index, if present, on the non-PK column. The company_id filter will be part of either the view or RLS.
    So, do I still need to partition the data?

    Thanks for sharing the 'pluggable database' soln. I don't think we will upgrade to 12c at this time just to make use of this new feature.

    rgds,
  • 13. Re: Disadvantages of Views?
    rp0428 Guru
    Currently Being Moderated
    >
    In our schema, we have a PK on every table. We do not plan to change this PK in the modified solution. Appln access is mostly through PK, and it will continue to do so. I have doubts whether it will ever use the company_id filter clause (and the standalone index on it which we will create anyways) since optmizer would pick access path through PK, no need to use company_id index.
    select * from table where PK = 'xxx' and company_id = 1;
    >
    Please explain that. For that query how can Oracle even consider using an index if 'company_id' is NOT part of the index?

    And what value will a standalone index on 'company_id' provide? If you have two companies and they each have 50% of the records Oracle isn't going to use that index even if all you want is data from the table for 'company_id = 1'. It will do a full-table scan of the table since it can use multi-block reads and the cost will be much lower.

    And you don't really think Oracle would just automatically try to use your primary key combined with a standalone index on 'company_id' on all those queries do you?

    Any good system will already have created and stored the execution plans for their major queries. You need to look at yours (you do have them don't you?) and see if there are any full table scans in there.

    Even ONE full table scan that has to read ALL data and automatically ignore HALF of it begins the non-scaleability of that solution.

    And that information you just provided conflicts with one of your statements in your original post
    >
    2) Do joins, subqueries on multiple views behave differently than on the base tables?
    >
    How would there be any subqueries on multiple tables/views if ALL access uses only the primary keys of the tables?

    If that statement were really true there couldn't be an issue using views.
    >
    Parent child joins will also continue to work as today. The new filter clause may actually be used when users want to perform non-PK based access on their data, which is when the company_id index scan can be expected along with the index, if present, on the non-PK column. The company_id filter will be part of either the view or RLS.
    >
    Then use of that filter will lead right back to full table scans that have to read, and ignore, data for ALL companies except the one of interest.

    In 20+ years I have NEVER seen any system where the bulk of the queries used only the PK and indexes to access data. Any system like that could not provide much in the way of reporting since that requires aggragation and most aggragation is going to require table scans to be performant.

    I expressed my view above: I think any viable solution you have will include partitioning.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points