How to optimize this nested SQL SELECT query

Question

I have a very slow query that looks like this:

SELECT  *
FROM    (
         SELECT ..., nn_key_fast(nachname) nnk, ...
         FROM   t1
           JOIN t2 ON
               ...
           JOIN t3 ON
               ...
           JOIN t4 ON
               ...
         WHERE  ...
         AND t4.POSTCODE='1234'
        )
WHERE  ... AND nnk LIKE "N%"

Now, the inner select takes ~2min. If I remove the last WHERE clause (t4.POSTCODE), it executes in ~4secs. The result without this clause will be < 1000 records, so a really small set.

So, my thought was: move that clause to the outside SELECT, then it will only be applied to the resulting <1000 records.

But no. The query takes exactly as long, so to be clear:

SELECT  *
FROM    (
         SELECT  .....
         FROM    t1
           JOIN  t2 ON
                 ...
           JOIN t3 ON
                 ...
           JOIN t4 ON
                 ...
         WHERE   ... 
        )
WHERE  ...
AND POSTCODE='1234'

takes the same 2 minutes that the first version takes.

This seems insane to me.

Intuitively, this must be executed by the query optimizer like this: Create a table from the inner select as in:

CREATE TABLE res_from_inner AS (
                                SELECT .....
                                FROM t1
                                  JOIN t2 ON
                                       ...
                                  JOIN t3 ON
                                       ...
                                  JOIN t4 ON
                                       ...
                                WHERE ... 
                               )

... and then do the outer select only on this table, like this:

SELECT  *
FROM    res_from_inner
WHERE   POSTCODE='1234'

And if I do this manually, the CREATE TABLE query takes ~4sec, and the second SELECT takes, as expected < 1sec.

How is this possible, and what to do about it?

What is in the select clause of the subquery --> SELECT ..... ? If you use any functions or expressions (like to_char( x ) as y or x+y as z) in the SELECT clause, the results of which yoou then use in the WHERE clause (like y = '123' or z=22, then Oracle cannot push predicates down to the subquery, indexes are useless and it must be slow. Without details, it's difficult to advise anything here, please show the full query and it's execution plan, for now I vote to close your question as too broad. — krokodilko
– krokodilko, Commented Apr 19, 2019 at 13:44
@krokodilko: Feel a bit stupid right now... you are right, there is a function in the select clause, and the calculated field is used in the outer select. However, when I execute the whole query, today it takes 13 seconds... BUT: if I create a table from that select (create table as...), it takes 2 minutes again. And there are only about 1000 records in the result set. Sorry for the confusion, I'm not sure what I'm actually asking anymore. — jackthehipster
– jackthehipster, Commented Apr 23, 2019 at 7:55

Gordon Linoff · Accepted Answer · 2019-04-19 13:15:36Z

1

You have to look at the execution plans to see what is really happening. Something is changing.

One thing you might try is a CTE:

with s as (
      <subquery here>
     )
select s.*
from s
where . . .;

Oracle might automatically materialize this. Or you can give a hint:

with s as (
      select /*+ materialize */ . . .
      . . .
     )
select s.*
from s
where . . .;

answered Apr 19, 2019 at 13:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jackthehipster Over a year ago

Thanks for this tip. Never used WITH before but this seems fairly straightforward. But also never heard of the materialize-hint, which seems to have become unnecessary since V10g (according to this: dba-oracle.com/t_materialize_sql_hint.htm). Interesting stuff.

ValFrizzle · Accepted Answer · 2019-04-19 13:16:50Z

1

I try to stay away from nested select statements. In this case, I'd have the inner select statement as a subquery and then select what I want from it.

with subquery as ( select...from...join...join...join...where )

select* from subquery

--hope that helps :]

answered Apr 19, 2019 at 13:16

ValFrizzle

135 bronze badges

1 Comment

jackthehipster Over a year ago

Thanks. Will try to refactor my nested selects (we use them a lot) in the future into WITH CTEs. This looks much cleaner.

Collectives™ on Stack Overflow

How to optimize this nested SQL SELECT query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related