Is it OK to nest database views?

Question

In the world of oracle, I have the impression that views based on other views are considered to be bad practice. I myself have complained about this when the trying to solve performance issues and the nesting seemed excessive and hid away unneeded complexity in the underlying views. Now I find myself in the situation of thinking that it may not be so clear-cut:

I have users who very specifically need the accounting numbers from one view to match those of another that does further processing on them. If they ever change anything in one, they want the other to reflect that immediately, without anyone having to think of this requirement in a few years time and reports showing non-matching numbers while they figure things out.

Is it ok to nest views in this case?

Does it change things if the inner view contains a further, important view that contains relevant prices (i.e. you're "always" supposed to use this view when determining prices)?

Answer 1

Best practice doesn't always cover everything. I think you have a clear-cut justification for nesting them, just this once.

Answer 2

It's also a good thing to note in the process of building complicated database queries sometimes nested views are the best thing - for one example, if you need any math operator built on 2 columns, for instance SUM(Col1, Col2) it can be better to nest views so that the sum is a column in itself instead of having to do something like

"SELECT Total / SUM(Col1, Col2), SUM(Col1, Col2) * 2, Col1 / SUM(Col1, Col2) ..."

However I'm not sure I understand 100% - Why are there 2 views needed? Can't both users look at the 1 view and further processing be derived in a view another layer above that one?

Answer 3

I'm nesting views 3 levels deep in Oracle 10g R2. Performance seem corelate to the select statements in the views, rather than the view depth. In particular the "IN" clause seems to be causing a lot of trouble.

Answer 4

I think you are on the slippery slope here where code reuse and performance are going to clash. You can try it and see how badly it wil affect performance. We have a couple of databases here where they have stacked views on top of views and frankly the performance is miserable and now everyone involved wished thhat they had not designed that way.

Answer 5

The best reasons to use a view would be to:

1. prevent duplicating the same query.
2. prevent direct access to tables by other query writers
3. create a layer of security (similar to #2).

I do realize it can also help to simplify a more complex query, but you get use to it. You may find that a user defined function (table) may be a better solution. Either way, performance will take a hit.

Answer 6

There's always a tradeoff between coding time, ease or quality of code, and performance.

Nesting views is really easy to code and, given the right circumstances, makes it easy to read. It can also reduce time. It's arguably reducing the quality and often reduces performance... but by how much?

It's all subjective. If it makes sense, roll with it. Don't prematurely optimize your code.

Answer 7

The main problem with nesting views is that the query optimiser is more likely to get confused and produce a sub-optimal plan. Apart from this, there is no specific overhead to using views on views.

This means that the best option is to try the nested views. See if you get sensible query plans out of the reports. If it does cause problems then you may have to re-think your strategy.

Answer 8

Nested views can make sense. Just be careful that you don't make them too general.

---

I did see a system that had a view with 14 tables mentioned explicitly, some of them connected with outer self-joins, and some of the 'tables' were themselves views. I didn't like it much, but the DBMS coped with it astonishingly well (given that it was back in the late 80s). A lot of the schema was machine generated by a data modelling tool.

CREATE VIEW IBB_V_Project AS
    SELECT  A.Project_Iref,
            A.Section_Iref,
            B.Section_Eref,
            N.Company_Iref,
            N.Company_Name,
            A.Product_Desc,
            A.Project_Type_Iref,
            D.Project_Type,
            A.Person_Iref,
            F.Full_Name,
            A.Respon_Iref,
            G.Post_Location,
            A.Project_Stat_Iref,
            E.Project_Status,
            A.Source_Iref,
            I.Source,
            A.Sic_Iref,
            L.Sic_Eref,
            A.Op_Activity_Iref,
            M.Op_Activity_Desc,
            A.Involve_Iref,
            K.IBB_Involvement,
            A.Nature_Iref,
            C.Nature_Of_Next_Act,
            A.Internat_Mobile,
            A.Whether_Cop_Case,
            A.Closed_Ind,
            A.Next_Action_Date,
            A.Creation_Date,
            A.Last_Edit_Date,
            A.Last_Editor_Iref,
            H.Logname

    FROM    IBB_Project A,
            IBB_Section B,
            IBB_R_Proj_Type D,
            IBB_R_Project_Stat E,
            IBB_Personnel H,
            OUTER IBB_R_Next_Act C,
            OUTER IBB_Personnel F,
            OUTER (IBB_Post_Respon X, OUTER IBB_V_Post_Resp2 G),
            OUTER IBB_R_Source I,
            OUTER IBB_R_Involvement K,
            OUTER IBB_Sic L,
            OUTER IBB_Op_Act M,
            OUTER IBB_V_Proj_Co2 N

    WHERE   A.Section_Iref      = B.Section_Iref
      AND   A.Project_Type_Iref = D.Project_Type_Iref
      AND   A.Project_Stat_Iref = E.Project_Stat_Iref
      AND   A.Last_Editor_Iref  = H.Person_Iref
      AND   A.Nature_Iref       = C.Nature_Iref
      AND   A.Person_Iref       = F.Person_Iref
      AND   A.Respon_Iref       = X.Respon_Iref
      AND   X.Respon_Iref       = G.Person_Iref
      AND   A.Source_Iref       = I.Source_Iref
      AND   A.Sic_Iref          = L.Sic_Iref
      AND   A.Op_Activity_Iref  = M.Op_Activity_Iref
      AND   A.Project_Iref      = N.Project_Iref
      AND   A.Involve_Iref      = K.Involve_Iref;

The outer join notation is specific to Informix (which now supports the SQL standard notation too).

Note that IBB_V_Post_Resp2 and IBB_V_Proj_Co2 are both themselves views. In fact, IBB_V_Proj_Co2 was a 3-table view, exact details unknown but of the form:

CREATE VIEW IBB_V_Proj_Co2 AS
    SELECT  A.Project_Iref,
            A.Some_Other_Col col01,
            B.Xxxx_Iref,
            B.Some_Other_Col col02,
            C.Yyyy_Iref,
            C.Some_Other_Col col03
    FROM    IBB_Project A,
            OUTER (IBB_R_Xxxx B, IBB_R_Yyyy C)
    WHERE   A.Xxxx_Iref = B.Xxxx_IrEf
      AND   B.Yyyy_Iref = C.Yyyy_Iref;

This means that the IBB_V_Project view has an outer self-join on IBB_Project. The IBB_V_Post_Resp2 view probably involved 3 tables too (my notes on that were a bit unclear, way back in 1993, when I recorded this information).

CREATE VIEW IBB_V_Post_Resp2 AS
    SELECT  A.Person_Iref,
            A.Some_Other_Col col01,
            B.Xxxx_Iref,
            B.Some_Other_Col col02,
            C.Yyyy_Iref,
            C.Some_Other_Col col03
    FROM    IBB_Personnel A,
            IBB_R_Xxxx B,
            IBB_R_Yyyy C
    WHERE   A.Xxxx_Iref = B.Xxxx_Iref
      AND   B.Yyyy_Iref = C.Yyyy_Iref;

The Zzzz_Iref columns were either SERIAL or INTEGER foreign keys referencing a SERIAL key.

The primary view definition refers to 14 tables, with 4 inner joins and 9 outer joins. When the cross-referenced views are taken into account, there are 18 tables in total, with 7 inner joins and 10 outer joins.

Answer 9

I'll just answer from a best practices perspective:

There are only a few times I would pause about using Views on Views.

1. Nesting seems to be getting out of hand ... like over 3 levels deep. The reason I am nesting is to make the code easier to maintain. As soon as I start getting to this point it starts feeling a little too complicated to understand.

2. Nesting a view which uses analytical functions. I've personally, for one reason or another, not had very good experience with nesting views with analytical function.

3. Nesting views that do full scans by nature. While I think the the query optimizer is probably smart enough to handle this it just looks wrong to me when I'm reviewing the logic of the view.

4. Performance is a great concern. This is not to say the optimizer might get it wrong but this is to say before I release it, I'm going to test it to see if I can't figure a faster way to do it.

Other than that I've used views on views quite successfully.

Answer 10

don't really want to get caught up in the whole nested view thing

have a think about this for an idea...your trying to join onto a table to find mismatches...i would use the Oracle function 'minus'....MINUS selects elements from the first table and then removes rows that are also returned by the second SELECT statement.

SELECT num FROM (SELECT 1 AS num FROM DUAL UNION ALL SELECT 2 AS num FROM DUAL UNION ALL SELECT 3 AS num FROM DUAL) base_view

MINUS

SELECT 2 AS num FROM DUAL