Originally posted by KenEwald
trunc(b.news_use_date (+) )

My fault ken. Used the incorrect index name. Sorry about that.

I tried it with the correct name now and to be sure that its exactly the same the the index i dropped and recreated the index. But it still does not work.

Here is what i did

SQL> drop index idx_newsstory_funnewsusedate;

Index dropped.

Elapsed: 00:00:09.74
SQL> create index idx_newsstory_funnewsusedate on news_story(trunc(news_use_date)) tablespace indx;


Index created.

Elapsed: 00:00:49.54
SQL> select /*+ INDEX(idx_newsstory_funnewsusedate ) */ a.product_name,count(b.product_location_id)
2 from lkp_product_location_name a,news_story b where
3 upper(a.product_name )=upper('headliner') and
4 a.product_location_id=b.product_location_id (+) and
5 trunc(b.news_use_date (+) ) between sysdate-10 and sysdate
6 group by a.product_name
7 /

PRODUCT_NAME COUNT(B.PRODUCT_LOCATION_ID)
-------------------------------------------------- ----------------------------
Headliner 119

Elapsed: 00:00:01.72

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=2 Bytes=120)
1 0 SORT (GROUP BY) (Cost=5 Card=2 Bytes=120)
2 1 NESTED LOOPS (OUTER) (Cost=3 Card=2 Bytes=120)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'LKP_PRODUCT_LOCATION
_NAME' (Cost=1 Card=1 Bytes=40)

4 3 INDEX (RANGE SCAN) OF 'IDX_FUNLKPPRODUCTLOCATIONNAME
' (NON-UNIQUE) (Cost=1 Card=1)

5 2 TABLE ACCESS (BY INDEX ROWID) OF 'NEWS_STORY' (Cost=2
Card=206 Bytes=4120)

6 5 INDEX (RANGE SCAN) OF 'IDX_NEWSSTORY_PRODLOCATIONID'
(NON-UNIQUE) (Cost=1 Card=206)





Statistics
----------------------------------------------------------
1749 recursive calls
10 db block gets
33110 consistent gets
0 physical reads
0 redo size
458 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
60 sorts (memory)
0 sorts (disk)
1 rows processed