Why is the function based index not being used in this query
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

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

Hybrid View

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577

    Arrow

    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.

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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

  3. #3
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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.

  4. #4
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Still waiting...
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  5. #5
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    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) */ ?
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  6. #6
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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.

  7. #7
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    trunc(b.news_use_date (+) )

    Don't funtion based indexes kick in ONLY if the query code looks EXACTLY like the index creation code?
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  8. #8
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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.

  9. #9
    Join Date
    Sep 2000
    Posts
    384
    Try using a Materialized view and see how it works ?
    Radhakrishnan.M

  10. #10
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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
  •  


Click Here to Expand Forum to Full Width