We help IT Professionals succeed at work.

How to deal with repeatedly used query within a big query?

jl66 used Ask the Experts™
Have the query logic below:

select some columns
from ( Query1) a, from ( Query1) b, other_tables
where some conditions including col1 <> b.col1, etc AND
other conditions including ( select col2 from  ( Query1));

In the above query, Query1 comes from expensive operations (many joins, selects, etc)
My question is
Are there ways to highly efficiently use Query1? No need to query it every time.
It seems that a temp table can be created to hold the query results.  Dear gurus, what is the best way to handle it?

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®


the output from Query1 is not so big ( a few 1000s) but it involves in several big tables and many expensive operations, so I try to re-use the result but not try to re-query it many times.
The name for this is self-join: use a single table twice on a query. The typical query is to ask for employers who wins more money than me:

SELECT e.name
FROM Employers e, Employers me
WHERE e.salary > me.salary
    AND me.id = 12345;

Where 12345 is my id.

Hope it helps.
Senior Software Engineer
Yes, temp tables would help you; by that matter even a view made of that Query 1 would help you.
The solution I'll show below is used when we would like to use just plain SQL...
query1_res as (select ... from ... where ...)  --just put your Query1 here
select some columns
from query1_res a, query1_res b, other_tables
where some conditions including col1 <> b.col1, etc AND
other conditions including ( select col2 from  query1_res);

Open in new window

This will execute query1_res once, put it in the spool and the result could be accessed in the main query.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
The above is called a CTE, and is usually a good way to cope with the demand of processing the same results in several different ways.
However, if you use the exact same query several times, the optimizer will detect that, and not perform the same operation more than once - as long as there are no references to outer values, that is. Still, using a CTE is much better for readability, reliability, and less prone to typos or other errors you might forget to correct on each occurance.


Excellent!! Thanks all of you so much.