We have table structure like this:

/******************************************************************************/
/* Table */
/******************************************************************************/

CREATE TABLE T2008_11_0002 (
    ID_REKORDU INTEGER NOT NULL,
    CZASPOCZ TIMESTAMP,
    CZASKON TIMESTAMP,
    CZASTRWANIA INTEGER,
    TYP CHAR(1),
    PODTYP CHAR(3),
    NRFIZYCZNYA INTEGER,
    NRFIZYCZNYB INTEGER,
    NRKATALOGOWYA VARCHAR(24),
    NRWIRTUALNYA VARCHAR(36),
    INFWYBIERCZA VARCHAR(36),
    CZASZESTAWIANIA INTEGER,
    IMPULSY INTEGER,
    CENA DECIMAL(7,2),
    VAT VARCHAR(4),
    ID_KIERUNKU INTEGER,
    ID_LOKALIZACJI INTEGER,
    TYPUDG SMALLINT,
    WIRT_SIEC CHAR(1),
    CZASBZ INTEGER,
    INFWYBBZ VARCHAR(36),
    NRFIZBZ INTEGER,
    NRKATALOGOWYB VARCHAR(24),
    PLAN_A VARCHAR(24),
    PLAN_B VARCHAR(24),
    WIAZKA_A VARCHAR(24),
    WIAZKA_B VARCHAR(24),
    FX_A VARCHAR(8),
    FX_B VARCHAR(8),
    WIRTSIEC_B CHAR(1),
    RDI VARCHAR(36),
    CALL_REF BIGINT,
    LOKALIZACJA VARCHAR(50),
    KIERUNEK VARCHAR(50),
    OKRES_DOB VARCHAR(36),
    STREFA VARCHAR(8),
    PRZEADR_Z VARCHAR(36),
    PRZEADR_NA VARCHAR(36),
    TYP_PRZEADR CHAR(1),
    PODTYP_PRZEADR CHAR(3),
    PRYWATNE CHAR(1),
    CENA_IMP DECIMAL(7,2),
    CENA_RMO DECIMAL(7,2),
    PARTYCJA VARCHAR(50),
    ID_WPROWADZONE_PLIKI INTEGER,
    KOD_PIN VARCHAR(10),
    REALIZACJA CHAR(1),
    CENA_BRUTTO DECIMAL(12,4),
    CENA_IMPULSU DECIMAL(7,2),
    PKWIU VARCHAR(10),
    TAR_IMP CHAR(1),
    IMPULSY_OBLICZONE INTEGER,
    ID_DANYCH INTEGER,
    KIER_POL VARCHAR(3)
);

/******************************************************************************/
/* Primary Keys */
/******************************************************************************/

ALTER TABLE T2008_11_0002 ADD PRIMARY KEY (ID_REKORDU);


/******************************************************************************/
/* Indices */
/******************************************************************************/

CREATE INDEX I1_2008_11_0002 ON T2008_11_0002 (CZASKON);
CREATE INDEX I2_2008_11_0002 ON T2008_11_0002 (NRFIZYCZNYA);
CREATE INDEX I3_2008_11_0002 ON T2008_11_0002 (CZASPOCZ);
CREATE INDEX I4_2008_11_0002 ON T2008_11_0002 (NRWIRTUALNYA);
CREATE INDEX I5_2008_11_0002 ON T2008_11_0002 (NRKATALOGOWYA);
CREATE UNIQUE INDEX I6_2008_11_0002 ON T2008_11_0002 (CZASKON, NRKATALOGOWYA, CZASTRWANIA, NRWIRTUALNYA, INFWYBIERCZA, NRFIZYCZNYA);
CREATE INDEX I7_2008_11_0002 ON T2008_11_0002 (ID_WPROWADZONE_PLIKI);
CREATE INDEX I8_2008_11_0002 ON T2008_11_0002 (ID_DANYCH);
CREATE INDEX I9_2008_11_0002 ON T2008_11_0002 (CALL_REF);


Problem is very simple, our queries returned wrong number of records. We observed this effect only in specific tables with rather big number of indexes (10 or so). And records are inserted to this tables with specific packages. Couple of hundreds of records in one transaction. We don't know if this is important, but we didn't observe this effect on the rest our tables.


Example number 1


query: "select count(*) from (select * from T2008_11_0002 order by NRFIZYCZNYA); "

  record_count: 56425 - incorrect record count
  used plan is incorrect: PLAN (T2008_11_0002 ORDER I7_2008_11_0002)
  index I7_2008_11_0002 is for ID_WPROWADZONE_PLIKI
 
but when we suggested correct plan in query: "select count(*) from (select * from T2008_11_0002 PLAN (T2008_11_0002 ORDER I2_2008_11_0002) order by NRFIZYCZNYA);"

  record_count: 57661 is correct
  used plan is also correct: PLAN (T2008_11_0002 ORDER I2_2008_11_0002)

Select from select in our query is only to force the use of query plan


Example 2


query: "select * from T2008_11_0002 where czaskon > '1970-01-01 00:00:00' "

 /* this condition is always true, because in this table czaskon is always higher than '2008-11-01 00:00:00' and is not null */;

record count: 141692 - incorrect record count
but used plan is correct: PLAN (T2008_11_0001 INDEX (I1_2008_11_0002))

but, when we suggested NATURAL plan in this query :"select * from T2008_11_0002 where czaskon > '1970-01-01' PLAN (T2008_11_0002 NATURAL);"
 
record count: 193551 - correct record count
used plan: PLAN (T2008_11_0002 NATURAL);

Both of this examples describe same problem, but during our searching we discover that this problem can occur in two different situations. We think that this is some problem with indexes, because rebuilding specific index (or backup and restore database) will solve this problem. We didn't know how this problem occurs and what are causes. This is very important issue for us, because major functionality in our software is failing.