[20241216]Oracle wrong result一则(优化器问题)2.txt

--//链接提到的问题https://www.cnblogs.com/PiscesCanon/p/12723513.html，自己测试看看。

1.环境：
SCOTT@book> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//注：在21c测试不出来问题。

2.问题提出：
SCOTT@book> create table test as select * from dba_objects;
Table created.
--//分析略。

SCOTT@book> with t as (select a.*,rownum rn from dba_objects a) select count(*) from t where t.rn=(select max(rn) from t);
  COUNT(*)
----------
         1

SCOTT@book> with t as (select a.*,rownum rn from test a) select count(*) from t where t.rn=(select max(rn) from t);
  COUNT(*)
----------
         0
--//理论讲至少存在1行。为什么呢？如果对比执行计划可以发现前者使用类似MATERIALIZE的提示，执行计划出现 TEMP TABLE TRANSFORMATION。
--//而后者没有出现。加入提示/MATERIALIZE也可以纠正这个错误。

COTT@book> with t as (select /*+ MATERIALIZE */ a.*,rownum rn from test a) select count(*) from t where t.rn=(select max(rn) from t);
  COUNT(*)
----------
         1

--//既然真实的表执行测试出现问题，可以直接采用真实表测试。

SCOTT@book> create table ta as select  rownum id  from dual ;
Table created.
--//分析表略。

SCOTT@book> with t as (select a.*,rownum rn from ta a) select * from t where t.rn=(select max(rn) from t);
        ID         RN
---------- ----------
         1          1
--//可以发现当表有1条记录时，执行结果是正确的。

SCOTT@book> insert into ta values(2);
1 row created.

SCOTT@book> commit;
Commit complete.
--//插入1条记录，分析表略。

SCOTT@book> with t as (select a.*,rownum rn from ta a) select * from t where t.rn=(select max(rn) from t);
no rows selected

--//可以发现当表有2条记录时，执行结果是错误的，为什么呢？

3.分析：
--//查看执行计划：
SCOTT@book> @ dpc '' outline ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  fytayvxpfzz5w, child number 0
-------------------------------------
with t as (select a.*,rownum rn from ta a) select * from t where
t.rn=(select max(rn) from t)
Plan hash value: 187064592
-------------------------------------------------------------------------------
| Id  | Operation             | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |        |       |     8 (100)|          |
|*  1 |  VIEW                 |      |      2 |    52 |     4   (0)| 00:00:01 |
|   2 |   COUNT               |      |        |       |            |          |
|   3 |    TABLE ACCESS FULL  | TA   |      2 |     6 |     4   (0)| 00:00:01 |
|   4 |   SORT AGGREGATE      |      |      1 |    13 |            |          |
|   5 |    VIEW               |      |      2 |    26 |     4   (0)| 00:00:01 |
|   6 |     COUNT             |      |        |       |            |          |
|   7 |      TABLE ACCESS FULL| TA   |      2 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$EA29B7B6 / T@SEL$2
   2 - SEL$EA29B7B6
   3 - SEL$EA29B7B6 / A@SEL$EA29B7B6
   4 - SEL$3
   5 - SEL$1        / T@SEL$3
   6 - SEL$1
   7 - SEL$1        / A@SEL$1

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$EA29B7B6")
      OUTLINE_LEAF(@"SEL$2")
      NO_ACCESS(@"SEL$2" "T"@"SEL$2")
      PUSH_SUBQ(@"SEL$3")
      FULL(@"SEL$EA29B7B6" "A"@"SEL$EA29B7B6")
      NO_ACCESS(@"SEL$3" "T"@"SEL$3")
      FULL(@"SEL$1" "A"@"SEL$1")
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("T"."RN"=)

--//我开始也怀疑Predicate Information 看到的过滤条件，没有信息，不过使用explain plan看有信息。

SCOTT@book> with t as (select a.*,rownum rn from ta a) select * from t where t.rn=(select max(rn) from t);
no rows selected

SCOTT@book> @ hash

HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
1794112700 fytayvxpfzz5w            0     130236       187064592  6aeffcbc  2024-12-16 09:38:53    16777220

SCOTT@book> @ expand_sql_text11g.sql fytayvxpfzz5w
PL/SQL procedure successfully completed.
M_SQL_OUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT "A1"."ID" "ID","A1"."RN" "RN" FROM  (SELECT "A4"."ID" "ID",ROWNUM "RN" FROM "SCOTT"."TA" "A4") "A1" WHERE "A1"."RN"= (SELECT MAX("A2"."RN") "MAX(RN)" FRO
M  (SELECT "A3"."ID" "ID",ROWNUM "RN" FROM "SCOTT"."TA" "A3") "A2")

--//格式化如下：
SELECT COUNT (*) "COUNT(*)"
  FROM (SELECT "A4"."ID" "ID"
              ,ROWNUM "RN"
          FROM "SCOTT"."TA" "A4") "A1"
 WHERE "A1"."RN" = (SELECT MAX ("A2"."RN") "MAX(RN)"
                      FROM (SELECT "A3"."ID" "ID"
                                  ,ROWNUM "RN"
                              FROM "SCOTT"."TA" "A3") "A2");

COUNT(*)
----------
         1
 
--//当我使用展开的sql语句执行结果又是正确的。

SELECT *
  FROM (SELECT "A4"."ID" "ID"
              ,ROWNUM "RN"
          FROM "SCOTT"."TA" "A4") "A1"
 WHERE "A1"."RN" = (SELECT MAX ("A2"."RN") "MAX(RN)"
                      FROM (SELECT "A3"."ID" "ID"
                                  ,ROWNUM "RN"
                              FROM "SCOTT"."TA" "A3") "A2");

--//仔细看前面的执行计划id=2，6存在两次count，因为没有使用MATERIALIZE提示，结果集不确定(也就是rownum对应的rn在变)。
--//如何验证我的推测呢？执行如下sql语句：

SCOTT@book> with t as (select a.*,rownum rn from ta a) select * from t where t.id=(select count(*) from t);
        ID         RN
---------- ----------
         2          3

--//注意看rn=3.不是2.(感觉应该是4)，不过至少说明select * from t中rn还在变化，只能讲11g这个版本存在bug。
--//这样就很容易理解，为什么表仅仅1条记录时，有显示输出，第2次计数从1开始，这样有输出。
--//注意看前面rn=3，第2次计数从2开始,我的理解。

SCOTT@book> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
2975981936 5bv0gm6sq3sbh            0     123248       187064592  b161e170  2024-12-16 09:45:19    16777217

SCOTT@book> @ expand_sql_text11g.sql 5bv0gm6sq3sbh
PL/SQL procedure successfully completed.

M_SQL_OUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT "A1"."ID" "ID","A1"."RN" "RN" FROM  (SELECT "A4"."ID" "ID",ROWNUM "RN" FROM "SCOTT"."TA" "A4") "A1" WHERE "A1"."ID"= (SELECT COUNT(*) "COUNT(*)" FROM  (S
ELECT "A3"."ID" "ID",ROWNUM "RN" FROM "SCOTT"."TA" "A3") "A2")

SCOTT@book> SELECT "A1"."ID" "ID","A1"."RN" "RN" FROM  (SELECT "A4"."ID" "ID",ROWNUM "RN" FROM "SCOTT"."TA" "A4") "A1" WHERE "A1"."ID"= (SELECT COUNT(*) "COUNT(*)" FROM  (SELECT "A3"."ID" "ID",ROWNUM "RN" FROM "SCOTT"."TA" "A3") "A2");
        ID         RN
---------- ----------
         2          2
--//展开执行是正确的。

4.看看 dbms_sqldiag的诊断分析：

SCOTT@book> @ 10053x 5bv0gm6sq3sbh 0
PL/SQL procedure successfully completed.

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T"."ID" "ID","T"."RN" "RN"
  FROM (SELECT "A"."ID" "ID",ROWNUM "RN" FROM "SCOTT"."TA" "A") "T"
 WHERE "T"."ID" = (SELECT COUNT(*) "COUNT(*)"
  FROM (SELECT ROWNUM "RN" FROM "SCOTT"."TA" "A") "T")

SCOTT@book> with t as (select a.*,rownum rn from ta a) select * from t where t.rn=(select max(rn) from t);
no rows selected

SCOTT@book> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
1794112700 fytayvxpfzz5w            0     130236       187064592  6aeffcbc  2024-12-16 09:52:12    16777221

SCOTT@book> @ 10053x fytayvxpfzz5w 0
PL/SQL procedure successfully completed.

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T"."ID" "ID","T"."RN" "RN"
  FROM (SELECT "A"."ID" "ID",ROWNUM "RN" FROM "SCOTT"."TA" "A") "T"
 WHERE "T"."RN" = (SELECT MAX("T"."RN") "MAX(RN)"
  FROM (SELECT ROWNUM "RN" FROM "SCOTT"."TA" "A") "T")
--//按照展开执行结果是正确的。这样看来11g并不是按照展开的方式执行。

5.附上测试使用相关sql脚本：

$ cat 10053x.sql
execute dbms_sqldiag.dump_trace(p_sql_id=>'&1',p_child_number=>&2,p_component=>'Compiler',p_file_id=>'a'||'&&1');

$ cat expand_sql_text11g.sql
variable m_sql_out clob

declare
    m_sql_in  clob ;
begin
        select sql_fulltext into m_sql_in from v$sqlarea where sql_id='&&1';
        dbms_sql2.expand_sql_text(        -- 11g
                m_sql_in,
                :m_sql_out
        );
--      if version = '12' then
--        select sql_fulltext into m_sql_in from v$sqlarea where sql_id='&&1';
--        dbms_utility.expand_sql_text(     -- 12c
--                m_sql_in,
--                :m_sql_out
--        );
--      end if;
end;
/
set long 20000
column m_sql_out format a160
print m_sql_out