title: Incorrect Count(*) of Table Records
body: <p>I am trying to take a count of a table in oracle.  When I run a very simple count such as:</p>
  
  <pre><code>SELECT COUNT(*) FROM EDW.SCADA_VALUE_HIST; --Returns [114315627]
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>SELECT COUNT(*) FROM EDW.SCADA_VALUE_HIST 
  WHERE UPDT_VAL_EFF_DTTM &lt;= (SYSDATE-5); --Returns [131416178]
  </code></pre>
  
  <p>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:</p>
  
  <ol>
  <li>The query ran in the same database within 10 seconds of each
  other </li>
  <li>The table inserts ~60k rows every 10 minutes through a
  scheduled job</li>
  <li>The scheduled job executes a procedure that uses a
  merge (below)</li>
  <li>UPDT_VAL_EFF_DTTM is a date field and this column is not nullable</li>
  <li>The table contains 5 total indexes (Composite Primary key (4 fields) and unique, followed by 4 non-unique indexes</li>
  <li>It is running on Oracle 11gr2</li>
  <li><p>Database is running DataGuard environment with 3 physical standbys and 1 primary</p>
  
  <pre><code>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;
  </code></pre></li>
  </ol>
  
  <p>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.</p>
  
  <p>EDIT:</p>
  
  <p>Explain plan for first query:</p>
  
  <p>SELECT STATEMENT -> SORT (AGGREGATE) -> INDEX (FAST FULL SCAN ON OBJECT PK)</p>
  
  <p>Cardinality: 1 -> 1 -> 146436917 
  Cost: 85031</p>
  
  <p>Explain plan for second query:</p>
  
  <p>SELECT -> SORT (AGGREGATE) -> INDEX (FAST FULL SCAN ON OBJECT DIFFERENT INDEX THAN PK (filter predicate index)) -> FILTER PREDICATES -> UPDT_VAL_EFF_DTTM</p>
  
  <p>Cardinality: 1 -> 1 -> 131379677
  Cost: 105341</p>