Thursday, 11 March 2010

How does query work on views

So here is the experiment we did to see that practically happening.

Lets see how this works with our experiment


SQL> select * from emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 14 518 3 (0) 00:00:01
1 TABLE ACCESS FULL EMP 14 518 3 (0) 00:00:01
--------------------------------------------------------------------------
SQL> create view emp_vw as select * from emp;
View created.
SQL> select * from emp_vw ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 14 518 3 (0) 00:00:01
1 TABLE ACCESS FULL EMP 14 518 3 (0) 00:00:01
--------------------------------------------------------------------------



Now lets try with a bind variable on table then on view


SQL> var emp varchar2(10);

SQL> select * from emp where empno = :emp ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------
------
Id Operation Name Rows Bytes Cost (%CPU) Time

--------------------------------------------------------------------------------
------
0 SELECT STATEMENT 1 37 1 (0) 00:0
0:01
1 TABLE ACCESS BY INDEX ROWID EMP 1 37 1 (0) 00:0
0:01
* 2 INDEX UNIQUE SCAN PK_EMP 1 0 (0) 00:0
0:01
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=TO_NUMBER(:EMP))
SQL> select * from emp_vw where empno =:emp ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------
------
Id Operation Name Rows Bytes Cost (%CPU) Time

--------------------------------------------------------------------------------
------
0 SELECT STATEMENT 1 37 1 (0) 00:0
0:01
1 TABLE ACCESS BY INDEX ROWID EMP 1 37 1 (0) 00:0
0:01
* 2 INDEX UNIQUE SCAN PK_EMP 1 0 (0) 00:0
0:01
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=TO_NUMBER(:EMP))



now lets create a view on emp_vw and try explain plan

SQL> create view emp_vw_1 as select * from emp_vw ;

View created.
SQL> select * from emp_vw_1 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 14 518 3 (0) 00:00:01
1 TABLE ACCESS FULL EMP 14 518 3 (0) 00:00:01
--------------------------------------------------------------------------



with bind variable on emp_vw_1


SQL> select * from emp_vw_1 where empno = :emp ;

Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------
------
Id Operation Name Rows Bytes Cost (%CPU) Time

--------------------------------------------------------------------------------
------
0 SELECT STATEMENT 1 37 1 (0) 00:0
0:01
1 TABLE ACCESS BY INDEX ROWID EMP 1 37 1 (0) 00:0
0:01
* 2 INDEX UNIQUE SCAN PK_EMP 1 0 (0) 00:0
0:01
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=TO_NUMBER(:EMP))



Now put that on inline view

SQL> select * from (select * from emp_vw_1) ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 14 518 3 (0) 00:00:01
1 TABLE ACCESS FULL EMP 14 518 3 (0) 00:00:01
--------------------------------------------------------------------------


inline view and bind variable

SQL>select * from (select * from emp_vw_1) a where a.empno = :emp
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------
------
Id Operation Name Rows Bytes Cost (%CPU) Time

--------------------------------------------------------------------------------
------
0 SELECT STATEMENT 1 37 1 (0) 00:0
0:01
1 TABLE ACCESS BY INDEX ROWID EMP 1 37 1 (0) 00:0
0:01
* 2 INDEX UNIQUE SCAN PK_EMP 1 0 (0) 00:0
0:01
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=TO_NUMBER(:EMP))
SQL> select * from (select * from emp_vw_1 where empno = :emp) a where a.empno = :emp
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------
------
Id Operation Name Rows Bytes Cost (%CPU) Time

--------------------------------------------------------------------------------
------
0 SELECT STATEMENT 1 37 1 (0) 00:0
0:01
1 TABLE ACCESS BY INDEX ROWID EMP 1 37 1 (0) 00:0
0:01
* 2 INDEX UNIQUE SCAN PK_EMP 1 0 (0) 00:0
0:01
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=TO_NUMBER(:EMP))
SQL> set autotrace off



Views are just queries stored in the database so that you don not have to type that again.

See Tom's explanation at the link below:
http://www.oracle.com/technology/oramag/oracle/05-mar/o25asktom.html