[20201110]FBI Bug reprise.txt

[20201110]FBI Bug reprise.txt

--//2015年的老帖子，連結：
--//好奇測試看看。

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

rem
rem Script: descending_bug_04.sql
rem Author: Jonathan Lewis
rem Dated: Jan 2015
rem

create table t1
nologging
as
with generator as (
    select  --+ materialize
        rownum id
    from dual
    connect by
        level <= 1e4
)
select
    rownum          id,
    cast(dbms_random.string('U',2) as char(2))  c1,
    cast(dbms_random.string('U',2) as char(2))  c2,
    cast(dbms_random.string('U',2) as char(2))  c3,
    cast(dbms_random.string('U',2) as char(2))  c4,
    rpad('x',100)       padding
from
    generator   v1,
    generator   v2
where
    rownum  <= 1e5    -- > comment to avoid wordpress formatting issue
;

begin
    dbms_stats.gather_table_stats(
        ownname      => user,
        tabname      =>'T1',
        method_opt   => 'for all columns size 1'
    );
end;
/

create index t1_iasc  on t1(c1, c2,      c3, c4) nologging;
create index t1_idesc on t1(c1, c2 desc, c3, c4) nologging;

2.測試：
SCOTT@book> alter session set statistics_level = all;
Session altered.

$ cat aaa.sql
select
        *
from t1
where
        (C1 = 'DE' and C2 >  'AB')
or      (C1 = 'DE' and C2 >= 'AB' and C3 > 'AA' )
or      (C1 = 'DE' and C2 >= 'AB' and C3 >= 'AA' and C4 >= 'BB')
order by
        C1, C2, C3, C4
;

Plan hash value: 263105257
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 |        |       |    24 (100)|          |    144 |00:00:00.01 |     147 |       |       |          |
|   1 |  SORT ORDER BY               |          |      1 |    148 | 17464 |    24   (5)| 00:00:01 |    144 |00:00:00.01 |     147 | 46080 | 46080 |40960  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T1       |      1 |    148 | 17464 |    23   (0)| 00:00:01 |    144 |00:00:00.01 |     147 |       |       |          |
|*  3 |    INDEX RANGE SCAN          | T1_IDESC |      1 |     21 |       |     2   (0)| 00:00:01 |    144 |00:00:00.01 |       3 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------

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("C1"='DE')
       filter(((SYS_OP_UNDESCEND("T1"."SYS_NC00007$")>'AB' AND "T1"."SYS_NC00007$"<SYS_OP_DESCEND('AB')) OR ("C3">'AA' AND
              SYS_OP_UNDESCEND("T1"."SYS_NC00007$")>='AB' AND "T1"."SYS_NC00007$"<=SYS_OP_DESCEND('AB')) OR ("C4">='BB' AND
              SYS_OP_UNDESCEND("T1"."SYS_NC00007$")>='AB' AND "C3">='AA' AND "T1"."SYS_NC00007$"<=SYS_OP_DESCEND('AB'))))

--//嗯，我的測試的執行計劃與原連結不一樣，使用的是T1_IDESC降序索引。cost與連結一致。難道真實的情況就是使用T1_IDESC索引嗎？

SCOTT@book> alter index t1_idesc   invisible;
Index altered.

--//重複執行：

Plan hash value: 2707920069
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |       |   150 (100)|          |    144 |00:00:00.01 |     148 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |      1 |    148 | 17464 |   150   (0)| 00:00:02 |    144 |00:00:00.01 |     148 |
|*  2 |   INDEX RANGE SCAN          | T1_IASC |      1 |    148 |       |     2   (0)| 00:00:01 |    144 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T1@SEL$1
   2 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"='DE')
       filter((("C2">'AB' AND SYS_OP_DESCEND("C2")<SYS_OP_DESCEND('AB')) OR ("C3">'AA' AND "C2">='AB' AND
              SYS_OP_DESCEND("C2")<=SYS_OP_DESCEND('AB')) OR ("C4">='BB' AND "C2">='AB' AND "C3">='AA' AND
              SYS_OP_DESCEND("C2")<=SYS_OP_DESCEND('AB'))))
--//仔細看過濾條件很奇怪，會使用SYS_OP_DESCEND函式。
--//刪除索引看看。
SCOTT@book>  drop index t1_idesc;
Index dropped.

--//重複執行

Plan hash value: 2707920069
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |       |   150 (100)|          |    144 |00:00:00.01 |     148 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |      1 |    148 | 17464 |   150   (0)| 00:00:02 |    144 |00:00:00.01 |     148 |
|*  2 |   INDEX RANGE SCAN          | T1_IASC |      1 |    148 |       |     2   (0)| 00:00:01 |    144 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T1@SEL$1
   2 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"='DE')
       filter(("C2">'AB' OR ("C3">'AA' AND "C2">='AB') OR ("C4">='BB' AND "C2">='AB' AND "C3">='AA')))

--//奇怪現在就不出現使用SYS_OP_DESCEND函式的情況。真心搞不懂oracle最佳化器如何工作的。

3.繼續：
SCOTT@book> create index t1_idesc on t1(c1, c2 desc, c3, c4) nologging;
Index created.

$ cat aaa.sql
select /*+ first_rows */
        *
from t1
where
        (C1 = 'DE' and C2 >  'AB')
or      (C1 = 'DE' and C2 >= 'AB' and C3 > 'AA' )
or      (C1 = 'DE' and C2 >= 'AB' and C3 >= 'AA' and C4 >= 'BB')
order by
        C1, C2, C3, C4
;


Plan hash value: 2707920069
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |       |   150 (100)|          |    144 |00:00:00.01 |     148 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |      1 |    148 | 17464 |   150   (0)| 00:00:02 |    144 |00:00:00.01 |     148 |
|*  2 |   INDEX RANGE SCAN          | T1_IASC |      1 |    148 |       |     2   (0)| 00:00:01 |    144 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T1@SEL$1
   2 - SEL$1 / T1@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      FIRST_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."C1" "T1"."C2" "T1"."C3" "T1"."C4"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"='DE')
       filter((("C2">'AB' AND SYS_OP_DESCEND("C2")<SYS_OP_DESCEND('AB')) OR ("C3">'AA' AND "C2">='AB' AND
              SYS_OP_DESCEND("C2")<=SYS_OP_DESCEND('AB')) OR ("C4">='BB' AND "C2">='AB' AND "C3">='AA' AND
              SYS_OP_DESCEND("C2")<=SYS_OP_DESCEND('AB'))))

--//執行計劃並沒有出現CONCATENATION的情況。放棄太複雜了。