Ive been searching for about 1 hour and i have not found anything.
I have this problem:
When I do this query:
select order_id
from or_order@smartflex
where order_id in ( select distinct numeroot from oym_planmantenimiento )
It takes too much, i'm talking about almost one hour !!!
However, when I do first the subquery, I export the results to excel (they are always like between 100 or 400 results) and then I pass ass static values, the query only takes 1 second:
select order_id
from or_order@smartflex
where order_id in ( 1230, 1231, 1232, 1233, ..., 1239 )
I want something like the subquery runs only once, pass the values and then the other query start running.
What can I do ?
Thanks
Roberto E.
New Information:
The table or_order has 78697214 and counting. This grows every second.
The table oym_planmantenimiento has at most 10000 records, however unique are only like 300~600. For this case, it was 358, because it changes with frequency but like every day, not every second. So for me, a static value at the beginning of the query is enough accurate.
For the first:
Plan Hash Value : 240660835
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 702 | 10530 | 96811 | 00:19:22 |
| * 1 | HASH JOIN RIGHT SEMI | | 702 | 10530 | 96811 | 00:19:22 |
| * 2 | INDEX FAST FULL SCAN | OYM_PLANMANTENIMIENTO_IDX03 | 832 | 1664 | 6 | 00:00:01 |
| 3 | REMOTE | OR_ORDER | 78399636 | 1019195268 | 96367 | 00:19:17 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("ORDER_ID"="NUMEROOT")
* 2 - filter("NUMEROOT" IS NOT NULL)
For the second:
Plan Hash Value :
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT REMOTE | | 358 | 2148 | 361 | 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| * 2 | INDEX UNIQUE SCAN | PK_OR_ORDER | 358 | 2148 | 361 | 00:00:01 |
--------------------------------------------------------------------------------
OYM_PLANMANTENIMIENTO has a index type: normal over the column numeroot.
OR_ORDER has a index type: unique over order_id.
Best Answer
Use
EXISTS
Normally you solve this with
EXISTS