本文围绕一个SQL查询展开，该查询结果出现重复值。作者在不同Oracle环境（如9.2.0.1、9204等）及优化器模式下测试，发现不同版本执行计划和结果有差异。推断问题出在Oracle在all_rows上对nl执行时驱动表选择的特殊情况，交换驱动表顺序可解决，且9204以前版本在all_rows下对内嵌视图有较多bug。
工作中遇到了这样一个sql：
select b.unitcode,b.unitname,b.fathercorp,pk_corp from bd_corp b
where b.pk_corp
in (SELECT v.pk_corp FROM v_sm_userpower1 v where v.fun_code like '20021025%'
and v.fun_name='查询' and v.userid='0001AA10000000000UYQ')
order by b.unitcode
v_sm_userpower1 是视图
查询得到的结果出现了重复值：----错误的结果
UNITCODE UNITNAME FATHERCORP PK_CORP
------------ -------------------------------------------------------- ---------- -------
01 红河州烟草公司（本部） 1001
01 红河州烟草公司（本部） 1001
0101 红河州烟草公司弥勒经营部 1001 1002
0101 红河州烟草公司弥勒经营部 1001 1002
0102 红河州烟草公司泸西经营部 1001 1003
0102 红河州烟草公司泸西经营部 1001 1003
0103 红河州烟草公司建水经营部 1001 1004
0103 红河州烟草公司建水经营部 1001 1004
0104 红河州烟草公司红河营销部 1001 1005
0104 红河州烟草公司红河营销部 1001 1005
0105 红河州烟草公司屏边营销部 1001 1006
0105 红河州烟草公司屏边营销部 1001 1006
0106 红河州烟草公司蒙自经营部 1001 1007
0106 红河州烟草公司蒙自经营部 1001 1007
0107 红河州烟草公司石屏经营部 1001 1008
0107 红河州烟草公司石屏经营部 1001 1008
0108 红河州烟草公司个旧经营部 1001 1009
0108 红河州烟草公司个旧经营部 1001 1009
0109 红河州烟草公司开远经营部 1001 1010
0109 红河州烟草公司开远经营部 1001 1010
0110 红河州烟草公司河口营销部 1001 1011
0110 红河州烟草公司河口营销部 1001 1011
0111 红河州烟草公司金平营销部 1001 1012
0111 红河州烟草公司金平营销部 1001 1012
0112 红河州烟草公司泸西复烤厂 1001 1013
0112 红河州烟草公司泸西复烤厂 1001 1013
oracle的环境：Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
优化器模式：CBO
我们来看一下在各种优化器模式下得到的结果和执行计划：
SQL> select /*+all_rows*/
2 b.unitcode,b.unitname,b.fathercorp,pk_corp from bd_corp b
3 where b.pk_corp
4 in (SELECT v.pk_corp FROM v_sm_userpower1 v where v.fun_code like '20021025%'
5 and v.fun_name='查询' and v.userid='0001AA10000000000UYQ')
6 order by b.unitcode
7 /
UNITCODE UNITNAME FATHERCORP PK_CORP
------------ -------------------------------------------------------- ---------- -------
01 红河州烟草公司（本部） 1001
01 红河州烟草公司（本部） 1001
0101 红河州烟草公司弥勒经营部 1001 1002
0101 红河州烟草公司弥勒经营部 1001 1002
0102 红河州烟草公司泸西经营部 1001 1003
0102 红河州烟草公司泸西经营部 1001 1003
0103 红河州烟草公司建水经营部 1001 1004
0103 红河州烟草公司建水经营部 1001 1004
0104 红河州烟草公司红河营销部 1001 1005
0104 红河州烟草公司红河营销部 1001 1005
0105 红河州烟草公司屏边营销部 1001 1006
0105 红河州烟草公司屏边营销部 1001 1006
0106 红河州烟草公司蒙自经营部 1001 1007
0106 红河州烟草公司蒙自经营部 1001 1007
0107 红河州烟草公司石屏经营部 1001 1008
0107 红河州烟草公司石屏经营部 1001 1008
0108 红河州烟草公司个旧经营部 1001 1009
0108 红河州烟草公司个旧经营部 1001 1009
0109 红河州烟草公司开远经营部 1001 1010
0109 红河州烟草公司开远经营部 1001 1010
0110 红河州烟草公司河口营销部 1001 1011
0110 红河州烟草公司河口营销部 1001 1011
0111 红河州烟草公司金平营销部 1001 1012
0111 红河州烟草公司金平营销部 1001 1012
0112 红河州烟草公司泸西复烤厂 1001 1013
0112 红河州烟草公司泸西复烤厂 1001 1013
26 rows selected
Rows Row Source Operation
------- ---------------------------------------------------
26 SORT ORDER BY
26 NESTED LOOPS
27 VIEW
27 SORT UNIQUE
27 UNION-ALL
0 TABLE ACCESS BY INDEX ROWID SM_USERGROUPPOWER －－nl的driving table
1 NESTED LOOPS
0 MERGE JOIN CARTESIAN
0 TABLE ACCESS BY INDEX ROWID SM_USER_RELA
0 INDEX RANGE SCAN IX_SM_USER_RELA (object id 32028)
0 BUFFER SORT
0 TABLE ACCESS BY INDEX ROWID SM_FUNCREGISTER
0 INDEX RANGE SCAN I_SM_FUNCREGISTER (object id 32005)
0 INDEX RANGE SCAN I_SM_USERGROUPPOWE (object id 32023)
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS FULL SM_USERGROUPPOWER
0 TABLE ACCESS BY INDEX ROWID SM_USER_RELA
0 INDEX RANGE SCAN IX_SM_USER_RELA (object id 32028)
0 TABLE ACCESS BY INDEX ROWID SM_BUTNREGISTER
0 INDEX UNIQUE SCAN PK_SM_BUTNREGISTER (object id 31993)
14 NESTED LOOPS
60369 TABLE ACCESS BY INDEX ROWID SM_APPUSERPOWER
60369 INDEX RANGE SCAN IX_SM_APPUSERPOWER (object id 31986)
14 TABLE ACCESS BY INDEX ROWID SM_FUNCREGISTER
7031 INDEX UNIQUE SCAN PK_SM_FUNCREGISTER (object id 32006)
13 NESTED LOOPS
60369 TABLE ACCESS BY INDEX ROWID SM_APPUSERPOWER
60369 INDEX RANGE SCAN IX_SM_APPUSERPOWER (object id 31986)
13 TABLE ACCESS BY INDEX ROWID SM_BUTNREGISTER
53338 INDEX UNIQUE SCAN PK_SM_BUTNREGISTER (object id 31993)
26 TABLE ACCESS BY INDEX ROWID BD_CORP
26 INDEX UNIQUE SCAN PK_BD_CORP (object id 30491)
SQL> select /*+first_rows*/
2 b.unitcode,b.unitname,b.fathercorp,pk_corp from bd_corp b
3 where b.pk_corp
4 in (SELECT v.pk_corp FROM v_sm_userpower1 v where v.fun_code like '20021025%'
5 and v.fun_name='查询' and v.userid='0001AA10000000000UYQ')
6 order by b.unitcode
7 /
正确的结果：
UNITCODE UNITNAME FATHERCORP PK_CORP
------------ -------------------------------------------- ---------- -------
01 红河州烟草公司（本部） 1001
0101 红河州烟草公司弥勒经营部 1001 1002
0102 红河州烟草公司泸西经营部 1001 1003
0103 红河州烟草公司建水经营部 1001 1004
0104 红河州烟草公司红河营销部 1001 1005
0105 红河州烟草公司屏边营销部 1001 1006
0106 红河州烟草公司蒙自经营部 1001 1007
0107 红河州烟草公司石屏经营部 1001 1008
0108 红河州烟草公司个旧经营部 1001 1009
0109 红河州烟草公司开远经营部 1001 1010
0110 红河州烟草公司河口营销部 1001 1011
0111 红河州烟草公司金平营销部 1001 1012
0112 红河州烟草公司泸西复烤厂 1001 1013
13 rows selected
Rows Row Source Operation
------- ---------------------------------------------------
13 NESTED LOOPS SEMI
13 TABLE ACCESS BY INDEX ROWID BD_CORP －－nl的driving table
13 INDEX FULL SCAN I_BD_CORP_1 (object id 30488)
13 VIEW
104 SORT UNIQUE
27 UNION-ALL
0 TABLE ACCESS BY INDEX ROWID SM_USERGROUPPOWER
1 NESTED LOOPS
0 MERGE JOIN CARTESIAN
0 TABLE ACCESS BY INDEX ROWID SM_USER_RELA
0 INDEX RANGE SCAN IX_SM_USER_RELA (object id 32028)
0 BUFFER SORT
0 TABLE ACCESS BY INDEX ROWID SM_FUNCREGISTER
0 INDEX RANGE SCAN I_SM_FUNCREGISTER (object id 32005)
0 INDEX RANGE SCAN I_SM_USERGROUPPOWE (object id 32023)
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS FULL SM_USERGROUPPOWER
0 TABLE ACCESS BY INDEX ROWID SM_USER_RELA
0 INDEX RANGE SCAN IX_SM_USER_RELA (object id 32028)
0 TABLE ACCESS BY INDEX ROWID SM_BUTNREGISTER
0 INDEX UNIQUE SCAN PK_SM_BUTNREGISTER (object id 31993)
14 NESTED LOOPS
60369 TABLE ACCESS BY INDEX ROWID SM_APPUSERPOWER
60369 INDEX RANGE SCAN IX_SM_APPUSERPOWER (object id 31986)
14 TABLE ACCESS BY INDEX ROWID SM_FUNCREGISTER
7031 INDEX UNIQUE SCAN PK_SM_FUNCREGISTER (object id 32006)
13 NESTED LOOPS
60369 TABLE ACCESS BY INDEX ROWID SM_APPUSERPOWER
60369 INDEX RANGE SCAN IX_SM_APPUSERPOWER (object id 31986)
13 TABLE ACCESS BY INDEX ROWID SM_BUTNREGISTER
53338 INDEX UNIQUE SCAN PK_SM_BUTNREGISTER (object id 31993)
SQL> select /*+rule*/
2 b.unitcode,b.unitname,b.fathercorp,pk_corp from bd_corp b
3 where b.pk_corp
4 in (SELECT v.pk_corp FROM v_sm_userpower1 v where v.fun_code like '20021025%'
5 and v.fun_name='查询' and v.userid='0001AA10000000000UYQ')
6 order by b.unitcode
7 /
正确的结果：
UNITCODE UNITNAME FATHERCORP PK_CORP
------------ ---------------------------------------------- ---------- -------
01 红河州烟草公司（本部） 1001
0101 红河州烟草公司弥勒经营部 1001 1002
0102 红河州烟草公司泸西经营部 1001 1003
0103 红河州烟草公司建水经营部 1001 1004
0104 红河州烟草公司红河营销部 1001 1005
0105 红河州烟草公司屏边营销部 1001 1006
0106 红河州烟草公司蒙自经营部 1001 1007
0107 红河州烟草公司石屏经营部 1001 1008
0108 红河州烟草公司个旧经营部 1001 1009
0109 红河州烟草公司开远经营部 1001 1010
0110 红河州烟草公司河口营销部 1001 1011
0111 红河州烟草公司金平营销部 1001 1012
0112 红河州烟草公司泸西复烤厂 1001 1013
13 rows selected
使用HINT：
SQL> select /*+use_hash(b,v)*/ b.unitcode,b.unitname,b.fathercorp,pk_corp from bd_corp b
2 where b.pk_corp
3 in (SELECT distinct v.pk_corp FROM v_sm_userpower1 v where v.fun_code like '20021025%'
4 and v.fun_name='查询' and v.userid='0001AA10000000000UYQ')
5 order by b.unitcode
6 /
正确的结果：
UNITCODE UNITNAME FATHERCORP PK_CORP
------------ ------------------------------------------ ---------- -------
01 红河州烟草公司（本部） 1001
0101 红河州烟草公司弥勒经营部 1001 1002
0102 红河州烟草公司泸西经营部 1001 1003
0103 红河州烟草公司建水经营部 1001 1004
0104 红河州烟草公司红河营销部 1001 1005
0105 红河州烟草公司屏边营销部 1001 1006
0106 红河州烟草公司蒙自经营部 1001 1007
0107 红河州烟草公司石屏经营部 1001 1008
0108 红河州烟草公司个旧经营部 1001 1009
0109 红河州烟草公司开远经营部 1001 1010
0110 红河州烟草公司河口营销部 1001 1011
0111 红河州烟草公司金平营销部 1001 1012
0112 红河州烟草公司泸西复烤厂 1001 1013
13 rows selected
Rows Row Source Operation
------- ---------------------------------------------------
13 SORT ORDER BY
13 HASH JOIN SEMI
13 TABLE ACCESS FULL BD_CORP －－改变了连接方式和driving table
27 VIEW
27 SORT UNIQUE
27 UNION-ALL
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS FULL SM_USERGROUPPOWER
0 TABLE ACCESS BY INDEX ROWID SM_USER_RELA
0 INDEX RANGE SCAN IX_SM_USER_RELA (object id 288328)
0 TABLE ACCESS BY INDEX ROWID SM_FUNCREGISTER
0 INDEX UNIQUE SCAN PK_SM_FUNCREGISTER (object id 288306)
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS FULL SM_USERGROUPPOWER
0 TABLE ACCESS BY INDEX ROWID SM_USER_RELA
0 INDEX RANGE SCAN IX_SM_USER_RELA (object id 288328)
0 TABLE ACCESS BY INDEX ROWID SM_BUTNREGISTER
0 INDEX UNIQUE SCAN PK_SM_BUTNREGISTER (object id 288289)
14 TABLE ACCESS BY INDEX ROWID SM_APPUSERPOWER
16 NESTED LOOPS
1 TABLE ACCESS BY INDEX ROWID SM_FUNCREGISTER
1 INDEX RANGE SCAN I_SM_FUNCREGISTER (object id 288305)
14 INDEX RANGE SCAN I_APPUSERPOWER_1 (object id 288281)
13 TABLE ACCESS BY INDEX ROWID SM_APPUSERPOWER
15 NESTED LOOPS
1 TABLE ACCESS FULL SM_BUTNREGISTER
13 INDEX RANGE SCAN I_APPUSERPOWER_1 (object id 288281)
采用分析统计信息的方法来解决这个问题：
exec dbms_utility.analyze_schema('HHYC','compute');
SQL> select /*+all_rows*/
2 b.unitcode,b.unitname,b.fathercorp,pk_corp from bd_corp b
3 where b.pk_corp
4 in (SELECT v.pk_corp FROM v_sm_userpower1 v where v.fun_code like '20021025%'
5 and v.fun_name='查询' and v.userid='0001AA10000000000UYQ')
6 order by b.unitcode
7 /
正确的结果：
UNITCODE UNITNAME FATHERCORP PK_CORP
------------ ---------------------------------------- ---------- -------
01 红河州烟草公司（本部） 1001
0101 红河州烟草公司弥勒经营部 1001 1002
0102 红河州烟草公司泸西经营部 1001 1003
0103 红河州烟草公司建水经营部 1001 1004
0104 红河州烟草公司红河营销部 1001 1005
0105 红河州烟草公司屏边营销部 1001 1006
0106 红河州烟草公司蒙自经营部 1001 1007
0107 红河州烟草公司石屏经营部 1001 1008
0108 红河州烟草公司个旧经营部 1001 1009
0109 红河州烟草公司开远经营部 1001 1010
0110 红河州烟草公司河口营销部 1001 1011
0111 红河州烟草公司金平营销部 1001 1012
0112 红河州烟草公司泸西复烤厂 1001 1013
13 rows selected
Rows Row Source Operation
------- ---------------------------------------------------
13 SORT ORDER BY
13 HASH JOIN SEMI
13 TABLE ACCESS FULL BD_CORP －－分析了统计信息，改变all_rows下原有的执行计划
27 VIEW
27 SORT UNIQUE
27 UNION-ALL
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS FULL SM_USERGROUPPOWER
0 TABLE ACCESS BY INDEX ROWID SM_USER_RELA
0 INDEX RANGE SCAN IX_SM_USER_RELA (object id 288328)
0 TABLE ACCESS BY INDEX ROWID SM_FUNCREGISTER
0 INDEX UNIQUE SCAN PK_SM_FUNCREGISTER (object id 288306)
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS FULL SM_USERGROUPPOWER
0 TABLE ACCESS BY INDEX ROWID SM_USER_RELA
0 INDEX RANGE SCAN IX_SM_USER_RELA (object id 288328)
0 TABLE ACCESS BY INDEX ROWID SM_BUTNREGISTER
0 INDEX UNIQUE SCAN PK_SM_BUTNREGISTER (object id 288289)
14 TABLE ACCESS BY INDEX ROWID SM_APPUSERPOWER
16 NESTED LOOPS
1 TABLE ACCESS BY INDEX ROWID SM_FUNCREGISTER
1 INDEX RANGE SCAN I_SM_FUNCREGISTER (object id 288305)
14 INDEX RANGE SCAN I_APPUSERPOWER_1 (object id 288281)
13 TABLE ACCESS BY INDEX ROWID SM_APPUSERPOWER
15 NESTED LOOPS
1 TABLE ACCESS FULL SM_BUTNREGISTER
13 INDEX RANGE SCAN I_APPUSERPOWER_1 (object id 288281)
oracle9204下的情况：
select /*+all_rows*/ b.unitcode,b.unitname,b.fathercorp,pk_corp from bd_corp b
where b.pk_corp
in (SELECT distinct v.pk_corp FROM v_sm_userpower1 v where v.fun_code like '20021025%'
and v.fun_name='查询' and v.userid='0001AA10000000000UYQ')
order by b.unitcode
UNITCODE UNITNAME FATHERCORP PK_CORP
------------ ---------------------------------------------------- ---------- -------
01 红河州烟草公司（本部） 1001
0101 红河州烟草公司弥勒经营部 1001 1002
0102 红河州烟草公司泸西经营部 1001 1003
0103 红河州烟草公司建水经营部 1001 1004
0104 红河州烟草公司红河营销部 1001 1005
0105 红河州烟草公司屏边营销部 1001 1006
0106 红河州烟草公司蒙自经营部 1001 1007
0107 红河州烟草公司石屏经营部 1001 1008
0108 红河州烟草公司个旧经营部 1001 1009
0109 红河州烟草公司开远经营部 1001 1010
0110 红河州烟草公司河口营销部 1001 1011
0111 红河州烟草公司金平营销部 1001 1012
0112 红河州烟草公司泸西复烤厂 1001 1013
13 rows selected
Rows Row Source Operation
------- ---------------------------------------------------
26 SORT ORDER BY
26 NESTED LOOPS
27 VIEW
27 SORT UNIQUE
27 UNION-ALL
0 TABLE ACCESS BY INDEX ROWID SM_USERGROUPPOWER
1 NESTED LOOPS
0 MERGE JOIN CARTESIAN
0 TABLE ACCESS BY INDEX ROWID SM_USER_RELA
0 INDEX RANGE SCAN IX_SM_USER_RELA (object id 32028)
0 BUFFER SORT
0 TABLE ACCESS BY INDEX ROWID SM_FUNCREGISTER
0 INDEX RANGE SCAN I_SM_FUNCREGISTER (object id 32005)
0 INDEX RANGE SCAN I_SM_USERGROUPPOWE (object id 32023)
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS FULL SM_USERGROUPPOWER
0 TABLE ACCESS BY INDEX ROWID SM_USER_RELA
0 INDEX RANGE SCAN IX_SM_USER_RELA (object id 32028)
0 TABLE ACCESS BY INDEX ROWID SM_BUTNREGISTER
0 INDEX UNIQUE SCAN PK_SM_BUTNREGISTER (object id 31993)
14 NESTED LOOPS
60369 TABLE ACCESS BY INDEX ROWID SM_APPUSERPOWER
60369 INDEX RANGE SCAN IX_SM_APPUSERPOWER (object id 31986)
14 TABLE ACCESS BY INDEX ROWID SM_FUNCREGISTER
7031 INDEX UNIQUE SCAN PK_SM_FUNCREGISTER (object id 32006)
13 NESTED LOOPS
60369 TABLE ACCESS BY INDEX ROWID SM_APPUSERPOWER
60369 INDEX RANGE SCAN IX_SM_APPUSERPOWER (object id 31986)
13 TABLE ACCESS BY INDEX ROWID SM_BUTNREGISTER
53338 INDEX UNIQUE SCAN PK_SM_BUTNREGISTER (object id 31993)
26 TABLE ACCESS BY INDEX ROWID BD_CORP
26 INDEX UNIQUE SCAN PK_BD_CORP (object id 30491)
我们发现，在9204下，该sql使用了和9201上一样的执行计划，但是得到了不同的结果！
根据以上各种情况的实验，我推断：该问题应该出在oracle在all_rows上对于nl执行时驱动表选择的特殊情况下的bug。用提示交换驱动表的顺序后就正确了。
在9203上执行该sql，执行结果不稳定，还是有出错的可能性。
9204以前的版本尤其是all_rows下对内嵌视图有很多bug。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
