Oracle 用where子句从视图中选择

嗨，我找不到，所以我想问:
我已经查看了我的视图:

create view myView
as select * from myTable;

这两者之间有什么区别

select * from myTable where myTable.id = 12345;

和

select * from myView where myView.id = 12345;

Select from view firstly doing whole select (select * from myTable) 和 after that takes only one rekord from all readed? Or maybe it is the same like select * from myTable where myTable.id = 12345;?

在这种情况下，没有区别。视图存储查询的文本，使您可以轻松地重用它。

所以查询视图和查询表本身是一样的。以下显示两者使用相同的执行计划:

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)   

但是，如果您开始向视图中添加额外的表达式，那么它不再与查询表本身相同。所以你最终可能会有不同的计划。

例如，假设您在计算表中的行数的视图中添加了一个分析函数:

create or replace view vw as 
  select t.*,
         count(*) over () row#
  from   t;

当您查询给定行的视图时，数据库仍然必须对表中的所有行进行计数以返回正确的结果。

因此，它不是只读取一行，而是查询整个表:

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)