views:

237

answers:

3

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!

A: 

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.

Tony Andrews
I'll check that tommorow, thanks for the answer
barser
A: 

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.

David Aldridge
I'll post the result here tommorow from work. Thanks!Actually I'm interesting in the posibility of addition Oracle hints to any nhibernate query. The case I described in the beginning is just an example... Or adding hints to sql isn't good practice in oracle world?
barser
the explain plan was posted as another answer because of lack of code formatting in comment field
barser
It looks like the optimiser believes that the table is tiny, because it rates the cost of a full table scan as being "2". The table probably has incorrect statistics and they need to be gathered again using DBMS_Stats. I appreciate that putting a hint in the query would do the job, but you'd have to do this for almost every query that touches the table -- much better to get the statistics sorted out.
David Aldridge
A: 

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
barser