-
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
ronnie_yours@yahoo.com
You can if you think you can.
-
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?
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
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
ronnie_yours@yahoo.com
You can if you think you can.
-
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
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) */ ?
-
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
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
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
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
Try using a Materialized view and see how it works ?
Radhakrishnan.M
-
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.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|