Have Geek Brain

Why is my counter (*) asking for a timeout but an inline view that it is not counting entries for?

Can't figure it out ... When I execute the select query it shows the explain plan cost ~ 4500 and takes ~ 3 seconds to return. When I complete this request (unchanged) inside:

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

Time. He's for 5 minutes and counts now.

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

+2


a source to share


6 answers


On execution, the COUNT(*)

optimizer changes its target to ALL_ROWS

, which can greatly affect the plan.



Could you post your request here?

+4


a source


The optimizer may not "know" what your tables look like.

Try to parse the tables used in the query:



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


a source


It is impossible to tell without seeing your entire query, but I would try to look at the plan explanation first .

0


a source


The operation COUNT(*)

counts all lines passed to it by the execution plan branch below it. This means that the optimizer waits for a return ALL_ROWS

before completing its execution, which can seriously affect your query plan.

0


a source


You want to use a specific selection for your query count

, the optimized one doesn't know what tables it will count.

Also, don't use count(*)

as it will count all columns, just usecount(COLUMN_NAME)

0


a source


If you are trying to do something like this:

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

Then you should try to specify in which field you want to do the count, for example:

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

Is this what you are talking about?

0


a source






More articles:


All Articles