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

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

Ronnie, you have to set in init.ora:

query_rewrite_enabled = true
query_rewrite_integrity = trusted

Thus you will enable the function based index functionallity.

Has the user who runs the query been granted query rewrite?

Hi Julian,

All the parameters are set and permissions granted.

Thats evident from the excution plan

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)

the index its using is
idx_funlkpproductlocationname on lkp_product_location_name(upper(product_name)) tablespace indx;


So.. Its using one of the function based indexes and not the other.

Still waiting...

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) */ ?

Quote:

---

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

trunc(b.news_use_date (+) )

Don't funtion based indexes kick in ONLY if the query code looks EXACTLY like the index creation code?

Originally posted by KenEwald
trunc(b.news_use_date (+) )

My fault ken. Used the incorrect index name. Sorry about that.

I tried it with the correct name now and to be sure that its exactly the same the the index i dropped and recreated the index. But it still does not work.

Here is what i did

SQL> drop index idx_newsstory_funnewsusedate;

Index dropped.

Elapsed: 00:00:09.74
SQL> create index idx_newsstory_funnewsusedate on news_story(trunc(news_use_date)) tablespace indx;


Index created.

Elapsed: 00:00:49.54
SQL> 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
7 /

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

Elapsed: 00:00:01.72

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
----------------------------------------------------------
1749 recursive calls
10 db block gets
33110 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

Try using a Materialized view and see how it works ?

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.

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

Quote:

---

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:

Code:

---

analayze table RONNIE compute statistics for column GENDER;

---

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

Quote:

---

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.

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