VIEW新的理解

来源：互联网发布：方舟子中医知乎编辑：程序博客网时间：2024/06/08 09:14

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新的理解呀。