Originally posted by julian Have you collected histograms statistics? Run analyze on the table, collecting the istogram statistics and try the query again. Try analyze with compute if the table is not too big.
I Analyzed the tables with compute statistics and still it does not use the index. Infact it has stopped using the other indexes also and now only does full table scans.
I am not sure what you mean by collect Histogram statistics.
Wrote file afiedt.buf
1 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
For OLTP only I am saying that you should use MV .The only thing that you will be using is some disk space .Otherthan that
I don't see anything wrong as far as I am concerned .It has worked for me ..Eventhough I have not used them very extensivly to say that It will