1

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.

  • How many rows are in each table? Please add your execution plans for the two options you have above. What indexes do you have on those two tables? – indiri Oct 19 '17 at 4:20
  • Done please review the new information – user3126458 Oct 19 '17 at 4:37
2

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
);
  • This solution is running two minutes ago and it has not finished... so it didn't work :( – user3126458 Oct 20 '17 at 23:19
  • @user3126458 Do you have indexes on numroot, and order_id? – Evan Carroll Oct 20 '17 at 23:23
  • Yes, I do. I think the problem is that oracle is trying to load the whole table through the db-link – user3126458 Oct 24 '17 at 12:49
0

If you move the subquery to a join it will usually speed up. However, querying the table is always going to have more cost that just giving it the numbers because it still has to scan at least an index and possibly a table. Do you have an index on the order_id in both tables? If not then you should add it.

SELECT sf.order_id
FROM or_order@smartflex sf
    INNER JOIN (select distinct numeroot from oym_planmantenimiento) x 
        ON sf.order_id=x.numeroot
  • It didn't work, in fact it has the same cost than the initial query. I started running that solution and it hasn't finished yet and it has more than 1 minute already. In brief, i alredy tried that. – user3126458 Oct 19 '17 at 4:16
0

You can try this instead:

  WITH x 
    AS ( SELECT DISTINCT numeroot 
           FROM oym_planmantenimiento )
SELECT sf.order_id
  FROM or_order@smartflex sf
 INNER JOIN x 
    ON sf.order_id=x.numeroot
  ORDER BY 1;

This essentially creates a temp table for the distinct values.

  • 2
    CTEs are not temp tables. – ypercubeᵀᴹ Oct 19 '17 at 20:47
  • It didn't work, it takes too much time. Exactly, it took 47 minutes to run :( – user3126458 Oct 20 '17 at 23:20
  • Try posting an explain plan for each select statement that people have given you. Also, are you getting about the same number of rows with every query, or does the number of rows change from one query to another? – Gandolf989 Oct 23 '17 at 12:32
0

Lose the "distinct".

All too often, I see the distinct clause used as a Band Aid for poor data structures, at the cost of [dire] performance.

Always remember that distinct removes duplicates so ...

select distinct a, b, c 
from ... 

... can be just as expensive as ...

select a, b, c
from ... 
group by a, b, c
order by a, b, c 

The database doesn't need distinct values to satisfy the "in" clause and forcing it to use one only serves to increase the work it has to do and forces it to do that work in a specific sequence. Bottleneck, anyone?

Of course, your best solution would be to use a join ...

select order_id
from or_order@smartflex t1 
inner join oym_planmantenimiento t2 
on t1.order_id = t2.numeroot

... but I note that you're using a database link here, so that might not be possible.

0

Can you do it in several steps?

CREATE GLOBAL TEMPORARY TABLE my_temp_table 
( numeroot ... )
ON COMMIT DELETE ROWS;

insert into my_temp_table (numeroot) 
select distinct numeroot from oym_planmantenimiento;

SELECT sf.order_id
FROM or_order@smartflex sf
JOIN my_temp_table x
    ON sf.order_id=x.numeroot;

DROP ...;
commit;

Another attempt might be to try materialize oym_planmantenimiento by adding a non-deterministic function. Note that I do not know Oracle, so this may not work at all:

WITH y AS ( SELECT numeroot, rand() as rnd 
            FROM oym_planmantenimiento )
   , x AS ( SELECT distinct numeroot FROM y )
SELECT sf.order_id
FROM or_order@smartflex sf
JOIN x 
    ON sf.order_id=x.numeroot
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.