Quote:
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.
Hi ,
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.
SQL> ed
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
SQL>
SQL> /
PRODUCT_NAME COUNT(B.PRODUCT_LOCATION_ID)
-------------------------------------------------- ----------------------------
Headliner 131
Elapsed: 00:00:00.71
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2204 Card=4 Bytes=12
4)
1 0 SORT (GROUP BY) (Cost=2204 Card=4 Bytes=124)
2 1 HASH JOIN (OUTER) (Cost=2192 Card=1658 Bytes=51398)
3 2 TABLE ACCESS (FULL) OF 'LKP_PRODUCT_LOCATION_NAME' (Co
st=2 Card=6 Bytes=126)
4 2 TABLE ACCESS (FULL) OF 'NEWS_STORY' (Cost=2189 Card=41
46 Bytes=41460)
Statistics
----------------------------------------------------------
0 recursive calls
99 db block gets
14420 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
1 sorts (memory)
0 sorts (disk)
1 rows processed