[20231225]Descending Bug.txt

lfree發表於2023-12-26

[20231225]Descending Bug.txt

--//
--//重複測試驗證看看:

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.
--//上一些解析:

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.
另一方面，這三個謂詞值應該在內部列表迭代器操作時(以某種形式)被報告是有爭議的——因此，這可能是表示計劃的最簡單策略與計劃
的實際活動不匹配的情況。

來自 “ ITPUB部落格 ” ，連結：https://blog.itpub.net/267265/viewspace-3001689/，如需轉載，請註明出處，否則將追究法律責任。

相關文章