两表连接视图，只查询一个表

要视图中有表连接，查询视图必然引起表连接，但今天才知道创建主键以及外键约束，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