主页 / dba / 问题 / 339358
Accepted
Gábor Major
Asked: 2024-05-09 16:02:57 +0800 CST

Oracle SQL查询即使有索引也很慢

  • 772

我有一个Oracle 19c数据库（版本号19.0.0.0.0），以及两个具有许多列的表，这里只列出相关的列：

表1：

  • createtime 日期
  • contact_key 字符型(96字节)

表2：

  • contact_key 字符型(96字节)
  • csi_tfid 可变字符型(300字节)

表1有2.1亿行数据，并且是按年度分区的。表2有680万条记录，没有分区。

以下查询运行得非常快，大约在0.05到0.1秒之间：

SELECT * FROM 表1 m, 表2 c
WHERE
c.contact_key = m.contact_key
AND c.csi_tfid = '1234567';

但是，一旦我添加一个条件来仅获取最后几条记录（这对应用程序来说是相关的），执行速度就会下降到1分钟，甚至更慢：

SELECT * FROM 表1 m, 表2 c
WHERE
c.contact_key = m.contact_key
AND c.csi_tfid = '1234567'
AND m.createtime >= (SYSDATE-30);

我尝试使用硬编码的日期，如TO_DATE('2024-04-09', 'YYYY-MM-DD')，结果相同。

我在查询中所有列上都有单独的索引：

表1：

  • ik_table1_contact_k 非唯一
  • ik_table1_createtime 非唯一

表2：

  • ik_table2_contact_key 唯一
  • ik_table2_csi_tfid 非唯一

我尝试在两个表上添加复合索引，包括contact_key和createtime（对于表1）以及contact_key和csi_tfid（对于表2），但似乎没有效果。

对于快速查询，Oracle生成了这个执行计划：

执行计划图

计划哈希值：323565418

-----------------------------------------------------------------------------------------------------------------------------------
| 编号 | 操作             | 名称                | 行数  | 字节 | 成本(%CPU) | 时间     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                     |     3 |  1857 |   118   (0)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS    |                     |     3 |  1857 |   118   (0)| 00:00:01 |       |       |
|   2 |  TABLE ACCESS BY INDEX ROWID BATCHED | 表2              |     3 |   966 |     7   (0)| 00:00:01 |       |       |
|*  3 |   INDEX RANGE SCAN | IK_TABLE2_CSI_TFID  |     3 |       |     3   (0)| 00:00:01 |       |       |
|   4 |  PARTITION RANGE ALL |                     |     1 |   297 |    37   (0)| 00:00:01 |     1 |    12 |
|   5 |  TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | 表1              |     1 |   297 |    37   (0)| 00:00:01 |     1 |    12 |
|*  6 |   INDEX RANGE SCAN | IK_TABLE1_CONTACT_K |     1 |       |    36   (0)| 00:00:01 |     1 |    12 |
-----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (通过操作ID识别)：
---------------------------------------------------

   3 - access("C"."CSI_TFID"='1234567')
   6 - access("C"."CONTACT_KEY"="M"."CONTACT_KEY")

对于带有createtime条件的慢查询，执行计划有很大不同：

执行计划图

计划哈希值：1504517877

-------------------------------------------------------------------------------------------------------------------------------------
| 编号 | 操作             | 名称                  | 行数  | 字节 | 成本(%CPU)
oracle

2 个回答

  1. 看起来你的表1中的统计信息过时了。优化器预期在m.createtime >= (sysdate-30)的情况下有3行数据。只需收集统计信息，问题应该就能解决。

    如果出于某些原因你无法收集统计信息，尝试使用优化器提示：

    select /*+leading(c m) use_nl_with_index(m IK_TABLE1_CONTACT_K) */
*
from table1 m, table2 c 
where c.contact_key = m.contact_key 
and c.csi_tfid = '1234567' 
and m.createtime >= (sysdate-30);
    • 1
  2. Best Answer

    嗯，最终的解决方案是数据库管理员(DBA)设法调整了数据库，使其强制使用与@Andy 数据分析师相同的执行计划，现在一切都好了。我没有得到更多关于他们调整了什么，或者是什么导致了5月1日执行计划的变化的信息。

    • 0

相关问题