Originally posted by KenEwald
Don't bite my head off if I don't get it right away ..

Looks like the CBO is not picking it up because it thinks it's not the *best* plan.

Can you force it with /*+ index(idx_newsstory_funnewsusedate) */ ?
This is what I get when i Give a hint. It still does not use the index

SQL> ed
Wrote file afiedt.buf

1 select /*+ INDEX(idx_newsstory_funupdateddate) */ a.product_name,count(b.pr
2 from lkp_product_location_name a,news_story b wh
3 upper(a.product_name )=upper('headliner') and
4 a.product_location_id=b.product_location_id (+)
5 trunc(b.news_use_date (+) ) between sysdate-10 a
6* group by a.product_name
SQL> /

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

Elapsed: 00:00:01.22

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
----------------------------------------------------------
1712 recursive calls
10 db block gets
33106 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