Oracle Partitioned Table - How to Count

user_544433 0
Viewed 316

I have a partitioned table (MYTABLE) on Oracle (11g). This is a quite large table, partitioned by INSERT_DATE column (without time).

The problem is that, Count(*) gives incorrect result.

The query below returns: 5,726,829,673

SELECT count(*) FROM MYTABLE WHERE INSERT_DATE >= TO_DATE('01/01/2015', 'DD/MM/YYYY')

The query below returns: 13,076,228,720

SELECT SUM(1) FROM MYTABLE WHERE INSERT_DATE >= TO_DATE('01/01/2015', 'DD/MM/YYYY')

How can it be possible? What is the reason for this difference?

partitioning oracle11g oracle
1 Answers

Check the Note section of the execution plans for the two queries - is there a plan management feature causing the 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 running the queries differently.

For example, a DBA might have created a SQL profile on count(*) version, forcing the optimizer to use an index, and that index is corrupt and needs to be rebuilt.

Or some evil developer used DBMS_ADVANCED_REWRITE to literally change the query text, but only for one of the statements. Check for any entries in DBA_REWRITE_EQUIVALENCES.

user_4091720
Related