NHibernate + Fluent NHibernate + Oracle Index

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
           )
NOPARA开发者_开发问答LLEL;

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!

Are your optimizer statistics up to date? If not you may find that once they are generated you don't need the hint at all.

Presumably the query without the hint is not using the index.

Try this in any sql tool:

explain plan for
select /*+ INDEX(x IDX_CASHFLOW_COMPLEX)*/ * 
from MYPR.CASHFLOW x 
where fk_debet in (21856, 21854, 21855)
/

select * from table(dbms_xplan.display)
/

... and post the output of the last command. We can use that to see what estimations oracle is making of the expected cardinality of the result set.

With hint /* +INDEX(...) */ :

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 238 | 26 |
| 1 | INLIST ITERATOR | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| CASHFLOW | 1 | 238 | 26 |
|* 3 | INDEX RANGE SCAN | IDX_CASHFLOW_COMPLEX | 1 | | 2 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("X"."FK_DEBIT"=21854 OR "X"."FK_DEBIT"=21855 OR "X"."FK_DEBIT"=21856)
Note: cpu costing is off

Without hint /* +INDEX(...) */ :

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 238 | 2 |
|* 1 | TABLE ACCESS FULL | CASHFLOW | 1 | 238 | 2 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X"."FK_DEBIT"=21854 OR "X"."FK_DEBIT"=21855 OR "X"
."FK_DEBIT"=21856)
Note: cpu costing is off