| < Day Day Up > |
|
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) …
| < Day Day Up > |
|