[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.
另一方面，这三个谓词值应该在内部列表迭代器操作时(以某种形式)被报告是有争议的——因此，这可能是表示计划的最简单策略与计划
的实际活动不匹配的情况。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3001689/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
