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

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!

16 个解决方案

#1

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子句并在代码中执行此操作。

#2

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.

要求第三方索引其连接列,因为它们应该首先完成!如果没有索引,甲骨文除了蛮力之外别无他法。

#3

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.

当然,如果更新了基础表,则需要刷新视图和索引。

#4

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中等效批量数据的最有效方式。他们应该出现在执行计划中。

#5

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

#6

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.

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

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

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

#7

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.

顺便说一下,你准许做什么?刚读过?如果您可以创建临时表并在其上放置索引,我可能会考虑制作表的临时副本,索引这些表,然后使用临时副本执行索引辅助连接。

#8

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 WITH clause.

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

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

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

#9

Without indexing, that query is only going to get worse as the table size increases. With that said, try removing the order by clause and doing that sort on the client side.

如果没有索引,那么随着表大小的增加,该查询只会变得更糟。话虽如此,请尝试删除order by子句并在客户端进行排序。

#10

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。然而,我很确定优化器足够聪明,可以自己解决这个问题 - 值得一试。

#11

Depending on the datatype of the ItemType column you may experience faster executing using the following if it's a varchar, Oracle will do implict conversions.

根据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

#12

Are statistics gathered on these tables? If not, gathering statistics might change the execution plan, although it wouldn't necessarily be for the better.

统计数据是否收集在这些表中?如果没有,收集统计数据可能会改变执行计划,尽管它不一定会好转。

Aside from that, look at the execution plan. You may see that it is joining the tables in a non-optimal order (e.g. it could be joining b and c before joining with a which has the filter condition).

除此之外,请查看执行计划。您可能会看到它以非最佳顺序连接表格(例如,它可能在加入具有过滤条件的a之前加入b和c)。

You could use hints to try to affect the access paths, join order, or join method.

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

Update: Responding to the comment led me to this presentation, which might be helpful or at least interesting.

更新:回应评论引导我参加此演示文稿,这可能有用或至少有趣。

#13

If you say that there are no indexes, then does this also mean that there are no primary or foreign keys defined? Obviously analysing the tables and gathering statistics are important but if metadata such as defining how the tables are supposed to be joined doesn't exist, then Oracle may well choose a poor execution path.

如果你说没有索引,那么这是否也意味着没有定义主键或外键?显然,分析表和收集统计信息很重要,但如果不存在定义表如何连接的元数据,则Oracle可能会选择较差的执行路径。

In that case using a hint such as /*+ ORDERED */ may well be the only option to make the optimizer reliably choose a good execution path. It may also be worth adding foreign keys and primary keys but define them as DISABLE and VALIDATE.

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

I guess the usefulness of this comment depends on how far the aversion to indexes goes so YMMV.

我想这个评论的有用性取决于对索引的厌恶程度如此之大YMMV。

#14

First create a view on this query and then generate a table from this view. Also create an index on date, make a job and schedule it in midnight time when the system is idle.

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

#15

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

使用as(select / * + MATERIALIZE * / ItemType,ItemNum,DateStored,ItemDate from 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

#16

Remove the ORDER BY

删除ORDER BY

perform the sort, after you pull the rows back to your application.

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