Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Oracle view efficiency

    Quick overview: my employer is in the process of migrating to Oracle, and the table names / field names will be altered in the process.

    Solution I'm investigating: create a view for every table as it's migrated, where the view name is the old table name, and the fields listed inside are the old field names. Benefit: SQL stmts in our Reporting tool won't need rewriting.


    I'm not fond of this approach, due to the amount of effort this requires. I can talk them out of it if there's some documentation indicating the ineffieciency of joining 2 or more views in more complex queries. Most of these reports use between 2-14 tables joined in a single SQL stmt.

    Anywhere I can look to prove that the following is terribly inefficient, or, <sigh> has Oracle solved this problem:

    select V1.F1, V2.F2
    from V_FIRST_VIEW V1, V_SECOND_VIEW V2
    where V1.PK = V2.FK

    -Chuck

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Oracle view efficiency

    "The amount of effort this requires" Er, what - compared to rewriting all the SQL in your reports?

    Using views like this is a great idea. And since each view is based on a single table, the queries will be exactly as efficient as they would be if you re-wrote them to use the base tables.

    Sorry, no support for your viewpoint from me!

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    So, if one of the views pulls all the records from a 10 million row table (V1), and another view pulls all the records from a 50 million row table (V2), and you combine the two with some additional criteria:

    select V1.PK, V2.FK
    from V1, V2
    WHERE V1.PK = V2.FK
    AND V2.DATE_CREATE > '01 Apr 2002'

    How does Oracle process the request? Does it buffer the results of the views prior to the join? Does the optimizer rewrite the query to draw from the individual tables?

    The only info I've found on this so far explains, "If your query uses joins on views, it could lead to a substantial time to execute the query." The explanation mentions that whenever a View is instantiated, it must be executed before anything can be done with the results. True?

    -Chuck

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    False - where did you read that nonsense? If the views were defined like this:

    create view v1 as select * from t1;
    create view v2 as select * from t2;

    ... then your query above would be interpreted exactly the same way as:

    select t1.PK, t2.FK
    from t1, t2
    WHERE t1.PK = t2.FK
    AND t2.DATE_CREATE > '01 Apr 2002'

    You can easily prove this using EXPLAIN PLAN. In fact, I will now do so with an example based on Oracle's ubiquitous EMP and DEPT tables:
    PHP Code:
    SQLcreate view empv as select from emp;

    View created.

    SQLcreate view deptv as select from dept;

    View created.

    SQLset autotrace on

    SQL
    select v1.enamev2.dname
      2  from empv v1
    deptv v2
      3  where v1
    .deptno v2.deptno
      4  
    and v2.loc like '%';

    ENAME      DNAME
    ---------- --------------
    SMITH      RESEARCH
    ALLEN      SALES
    WARD       SALES
    JONES      RESEARCH
    MARTIN     SALES
    BLAKE      SALES
    CLARK      ACCOUNTING
    SCOTT      RESEARCH
    KING       ACCOUNTING
    TURNER     SALES
    ADAMS      RESEARCH
    JAMES      SALES
    FORD       RESEARCH
    MILLER     ACCOUNTING

    14 rows selected
    .


    Execution Plan
    ----------------------------------------------------------
       
    0      SELECT STATEMENT Optimizer=CHOOSE
       1    0   NESTED LOOPS
       2    1     TABLE ACCESS 
    (FULLOF 'EMP'
       
    3    1     TABLE ACCESS (BY INDEX ROWIDOF 'DEPT'
       
    4    3       INDEX (UNIQUE SCANOF 'DEPT_PK' (UNIQUE)


    SQLselect t1.enamet2.dname
      2  from emp t1
    dept t2
      3  where t1
    .deptno t2.deptno
      4
    * and t2.loc like '%';

    ENAME      DNAME
    ---------- --------------
    SMITH      RESEARCH
    ALLEN      SALES
    WARD       SALES
    JONES      RESEARCH
    MARTIN     SALES
    BLAKE      SALES
    CLARK      ACCOUNTING
    SCOTT      RESEARCH
    KING       ACCOUNTING
    TURNER     SALES
    ADAMS      RESEARCH
    JAMES      SALES
    FORD       RESEARCH
    MILLER     ACCOUNTING

    14 rows selected
    .


    Execution Plan
    ----------------------------------------------------------
       
    0      SELECT STATEMENT Optimizer=CHOOSE
       1    0   NESTED LOOPS
       2    1     TABLE ACCESS 
    (FULLOF 'EMP'
       
    3    1     TABLE ACCESS (BY INDEX ROWIDOF 'DEPT'
       
    4    3       INDEX (UNIQUE SCANOF 'DEPT_PK' (UNIQUE
    Same plan either way.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Cool. Can I reliably expect a query plan using the base tables for the situation I've described? Or are there variations based upon whether GROUP BY clauses (or similar) are implemented?

    I appreciate your responses. I would try some of these test situations, but we've not got a stable development installation available yet.

    Thanks Tony,
    -Chuck

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I got the impression from your first post that these views were being created merely to save renaming tables and columns? In which case there should be no GROUP BY clauses and no possible performance issues.

    Once you get into more complex views with GROUP BY clauses and table joins, then certainly the possibility exists to end up with sub-optimal performance. This is not because views are "bad", but because you may end up making Oracle do more work than is required for a particular query. I feel an example coming on:

    PHP Code:
      1  create view deptv2 as
      
    2  select dept.deptnodept.dnamecount(*) num_empssum(emp.saltot_sal
      3  from dept
    emp
      4  where dept
    .deptno emp.deptno (+)
      
    5group by dept.deptnodept.dname;

    View created.

    SQLset autotrace on

    SQL
    select from deptv2;

        
    DEPTNO DNAME            NUM_EMPS    TOT_SAL
    ---------- -------------- ---------- ----------
            
    10 ACCOUNTING              3       8750
            20 RESEARCH                5      10875
            30 SALES                   6       9400
            40 OPERATIONS              1

    Execution Plan
    ----------------------------------------------------------
       
    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=56 Card=9 Bytes=432)
       
    1    0   VIEW OF 'DEPTV2' (Cost=56 Card=9 Bytes=432)
       
    2    1     SORT (GROUP BY) (Cost=56 Card=9 Bytes=180)
       
    3    2       HASH JOIN (OUTER) (Cost=3 Card=14 Bytes=280)
       
    4    3         TABLE ACCESS (FULLOF 'DEPT' (Cost=1 Card=4 Bytes=5
              2
    )

       
    5    3         TABLE ACCESS (FULLOF 'EMP' (Cost=1 Card=14 Bytes=9
              8
    )





    Statistics
    ----------------------------------------------------------
              
    7  recursive calls
              8  db block gets
              4  consistent gets
              2  physical reads
              0  redo size
            583  bytes sent via SQL
    *Net to client
            454  bytes received via SQL
    *Net from client
              4  SQL
    *Net roundtrips to/from client
              5  sorts 
    (memory)
              
    0  sorts (disk)
              
    4  rows processed


    SQL
    select deptnodname from deptv2;


        
    DEPTNO DNAME
    ---------- --------------
            
    10 ACCOUNTING
            20 RESEARCH
            30 SALES
            40 OPERATIONS


    Execution Plan
    ----------------------------------------------------------
       
    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=56 Card=9 Bytes=198)
       
    1    0   VIEW OF 'DEPTV2' (Cost=56 Card=9 Bytes=198)
       
    2    1     SORT (GROUP BY) (Cost=56 Card=9 Bytes=144)
       
    3    2       HASH JOIN (OUTER) (Cost=3 Card=14 Bytes=224)
       
    4    3         TABLE ACCESS (FULLOF 'DEPT' (Cost=1 Card=4 Bytes=5
              2
    )

       
    5    3         TABLE ACCESS (FULLOF 'EMP' (Cost=1 Card=14 Bytes=4
              2
    )





    Statistics
    ----------------------------------------------------------
              
    7  recursive calls
              8  db block gets
              4  consistent gets
              0  physical reads
              0  redo size
            491  bytes sent via SQL
    *Net to client
            466  bytes received via SQL
    *Net from client
              4  SQL
    *Net roundtrips to/from client
              5  sorts 
    (memory)
              
    0  sorts (disk)
              
    4  rows processed 
    In the second query, I only needed to look at the DEPT table, but because I queried from the view I forced Oracle to perform an unnecessary join to EMP and an unnecessary GROUP BY.
    Last edited by andrewst; 02-10-04 at 07:30.

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    Sorry for the confusion, no you are correct. Each view will essentially be a 'select * from table', but GROUP BY clause and so on will be used when the views are combined for queries in our reporting tool.

    I was just trying to be overly inquisitive, as I'm having trouble finding documentation for this behavior you've described. If we take the view approach, and find later that it's inefficient, then we have to rewrite the queries.

    Do you know of a reference text which outlines this behavior?

    -cf

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Look at the Concepts guide, chapter 10:

    http://download-west.oracle.com/docs...chem.htm#24841

  9. #9
    Join Date
    Jul 2012
    Location
    Virginia, USA
    Posts
    2
    This situation might be better addressed with synonyms.

  10. #10
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Cool

    Quote Originally Posted by DropD View Post
    This situation might be better addressed with synonyms.
    Nope, chuck_forbes said that they require columns names to be remapped which synonyms will not be able to solve.

    andrewst, I wasn't able to navigate that link you posted. Here is another to Overview of Views in the Oracle Concepts guide for Oracle 10.2.
    In actual fact this document does a good job of explaining how views work in Oracle and I'm sure it will answer a lot of chuck_forbes' questions.
    Last edited by dayneo; 07-26-12 at 11:49.

  11. #11
    Join Date
    Dec 2003
    Posts
    1,074
    It's pretty entertaining to read your own posts from early on (see the date, back in 2004). If anyone's curious, we ended up rewriting the queries in our reports, and aliasing the column names to match the old column names. --=cf

  12. #12
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Hahaha... I didn't even notice the original date. Looks like DropD bumped it.

  13. #13
    Join Date
    Jul 2012
    Location
    Virginia, USA
    Posts
    2

    More on view/synonym interfaces

    Yeah it's old, but you guys gave good info for folk searching. I should have elaborated more when adding my 2 cents.

    Technically doing it without synonyms works. In my experience it gets confusing. Usually views follow a naming convention to indicate that they're views, and that is useful. I would suggest to folks doing what chuck did, is that they write the view to simulate the old table, but don't give it the old table name. Give it a name that follows your conventions (usually indicating a view). Then assuming read-only users are not connecting as the schema owner (which they shouldn't be), create a synonym for the view with the old table name. This will be less confusing when working within the schema. It also allows you to change table structure without necessarily changing the table name.

    I'm in the same boat in that other apps are reading my tables directly. I can't change anything without risk of breaking the unknown. What I'm trying to go to, is not allowing any access to my tables. All access would be through views which I could adjust if I make internal changes. It's a pain because it's an old schema in desperate need of normalization.

    Thanks for the post and I hope my 3 cents (now) helps.
    Last edited by DropD; 07-26-12 at 14:08.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •