Hi, I cant find so I'd like to ask:
I have view myView:
create view myView
as select * from myTable;
and what is the difference between
select * from myTable where myTable.id = 12345;
and
select * from myView where myView.id = 12345;
Select from view firstly doing whole select (select * from myTable) and after that takes only one rekord from all readed? Or maybe it is the same like select * from myTable where myTable.id = 12345;?
In this case, there is no difference. A view stores the text of a query, allowing you to reuse it easily.
So querying the view is the same as querying the table itself. The following shows that both use the same execution plan:
create table t (
c1 primary key,
c2 not null
) as
select level, lpad ( 'x', 20, 'x' )
from dual
connect by level <= 100;
create or replace view vw as
select * from t;
alter session set statistics_level = all;
set serveroutput off
select * from t
where c1 = 1;
select *
from table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST'));
PLAN_TABLE_OUTPUT
SQL_ID 4rd58vgqsks2k, child number 0
-------------------------------------
select * from t where c1 = 1
Plan hash value: 2701941032
-------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 1 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0013089 | 1 | 1 | 1 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=1)
select * from vw
where c1 = 1;
select *
from table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST'));
PLAN_TABLE_OUTPUT
SQL_ID 8hmj9cb41mntu, child number 0
-------------------------------------
select * from vw where c1 = 1
Plan hash value: 2701941032
-------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 1 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0013089 | 1 | 1 | 1 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=1) But if you start adding extra expressions to your view, then it's no longer the same as querying the table itself. So you may end up with different plans.
For example, say you add an analytic function to the view counting the number of rows in the table:
create or replace view vw as
select t.*,
count(*) over () row#
from t;When you query the view for a given row, the database still has to count all the rows in the table to return the correct result.
So instead of reading just one row, it queries the whole table:
select * from vw
where c1 = 1;
select *
from table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST'));
PLAN_TABLE_OUTPUT
SQL_ID 8hmj9cb41mntu, child number 0
-------------------------------------
select * from vw where c1 = 1
Plan hash value: 2590755431
---------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |
|* 1 | VIEW | VW | 1 | 100 | 1 |
| 2 | WINDOW BUFFER | | 1 | 100 | 100 |
| 3 | TABLE ACCESS FULL| T | 1 | 100 | 100 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1"=1)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)