Many folks in IT industry think that using database view is an evil, degrade performance on usage and should never be used. This article provides substantial evidence that view never impacts performance.
By definition, a database view is a virtual query. It never exists as an object. If a view is slow in performance, it is not due to view and it is due to the underlying query. Many fail to understand this basic concept. In order to understand this, I have created the following tables with index on EMPNO and each table has 100,000 records.
1. EMPLOYEE
2. EMPSALARY
3. EMPADDRESS
To begin with, I have created a view called "EMPVIEW" joining the tables EMPLOYEE and EMPSALARY and its definition is given below:
CREATE VIEW EMPVIEW AS SELECT EMP.EMPNO, EMP.EMPNAME, EMPS.EMPMONTH, EMPS.EMPBASIC, EMPS.EMPHRA, EMPS.EMPCONV FROM EMPLOYEE EMP JOIN EMPSALARY EMPS ON EMP.EMPNO=EMPS.EMPNO;
SQL> SELECT * FROM EMPVIEW;
100000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 820308138
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 83 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 83 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEE | 1 | 25 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPSALARY | 1 | 58 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access ("EMP"."EMPNO"="EMPS"."EMPNO")
Statistics
----------------------------------------------------------
612 recursive calls
0 db block gets
7436 consistent gets
0 physical reads
0 redo size
3518422 bytes sent via SQL*Net to client
73707 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
16 sorts (memory)
0 sorts (disk)
100000 rows processed
Now, if we execute the underlying query of the view (which is given below), obviously we will come to know that the explain plan is same except for a very small difference in the statistics.
SELECT EMP.EMPNO, EMP.EMPNAME, EMPS.EMPMONTH, EMPS.EMPBASIC, EMPS.EMPHRA, EMPS.EMPCONV FROM EMPLOYEE EMP JOIN EMPSALARY EMPS ON EMP.EMPNO=EMPS.EMPNO;
100000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 820308138
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 83 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 83 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEE | 1 | 25 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPSALARY | 1 | 58 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."EMPNO"="EMPS"."EMPNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7343 consistent gets
0 physical reads
0 redo size
3518422 bytes sent via SQL*Net to client
73707 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed
Now, I am joining all the three tables and the explain plan for the same is given below:
SELECT EMP.EMPNO, EMP.EMPNAME, EMPA.EMPADDR1, EMPA.EMPADDR2, EMPA.EMPADDR3, EMPS.EMPMONTH, EMPS.EMPBASIC, EMPS.EMPHRA, EMPS.EMPCONV FROM EMPLOYEE EMP JOIN EMPADDRESS EMPA ON EMP.EMPNO=EMPA.EMPNO JOIN EMPSALARY EMPS ON EMP.EMPNO=EMPS.EMPNO
100000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3201299630
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 4492K| | 838 (4)|00:00:11 |
|* 1 | HASH JOIN | | 100K| 4492K| 3328K| 838 (4)|00:00:11 |
|* 2 | HASH JOIN | | 100K| 2148K| 1960K| 398 (4)|00:00:05 |
| 3 | TABLE ACCESS FULL| EMPLOYEE | 100K| 781K| | 72 (5)|00:00:01 |
| 4 | TABLE ACCESS FULL| EMPADDRESS | 100K| 1367K| | 100 (4)|00:00:02 |
| 5 | TABLE ACCESS FULL | EMPSALARY | 100K| 2343K| | 100 (4)|00:00:02 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."EMPNO"="EMPS"."EMPNO")
2 - access("EMP"."EMPNO"="EMPA"."EMPNO")
Statistics
----------------------------------------------------------
942 recursive calls
0 db block gets
7922 consistent gets
1130 physical reads
0 redo size
3618604 bytes sent via SQL*Net to client
73707 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
18 sorts (memory)
0 sorts (disk)
100000 rows processed
Now let us join the view with the table “EMPADDRESS” and see the results:
SELECT EMPV.EMPNO, EMPV.EMPNAME, EMPA.EMPADDR1, EMPA.EMPADDR2, EMPA.EMPADDR3, EMPV.EMPMONTH, EMPV.EMPBASIC, EMPV.EMPHRA, EMPV.EMPCONV FROM EMPVIEW EMPV JOIN EMPADDRESS EMPA ON EMPV.EMPNO=EMPA.EMPNO
100000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3201299630
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 4492K| | 838 (4)|00:00:11 |
|* 1 | HASH JOIN | | 100K| 4492K| 3328K| 838 (4)|00:00:11 |
|* 2 | HASH JOIN | | 100K| 2148K| 1960K| 398 (4)|00:00:05 |
| 3 | TABLE ACCESS FULL| EMPLOYEE | 100K| 781K| | 72 (5)|00:00:01 |
| 4 | TABLE ACCESS FULL| EMPADDRESS | 100K| 1367K| | 100 (4)|00:00:02 |
| 5 | TABLE ACCESS FULL | EMPSALARY | 100K| 2343K| | 100 (4)|00:00:02 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."EMPNO"="EMPS"."EMPNO")
2 - access("EMP"."EMPNO"="EMPA"."EMPNO")
Statistics
----------------------------------------------------------
588 recursive calls
0 db block gets
7878 consistent gets
3 physical reads
0 redo size
3618604 bytes sent via SQL*Net to client
73707 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
20 sorts (memory)
0 sorts (disk)
100000 rows processed
This proves a point that the execution plan of a query with view is same as using the actual query. However, let us see what happens if we create a view on another view. For this let us create another table called "EMPWORKS" having 100,000 records
Let us create a view called “EMPWORKVIEW” by joining the already created EMPVIEW with EMPWORKS table.
CREATE VIEW EMPWORKVIEW AS SELECT EMPV.EMPNO, EMPV.EMPNAME, EMPW.EMPWRKAREA, EMPW.EMPWRKLOC, EMPW.EMPROLE, EMPV.EMPMONTH, EMPV.EMPBASIC, EMPV.EMPHRA, EMPV.EMPCONV FROM EMPVIEW EMPV JOIN EMPWORKS EMPW ON EMPV.EMPNO=EMPW.EMPNO
SELECT * FROM EMPWORKVIEW
100000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2018455878
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 84747 | 5462K| | 918 (3)| 00:00:12 |
|* 1 | HASH JOIN | | 84747 | 5462K| 3808K| 918 (3)| 00:00:12 |
| 2 | TABLE ACCESS FULL | EMPWORKS | 84747 | 2813K| | 72 (5)| 00:00:01 |
|* 3 | HASH JOIN | | 100K| 3125K| 1960K| 446 (4)| 00:00:06 |
| 4 | TABLE ACCESS FULL| EMPLOYEE | 100K| 781K| | 72 (5)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMPSALARY | 100K| 2343K| | 100 (4)| 00:00:02 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access ("EMP"."EMPNO"="EMPW"."EMPNO")
3 - access ("EMP"."EMPNO"="EMPS"."EMPNO")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
252 recursive calls
1 db block gets
7751 consistent gets
0 physical reads
176 redo size
4502964 bytes sent via SQL*Net to client
73707 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
100000 rows processed
Now, if we join the three tables EMPWORKS, EMPSALARY and EMPWORKS we can see that the execution plan remains the same, but with meager difference in the statistics.
SELECT EMP.EMPNO, EMP.EMPNAME, EMPS.EMPMONTH, EMPS.EMPBASIC, EMPS.EMPHRA, EMPS.EMPCONV, EMPW.EMPWRKAREA, EMPW.EMPWRKLOC, EMPW.EMPROLE FROM EMPLOYEE EMP JOIN EMPSALARY EMPS ON EMP.EMPNO=EMPS.EMPNO JOIN EMPWORKS EMPW ON EMP.EMPNO=EMPW.EMPNO;
100000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2018455878
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 84747 | 5462K| | 918 (3)| 00:00:12 |
|* 1 | HASH JOIN | | 84747 | 5462K| 3808K| 918 (3)| 00:00:12 |
| 2 | TABLE ACCESS FULL | EMPWORKS | 84747 | 2813K| | 72 (5)| 00:00:01 |
|* 3 | HASH JOIN | | 100K| 3125K| 1960K| 446 (4)| 00:00:06 |
| 4 | TABLE ACCESS FULL| EMPLOYEE | 100K| 781K| | 72 (5)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMPSALARY | 100K| 2343K| | 100 (4)| 00:00:02 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access ("EMP"."EMPNO"="EMPW"."EMPNO")
3 - access ("EMP"."EMPNO"="EMPS"."EMPNO")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
134 recursive calls
0 db block gets
7732 consistent gets
0 physical reads
0 redo size
4502964 bytes sent via SQL*Net to client
73707 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
100000 rows processed
Now this proves a major point that a view created on a view is also same as the joins of the underlying tables. But, if we observe very closely, the cost is increased from 838 to 918. We also need to understand that the increase in cost is not because of the view, but because of the underlying query.
Please also note that, we have indexes in all the tables for the EMPNO column. But the indexes are not used by the query. As a by product of this experiment, I also found that indexes will not be used in the query when we have only JOIN clause but no where clause.
To prove further on this line of thought, I created three more tables(EMPTYPES, EMPPHONES, AND EMPEMAIL) and created views and observed the following:
The view is created with EMPTYPES table and EMPWORKVIEW.
CREATE VIEW EMPTYPEVIEW AS SELECT EMPW.EMPNO, EMPW.EMPNAME, EMPW.EMPWRKAREA, EMPW.EMPWRKLOC, EMPW.EMPROLE, EMPW.EMPMONTH, EMPW.EMPBASIC, EMPW.EMPHRA, EMPW.EMPCONV, EMPT.EMPTYPE, EMPT.EMPTYPEDESC FROM EMPWORKVIEW EMPW JOIN EMPTYPES EMPT ON EMPW.EMPNO=EMPT.EMPNO;
SELECT * FROM EMPTYPEVIEW;
100000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3664500689
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 84748 | 7779K| | 1492 (3)|00:00:18 |
|* 1 | HASH JOIN | | 84748 | 7779K| 4032K| 1492 (3)|00:00:18 |
| 2 | TABLE ACCESS FULL | EMPTYPES | 103K| 2817K| | 59 (7)|00:00:01 |
|* 3 | HASH JOIN | | 84747 | 5462K| 3808K| 918 (3)|00:00:12 |
| 4 | TABLE ACCESS FULL | EMPWORKS | 84747 | 2813K| | 72 (5)|00:00:01 |
|* 5 | HASH JOIN | | 100K| 3125K| 1960K| 446 (4)|00:00:06 |
| 6 | TABLE ACCESS FULL| EMPLOYEE | 100K| 781K| | 72 (5)|00:00:01 |
| 7 | TABLE ACCESS FULL| EMPSALARY | 100K| 2343K| | 100 (4)|00:00:02 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."EMPNO"="EMPT"."EMPNO")
3 - access("EMP"."EMPNO"="EMPW"."EMPNO")
5 - access("EMP"."EMPNO"="EMPS"."EMPNO")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
282 recursive calls
1 db block gets
8075 consistent gets
0 physical reads
132 redo size
5148458 bytes sent via SQL*Net to client
73707 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
100000 rows processed
SELECT EMP.EMPNO, EMP.EMPNAME, EMPS.EMPMONTH, EMPS.EMPBASIC, EMPS.EMPHRA, EMPS.EMPCONV, EMPW.EMPWRKAREA, EMPW.EMPWRKLOC, EMPW.EMPROLE, EMPT.EMPTYPE, EMPT.EMPTYPEDESC FROM EMPLOYEE EMP JOIN EMPSALARY EMPS ON EMP.EMPNO=EMPS.EMPNO JOIN EMPWORKS EMPW ON EMP.EMPNO=EMPW.EMPNO JOIN EMPTYPES EMPT ON EMP.EMPNO=EMPT.EMPNO;
100000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3664500689
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 84748 | 7779K| | 1492 (3)|00:00:18 |
|* 1 | HASH JOIN | | 84748 | 7779K| 4032K| 1492 (3)|00:00:18 |
| 2 | TABLE ACCESS FULL | EMPTYPES | 103K| 2817K| | 59 (7)|00:00:01 |
|* 3 | HASH JOIN | | 84747 | 5462K| 3808K| 918 (3)|00:00:12 |
| 4 | TABLE ACCESS FULL | EMPWORKS | 84747 | 2813K| | 72 (5)|00:00:01 |
|* 5 | HASH JOIN | | 100K| 3125K| 1960K| 446 (4)|00:00:06 |
| 6 | TABLE ACCESS FULL| EMPLOYEE | 100K| 781K| | 72 (5)|00:00:01 |
| 7 | TABLE ACCESS FULL| EMPSALARY | 100K| 2343K| | 100 (4)|00:00:02 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."EMPNO"="EMPT"."EMPNO")
3 - access("EMP"."EMPNO"="EMPW"."EMPNO")
5 - access("EMP"."EMPNO"="EMPS"."EMPNO")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
472 recursive calls
0 db block gets
8096 consistent gets
0 physical reads
0 redo size
5148458 bytes sent via SQL*Net to client
73707 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
22 sorts (memory)
0 sorts (disk)
100000 rows processed
The next view is created between EMPTYPEVIEW and EMPTYPES table:
CREATE VIEW EMPPHONEVIEW AS SELECT EMPW.EMPNO, EMPW.EMPNAME, EMPW.EMPWRKAREA, EMPW.EMPWRKLOC, EMPW.EMPROLE, EMPW.EMPMONTH, EMPW.EMPBASIC, EMPW.EMPHRA, EMPW.EMPCONV, EMPW.EMPTYPE, EMPW.EMPTYPEDESC, EMPP.EMPPHONENO, EMPP.EMPPHONETYPE FROM EMPTYPEVIEW EMPW JOIN EMPPHONES EMPP ON EMPW.EMPNO=EMPP.EMPNO;
SELECT * FROM EMPPHONEVIEW;
100000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3351495524
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 6738K| | 1642 (3)| 00:00:20 |
|* 1 | HASH JOIN | | 100K| 6738K| 3520K| 1642 (3)| 00:00:20 |
| 2 | TABLE ACCESS FULL | EMPSALARY | 100K| 2343K| | 100 (4)| 00:00:02 |
|* 3 | HASH JOIN | | 100K| 4394K| 2448K| 1094 (4)| 00:00:14 |
| 4 | TABLE ACCESS FULL | EMPPHONES | 100K| 1269K| | 73 (6)| 00:00:01 |
|* 5 | HASH JOIN | | 100K| 3125K| 2448K| 688 (4)| 00:00:09 |
| 6 | TABLE ACCESS FULL | EMPWORKS | 100K| 1269K| | 73 (6)| 00:00:01 |
|* 7 | HASH JOIN | | 100K| 1855K| 1960K| 342 (5)| 00:00:05 |
| 8 | TABLE ACCESS FULL| EMPLOYEE | 100K| 781K| | 72 (5)| 00:00:01 |
| 9 | TABLE ACCESS FULL| EMPTYPES | 100K| 1074K| | 59 (7)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."EMPNO"="EMPS"."EMPNO")
3 - access("EMP"."EMPNO"="EMPP"."EMPNO")
5 - access("EMP"."EMPNO"="EMPW"."EMPNO")
7 - access("EMP"."EMPNO"="EMPT"."EMPNO")
Statistics
----------------------------------------------------------
579 recursive calls
0 db block gets
8318 consistent gets
1558 physical reads
0 redo size
6049063 bytes sent via SQL*Net to client
73707 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
100000 rows processed
SELECT EMP.EMPNO, EMP.EMPNAME, EMPS.EMPMONTH, EMPS.EMPBASIC, EMPS.EMPHRA, EMPS.EMPCONV, EMPW.EMPWRKAREA, EMPW.EMPWRKLOC, EMPW.EMPROLE, EMPT.EMPTYPE, EMPT.EMPTYPEDESC,EMPP.EMPPHONENO, EMPP.EMPPHONETYPE FROM EMPLOYEE EMP JOIN EMPSALARY EMPS ON EMP.EMPNO=EMPS.EMPNO JOIN EMPWORKS EMPW ON EMP.EMPNO=EMPW.EMPNO JOIN EMPTYPES EMPT ON EMP.EMPNO=EMPT.EMPNO JOIN EMPPHONES EMPP ON EMP.EMPNO=EMPP.EMPNO;
100000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3351495524
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 6738K| | 1642 (3)| 00:00:20 |
|* 1 | HASH JOIN | | 100K| 6738K| 3520K| 1642 (3)| 00:00:20 |
| 2 | TABLE ACCESS FULL | EMPSALARY | 100K| 2343K| | 100 (4)| 00:00:02 |
|* 3 | HASH JOIN | | 100K| 4394K| 2448K| 1094 (4)| 00:00:14 |
| 4 | TABLE ACCESS FULL | EMPPHONES | 100K| 1269K| | 73 (6)| 00:00:01 |
|* 5 | HASH JOIN | | 100K| 3125K| 2448K| 688 (4)| 00:00:09 |
| 6 | TABLE ACCESS FULL | EMPWORKS | 100K| 1269K| | 73 (6)| 00:00:01 |
|* 7 | HASH JOIN | | 100K| 1855K| 1960K| 342 (5)| 00:00:05 |
| 8 | TABLE ACCESS FULL| EMPLOYEE | 100K| 781K| | 72 (5)| 00:00:01 |
| 9 | TABLE ACCESS FULL| EMPTYPES | 100K| 1074K| | 59 (7)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."EMPNO"="EMPS"."EMPNO")
3 - access("EMP"."EMPNO"="EMPP"."EMPNO")
5 - access("EMP"."EMPNO"="EMPW"."EMPNO")
7 - access("EMP"."EMPNO"="EMPT"."EMPNO")
Statistics
----------------------------------------------------------
208 recursive calls
0 db block gets
8259 consistent gets
0 physical reads
0 redo size
6049063 bytes sent via SQL*Net to client
73707 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
100000 rows processed
The last view is created between EMPPHONEVIEW and EMPEMAIL table.
CREATE VIEW EMPEMAILVIEW AS SELECT EMPW.EMPNO, EMPW.EMPNAME, EMPW.EMPWRKAREA, EMPW.EMPWRKLOC, EMPW.EMPROLE, EMPW.EMPMONTH, EMPW.EMPBASIC, EMPW.EMPHRA, EMPW.EMPCONV, EMPW.EMPTYPE, EMPW.EMPTYPEDESC, EMPW.EMPPHONENO, EMPW.EMPPHONETYPE, EMPP.EMPEMAILADDR1, EMPP.EMPEMAILADDR2 FROM EMPPHONEVIEW EMPW JOIN EMPEMAIL EMPP ON EMPW.EMPNO=EMPP.EMPNO;
SELECT * FROM EMPEMAILVIEW;
100000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 775852864
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 9082K| | 2304 (3)| 00:00:28 |
|* 1 | HASH JOIN | | 100K| 9082K| 3520K| 2304 (3)| 00:00:28 |
| 2 | TABLE ACCESS FULL | EMPEMAIL | 100K| 2343K| | 100 (4)| 00:00:02 |
|* 3 | HASH JOIN | | 100K| 6738K| 3520K| 1642 (3)| 00:00:20 |
| 4 | TABLE ACCESS FULL | EMPSALARY | 100K| 2343K| | 100 (4)| 00:00:02 |
|* 5 | HASH JOIN | | 100K| 4394K| 2448K| 1094 (4)| 00:00:14 |
| 6 | TABLE ACCESS FULL | EMPPHONES | 100K| 1269K| | 73 (6)| 00:00:01 |
|* 7 | HASH JOIN | | 100K| 3125K| 2448K| 688 (4)| 00:00:09 |
| 8 | TABLE ACCESS FULL | EMPWORKS | 100K| 1269K| | 73 (6)| 00:00:01 |
|* 9 | HASH JOIN | | 100K| 1855K| 1960K| 342 (5)| 00:00:05 |
| 10 | TABLE ACCESS FULL| EMPLOYEE | 100K| 781K| | 72 (5)| 00:00:01 |
| 11 | TABLE ACCESS FULL| EMPTYPES | 100K| 1074K| | 59 (7)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."EMPNO"="EMPP"."EMPNO")
3 - access("EMP"."EMPNO"="EMPS"."EMPNO")
5 - access("EMP"."EMPNO"="EMPP"."EMPNO")
7 - access("EMP"."EMPNO"="EMPW"."EMPNO")
9 - access("EMP"."EMPNO"="EMPT"."EMPNO")
Statistics
----------------------------------------------------------
685 recursive calls
0 db block gets
8772 consistent gets
1991 physical reads
0 redo size
8048509 bytes sent via SQL*Net to client
73707 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
100000 rows processed
SELECT EMP.EMPNO, EMP.EMPNAME, EMPS.EMPMONTH, EMPS.EMPBASIC, EMPS.EMPHRA, EMPS.EMPCONV, EMPW.EMPWRKAREA, EMPW.EMPWRKLOC, EMPW.EMPROLE, EMPT.EMPTYPE, EMPT.EMPTYPEDESC,EMPP.EMPPHONENO, EMPP.EMPPHONETYPE,EMPE.EMPEMAILADDR1, EMPE.EMPEMAILADDR2 FROM EMPLOYEE EMP JOIN EMPSALARY EMPS ON EMP.EMPNO=EMPS.EMPNO JOIN EMPWORKS EMPW ON EMP.EMPNO=EMPW.EMPNO JOIN EMPTYPES EMPT ON EMP.EMPNO=EMPT.EMPNO JOIN EMPPHONES EMPP ON EMP.EMPNO=EMPP.EMPNO JOIN EMPEMAIL EMPE ON EMP.EMPNO=EMPE.EMPNO;
100000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 775852864
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 9082K| | 2304 (3)| 00:00:28 |
|* 1 | HASH JOIN | | 100K| 9082K| 3520K| 2304 (3)| 00:00:28 |
| 2 | TABLE ACCESS FULL | EMPEMAIL | 100K| 2343K| | 100 (4)| 00:00:02 |
|* 3 | HASH JOIN | | 100K| 6738K| 3520K| 1642 (3)| 00:00:20 |
| 4 | TABLE ACCESS FULL | EMPSALARY | 100K| 2343K| | 100 (4)| 00:00:02 |
|* 5 | HASH JOIN | | 100K| 4394K| 2448K| 1094 (4)| 00:00:14 |
| 6 | TABLE ACCESS FULL | EMPPHONES | 100K| 1269K| | 73 (6)| 00:00:01 |
|* 7 | HASH JOIN | | 100K| 3125K| 2448K| 688 (4)| 00:00:09 |
| 8 | TABLE ACCESS FULL | EMPWORKS | 100K| 1269K| | 73 (6)| 00:00:01 |
|* 9 | HASH JOIN | | 100K| 1855K| 1960K| 342 (5)| 00:00:05 |
| 10 | TABLE ACCESS FULL| EMPLOYEE | 100K| 781K| | 72 (5)| 00:00:01 |
| 11 | TABLE ACCESS FULL| EMPTYPES | 100K| 1074K| | 59 (7)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."EMPNO"="EMPE"."EMPNO")
3 - access("EMP"."EMPNO"="EMPS"."EMPNO")
5 - access("EMP"."EMPNO"="EMPP"."EMPNO")
7 - access("EMP"."EMPNO"="EMPW"."EMPNO")
9 - access("EMP"."EMPNO"="EMPT"."EMPNO")
Statistics
----------------------------------------------------------
909 recursive calls
0 db block gets
8777 consistent gets
0 physical reads
0 redo size
8048509 bytes sent via SQL*Net to client
73707 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
26 sorts (memory)
0 sorts (disk)
100000 rows processed
Now, it is concretely observed that the increase in cost is not because of view, but the underlying query. Hence the prevailing thought that view is an evil is misconception.
Please do send me your comments
No comments:
Post a Comment