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

[ronnie]

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

[julian]

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?

[ronnie]

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.

[ronnie]

Still waiting...

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

[ronnie]

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

[KenEwald]

trunc(b.news_use_date (+) )

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

[ronnie]

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

[omegamark]

Try using a Materialized view and see how it works ?

[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.