CODEWITHSUNDEEP
sqloracle-database
Giovanni
Giovanni

Reputation: 543

Oracle subquery with MAX operator taking very long

I have a web application that was using a very complex database view to retrieve some data which appeared to be very slow, taking up to 3 minutes to complete only . After a thorough investigation I've found what was the cause of the problem. In my code I was using the following condition in the WHERE clause to retrieve only the LAST element of a joined table:

SELECT ...
FROM MY_TABLE
JOIN TABLE_JOIN_XX tableA on ....
..lots of other joins ...
WHERE
 tableA.id = 
   (SELECT MAX (id) FROM TABLE_JOIN_XX tableB WHERE tableA.id_parent = tableB.id_parent)

I have then changed the condition in the following way:

 tableA.id >= ALL 
   (SELECT id FROM TABLE_JOIN_XX tableB WHERE tableA.id_parent = tableB.id_parent)

and now the query takes only a couple of seconds.

Now I'm wondering why there is this huge difference in execution time between using the MAX operator and the ALL operator. I am quite surprised indeed. I am no DBA and not very expert in query optimization, but maybe there is something that I don't know and that I should take in consideration while developing my queries for database access.

Or maybe is something related to a problem in that specific Oracle instance and not to the query? I've never noticed this problem in other instances of the same database.

Looking at the explain plan I've noticed that in the second case (and not in the first one) Oracle replaces the ALL operator with a NOT EXISTS:

 not exists 
  (select 0 from TABLE_JOIN_XX tableA 
     where tableA.id_parent=:b1 and LNNVL (id<=:b2))

Any suggestion? Many thanks.

Upvotes: 1

Views: 1076

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Your query seems malformed. This is your statement:

WHERE tableA.id = (SELECT MAX(id) FROM TABLE_JOIN_XX tableB WHERE tableA.id = tableB.id)

You are doing a correlated subquery on the column id. Then you are choosing the maximum value. The subquery can only return tableA.id or NULL, so this is equivalent to:

WHERE EXISTS (SELECT 1 FROM TABLE_JOIN_XX tableB WHERE tableA.id = tableB.id)

Perhaps Oracle is getting a bit confused. In any case, by using MAX(), you are saying that all the values need to be processed, so Oracle is probably doing that. In fact, it only needs to find one row with a value.

An index on TABLE_JOIN_XX(id) should help this query even more.

Upvotes: 2

Related Questions