Oracle Partition Table - How to Count
I have a partitioned table (MYTABLE) for Oracle (11g). It is a rather large table, divided into an INSERT_DATE column (no time).
The problem is that Count (*) is giving the wrong result .
Returned request: 5 726 829 673
SELECT count(*) FROM MYTABLE WHERE INSERT_DATE >= TO_DATE('01/01/2015', 'DD/MM/YYYY')
Below is the request: 13 076 228 720
SELECT SUM(1) FROM MYTABLE WHERE INSERT_DATE >= TO_DATE('01/01/2015', 'DD/MM/YYYY')
How is this possible? What is the reason for this difference?
source to share
Check out the " Note
" section of execution plans for two queries - is there a plan management feature that forces queries to use radically different plans? Run explain plan for SELECT...
and then select * from table(dbms_xplan.display);
to find out if Oracle is performing queries differently.
For example, a DBA might create a SQL profile for a version count(*)
, forcing the optimizer to use an index, and that index is corrupted and needs to be rebuilt.
Or some evil developer used DBMS_ADVANCED_REWRITE
to literally change the request text, but only for one of the statements. Check for entries in DBA_REWRITE_EQUIVALENCES
.
source to share