Oracle中SELECT *与指定列查询返回行数不同的原因咨询

问题重现 #

测试环境搭建 #

create table tq84_tab (
   id          number(7)    not null,
   val_1       number(4,1)  not null,
   val_2       varchar2(10) not null
);

create table tq84_flg (
   tab_rowid   rowid
);

create or replace view tq84_v_s_t_hist_anton as
select
   tab.*,
   case when flg.tab_rowid is not null then 1 else 0 end flg
from
   tq84_tab   tab                                left join
   tq84_flg   flg on tab.rowid = flg.tab_rowid
;

插入测试数据 #

begin
   insert into tq84_tab values( 10001, 4.2, 'XXX');
   insert into tq84_tab values( 10002, 5.1, 'YYY');
   -- 重复插入同一行的rowid到标记表
   insert into tq84_flg(tab_rowid) select rowid from tq84_tab where val_2 = 'YYY';
   insert into tq84_flg(tab_rowid) select rowid from tq84_tab where val_2 = 'YYY';
   commit;
end;
/

查询差异现象 #

• 执行select * from tq84_v_s_t_hist_anton返回3条记录：

  ID      VAL_1 VAL_2             FLG
  ---------- ---------- ---------- ----------
       10002        5.1 YYY                 1
       10002        5.1 YYY                 1
       10001        4.2 XXX                 0
  

• 执行select val_1, val_2 from tq84_v_s_t_hist_anton仅返回2条记录：

  VAL_1 VAL_2     
  ---------- ----------
         4.2 XXX       
         5.1 YYY 
  

原因分析 #

该差异由Oracle查询优化器的视图合并与冗余消除逻辑导致：

1. SELECT * 场景：
   查询包含视图定义的flg列，该列依赖于右表tq84_flg的关联结果。优化器必须执行左连接逻辑：tq84_tab左连接tq84_flg，由于tq84_flg中存在两条相同的rowid，关联后会为tq84_tab中id=10002的行生成两条匹配记录，最终返回3条结果。

2. 指定列查询场景：
   查询的所有列（val_1、val_2）均来自左表tq84_tab，且tq84_tab本身无重复行。Oracle优化器会识别到右表tq84_flg的关联对最终返回的列无影响，因此跳过左连接操作，直接从tq84_tab读取数据，返回原表的2条记录，不会产生重复行。

验证方式 #

通过添加NO_MERGE提示强制关闭视图合并优化，可让指定列查询也返回3条记录：

select /*+ NO_MERGE(tq84_v_s_t_hist_anton) */ val_1, val_2 from tq84_v_s_t_hist_anton;

此时优化器无法跳过连接逻辑，会返回与select *一致的3条记录。

内容的提问来源于stack exchange，提问作者René Nyffenegger