Why is the function based index not being used in this query

[ronnie]

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

[julian]

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.

Probably the table is too small for the Optimizer to bother with indexes.

For histograms, check for example:

http://www.dbasupport.com/forums/sho...threadid=21503

If you want to recompute the statistics on the table RONNIE's column GENDER with the default number of buckets (it is 75), then you run:

analayze table RONNIE compute statistics for column GENDER;

[omegamark]

I am sure if use Materialise view you will get good performance .Just give a try and see how it works ..

[ronnie]

Originally posted by omegamark
I am sure if use Materialise view you will get good performance .Just give a try and see how it works ..

Hi Radha,

The performance is not that bad. Its only that i was wondering why is it not using the Function based indexes and is there any way to improve the query further.

Also the database is OLTP and i cannot use MV's for it bcos in the long run it will slow down the performance . Atleast thats what i think i have read. Coorect me if i am wrong.

[omegamark]

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