Why does my count(*) query time out but the inline view whose records it is counting does not?

Question

Can't figure this one out... when I execute a select query it shows an explain plan cost of ~4500 and takes ~3 seconds to return. When I wrap that query (no changes) inside of:

select count(*) from (
 /*query here*/
)

It times out. It's at 5 minutes and counting now.

I tried this in SQL Developer and Aqua Data Studio -- same results.

Answer 1

It could be that optimizer doesn't "know" what your tables really look like.

Try to analyze the tables that are used in the query:

EXEC dbms_stats.gather_table_stats(
    ownname => <owner-name>,
    tabname => <table-name>,
    cascade => TRUE );

Answer 2

It's impossible to tell without seeing your entire query, but for starters, I'd try looking at the explain plan output.

Answer 3

When doing COUNT(*), the optimizer changes its goal to ALL_ROWS which may affect the plan severely.

Could you please post your query here?

Answer 4

The COUNT(*) operation counts all rows fed to it by execution plan branch under it. Meaning that the optimizer waits for ALL_ROWS to return before completing its execution which can severely affect your query plan.

Answer 5

You want to use a defined select for your count query, the optimized doesn't know what tables it will be counting over.

Also, dont use count(*) as it will be counting across all columns, just use count(COLUMN_NAME)

Answer 6

If you are trying to do something like this:

SELECT COUNT(*)
FROM (SELECT...FROM...);

Then you should try specifying what field you wish to perform the count on, for example:

SELECT COUNT(myfield)
FROM (SELECT whatever, whatever, whatever FROM MYTABLE);

Is that what you are referring to?