Improving Performance of SELECT COUNT(*)
870354Jun 21 2011 — edited Jun 22 2011We are trying to get a count on a huge table on the basis of a non-indexed date column:-
e.g.
SELECT COUNT(*) FROM TABLE1 WHERE TABLE1.DATE_COL1 > SYSDATE-7
Here SYSDATE-7 is some threshold, this could be anything. Basically we are trying to get count from the table where a certain date column is greater than a given threshold. So you can consider the above query for example.
The problem is that it is taking too much time to return the result. The table is a huge one containing 500+ million records. The database is Oracle.
Now the column in question , that is DATE_COL1 is a non indexed one and also I am not allowed to create any index on it. So the query takes more than half an hour.
Also we are not allowed to use hint such as PARALLEL.
So in the face of these constraints would you please suggest something that will improve the performance?