SQL slow when having subquery in in statement

Question

I have the following statement:

 WITH foos AS (
    select regexp_substr('H50','[^,]+', 1, level) x from dual
    connect by regexp_substr('H50', '[^,]+', 1, level) is not null
), baars AS (
    select regexp_substr('G30','[^,]+', 1, level) x from dual
    connect by regexp_substr('G30', '[^,]+', 1, level) is not null
) 
  select 
  count(*) 
  from VIEW
  where foo in (
    'H50'
    -- select x from foos
  )
  and bar in (
   'G30'
   -- select x from bars
  );

When using the constants G30 and H50 it is really fast. However when I use the subquerys, it is realy slow (~5 seconds). I have no idea why this can be the case. any ideas?

Performance questions should include EXPLAIN ANALYZE and some information about table size, index, current time performance, desire time, etc. Slow is a relative term and we need a real value to compare. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Nov 24, 2016 at 14:44
Why do you use where foo in ('H50') and not where foo ='H50'? Not sure this has something to do with the performance issue you are facing, but still... (the same for the second condition of course). — FDavidov
– FDavidov, Commented Nov 24, 2016 at 14:46
Because I want to have multiple foos. I want to pass it as string parameter like 'H50,H51,H52,H53' — matthias
– matthias, Commented Nov 24, 2016 at 14:47
So you want to know why where foo in ('H50') is faster than where foo in (select x from foos)? Check the EXPLAIN PLAN — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Nov 24, 2016 at 14:50

David דודו Markovitz · Accepted Answer · 2016-11-24 16:32:31Z

1

For start, the inline views are not being used in the first use-case, as in not being executed at all.

with    t1 (n) as (select 1 from dual union all select n+1 from t where n <= 10)
       ,t2 (n) as (select 1 from dual union all select n+1 from t where n <= 1000000000000)

select  *
from    t1
;

edited Nov 24, 2016 at 16:32

answered Nov 24, 2016 at 15:10

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

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

Collectives™ on Stack Overflow

SQL slow when having subquery in in statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related