CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Emerson Yassunaga
Emerson Yassunaga

Reputation: 89

Select count taking long time to return

I´m doing a complex query which is completed in 3 seconds:

WITH
Query1 as
( select ...
),
Query2 as
( select ... from Query1 ...
),
ComplexQuery AS
( select ... from Query2 ...
)
select * from ComplexQuery;

Along these queries there are a lot of operations such as GROUP BY, ORDER BY, UNION.

But when I change the last line with this:

select count (*) from ComplexQuery;

This produces a strange effect. The query starts to execute for a long time. I waited about 30 minutes and stopped it.

I expected that, as the original query was executing in 3 seconds, the count should take about 0.01s so the total time would be of 3.01s. Instead, what I see is that the count seems to start some kind of recursion involving all the previous subqueries - that´s what I can imagine.

Is it normal with Oracle/SQL ? Or, why does this happen ? How could I avoid this problem ?

Upvotes: 1

Views: 601

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175726

You could try to hint optimizer to materialize intermediate steps with /*+MATERIALIZE*/:

WITH Query1 as       ( select /*+MATERIALIZE*/ ...)
    ,Query2 as       ( select /*+MATERIALIZE*/ ... from Query1 ...)
    ,ComplexQuery AS ( select /*+MATERIALIZE*/ ... from Query2 ...)
select count (*) from ComplexQuery;

Upvotes: 1

ca.po. Rodriguez
ca.po. Rodriguez

Reputation: 46

This will sound super silly, but give it a try.

I hope it helps!

WITH
Query1 as
( select ...
),
Query2 as
( select ... from Query1 ...
),
ComplexQuery AS
( select ... from Query2 ...
),
CountRowsQuery AS
( select count (*) as RowsInQuery from ComplexQuery
)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269883

This is an educated guess.

Your query has no order by or window functions in the outer query. This means that when results start being generated, then they can be returned. That is, you are seeing the first rows being returned, but not the last rows.

When you do select count(*), the entire result set from the complex query needs to be generated -- and then counted. So, this query cannot return any results until all the results have been processed.

I associate this behavior with nested-loop joins (although that is not the only time this happens). If this is happening you will see delays as you page through the result set being returned.

Upvotes: 0

Related Questions