往期回顾：

主外键约束能够让Oracle优化器对视图查询进行智能优化，在特定条件下完全消除不必要的表访问。本文作为《左右SQL执行计划妙招》系列第十六篇，展示主外键约束如何让关联视图查询实现单表访问优化。

环境准备

drop table t1 cascade constraints purge;
drop table t2 cascade constraints  purge;
create table t1 as select * from dba_objects;
create table t2 as select * from dba_objects where rownum<=10000;
update t1 set object_id=rownum ;
update t2 set object_id=rownum ;
commit;
-- 创建关联视图
create or replace view v_t1_join_t2 
as select t2.object_id,t2.object_name,t1.object_type,t1.owner 
   from t1,t2 
   where t1.object_id=t2.object_id;
set linesize 1000
set pagesize 2000
set autotrace traceonly

三场景对比：约束如何改变视图查询

场景1：无约束的视图查询

select object_id,object_name from v_t1_join_t2;

执行计划：

---------------------------------------------------------------------------
Plan hash value: 2959412835
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 12187 |  1094K|   333   (1)| 00:00:04 |
|*  1 |  HASH JOIN         |      | 12187 |  1094K|   333   (1)| 00:00:04 |
|   2 |   TABLE ACCESS FULL| T2   | 12186 |   940K|    40   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1   | 64114 |   813K|   292   (1)| 00:00:04 |
---------------------------------------------------------------------------
统计信息
---------------------------------------------------------------------------
          0  recursive calls
          0  db block gets
       1841  consistent gets
          0  physical reads
          0  redo size
     319671  bytes sent via SQL*Net to client
       7741  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

场景2：添加主键后的视图查询

alter table T1 add constraint pk_object_id primary key (OBJECT_ID);   
select object_id,object_name from v_t1_join_t2;

执行计划：

-----------------------------------------------------------------------------------
Plan hash value: 1632777847
-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              | 12187 |  1094K|    41   (3)| 00:00:01 |
|   1 |  NESTED LOOPS      |              | 12187 |  1094K|    41   (3)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T2           | 12186 |   940K|    40   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_OBJECT_ID |     1 |    13 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
统计信息
-----------------------------------------------------------------------------------
          0  recursive calls
          0  db block gets
       1617  consistent gets
          0  physical reads
          0  redo size
     319671  bytes sent via SQL*Net to client
       7741  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

场景3：主外键约束实现表访问消除

alter table T2 
add constraint fk_object_id foreign key (OBJECT_ID) references t1 (OBJECT_ID);
select object_id,object_name from v_t1_join_t2;

执行计划：

--------------------------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 12186 |   940K|    40   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   | 12186 |   940K|    40   (0)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
--------------------------------------------------------------------------
          0  recursive calls
          0  db block gets
        794  consistent gets
          0  physical reads
          0  redo size
     319671  bytes sent via SQL*Net to client
       7741  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

关键发现

优化效果：查询同一个关联视图，在场景3中Oracle完全消除了T1表的访问，仅通过T2表就完成查询。

优化原理：外键约束保证T2表中每个object_id都在T1表中存在，而查询结果只需要T2表字段，因此T1表访问变得不必要。

性能对比

在这里插入图片描述

应用条件

- 查询字段仅来自外键表（子表）

- 存在有效的主外键约束关系

- 视图包含标准的主外键关联逻辑

总结

主外键约束让Oracle能够智能识别关联视图查询中的冗余表访问。本案例显示，完整的约束设计可以将视图查询从双表访问优化为单表访问，性能提升近60%。这体现了Oracle优化器理解约束逻辑、消除冗余操作的强大能力。

未完待续..

公众号：收获不止数据库

微信：ljbyxl1213

系列回顾

ITPub博客

左右SQL执行计划妙招 ⑯ ——执行计划利用设计特性改变【主外键影响】

环境准备

三场景对比：约束如何改变视图查询

关键发现

总结

最新文章