当前位置：首页 > news >正文

崖山数据库-谓词没提前过滤优化器BUG

news 2026/6/7 1:07:40

数据库版本崖山23.5.1

SQL> select * from v$version; BANNER VERSION_NUMBER ---------------------------------------------------------------- ----------------- Enterprise Edition Release 23.5.1.100 x86_64 23.5.1.100 1 row fetched.

测试SQL

select * from test02 t2 where t2.data_object_id = (select max(data_object_id) from test01 t3 where t3.object_id = t2.object_id) and t2.owner = 'SCOTT';

执行计划

SQL> select * from test02 t2 where t2.data_object_id = (select max(data_object_id) from test01 t3 where t3.object_id = t2.object_id) and t2.owner = 'SCOTT'; 2 3 4 5 6 7 Execution Plan ---------------------------------------------------------------- SQL hash value: 3493843295 Optimizer: ADOPT_C +----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+ | Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info | +----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+ | 0 | SELECT STATEMENT | | | | 31| | 14233163| 32| 0| 0| | | 1 | SUBQUERY | QUERY[1] | | | 86987| | 14097886| 173974| 0| 0| | | 2 | AGGREGATE | | | 1| 86987| 5( 0)| 14069037| 173974| 0| 0| | |* 3 | TABLE ACCESS BY INDEX ROWID | TEST01 | SCOTT | 145| | 5( 0)| | | | | | |* 4 | INDEX RANGE SCAN | IDX_TEST01_OBJECT_ID | SCOTT | 446| 4696064| 1( 0)| 13655265| 4783051| 0| 0| | |* 5 | TABLE ACCESS FULL | TEST02 | SCOTT | 1| 31| 238( 0)| 14233153| 32| 0| 0| | +----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+ Operation Information (identified by operation id): --------------------------------------------------- 1 - Subquery NDV info - NDV percentage: 0.000011, NDV Expression: ("T2"."OBJECT_ID"[OPTMZ-1]) 3 - Predicate : filter("T3"."DATA_OBJECT_ID"[OPTMZ-1] IS NOT NULL) 4 - Predicate : access("T3"."OBJECT_ID" = "T2"."OBJECT_ID"[OPTMZ-1][OPTMZ-2]) 5 - Predicate : filter("T2"."DATA_OBJECT_ID"[OPTMZ-1] = QUERY[1][OPTMZ-0] AND "T2"."OWNER"[OPTMZ-1] = 'SCOTT'[OPTMZ-0]) Statistics ---------------------------------------------------------------------------------------------------- 0 physical reads 44795489 db block gets 0 consistent gets 0 redo size 0 recursive calls 0 bytes sent via SQL*Net to client 0 bytes received via SQL*Net from client 0 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 31 rows processed 0 bytes sent via PX 0 block received 34 rows fetched. Elapsed: 00:00:14.238

SQL耗时14秒，正常来说应该秒杀才对

改写(方法一)

SQL> select * from (select * from test02 where owner = 'SCOTT' and rownum >= 1) t2 where t2.data_object_id = (select max(data_object_id) from test01 t3 where t3.object_id = t2.object_id); 2 3 4 5 6 7 8 9 Execution Plan ---------------------------------------------------------------- SQL hash value: 2360477481 Optimizer: ADOPT_C +----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+ | Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info | +----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+ | 0 | SELECT STATEMENT | | | | 31| | 26400| 32| 0| 0| | | 1 | SUBQUERY | QUERY[1] | | | 36| | 20842| 72| 0| 0| | | 2 | AGGREGATE | | | 1| 36| 5( 0)| 20745| 72| 0| 0| | |* 3 | TABLE ACCESS BY INDEX ROWID | TEST01 | SCOTT | 145| | 5( 0)| | | | | | |* 4 | INDEX RANGE SCAN | IDX_TEST01_OBJECT_ID | SCOTT | 446| 15872| 1( 0)| 19480| 15908| 0| 0| | |* 5 | VIEW | | | 12| 31| 223( 0)| 26398| 32| 0| 0| | | 6 | COUNT | | | 12| 36| 216( 0)| 5418| 37| 0| 0| | |* 7 | RESULT | | | 12| 36| 216( 0)| 5410| 37| 0| 0| | |* 8 | TABLE ACCESS FULL | TEST02 | SCOTT | 36| 36| 216( 0)| 5390| 37| 0| 0| | +----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+ Operation Information (identified by operation id): --------------------------------------------------- 3 - Predicate : filter("T3"."DATA_OBJECT_ID"[OPTMZ-1] IS NOT NULL) 4 - Predicate : access("T3"."OBJECT_ID" = "TEST02"."OBJECT_ID"[OPTMZ-3][OPTMZ-2]) 5 - Predicate : filter("TEST02"."DATA_OBJECT_ID"[OPTMZ-3] = QUERY[1][OPTMZ-0]) 7 - Predicate : filter(ROWNUM[OPTMZ-0] >= 1[OPTMZ-0]) 8 - Predicate : filter("TEST02"."OWNER"[OPTMZ-1] = 'SCOTT'[OPTMZ-0]) Statistics ---------------------------------------------------------------------------------------------------- 0 physical reads 19859 db block gets 0 consistent gets 0 redo size 0 recursive calls 0 bytes sent via SQL*Net to client 0 bytes received via SQL*Net from client 0 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 31 rows processed 0 bytes sent via PX 0 block received 38 rows fetched. Elapsed: 00:00:00.029

改写(方法二)

SQL> select * from (select t2.*, (select max(data_object_id) from test01 t3 where t3.object_id = t2.object_id) max_data_object_id from test02 t2) where data_object_id = max_data_object_id and own 2 3 4 5 6 7 8 er = 'SCOTT'; Execution Plan ---------------------------------------------------------------- SQL hash value: 1214109186 Optimizer: ADOPT_C +----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+ | Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info | +----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+ | 0 | SELECT STATEMENT | | | | 31| | 19245| 32| 0| 0| | | 1 | SUBQUERY | QUERY[1] | | | 31| | 13584| 62| 0| 0| | | 2 | AGGREGATE | | | 1| 31| 5( 0)| 13568| 62| 0| 0| | |* 3 | TABLE ACCESS BY INDEX ROWID | TEST01 | SCOTT | 145| | 5( 0)| | | | | | |* 4 | INDEX RANGE SCAN | IDX_TEST01_OBJECT_ID | SCOTT | 446| 15872| 1( 0)| 12120| 15903| 0| 0| | |* 5 | VIEW | | | 12| 31| 225( 0)| 19242| 32| 0| 0| | |* 6 | TABLE ACCESS FULL | TEST02 | SCOTT | 12| 31| 218( 0)| 5533| 32| 0| 0| | +----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+ Operation Information (identified by operation id): --------------------------------------------------- 3 - Predicate : filter("T3"."DATA_OBJECT_ID"[OPTMZ-1] IS NOT NULL) 4 - Predicate : access("T3"."OBJECT_ID" = "T2"."OBJECT_ID"[OPTMZ-3][OPTMZ-2]) 5 - Predicate : filter("T2"."DATA_OBJECT_ID"[OPTMZ-3] = QUERY[1][OPTMZ-2]) 6 - Predicate : filter("T2"."OWNER"[OPTMZ-1] = 'SCOTT'[OPTMZ-0] AND "T2"."DATA_OBJECT_ID"[OPTMZ-1] IS NOT NULL) Statistics ---------------------------------------------------------------------------------------------------- 0 physical reads 17283 db block gets 0 consistent gets 0 redo size 0 recursive calls 0 bytes sent via SQL*Net to client 0 bytes received via SQL*Net from client 0 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 31 rows processed 0 bytes sent via PX 0 block received 35 rows fetched. Elapsed: 00:00:00.026

结论：看上去原始SQL没有提前过滤t2.owner = ‘SCOTT’，优化器有BUG

查看全文

http://www.jsqmd.com/news/564177/