要视图中有表连接，查询视图必然引起表连接，但今天才知道创建主键以及外键约束，oracle可以只查
询某一张表（如果查询的数据只在这张表中）
RUDY@linux_orcl>create table t1 as select * from dba_objects;
Table created.
RUDY@linux_orcl>create table t2 as select * from dba_objects where rownum<=10000;
Table created.
---递增更新每列的object_id
RUDY@linux_orcl>update t1 set object_id=rownum ;
72012 rows updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 1665816015
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 74955 | 951K| 287 (1)| 00:00:04 |
| 1 | UPDATE | T1 | | | | |
| 2 | COUNT | | | | | |
| 3 | TABLE ACCESS FULL| T1 | 74955 | 951K| 287 (1)| 00:00:04 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
131 recursive calls
85630 db block gets
2595 consistent gets
2227 physical reads
27437740 redo size
559 bytes sent via SQL*Net to client
502 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
72012 rows processed
RUDY@linux_orcl>update t2 set object_id=rownum ;
10000 rows updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 3791457002
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 10320 | 131K| 40 (0)| 00:00:01 |
| 1 | UPDATE | T2 | | | | |
| 2 | COUNT | | | | | |
| 3 | TABLE ACCESS FULL| T2 | 10320 | 131K| 40 (0)| 00:00:01 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
50 recursive calls
790 db block gets
207 consistent gets
193 physical reads
1070216 redo size
560 bytes sent via SQL*Net to client
502 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10000 rows processed
RUDY@linux_orcl>commit;
Commit complete.
---创建测试视图
RUDY@linux_orcl>create or replace view v_t1_join_t2
2 as select t2.object_id,t2.object_name,t1.object_type,t1.owner from t1,t2
3 where t1.object_id=t2.object_id;
View created.
RUDY@linux_orcl>
RUDY@linux_orcl>set autotrace traceonly
RUDY@linux_orcl>set linesize 1000
RUDY@linux_orcl>select * from v_t1_join_t2;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10320 | 1209K| 328 (1)| 00:00:04 |
|* 1 | HASH JOIN | | 10320 | 1209K| 328 (1)| 00:00:04 |
| 2 | TABLE ACCESS FULL| T2 | 10320 | 796K| 40 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 74955 | 3001K| 287 (1)| 00:00:04 |
---------------------------------------------------------------------------
---两个全表查询，由于表中的数据比较多，故采用hash join连接
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
79 recursive calls
0 db block gets
1967 consistent gets
0 physical reads
0 redo size
380104 bytes sent via SQL*Net to client
7689 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
RUDY@linux_orcl>select object_id,object_name from v_t1_join_t2;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10320 | 927K| 328 (1)| 00:00:04 |
|* 1 | HASH JOIN | | 10320 | 927K| 328 (1)| 00:00:04 |
| 2 | TABLE ACCESS FULL| T2 | 10320 | 796K| 40 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 74955 | 951K| 287 (1)| 00:00:04 |
---------------------------------------------------------------------------
---明明只查询t2表的object_id,object_name，由于没有在t2表创建索引，故采用两个全表查询经及，和hash join
---连接
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
1961 consistent gets
0 physical reads
0 redo size
291606 bytes sent via SQL*Net to client
7689 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
RUDY@linux_orcl>alter table T1 add constraint pk_object_id primary key (OBJECT_ID);
Table altered.
RUDY@linux_orcl>select object_id,object_name from v_t1_join_t2;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1632777847
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10837 | 973K| 41 (3)| 00:00:01 |
| 1 | NESTED LOOPS | | 10837 | 973K| 41 (3)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T2 | 10837 | 836K| 40 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| PK_OBJECT_ID | 1 | 13 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
---此时在t1表在创建了主键约束，故执行计划用了索引，但由于t2表没有外键约束，故还是要查询t2表
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
1693 consistent gets
20 physical reads
0 redo size
291606 bytes sent via SQL*Net to client
7689 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
RUDY@linux_orcl>alter table T2 add constraint fk_objecdt_id foreign key (OBJECT_ID) references t1 (OBJE
Table altered.
RUDY@linux_orcl>
RUDY@linux_orcl>select * from v_t1_join_t2;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10320 | 1209K| 328 (1)| 00:00:04 |
|* 1 | HASH JOIN | | 10320 | 1209K| 328 (1)| 00:00:04 |
| 2 | TABLE ACCESS FULL| T2 | 10320 | 796K| 40 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 74955 | 3001K| 287 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
2741 recursive calls
101 db block gets
2658 consistent gets
20 physical reads
16036 redo size
380104 bytes sent via SQL*Net to client
7689 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
70 sorts (memory)
0 sorts (disk)
10000 rows processed
RUDY@linux_orcl>select object_id,object_name from v_t1_join_t2;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10320 | 796K| 40 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 10320 | 796K| 40 (0)| 00:00:01 |
--------------------------------------------------------------------------
---查询t2表的object_id,object_name，由于创建主建约束，以及外键约束，故此时oracle知道只查询t2表就可以了
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T2"."OBJECT_ID" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
862 consistent gets
0 physical reads
0 redo size
291606 bytes sent via SQL*Net to client
7689 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
