学习随笔：ORACLE：优化器缺陷

diannao/2025/9/27 4:54:26/文章来源:href="https://blog.51cto.com/luoping/14132084" target="_blank"

我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！由于博客中有大量代码，通过页面浏览效果更佳。

本文转自朋友的真实案例分享。

学习随笔：ORACLE：优化器缺陷

ORACLE的优化器，在众多dba眼中都是世界上最好的优化器，一直是其他数据库，包括国产数据库追赶的目标，特别是在查询转换上，oracle确实所向无敌。但是本文要介绍的一个小案例对于oracle来说应该算是一个美中不足的地方。

该sql是一条非常简单的分页sql，由于生产sql不便展示，我在自己的测试环境中复现了一下，发现及时最新的23ai优化器存在着这一点点的小不足。

sql文本非常的简单，T1的object_id列是存在索引的。

SELECT *
FROM (SELECT t1.object_name,t2.DATA_OBJECT_ID,t2.OBJECT_TYPE,t2.LAST_DDL_TIME,t1.STATUSFROM t1,t2WHERE t1.object_id = t2.object_idAND t2.OWNER = 'SYS'ORDER BY t2.LAST_DDL_TIME DESC)
WHERE rownum <= 20;

执行计划和执行消耗：

SQL> set autot trace
SQL> select * from (select t1.object_name,t2.DATA_OBJECT_ID,t2.OBJECT_TYPE,t2.LAST_DDL_TIME,t1.STATUS from t1,t2 where t1.object_id=t2.object_id and t2.owner='SYS' order by t2.LAST_DDL_TIME desc) where rownum<=20;20 rows selected.Elapsed: 00:00:03.54Execution Plan
----------------------------------------------------------
Plan hash value: 2229090023----------------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |    20 |  2120 |       |  2446   (1)| 00:00:01 |
|*  1 |  COUNT STOPKEY          |      |       |       |       |            |          |
|   2 |   VIEW                  |      | 19910 |  2060K|       |  2446   (1)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY|      | 19910 |  1555K|  1824K|  2446   (1)| 00:00:01 |
|*  4 |     HASH JOIN           |      | 19910 |  1555K|       |  2075   (1)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL  | T2   | 10115 |   296K|       |  1244   (1)| 00:00:01 |
|   6 |      TABLE ACCESS FULL  | T1   |   141K|  6914K|       |   831   (1)| 00:00:01 |
----------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter(ROWNUM<=20)3 - filter(ROWNUM<=20)4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")5 - filter("T2"."OWNER"='SYS')Statistics
----------------------------------------------------------0  recursive calls0  db block gets98234  consistent gets50989  physical reads3951508  redo size1381  bytes sent via SQL*Net to client133  bytes received via SQL*Net from client3  SQL*Net roundtrips to/from client1  sorts (memory)0  sorts (disk)20  rows processed

要优化该sql非常简单，按照之前写过的一篇分页sql优化文章的方法，很快就能把sql优化掉。由于T1的object_id列是存在索引的，所以只需要创建T2表的(OWNER,LAST_DDL_TIME)基本可以实现秒出，具体原因参考之前的文章。http://www.minniebaby.tech/2023/05/04/sql优化案例：分页查询一/

sql优化：

SQL> create index idx2 on t2(owner,last_ddl_time);Index created.Elapsed: 00:00:00.31
SQL> set autot trace
SQL> SELECT *2  FROM (SELECT t1.object_name3             ,t2.DATA_OBJECT_ID4             ,t2.OBJECT_TYPE5             ,t2.LAST_DDL_TIME6             ,t1.STATUS7     FROM t1,t28     WHERE t1.object_id = t2.object_id9             AND t2.OWNER = 'SYS'10     ORDER BY t2.LAST_DDL_TIME DESC)11  WHERE rownum <= 20;20 rows selected.Elapsed: 00:00:00.01Execution Plan
----------------------------------------------------------
Plan hash value: 3110385888-----------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |      |    20 |  2120 |    37   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                   |      |       |       |            |          |
|   2 |   VIEW                           |      |    21 |  2226 |    37   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                  |      |    21 |  1680 |    37   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                 |      |    22 |  1680 |    37   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID | T2   | 10115 |   296K|     4   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN DESCENDING| IDX2 |    11 |       |     3   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN            | IDX1 |     2 |       |     1   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID  | T1   |     2 |   100 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter(ROWNUM<=20)6 - access("T2"."OWNER"='SYS')7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")Statistics
----------------------------------------------------------1  recursive calls0  db block gets31  consistent gets23  physical reads0  redo size1381  bytes sent via SQL*Net to client133  bytes received via SQL*Net from client3  SQL*Net roundtrips to/from client0  sorts (memory)0  sorts (disk)20  rows processed

但是虽然优化完了该sql，也发现了oracle优化器的一个不足，即使没有创建索引，该sql也应该利用rownum的stopkey特性，先去扫描T2表并且排序之后再去连接T1表，而不是先连接再排序再stopkey。

我们把索引删除，手动去改写sql：

SQL> SELECT *2  FROM (SELECT t1.object_name3             ,t2.DATA_OBJECT_ID4             ,t2.OBJECT_TYPE5             ,t2.LAST_DDL_TIME6             ,t1.STATUS7     FROM t18             ,(SELECT *9                     FROM t210                     WHERE t2.OWNER = 'SYS'11                     ORDER BY t2.LAST_DDL_TIME DESC) t212     WHERE t1.object_id = t2.object_id)13  WHERE rownum <= 20;20 rows selected.Elapsed: 00:00:00.09Execution Plan
----------------------------------------------------------
Plan hash value: 1310430299----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |    20 |  1800 |       |    35   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |      |       |       |       |            |          |
|   2 |   NESTED LOOPS                |      |    21 |  1890 |       |    35   (0)| 00:00:01 |
|   3 |    NESTED LOOPS               |      |    22 |  1890 |       |    35   (0)| 00:00:01 |
|   4 |     VIEW                      |      | 10115 |   474K|       |     2   (0)| 00:00:01 |
|   5 |      SORT ORDER BY            |      | 10115 |  1501K|  2144K|  1588   (1)| 00:00:01 |
|*  6 |       TABLE ACCESS FULL       | T2   | 10115 |  1501K|       |  1244   (1)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN          | IDX1 |     2 |       |       |     1   (0)| 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID| T1   |     2 |   100 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter(ROWNUM<=20)6 - filter("T2"."OWNER"='SYS')7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")Statistics
----------------------------------------------------------57  recursive calls0  db block gets4644  consistent gets4599  physical reads0  redo size1381  bytes sent via SQL*Net to client133  bytes received via SQL*Net from client3  SQL*Net roundtrips to/from client4  sorts (memory)0  sorts (disk)20  rows processed

虽然肯定比不上之前创建索引的方式，但是也比之前好很多，oracle的查询转换居然没考虑到这种情况。

相比之下mysql居然有这方面的考虑，在没有被优化过的情况下，该sql场景mysql选择了最正确的执行计划。

mysql> explain select t1.name,t1.email,t2.created_at,t2.is_active,t2.balance from sample_table t1,sample_table1 t2 where t1.id=t2.id order by t2.created_at desc,t2.id limit 20;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+--------+----------+----------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref           | rows   | filtered | Extra          |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+--------+----------+----------------+
|  1 | SIMPLE      | t2    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL          | 198266 |   100.00 | Using filesort |
|  1 | SIMPLE      | t1    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test_db.t2.id |      1 |   100.00 | NULL           |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+--------+----------+----------------+
2 rows in set, 1 warning (0.00 sec)

先扫描T2并且排序之后再与T1关联，很好的用上了limit的特点，而不是像oracle一样先关联再去对关联出的大量结果集排序再去做stopkey。

这也给了我们一个启发，就是如果是从mysql迁移到其他数据库的情况，需要考虑到这种sql场景在mysql上可能会跑的不错，但是在其他数据库上会明显变慢，需要做对应的优化。

------------------作者介绍-----------------------

姓名：黄廷忠现就职：Oracle中国高级服务团队曾就职：OceanBase、云和恩墨、东方龙马等电话、微信、QQ：18081072613

个人博客: (http://www.htz.pw)

CSDN地址: (https://blog.csdn.net/wwwhtzpw)

博客园地址: (https://www.cnblogs.com/www-htz-pw)