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

[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