[20231225]Descending Bug.txt

[20231225]Descending Bug.txt --//https://jonathanlewis.wordpress.com/2023/12/21/descending-bug-2/ --//重复测试验证看看: 1.环境: SCOTT@test01p> @ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 2.测试例子建立 create table t1 as with generator as (         select  rownum id         from    dual         connect by                 level <= 1e4 ) select         rownum                                  id,         substr(dbms_random.string('U',6),1,6)   v1,         rpad('x',100,'x')                       padding from         generator /   alter table t1 modify v1 not null;   update t1 set v1 = 'BRAVO'      where id = 5000; update t1 set v1 = 'MIKE'       where id = 1000; update t1 set v1 = 'YANKEE'     where id = 9000; commit ; create index t1_i1 on t1(v1 desc); 3.测试: SCOTT@test01p> @ sl all alter session set statistics_level = all; Session altered. SCOTT@test01p> select  v1, id from    t1 where   v1 in ( 'MIKE', 'YANKEE', 'BRAVO') order by v1 ; V1       ID ------ ---- YANKEE 9000 MIKE   1000 BRAVO  5000 --//可以发现输出结果并没有按照v1排序,而是相反顺序输出.可以将降序索引的bug在这个版本是存在的. SCOTT@test01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  fzyn715b7sq20, child number 0 ------------------------------------- select  v1, id from    t1 where   v1 in ( 'MIKE', 'YANKEE', 'BRAVO') order by v1 Plan hash value: 4226741654 --------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                     | Name  | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | --------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT              |       |      1 |        |       |    43 (100)|          |      3 |00:00:00.01 |       9 | |   1 |  INLIST ITERATOR              |       |      1 |        |       |            |          |      3 |00:00:00.01 |       9 | |   2 |   TABLE ACCESS BY INDEX ROWID | T1    |      3 |      3 |    33 |    43   (0)| 00:00:01 |      3 |00:00:00.01 |       9 | |*  3 |    INDEX RANGE SCAN DESCENDING| T1_I1 |      3 |     40 |       |     3   (0)| 00:00:01 |      3 |00:00:00.01 |       6 | --------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$1 / T1@SEL$1    3 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    3 - access(("T1"."SYS_NC00004$"=HEXTORAW('BDADBEA9B0FF') OR "T1"."SYS_NC00004$"=HEXTORAW('B2B6B4BAFF') OR               "T1"."SYS_NC00004$"=HEXTORAW('A6BEB1B4BABAFF')))        filter((SYS_OP_UNDESCEND("T1"."SYS_NC00004$")='BRAVO' OR SYS_OP_UNDESCEND("T1"."SYS_NC00004$")='MIKE' OR               SYS_OP_UNDESCEND("T1"."SYS_NC00004$")='YANKEE')) 31 rows selected. --//尝试加入提示index(t1 t1_i1): SCOTT@test01p> select  /*+ index(t1 t1_i1) */  v1, id from t1 where v1 in ('MIKE','YANKEE','BRAVO') order by  v1; V1                   ID ------------ ---------- BRAVO              5000 MIKE               1000 YANKEE             9000 --//可以发现输出结果正确! SCOTT@test01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  50920pyr8518u, child number 0 ------------------------------------- select  /*+ index(t1 t1_i1) */  v1, id from t1 where v1 in ('MIKE','YANKEE','BRAVO') order by  v1 Plan hash value: 1337030419 -------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                             | Name  | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                      |       |      1 |        |       |    44 (100)|          |      3 |00:00:00.01 |       8 |       |       |          | |   1 |  SORT ORDER BY                        |       |      1 |      3 |    33 |    44   (3)| 00:00:01 |      3 |00:00:00.01 |       8 |  2048 |  2048 | 2048  (0)| |   2 |   INLIST ITERATOR                     |       |      1 |        |       |            |          |      3 |00:00:00.01 |       8 |       |       |          | |   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1    |      3 |      3 |    33 |    43   (0)| 00:00:01 |      3 |00:00:00.01 |       8 |       |       |          | |*  4 |     INDEX RANGE SCAN                  | T1_I1 |      3 |     40 |       |     3   (0)| 00:00:01 |      3 |00:00:00.01 |       5 |       |       |          | -------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    3 - SEL$1 / T1@SEL$1    4 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    4 - access(("T1"."SYS_NC00004$"=HEXTORAW('BDADBEA9B0FF') OR "T1"."SYS_NC00004$"=HEXTORAW('B2B6B4BAFF') OR               "T1"."SYS_NC00004$"=HEXTORAW('A6BEB1B4BABAFF')))        filter((SYS_OP_UNDESCEND("T1"."SYS_NC00004$")='BRAVO' OR SYS_OP_UNDESCEND("T1"."SYS_NC00004$")='MIKE' OR               SYS_OP_UNDESCEND("T1"."SYS_NC00004$")='YANKEE')) --//仔细看执行计划,仅仅出现INDEX RANGE SCAN. id=3变成了TABLE ACCESS BY INDEX ROWID BATCHED. 4.补充测试: --//测试v1 desc的输出结果. SCOTT@test01p> select  v1, id from    t1 where   v1 in ( 'MIKE', 'YANKEE', 'BRAVO') order by v1 desc ; V1                   ID ------------ ---------- BRAVO              5000 MIKE               1000 YANKEE             9000 --//可以发现结果输出是反了. SCOTT@test01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  05qr74tz972rn, child number 0 ------------------------------------- select  v1, id from    t1 where   v1 in ( 'MIKE', 'YANKEE', 'BRAVO') order by v1 desc Plan hash value: 3456773112 -------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                    | Name  | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | -------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |       |      1 |        |       |    43 (100)|          |      3 |00:00:00.01 |       9 | |   1 |  INLIST ITERATOR             |       |      1 |        |       |            |          |      3 |00:00:00.01 |       9 | |   2 |   TABLE ACCESS BY INDEX ROWID| T1    |      3 |      3 |    33 |    43   (0)| 00:00:01 |      3 |00:00:00.01 |       9 | |*  3 |    INDEX RANGE SCAN          | T1_I1 |      3 |     40 |       |     3   (0)| 00:00:01 |      3 |00:00:00.01 |       6 | -------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$1 / T1@SEL$1    3 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    3 - access(("T1"."SYS_NC00004$"=HEXTORAW('BDADBEA9B0FF') OR "T1"."SYS_NC00004$"=HEXTORAW('B2B6B4BAFF') OR               "T1"."SYS_NC00004$"=HEXTORAW('A6BEB1B4BABAFF')))        filter((SYS_OP_UNDESCEND("T1"."SYS_NC00004$")='BRAVO' OR SYS_OP_UNDESCEND("T1"."SYS_NC00004$")='MIKE' OR               SYS_OP_UNDESCEND("T1"."SYS_NC00004$")='YANKEE')) 31 rows selected. --//总之一些版本存在这个bug. --//https://jonathanlewis.wordpress.com/2023/12/21/descending-bug-2/上一些解析: Best Guess 最好的猜测 The way Oracle handles an IN-list is to start by reducing it to a sorted list of distinct items, before iterating through each item in turn. Then, if there is an order by clause that matches the order of the sorted in-list, and Oracle can walk the index in the right order then it can avoid a "sort order by" operation. Oracle处理in列表的方式是先将其简化为不同项的排序列表，然后依次遍历每个项。然后，如果排序顺序与排序列表中的顺序匹配， Oracle可以按照正确的顺序行走索引，那么它可以避免按操作顺序排序。 I'm guessing that there may be two separate optimizer strategies in the "descending columns" case that have collided and effectively cancelled each other out: 我猜在降列的情况中有两种相互碰撞并有效相互抵消的优化策略：     Hypothetical Strategy 1: If there is a "descending index" that can be range scanned for the data the in-list should     be sorted in descending order before iterating. (There is a flaw in this suggestion – see below)     假设策略1：如果存在降序索引，可以进行数据范围扫描，应在迭代之前按降序排序。(这个建议存在一个缺陷，见下文)     Hypothetical strategy 2: Because the query has an order by (ascending) clause the index scan should be in descending     order to avoid a sort operation.     假设策略2：由于查询的顺序是按(升序)子句排列，因此索引扫描应按降序排列，以避免执行排序操作。 The flaw in the first suggestion is that the Predicate Information suggests that it's not true. This is what you get in every case (though the operation number changes to 4 when the plan includes a "sort order by" operation): 第一个建议的缺陷是，谓词信息表明它不是真的。这是您在每种情况下得到的结果(尽管当计划包括按操作排序的顺序时，操作编号将变 为4)：      Predicate Information (identified by operation id): ---------------------------------------------------    3 - access(("T1"."SYS_NC00004$"=HEXTORAW('BDADBEA9B0FF') OR               "T1"."SYS_NC00004$"=HEXTORAW('B2B6B4BAFF') OR               "T1"."SYS_NC00004$"=HEXTORAW('A6BEB1B4BABAFF')))        filter((SYS_OP_UNDESCEND("T1"."SYS_NC00004$")='BRAVO' OR               SYS_OP_UNDESCEND("T1"."SYS_NC00004$")='MIKE' OR               SYS_OP_UNDESCEND("T1"."SYS_NC00004$")='YANKEE')) As you can see the values appearing in the access() predicate are the one's complements of BRAVO, MIKE and YANKEE in that order; in no case was the order reversed, and previous experience says that predicates are used in the order they appear in the Predicate Information. 正如您所看到的，在访问()谓词中出现的值是BRAVO、MIKE和YANKEE的补体；在任何情况下，顺序都不是颠倒的，以前的经验说，谓词是 按照它们在谓词信息中出现的顺序使用的。 On the other hand, it's arguable that the three predicate values should have been reported (in some form) at the inlist iterator operation – so this may be a case where the simplest strategy for presenting the plan doesn't match the actual activity of the plan. 另一方面，这三个谓词值应该在内部列表迭代器操作时(以某种形式)被报告是有争议的——因此，这可能是表示计划的最简单策略与计划 的实际活动不匹配的情况。