database - 优化 ORACLE 上的查询。试用指数

Question

我有这个查询，我想通过添加适当的索引来提高它的性能。

DELETE FROM MYTAB1 WHERE MYID1 IN (SELECT MYID2 FROM MYTAB2);

我不熟悉索引的语法以及它们所需的设置类型。请提供相同的。这里的主要问题是 MYTAB1 有数百万条记录，因此查询需要很多时间。但是，MYTAB2 只有 1000 条记录。 MYID1 是 MYTAB1 的主键

我试过创建索引:

CREATE INDEX IDX_TAB1_ID1 ON MYTAB1(MYID1);

对查询的性能影响不大。

我运行了解释计划并得到了这个:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------   
| Id  | Operation             | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)|
------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |               | 63977 |    11M|       | 62216   (2)|
|   1 |  DELETE               | MYTAB1        |       |       |       |            |
|   2 |   HASH JOIN RIGHT SEMI|               | 63977 |    11M|  7688K| 62216   (2)|
|   3 |    TABLE ACCESS FULL  | MYTAB2        |   437K|  2561K|       |  1189   (2)|
|   4 |    TABLE ACCESS FULL  | MYTAB1        |  3761K|   678M|       | 24718   (4)|   
------------------------------------------------------------------------------------

Answer 1

"the thing is MYTAB2 has only 1000 records! "

是的，但相关数据点是 MYTAB1 中有多少条记录与那千条记录相匹配？该数字代表整个表格的多少百分比？而这些记录在 MYTAB1 中的分布是怎样的呢？

如果您要删除 MYTAB1 中 20% 的行，索引只会使性能变差(如果优化器愚蠢到使用它的话)。如果您只删除 MYTAB1 中 0.1% 的记录，但这些记录分布在表中的每个 block 中，那么全表扫描同样是更有效的选择。

调优没有简单的解决方案。它总是取决于许多不同因素的相互作用。您希望多久运行一次此删除操作？您是否拥有企业版许可证和大量备用 CPU 容量？等等。

---

如果 MYID1 是 MYTAB1 的主键，那么该列上应该已经有一个 UNIQUE 索引。所以你不需要创建一个新的索引。

除非您是那些懒得在您的表上应用完整性约束的地方之一。那是不好的做法。除了强制完整性的明显好处外，约束还可以为优化器提供有用的信息，从而制定更好的执行计划。

无论如何，在您现在发布的解释计划中，问题的根源已经很清楚了。你说 MYTAB2 只有一千行，但优化器似乎认为它有四十三万七千行。因此，显然您需要在该表上收集新的统计数据:

 exec dbms_state.gather_table_stats(ownname=>user, tabname=>'MYTAB2',estimate_percent=>100)

我想 MYTAB1 的统计数据是正确的，它确实有大约 370 万行？如果是这样，索引查找将是性能最高的选项。您需要检查该主键列上是否有唯一索引:

 select i.index_name, i.uniqueness
 from user_indexes i
     join user_ind_columns c
         on ( i.index_name =  c.index_name)
 where i.table_name = 'MYTAB1'
 and c.column_name = 'MYID1'

如果您没有索引，则需要创建一个:

 create unique index mytab1_uidx on mytab1(myid1)
 /

如果您有一个索引但它不是唯一的，那么您可能应该删除它并构建一个唯一索引。

请注意，如果您弄错了并且该列不是主键 - 即它有重复项 - 那么 CREATE INDEX 语句将失败。在这种情况下，你有一个更大的问题需要你思考。

---

"however the number of rows that [MYTAB2] contains is highly volatile... basically some rows are added to the table then some are removed and the process continues "

在这种情况下，拥有任何固定的统计数据都是有帮助的。一个更好的主意是强制优化器在运行时动态生成统计信息。

exec dbms_state.delete_table_stats(ownname=>user, tabname=>'MYTAB2')
exec dbms_state.lock_table_stats(ownname=>user, tabname=>'MYTAB2')

删除表的统计信息然后将其锁定将强制数据库在每次将表包含在查询中时为该表生成统计信息，前提是您已启用动态采样。这应该会在您运行该删除语句时生成更好的执行计划，而不管 MYTAB2 当时恰好持有多少行。

Find out more.