CODEWITHSUNDEEP
sqloracle-database
Flounderer
Flounderer

Reputation: 652

Nested subquery in Oracle is slow

I have a table with three fields: ID, date and action. The three possible values of action are X, Y and Z. I want a query which, for each ID in the table for which there ia a row with a Z action, will return the most recent row for the same ID with an X action, if one exists.

Here is what I want to do in MySQL. I copy-and-pasted the code from sqlfiddle.com, so I know it works.

create table a (id int,
              date int,
              action varchar(1));

insert into a values(1, 1, 'X');
insert into a values(1, 2, 'X');
insert into a values(1, 3, 'Z');
insert into a values(2, 1, 'X');
insert into a values(2, 2, 'Y');
insert into a values(2, 3, 'Y');
insert into a values(2, 4, 'Z');
insert into a values(3, 1, 'X');
insert into a values(3, 2, 'Y');
insert into a values(3, 3, 'X');
insert into a values(3, 4, 'Z');
insert into a values(4, 3, 'X');


SELECT a.id,
       max(a.date) as Xdate,
       b.date as Zdate,
       a.action FROM a,

               (SELECT * FROM a WHERE a.action = 'Z') b
                WHERE
                    a.date < b.date AND
                    a.ID = b.ID AND
                    a.action = 'X'
                GROUP BY a.id;

I can't use the GROUP BY clause with other fields like this in Oracle, so I did a nested subquery which found the maximum date for each ID separately, but it is very slow (I am expecting to get about 10^5 rows) and I was hoping that there was a better way to do it which would be faster. (I can't post my actual Oracle query at the moment because it does not run in SQLfiddle; it keeps complaining about rows being ambiguously defined.)

Can the above query be made to work in Oracle somehow? If not, is there an equivalent way to do it which will run in a reasonable time?

Upvotes: 0

Views: 3313

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

One problem trying to go between MySQL and Oracle is that Oracle is simply much more powerful.

You can solve your problem using analytic functions in Oracle. These are not available in MySQL.

Here is the Oracle solution:

select a.id, a.thedate as Xdate, a.ZDate
from (select a.*,
             min(case when action = 'Z' then thedate end) over
                 (partition by id
                  order by thedate
                  rows between current row and unbounded following
                 ) as Zdate,
             row_number() over (partition by id, action
                                order by thedate desc
                               ) as seqnum
      from a
     ) a
where a.action = 'X' and a.seqnum = 1;

You can see it on SQL Fiddle here.

Upvotes: 0

Gaston Flores
Gaston Flores

Reputation: 2467

Here other solution:

WITH temp AS (
     SELECT a.id, a.date_f, a.action FROM a WHERE a.action = 'Z'
)
SELECT a.id,
       max(a.date_f) as Xdate,
       temp.date_f as Zdate,
       a.action
FROM a
INNER JOIN temp ON temp.id = a.id AND a.date_f < temp.date_f
WHERE a.action = 'X'
GROUP BY a.id,temp.date_f,a.action;

Note: date_f is the field date. SQL Fiddle Demo.

Upvotes: 1

sgeddes
sgeddes

Reputation: 62831

This works (although it may be able to be simplified):

SELECT a.id,
       max(a."date") as Xdate,
       b."date" as Zdate,
       a.action 
FROM a INNER JOIN 
  (SELECT * 
   FROM a 
   WHERE a.action = 'Z') b
  ON a.ID = b.ID 
WHERE a."date" < b."date" 
   AND a.action = 'X'
GROUP BY a.id, b."date", a.action

Please note, you need to use " around reserved words -- in this case the column date. You also need to add all of your fields to the GROUP BY clause. MySQL allows it without, but Oracle does not.

Edit, simplified version:

SELECT a.Id, max(a."date"), b."date", a.action
FROM a
  INNER JOIN a b ON a.Id = b.Id AND b.action = 'Z'
WHERE a.action = 'X' 
  AND a."date" < b."date"
GROUP BY a.Id, b."date", a.action

Upvotes: 2

Related Questions