CODEWITHSUNDEEP
sqloracle-database
Amar
Amar

Reputation: 19

Does having too many subqueries in the from clause slow down the query

I'm having to create subqueries in the from clause just to write cleaner code but in return does having too many subqueries slow down the query.

for example:

select id,
  name,
  annual_income * 0.10 AS tax
from (
  select id,
    name,
    annual_income
  from (
     select id,
       first_name || ' ' || last_name AS name
       income * 12 AS annual_income
  )
);

the above is just a made up example

Upvotes: 0

Views: 445

Answers (1)

MT0
MT0

Reputation: 168147

[TL;DR] It depends on the queries you are using in the sub-queries.

In your case:

select id,
       name,
       annual_income * 0.10 AS tax
from   (
  select id,
         name,
         annual_income
  from   (
    select id,
           first_name || ' ' || last_name AS name
           income * 12 AS annual_income
    from   table_name
  )
);

Will get rewritten by the SQL engine to:

select id,
       first_name || ' ' || last_name AS name
       income * 1.2 AS tax
from   table_name;

There will be no difference in performance between the two queries and if it is easier for you to understand and/or maintain the query in its expanded form then you should use that format and not worry about the nested sub-queries.

However, there are some cases when sub-queries can affect performance. For example, this question was a particularly complex issue where the sub-query factoring clause was being materialized by the inclusion of the ROWNUM pseudo-column and that forced the SQL engine to execute in a particular order and prevented if from rewriting the query into a more optimal form and prevented it from using an index which made the query very slow.

Upvotes: 1

Related Questions