Incorrect Count(*) of Table Records

Question

I am trying to take a count of a table in oracle. When I run a very simple count such as:

SELECT COUNT(*) FROM EDW.SCADA_VALUE_HIST; --Returns [114315627]

It returns a result (in brackets outside the query) that seems correct. Now when I apply filter criteria to the same table, it is returning MORE rows than the count(*) of the table:

SELECT COUNT(*) FROM EDW.SCADA_VALUE_HIST 
WHERE UPDT_VAL_EFF_DTTM <= (SYSDATE-5); --Returns [131416178]

In addition, I went ahead and checked the stats of the table (details in sql developer) and it returns an even higher count [146436917] (I'm aware this is not 100% accurate but it should be reasonable for this exercise). I'm not seeing how a filter condition can return more count rows than the table itself. Here are the details:

1. The query ran in the same database within 10 seconds of each other
2. The table inserts ~60k rows every 10 minutes through a scheduled job
3. The scheduled job executes a procedure that uses a merge (below)
4. UPDT_VAL_EFF_DTTM is a date field and this column is not nullable
5. The table contains 5 total indexes (Composite Primary key (4 fields) and unique, followed by 4 non-unique indexes
6. It is running on Oracle 11gr2

7. Database is running DataGuard environment with 3 physical standbys and 1 primary

   create or replace PROCEDURE UPDATE_VALUE_HIST AS
       v_date VARCHAR2(16);
       g_counter NUMBER(10,0) := 0;
       g_insertspeed NUMBER (10,0) := 1000;
       g_inserttime NUMBER (10,0) := 20; 
   BEGIN
     BEGIN
   
       MERGE INTO EDW.SCADA_VALUE_HIST SVH  
       USING 
      (SELECT
          SV.COL1,SV.COL2,SD.COL3,SD.COL4,
          SVD.COL5,SVD.COL6,SVD.COL7,SV.COL8,
          SVT.COL9 AS VALUE_TYPE_NAME,SV.COL10,SD.COL11,
          SYSDATE as UPDT_VAL_EFF_DTTM,'U',SV.COL13,SV.COL14
       FROM SCHEMA.T1 SV,
            SCHEMA.T2 SVD,
            SCHEMA.T3 SVT,
            SCHEMA.T4 SD
       WHERE SV.FIELD1= SVD.FIELD1
       AND SVD.FIELD2= SVT.FIELD2
       AND SD.FIELD3= SVD.FIELD3
       AND SV.FIELD4 IS NOT NULL) B
      ON (
       SVH.FIELD1= B.FIELD1
       AND SVH.FIELD2= B.FIELD2
       AND SVH.FIELD3= B.FIELD3
       AND SVH.FIELD4 = B.FIELD4 
       )
     WHEN NOT MATCHED
       THEN
          INSERT (COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, 
            COL9, COL10, COL11, SYSDATE, 'U', COL13, COL14);
   
     COMMIT;  
   
     END;                
   END;
   

I've tried googling a couple of times but most of the error in count posts deal with joins and filtering conditions. This is a very strange one to me.

EDIT:

Explain plan for first query:

SELECT STATEMENT -> SORT (AGGREGATE) -> INDEX (FAST FULL SCAN ON OBJECT PK)

Cardinality: 1 -> 1 -> 146436917 Cost: 85031

Explain plan for second query:

SELECT -> SORT (AGGREGATE) -> INDEX (FAST FULL SCAN ON OBJECT DIFFERENT INDEX THAN PK (filter predicate index)) -> FILTER PREDICATES -> UPDT_VAL_EFF_DTTM

Cardinality: 1 -> 1 -> 131379677 Cost: 105341

Answer 1

When I've seen this happen (just once or twice) it was because an index was corrupted. We either rebuilt or dropped and recreated the index and the problem went away.

This is only an anecdote, and doesn't explain what happened or why. But before you waste a huge amount of time dealing with Oracle support you'll want to try these "bad" solutions first. Spend the 5 minutes now to rebuild it and you might avoid days of investigation later. If the problem never happens again the root cause doesn't really matter.