6.8 Using Views

---

Views are application-and security-friendly. Views can also be used to reduce complexity, particularly in development. In general, views are not conducive to good performance. A view is a logical overlay on top of one or more tables. A view is created using an SQL statement. A view does not contain data itself. The biggest problem with a view is that whenever it is queried its defining SQL statement is re-executed. It is common in applications for a developer to query a view and add additional filtering. The potential results are views containing large queries where programmers will then execute small row number retrievals from the view. Thus two queries are executed, commonly with the view query selecting all the rows in the underlying table or join.

Let's try to prove that views are inherently slower than direct table queries. Firstly, I create a view on my largest Accounts schema table.

CREATE VIEW glv AS SELECT * FROM generalledger;

Now let's do some query plans. I have four queries and query plans listed. The first two retrieve a large number of rows from the view and then the table. It is apparent that the query plans are identical in cost.

Selecting from the view:

EXPLAIN PLAN SET statement_id='TEST' FOR       SELECT * FROM glv WHERE coa# = '40003';     Query                                 Cost     Rows       Bytes ---------------------------------   ------   ------   --------- SELECT STATEMENT on                    165   150548     3914248  TABLE ACCESS BY INDEX ROWID on    GENERALLEDGE                        165   150548     3914248   INDEX RANGE SCAN on XFK_GL_COA#        5   150548

Selecting from the table:

EXPLAIN PLAN SET statement_id='TEST' FOR       SELECT * FROM generalledger WHERE coa# = '40003';     Query                                 Cost     Rows       Bytes ---------------------------------   ------   ------   --------- SELECT STATEMENT on                    165   150548     3914248  TABLE ACCESS BY INDEX ROWID on    GENERALLEDGE                        165   150548     3914248   INDEX RANGE SCAN on XFK_GL_COA#        5   150548

Now let's filter and return much fewer rows. Once again the query plans are the same.

Selecting from the view:

EXPLAIN PLAN SET statement_id='TEST' FOR       SELECT * FROM glv WHERE generalledger_id = 500000;     Query                                 Cost     Rows       Bytes ---------------------------------   ------   ------   --------- SELECT STATEMENT on                      3        1          26  TABLE ACCESS BY INDEX ROWID on    GENERALLEDGE                          3        1          26   INDEX UNIQUE SCAN on XPKGENERALLEDGER  2        1

Selecting from the table:

EXPLAIN PLAN SET statement_id='TEST' FOR       SELECT * FROM generalledger WHERE generalledger_id =          500000;     Query                                 Cost     Rows       Bytes ---------------------------------   ------   ------   --------- SELECT STATEMENT on                      3        1          26  TABLE ACCESS BY INDEX ROWID on    GENERALLEDGE                          3        1          26   INDEX UNIQUE SCAN on XPKGENERALLEDGER  2        1

So let's now try some time tests. The COUNT function is used as a wrapper and each query is executed twice to ensure there is no conflict between reading from disk and memory.

SELECT COUNT(*) FROM(SELECT * FROM glv WHERE coa# = '40003'); SELECT COUNT(*) FROM(SELECT * FROM generalledger WHERE    coa# = '40003'); SELECT COUNT(*) FROM(SELECT * FROM glv       WHERE generalledger_id = 500000); SELECT COUNT(*) FROM(SELECT * FROM generalledger       WHERE generalledger_id = 500000);

In the first two instances retrieving from the view is faster than reading from the table.

SQL> SELECT COUNT(*) FROM(SELECT * FROM glv WHERE    coa# = '40003');       COUNT(*) ----------      66287     Elapsed: 00:00:04.04     SQL> SELECT COUNT(*) FROM(SELECT * FROM generalledger WHERE    coa# = '40003');        COUNT(*) -----------       66287     Elapsed: 00:00:04.09     SQL> SELECT COUNT(*) FROM(SELECT * FROM glv WHERE    generalledger_id = 500000);        COUNT(*) -----------           1     Elapsed: 00:00:00.00     SQL> SELECT COUNT(*) FROM(SELECT * FROM generalledger WHERE    generalledger_id = 500000);        COUNT(*) -----------           1     Elapsed: 00:00:00.00 

For a single table and a view on that table there is no difference in query plan or execution time.

Now let's go and re-create our view and re-create it with a join rather than just a single table. This code drops and re-creates the view I created previously.

DROP VIEW glv; CREATE VIEW glv AS       SELECT gl.generalledger_id, coa.coa#, t.text AS type,             st.text AS subtype, coa.text as coa, gl.dr,                gl.cr, gl.dte       FROM type t JOIN coa USING(type)             JOIN subtype st USING(subtype)                   JOIN generalledger gl ON(gl.coa# =                       coa.coa#);

When retrieving a large percentage of rows in the following two queries the cost in the query plan is much better when retrieving using the tables join rather than the view.

Selecting from the view:

EXPLAIN PLAN SET statement_id='TEST' FOR       SELECT * FROM glv WHERE coa# = '40003';     Query                                 Cost     Rows     Bytes -----------------------------------   ----   ------   ------- SELECT STATEMENT on                    168    30110   2107700  NESTED LOOPS on                       168    30110   2107700   NESTED LOOPS on                        3        1        44    NESTED LOOPS on                       2        1        34     TABLE ACCESS BY INDEX ROWID on COA   1        1        25      INDEX UNIQUE SCAN on XPKCOA                  1     TABLE ACCESS BY INDEX ROWID on       TYPE                               1        6        54      INDEX UNIQUE SCAN on XPKTYPE                 6    TABLE ACCESS BY INDEX ROWID on      SUBTYPE                             1        4        40     INDEX UNIQUE SCAN on XPKSUBTYPE               4   TABLE ACCESS BY INDEX ROWID on     GENERALLEDG                        165   150548   3914248    INDEX RANGE SCAN on XFK_GL_COA#       5   150548

Selecting from the table join:

EXPLAIN PLAN SET statement_id='TEST' FOR       SELECT gl.generalledger_id, coa.coa#, t.text AS type,             st.text AS subtype, coa.text as coa, gl.dr,                gl.cr, gl.dte       FROM type t JOIN coa USING(type)             JOIN subtype st USING(subtype)                   JOIN generalledger gl ON(gl.coa# = coa.coa#)       WHERE gl.coa# = '40003';     Query                                 Cost     Rows     Bytes -----------------------------------   ----   ------   ------- SELECT STATEMENT on                      5    30110   2107700  NESTED LOOPS on                         5    30110   2107700   NESTED LOOPS on                        3        1        44    NESTED LOOPS on                       2        1        34     TABLE ACCESS BY INDEX ROWID        on COA                             1        1        25      INDEX UNIQUE SCAN on XPKCOA                  1     TABLE ACCESS BY INDEX ROWID on        TYPE                               1        6        54      INDEX UNIQUE SCAN on XPKTYPE                 6    TABLE ACCESS BY INDEX ROWID on       SUBTYPE                             1        4        40     INDEX UNIQUE SCAN on XPKSUBTYPE               4   TABLE ACCESS BY INDEX ROWID on     GENERALLEDG                          2   150548   3914248    INDEX RANGE SCAN on XFK_GL_COA#       1   150548

Let's try some timing tests. The first timing test retrieves a large set of rows from the view.

SQL> SELECT COUNT(*) FROM(SELECT * FROM glv WHERE coa# =    '40003');        COUNT(*) -----------       66287     Elapsed: 00:00:04.02 

The second timing test retrieves the same large set of rows from the table join and is obviously much faster.

SQL> SELECT COUNT(*) FROM(   2 SELECT gl.generalledger_id, coa.coa#, t.text AS type,       st.text AS subtype, coa.text as coa, gl.dr, gl.cr,       gl.dte   3 FROM type t JOIN coa USING(type)   4 JOIN subtype st USING(subtype)   5 JOIN generalledger gl ON(gl.coa# = coa.coa#)   6 WHERE gl.coa# = '40003');        COUNT(*) -----------       66287     Elapsed: 00:00:00.07 

Comparing times to retrieve a single row, there is no difference between the view and the retrieval from the join. In a highly active concurrent environment this would probably not be the case.

SQL> SELECT COUNT(*) FROM(SELECT * FROM glv WHERE    generalledger_id = 500000);        COUNT(*) -----------           1     Elapsed: 00:00:00.00     SQL> SELECT COUNT(*) FROM (SELECT gl.generalledger_id,   coa.coa#, t.text AS type, st.text AS subtype, coa.text as coa, gl.dr, gl.cr, gl.dte   2 FROM generalledger gl JOIN coa ON(gl.coa# = coa.coa#)   3 JOIN type t USING(type)   4 JOIN subtype st USING(subtype)   5 WHERE generalledger_id = 500000);        COUNT(*) -----------           1     Elapsed: 00:00:00.00 

Views can now have constraints, including primary and foreign key constraints. These may help performance of data retrieval from views. However, assuming that views are created for coding development simplicity and not security, adding complexity to a view would negate the simplification issue.

The exception to views re-executing every time they are queried is a Materialized View. A Materialized View is a separate database object in Oracle Database and stores the results of a query. Thus when a Materialized View is queried, data is extracted from the view and not the underlying objects in the query. Materialized Views are read only and intended for use in data warehousing and replication.

Views are not performance friendly! For the sake of performance do not use views. Some applications are built with multiple layers of views. This type of application design is often application convenient and can produce disappointing results with respect to database performance. There is simply too much metadata in the shared pool. A brute force method of resolving selection of rows from multiple layered sets of views is to use a form of the FROM clause in the SELECT statement with the ONLY clause included as shown in the following syntax. The ONLY clause will not retrieve rows from subset views.

SELECT … FROM ONLY (query) …

---