1 Reply Latest reply: Jan 28, 2013 3:11 PM by SomeoneElse RSS

    Difference in record count for a same date -11gr2

    IamHariKrishna
      Guys - 11gr2 on Windows2005,64 bit.

      BILLING_RECORD_KPN_ESP - is a monthly partitioned table.
      BILLING_RECORD_IDX#DATE - is a local index on "charge_date" in above table.

      SQL> select /*+ index(BILLING_RECORD_KPN_ESP BILLING_RECORD_IDX#DATE) */
      2 (trunc(CHARGE_DATE)) CHARGE_DATE,
      3 count(1) Record_count
      4 FROM "RATOR_CDR"."BILLING_RECORD_KPN_ESP"
      5 where CHARGE_DATE='20-JAN-2013'
      6 group by trunc(CHARGE_DATE)
      7 /

      CHARGE_DATE RECORD_COUNT
      ------------------ ------------
      20-JAN-13 2401-------------->> Few records here.

      ----->> Here I can see only "2041" records for Jan/20. But in the below Query it shows "192610" for the same date.

      Why is this difference in record count?

      SQL> select /*+ index(BILLING_RECORD_KPN_ESP BILLING_RECORD_IDX#DATE) */
      (trunc(CHARGE_DATE)) CHARGE_DATE,
      2 count(1) Record_count
      3 FROM "RATOR_CDR"."BILLING_RECORD_KPN_ESP"
      4 where CHARGE_DATE>'20-JAN-2013'
      5 group by trunc(CHARGE_DATE)
      6 order by trunc(CHARGE_DATE)
      7 /

      CHARGE_DATE RECORD_COUNT
      ------------------ ------------
      20-JAN-13 192610---------------------->> More records here
      21-JAN-13 463067
      22-JAN-13 520041
      23-JAN-13 451212
      24-JAN-13 463273
      25-JAN-13 403276
      26-JAN-13 112077
      27-JAN-13 10478
      28-JAN-13 39158

      Thanks!