优化Oracle查询 [英] Optimisation of an oracle query

问题描述

我试图使我的查询尽快运行，但是我努力在5秒之内得到它.

I'm trying to make my query run as quickly as possible but i'm struggling to get it under 5 seconds.

我认为这是因为我要引用两个链接的数据库

I think it's because i'm referencing two linked databases

这是我的查询

select column2, column3, column4 
  from table1@dev 
 where column1 in (
          select distinct column2 
            from table2@dev 
           where column3 > 0
                  ) 
order by column1

是否有进一步优化此查询的方法?

Is there a way to optimise this query any more?

我尝试使用join，但这似乎会使查询运行更长的时间

I've tried using join but it seems to make the query run longer

预先感谢

编辑

根据进一步的调查，DRIVING_SITE使其运行起来非常快

From further investigation the DRIVING_SITE makes it run very quick like this

select /*+ DRIVING_SITE(table1) */ t1.column2, t1.column3, t1.column4
from table1@dev t1, table2@dev t2
WHERE t2.column3 > 0

但是，一旦我在其中添加distinct column2，它就会运行得很慢

But as soon as I add the distinct column2 in it makes it run really slow

推荐答案

首先，不需要distinct.查询可以写为:

First, no need for distinct. The query can be written as:

select * 
  from table1@dev 
 where column1 in (
          select column2 
            from table2@dev 
           where column3 > 0
                  ) 
order by column1

第二，还有(至少)两种写法.要么使用JOIN:

Second, there are (at least) two more ways to write it. Either with JOIN:

select t1.* 
  from table1@dev t1
  join table2@dev t2
 where t2.column2 = t1.column1
   and t2.column3 > 0 
group by
       t1.id, t1.column1, ... 

order by t1.column1

或(我的喜好)与EXISTS:

select t1.* 
  from table1@dev t1 
 where exists
       ( select *
           from table2@dev 
          where t2.column2 = t1.column1
            and t2.column3 > 0
                  ) 
order by column1

---

无论如何，您应该检查所有计划的执行计划.

---

In any case, you should check the execution plans for all of them.

如果您在table1.column1上有索引，并且对于table2是column2上的索引或(column3, column2)

I would expect performance to be best if you have an index on table1.column1 and for table2, either an index on column2 or a composite index on (column3, column2)

这篇关于优化Oracle查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！