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

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

  1. #11
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  2. #12
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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;
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  3. #13
    Join Date
    Sep 2000
    Posts
    384
    I am sure if use Materialise view you will get good performance .Just give a try and see how it works ..
    Radhakrishnan.M

  4. #14
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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.
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  5. #15
    Join Date
    Sep 2000
    Posts
    384
    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
    Radhakrishnan.M

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