无需索引即可提高Oracle查询性能

首先,我将把查询重写为ANSI标准:

SELECT c.ClaimNumber, a.ItemDate, c.DTN, b.FilePath
FROM items a
INNER JOIN itempages b ON b.ItemNum = a.ItemNum
INNER JOIN keygroupdata c ON c.ItemNum = b.ItemNum
WHERE a.ItemType IN (112,115,189,241)
ORDER BY a.DateStored DESC

这使得阅读和理解正在发生的事情变得更加容易。它还可以帮助您避免犯可能导致真正大问题的错误(即交叉连接)。然后我会得到解释计划,看看DBMS对这个查询做了什么。它是否试图使用一些索引?它是否正确地连接了桌子?

然后我会查看正在使用的表,看看是否有任何索引已经存在,我可以使用这些索引来加快查询速度。最后,正如其他人所建议的那样,我将删除ORDERBY子句,并在代码中这样做。

请第三方为其联接列编制索引,因为它们首先应该这样做!没有索引,甲骨文除了蛮力之外什么也干不了。

您可能希望尝试在这些表中的任何一个上创建物化视图。然后,您可以在物化视图上创建一个索引,这将有助于加快查询速度(然后将查询物化视图而不是原始表)。

当然,如果您的基础表被更新,您的视图和索引将需要刷新。

首先,看看执行计划。它是否准确反映了在查询执行的每个阶段要检索的行数?谓词“a.itemtype in(112115189241)”的选择性如何?执行计划是否显示了对连接或排序的临时磁盘空间的任何使用?

实际上,也许您可以修改这个问题以包含执行计划。

还要确保没有禁用哈希连接,这在OLTP调优的系统中有时是如此,因为它们是在Oracle中均衡批量数据的最有效方法。他们应该出现在执行计划中。

在联接表之前,可以尝试筛选项类型,如图所示。

如果您在9i之前运行Oracle,这有时会带来意想不到的好处。

select 
  c.claimnumber,
  a.itemdate, 
  c.dtn,
  b.filepath
from 
  (
  select itemdate
  from items it
  where it.itemtype in(112,115,189,241)
  ) a
  itempages b,
  keygroupdata c
where a.itemnum = b.itemnum
  and b.itemnum = c.itemnum

您也可以尝试添加提示/ +规则 /或/ +序 /看看会发生什么…同样,特别是对于旧版本,这些有时会产生令人惊讶的结果。

SELECT /*+RULE*/
  c.ClaimNumber, a.ItemDate, c.DTN, b.FilePath
FROM
  items a,
  itempages b,
  keygroupdata c
WHERE a.ItemType IN (112,115,189,241)
  AND a.ItemNum = b.ItemNum
  AND b.ItemNum = c.ItemNum
ORDER BY a.DateStored DESC

如果查询输入是常量或可预测的 itemType IN (...) ,然后另一种选择是每天运行一次或两次查询,并将结果存储在本地表中,在适当的情况下使用索引。

然后,您可以使代价高昂的查询“脱机”,并为交互式查询提供更快/更好的结果。

这是您经常运行的查询吗?DB所有者似乎有兴趣创建您需要的索引来加速这个查询。运行查询所花费的3.5分钟必须对其生产环境产生一定的影响!

另外,他们是否在表上运行更新统计信息?这可能会提高性能,因为联接顺序是根据表的统计信息计算的。

顺便问一下,你可以做什么?刚读过?如果您可以创建临时表并将索引放在这些表上,那么我可以考虑创建临时表副本,为这些表编制索引,然后使用临时副本进行索引辅助联接。

我知道这个线程非常古老,但是对于搜索引擎来说,我仍然想提供一个可以在Oracle上工作的替代解决方案,并且依赖于数据可能更快。

with a as (
  select 
    * 
  from 
    items 
  where 
    ItemType IN (112,115,189,241)
)
SELECT 
  c.ClaimNumber
  , a.ItemDate
  , c.DTN, b.FilePath
FROM 
  a,
  itempages b,
  keygroupdata c
WHERE 
  a.ItemNum = b.ItemNum
  AND b.ItemNum = c.ItemNum
ORDER BY 
  a.DateStored DESC

您也可以尝试 /*+ MATERIALIZE */ 暗示 WITH 条款。

实际上,我发现Oracle的旧连接语法比ansi-sql更容易阅读。^^

如果没有索引,查询只会随着表大小的增加而变得更糟。 尽管如此,尝试删除ORDERBY子句并在客户端执行该排序。

有时,通过向WHERE子句中添加似乎是多余元素的元素,为优化器选择额外的路径,您可以看到一个好处。

例如,您有a.itemnum=b.itemnum和b.itemnum=c.itemnum。也可以尝试添加a.itemNum=c.itemNum。不过,我很肯定优化器足够聪明,可以自己解决这个问题——不过值得一试。

根据itemtype列的数据类型,如果它是varchar,则使用以下命令可能会更快地执行,Oracle将执行隐式转换。

SELECT c.ClaimNumber, a.ItemDate, c.DTN, b.FilePath
FROM items a,
itempages b,
keygroupdata c
WHERE ((a.ItemType IN ('112','115','189','241'))
AND (a.ItemNum = b.ItemNum)
AND (b.ItemNum = c.ItemNum))
ORDER BY a.DateStored DESC

这些表收集了统计数据吗?如果不是这样,收集统计数据可能会改变执行计划,尽管这不一定是为了更好。

除此之外,看看执行计划。您可能会看到它正在以非最佳顺序连接表(例如,它可能在连接具有筛选条件的A之前连接B和C)。

您可以使用提示来尝试影响访问路径、联接顺序或联接方法。

更新 :对评论的回应导致我 this 介绍,这可能会有帮助,或者至少有趣。

如果您说没有索引,那么这是否意味着没有定义主键或外键?显然,分析表和收集统计信息很重要,但如果不存在诸如定义表的联接方式之类的元数据,那么Oracle很可能会选择一个糟糕的执行路径。

在这种情况下,使用诸如/*+ordered*/之类的提示很可能是使优化器可靠地选择良好执行路径的唯一选项。也可能值得添加外键和主键,但将它们定义为禁用和验证。

我想这个评论的有效性取决于对指数的厌恶程度达到了多大程度。

首先在此查询上创建一个视图,然后从此视图生成一个表。还可以创建日期索引,生成作业,并在系统空闲时将其安排在午夜。

好吧,既然您不能创建索引,那么我会确保统计信息都是最新的,然后我会用以下方式重写查询:

with a as (select /*+ MATERIALIZE */ ItemType, ItemNum, DateStored, ItemDate from items where ItemType in (112,115,189,241)) SELECT c.ClaimNumber, a.ItemDate, c.DTN, b.FilePath FROM a, itempages b, keygroupdata c WHERE a.ItemNum = b.ItemNum AND b.ItemNum = c.ItemNum ORDER BY a.DateStored DESC

删除订单的方式

将行拉回到应用程序后,执行排序。