Hi,

Here are the details of the 2 Tables

SQL> desc lkp_product_location_name
Name Null? Type
----------------------------------------- -------- ----------------------------
PRODUCT_LOCATION_ID NOT NULL NUMBER
PRODUCT_LOCATION_DESCRIPTION VARCHAR2(100)
CREATED_DATE DATE
CREATED_BY VARCHAR2(15)
SORT_ORDER NUMBER
PRODUCT_NAME VARCHAR2(50)

SQL> desc news_story
Name Null? Type
----------------------------------------- -------- ----------------------------
STORY_ID NOT NULL NUMBER
RAW_ID NOT NULL NUMBER
HEADLINE VARCHAR2(200)
STORY CLOB
AUTHOR VARCHAR2(100)
DATE_OF_STORY DATE
SOURCE VARCHAR2(200)
NEWS_USE_DATE DATE
RANK NUMBER
CREATED_DATE DATE
CREATED_BY VARCHAR2(20)
UPDATED_DATE DATE
UPDATED_BY VARCHAR2(20)
ANALYST_ID NUMBER
PRODUCT_LOCATION_ID NOT NULL NUMBER

SQL> select count(*) from lkp_product_location_name;

COUNT(*)
----------
36


SQL> select count(*) from news_story;

COUNT(*)
----------
83091


Here is the list of all the indexes created on the 2 tables

create index idx_newsstory_rawid on news_story(raw_id) tablespace indx;

create index idx_newsstory_headline on news_story(headline) tablespace indx;

create index idx_newsstory_prodlocationid on news_story(product_location_id) tablespace indx;

create index idx_newsstory_dateofstory on news_story(date_of_story) tablespace indx;

create index idx_newsstory_fundateofstory on news_story(trunc(date_of_story)) tablespace indx;

create index idx_newsstory_funupdateddate on news_story(trunc(updated_date)) tablespace indx;

create index idx_newsstory_funnewsusedate on news_story(trunc(news_use_date)) tablespace indx;

create index idx_funlkpproductlocationname on lkp_product_location_name(upper(product_name)) tablespace indx;



Here is the query i am trying to excute. From the explain plan I can see that the index on trunc(news_use_date) on the news_story table is not being used.

SQL> ed
Wrote file afiedt.buf

1 select a.product_name,count(*)
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 (+) ) = trunc(CoDB_News.Next_Business_Day( SYSDATE ))
6* group by a.product_name
SQL> /

PRODUCT_NAME COUNT(*)
-------------------------------------------------- ----------
Headliner 14

Elapsed: 00:00:03.75

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=8 Bytes=480)
1 0 SORT (GROUP BY) (Cost=5 Card=8 Bytes=480)
2 1 NESTED LOOPS (OUTER) (Cost=3 Card=8 Bytes=480)
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=824 Bytes=16480)

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





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
32726 consistent gets
0 physical reads
52 redo size
437 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed


Also I feel that 3.75 seconds for this query is very high and will affect the eprformance of the application overall as there are quite a few of such queries . Is there any way i can improve the performance of the query.

Please suggest