create table test1 as select * from dba_objects where object_id <40
test1有38笔数据
create table test2 as select * from dba_objects
create table test3 as select * from dba_objects
create table test4 as select * from dba_objects where object_id <10
test4只有8笔数据
create index idx_test2 on test2 (object_id)
create index idx_test3 on test3 (object_id)
create view v_view as
select test1.* from test1,test2,test3 where test1.object_id = test2.object_id and test2.object_id = test3.object_id;
Plan hash value: 372490522
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 27 |00:00:00.55 | 2125 | | | |
| 1 | NESTED LOOPS | | 1 | 12 | 27 |00:00:00.55 | 2125 | | | |
| 2 | TABLE ACCESS FULL | TEST4 | 1 | 8 | 8 |00:00:00.01 | 4 | | | |
|* 3 | VIEW | V_VIEW | 8 | 1 | 27 |00:00:00.55 | 2121 | | | |
|* 4 | HASH JOIN | | 8 | 38 | 304 |00:00:00.55 | 2121 | 776K| 776K| 1194K (0)|
|* 5 | HASH JOIN | | 8 | 38 | 304 |00:00:00.27 | 1072 | 780K| 780K| 1189K (0)|
| 6 | TABLE ACCESS FULL | TEST1 | 8 | 38 | 304 |00:00:00.01 | 24 | | | |
| 7 | INDEX FAST FULL SCAN| IDX_TEST2 | 8 | 56100 | 448K|00:00:00.01 | 1048 | | | |
| 8 | INDEX FAST FULL SCAN | IDX_TEST3 | 8 | 56101 | 448K|00:00:00.01 | 1049 | | | |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("TEST4"."DATA_OBJECT_ID"="V_VIEW"."DATA_OBJECT_ID")
4 - access("TEST2"."OBJECT_ID"="TEST3"."OBJECT_ID")
5 - access("TEST1"."OBJECT_ID"="TEST2"."OBJECT_ID")
TEST4里有8行,每行都循环一次做* 3 的FILTER, 被推入到TEST1的谓词上,所以步骤6的STARTS是8,因为重复了8次,所以A-Rows是 8*38=304
这个真的是view新的理解呀。