I have a table with more than 10 000 000 rows. In TOAD this query works very well on it:
select /*+ INDEX(x IDX_CASHFLOW_COMPLEX)*/ *
from MYPR.CASHFLOW x
where fk_debet in (21856, 21854, 21855)
IDX_CASHFLOW_COMPLEX is index on 5 columns created by following script:
CREATE INDEX MYPR.IDX_CASHFLOW_COMPLEX ON MYPR.CASHFLOW
(FK_DEBIT, FK_CREDIT, FK_DOCUMENT, PAYMENTDATE, FK_PAYMENTCODE)
LOGGING
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
Similar query but without Oracle hint syntax works significantly slower!
Would you please suggest is it possible to tell NHibernate to add Oracle hint in the query?
Thanks!