logo标签列表

优化Oracle SQL查询

sqloracleoptimization
4

你好，我有一个需要优化的 SQL 查询。这个查询目前只需要 0.3 秒就能完成，但是我需要运行同样的查询来处理大量不同的 storeId -- 有没有什么方法可以优化这个查询让它运行得更快，或者改变它以一次获取所有的 storeId。

我可以在 C# 中创建一个新的命令，将其连接成大量不同查询的联合。

select /*+ PUSH_SUBQ */ *  
from mytable r
where r.s in (1, 7)
and r.d in (1, 75)
and r.storeid = 1162
and r.period = 20110528
and r.pid in (select /*+ no_unnest qb_name(subq1) */ 
productid from otherTable where itmid=9999)

我已经尝试过类似的方法，但是它需要很长时间。

select  /*+ PUSH_SUBQ */ * 
    from mytable r
    where r.s in (1, 7)
    and r.d in (1, 75)
    and r.storeid in (1162, 1223, 1231, 51231, 231, ...)
    and r.period = 20110528
    and r.pid in (select /*+ no_unnest qb_name(subq1) */ 
    productid from otherTable where itmid=9999)

我的表格有如下的索引： pid是非唯一的，已分区的，无联接索引 其他所有列都是唯一的，已分区的，无联接索引

你是什么意思说“所有其他列都是唯一的”？每列本身都是唯一的（我猜不是这个意思）？还是有一个除pid以外的涵盖所有列的唯一索引？或者还有其他情况？如果有多列索引，每个索引中的列顺序是什么？索引被分区了，那表也被分区了吗？你当前查询的执行计划是什么？ - Dave Costa
你能发布实际的执行计划吗？你可以使用DBMS_XPLAN获取它。 - eaolson
另外，您使用的Oracle版本是哪个？ - Justin Cave
如果您需要为“大量的storeids”运行此查询，则可能需要重新考虑架构。如果此查询位于循环内部，则应将查询“向上推”一级。也就是说，可能要获取所有storeids，然后迭代更大的结果集。当然，单个查询可能需要很长时间，但是遍历结果可能会更快。 - Adam Hawkes
5个回答
5
更好的方法是运行查询，然后使用DBMS_XPLAN查看实际使用的执行计划。 - Dave Costa
1

没有看到执行计划和实际的索引/分区细节，我不知道该建议什么来使查询的执行更快。但是，在这种情况下，解析时间可能是重要的。您是否真的在样例中为所有条件使用文字值？您应该使用绑定变量；否则，您将解析每个单独的查询，这不仅需要时间，而且会强制产生瓶颈。

您可以运行扩展SQL跟踪，要么手动阅读跟踪，要么通过性能分析器运行它。

将查询更改为一次获取“所有storeid”很简单——完全删除storeid上的条件。如果您确实需要每个可能的storeid的结果，那么您很有可能通过为每个storeid运行一个查询来反复访问块从而浪费大量时间。但也许您所说的“所有storeid”只是某些小型ID集合的所有内容。

1
如果您需要在许多不同的商店上运行此程序，您可能会指定一个StoreID值列表，例如：
select *  
  from mytable r
 where r.s in (1, 7)
   and r.d in (1, 75)
   and r.storeid IN( 1162, 1163, 1164, ... )
   and r.period = 20110528
   and r.pid in (select productid 
                   from otherTable 
                  where itmid=9999)

如果你想优化查询的性能，你需要提供两个表（MyTable和OtherTable）的结构，让我们知道这两个表上存在哪些索引，并对各种条件的基数给出一些想法。

我很担心提示的存在（特别是PUSH_SUBQ提示将被忽略，因为它在错误的位置。虽然很少情况下向查询添加显式提示是适当和必要的，但通常情况下，当Oracle生成不良查询计划时，底层统计数据会误导优化器。如果是这种情况，与其提示查询，你最好修复统计数据。

0

我猜使用连接而不是子查询可能有所帮助。还要仅选择您将使用的列：

select r.d, r.storeid, r.period, r.pid /* select only columns you need */
from mytable r, otherTable o
where r.s in (1, 7)
and r.d in (1, 75)
and r.storeid = 1162
and r.period = 20110528
and r.pid = o.productid /* use a join instead of subquery */
and o.itmid=9999

测量并查看。

1
Charlie，1989年打电话给你，他们要回他们隐式的where连接。 - Johan
1
@Johan：将连接转换为JOIN ON语法留给读者作为练习 :) - Karel Petranek
这是原始查询，它要慢得多。 - Overload119
0
 select r.*  
  from mytable r 
   inner join otherTable o on r.pid = o.productid and o.itmid = 9999
  where r.s in (1, 7)
   and r.d in (1, 75)
   and r.storeid IN( 1162, 1163, 1164, ... )
   and r.period = 20110528

尝试使用这个查询，它应该需要更少的时间。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接