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

Improve oracle query performance without indexing

提问by Chris Conway

What are some things I can do to improve query performance of an oracle query without creating indexes?

在不创建索引的情况下，我可以做哪些事情来提高 oracle 查询的查询性能？

Here is the query I'm trying to run faster:

这是我试图运行得更快的查询：

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

None of these columns are indexed and each of the tables contains millions of records. Needless to say, it takes over 3 and half minutes for the query to execute. This is a third party database in a production environment and I'm not allowed to create any indexes so any performance improvements would have to be made to the query itself.

这些列都没有索引，每个表都包含数百万条记录。不用说，执行查询需要 3 分多钟。这是生产环境中的第三方数据库，我不允许创建任何索引，因此必须对查询本身进行任何性能改进。

Thanks!

谢谢！

采纳答案by Rob Booth

First I'd rewrite the query to be ANSI standard:

首先，我将查询重写为 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

This makes it easier to read and understand what is going on. It also helps you not make mistakes (i.e. Cross Joining)that might cause real big problems. Then I'd get the Explain plan to see what the DBMS is doing with that query. Is it trying to use some indexes? Is it joining the tables correctly?

这使得阅读和理解正在发生的事情变得更容易。它还可以帮助您不犯可能导致真正大问题的错误（即交叉连接）。然后我会得到解释计划，看看 DBMS 正在对那个查询做什么。它是否试图使用一些索引？它是否正确加入表格？

Then I'd review the tables that I'm working with to see if there are any indexes that already exist that I could be using to make my query faster. Finally as everyone else has suggested I'd remove the Order By clause and just do that in code.

然后我会查看我正在使用的表，看看是否有任何已经存在的索引可以用来加快查询速度。最后，正如其他人所建议的那样，我将删除 Order By 子句并在代码中执行此操作。

回答by Tony Andrews

Ask the third party to index its join columns, as they should have done in the first place! Without indexes, Oracle has nothing to go on other than brute force.

要求第三方为其连接列建立索引，因为他们一开始就应该这样做！没有索引，Oracle 除了蛮力之外别无他法。

回答by BQ.

You may want to try creating a materialized view on any of those tables. You can then create an index on the materialized view that will help speed the query (which would then be querying the materialized view instead of the raw table).

您可能想尝试在任何这些表上创建物化视图。然后，您可以在物化视图上创建索引，这将有助于加快查询速度（然后查询物化视图而不是原始表）。

Of course, if your underlying table is updated your view and indexes will need to be refreshed.

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

回答by David Aldridge

First, look at the execution plan. Does it accurately reflect the number of rows to be retrieved at each stage of the query execution? How selective is the predicate "a.ItemType IN (112,115,189,241)"? Does the execution plan show any use of temporary disk space for joins or sorts?

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

Actually, maybe you can modify the question to include the execution plan.

实际上，也许您可​​以修改问题以包含执行计划。

Also make sure you do not have hash joins disabled, which is sometimes the case in OLTP-tuned systems, as they are the most efficient way of equijoining bulk data in Oracle. They ought to show up in the execution plan.

还要确保您没有禁用散列连接，这在 OLTP 调优系统中有时是这种情况，因为它们是在 Oracle 中等连接批量数据的最有效方法。他们应该出现在执行计划中。

回答by JosephStyons

You can try filtering on item type before you join your tables, as shown here.

您可以在加入表格之前尝试过滤项目类型，如下所示。

If you are running on Oracle prior to 9i, this would sometimes give surprising benefits.

如果您在 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

You can also try adding the hints /+RULE/ or /+ORDERED/ to see what happens... again, particularly with older versions, these would sometimes give surprising results.

您还可以尝试添加提示 / +RULE/ 或 / +ORDERED/ 以查看会发生什么......再次，特别是对于旧版本，这些有时会产生令人惊讶的结果。

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

回答by Ken Gentle

If the query inputs are constant or predictable (the itemType IN (...)), then an alternative would be to run the query once or twice a day and store the results in a local table, with indices where appropriate.

如果查询输入是恒定的或可预测的 (the itemType IN (...))，那么另一种方法是每天运行一次或两次查询并将结果存储在本地表中，并在适当的情况下使用索引。

You can then make the costly query 'offline' and have quicker/better results for an interactive query.

然后，您可以使昂贵的查询“离线”，并为交互式查询获得更快/更好的结果。

回答by Toybuilder

Is this a query that you run often? It seems like it would be in the DB owner's interest to create the indexes that you need to speed this query up. The 3.5 minutes you're spending running the query must have some impact on their production environment!

这是您经常运行的查询吗？创建加速此查询所需的索引似乎符合数据库所有者的利益。您花在运行查询上的 3.5 分钟一定会对他们的生产环境产生一些影响！

Also, have they been running update statistics on the tables? That might improve performance, as the join order is computed based on the statistics of the tables.

另外，他们是否一直在对表运行更新统计信息？这可能会提高性能，因为连接顺序是根据表的统计信息计算的。

BTW, what are you allowed to do? Just read? If you can create temporary tables and put indexes on those, I might consider making temporary copies of the table, indexing those, and then do the index-assisted join with the temp copies.

BTW，你有什么资格做？刚读？如果您可以创建临时表并在其上放置索引，我可能会考虑制作该表的临时副本，为它们编制索引，然后对临时副本进行索引辅助连接。

回答by armin walland

I know this thread is very old, but for the search engines I still wanted to offer an alternative solution that will work on oracle and depending on the data might be much faster.

我知道这个线程很旧，但是对于搜索引擎，我仍然想提供一种替代解决方案，该解决方案可以在 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

You can also try the /*+ MATERIALIZE */hint in the WITHclause.

您也可以尝试子句中的/*+ MATERIALIZE */提示WITH。

Actually I find oracle's old join syntax much easier to read than ansi sql ^^

其实我发现oracle的旧连接语法比ansi sql更容易阅读^^

回答by Max Lambertini

Well, since you can't create indexes, I'd make sure that statistics are all up-to-date then, I'd rewrite the query this way:

好吧，由于您无法创建索引，因此我会确保统计信息都是最新的，然后我会以这种方式重写查询：

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

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

回答by ScottCher

Sometimes you can see a benefit by adding extra pathways for the optimizer to choose by adding what seems like redundant elements to the where clause.

有时，您可以通过在 where 子句中添加看似冗余的元素来为优化器添加额外的路径来选择的好处。

For instance, you've got A.ItemNum = B.ItemNum AND B.ItemNum = C.ItemNum. Try adding A.ItemNum = C.ItemNum as well. I'm pretty sure, however, that the optimizer is intelligent enough to figure that out on its own - worth a try though.

例如，您有 A.ItemNum = B.ItemNum AND B.ItemNum = C.ItemNum。也尝试添加 A.ItemNum = C.ItemNum。然而，我很确定优化器足够聪明，可以自己解决这个问题——不过值得一试。