[20201110]FBI Bug reprise.txt

来源：这里教程网时间：2026-03-03 16:16:20 作者：

[20201110]FBI Bug reprise.txt --//2015年的老帖子，链接：https://jonathanlewis.wordpress.com/2015/01/12/fbi-bug-reprise/ --//好奇测试看看。 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的情况。放弃太复杂了。