IN clause too slow

dblinkoraclesubquerywhere

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

SELECT order_id
FROM or_order@smartflex AS outer
WHERE EXISTS (
  SELECT 1
  FROM oym_planmantenimiento
  WHERE numroot = outer.order_id
);