关闭

VIEW新的理解

标签: objecttableaccessfilterloopsnested
394人阅读 评论(0) 收藏 举报
分类:

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

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:155639次
    • 积分:1911
    • 等级:
    • 排名:千里之外
    • 原创:44篇
    • 转载:9篇
    • 译文:0篇
    • 评论:6条
    最新评论