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
Here's my query
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?
I've tried using join
but it seems to make the query run longer
Thanks in advance
EDIT
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
But as soon as I add the distinct column2
in it makes it run really slow
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
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
or (my preference) with 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.
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)