Function Based Index Creation
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Function Based Index Creation

  1. #1
    Join Date
    Sep 2003
    Posts
    19

    Function Based Index Creation

    Hi,
    I am using following syntax to create a function based index on a column which is of type varchar2

    create index tcs_mis_manpower_hist_idx6 on tcs_mis_manpower_dist_hist_t to_date(period,'MON-YY');

    After this I am using dbms_stats.gather_table_stats to gather statistics on the table with indexes.

    Is that the correct way?
    Preeti

  2. #2
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    yeah

    u also need to have

    QUERY_REWRITE_INTEGRITY = TRUSTED

    QUERY_REWRITE_ENABLED = TRUE

    give a sql stmt and check the explain plan to see if it uses the index or not.


    Srini

  3. #3
    Join Date
    Sep 2003
    Posts
    19
    The index is not getting used at all. The query does FTS to select 292 rows from 7 lakh records.
    My query_rewrite_integrity is 'enforced' though
    Preeti

  4. #4
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    u need to have it to TRUSTED

    give this

    Alter Session SET QUERY_REWRITE_INTEGRITY = TRUSTED

    and see,

    there r restrictions, if u have complex joins it may not use index scan.

    Srini

  5. #5
    Join Date
    Sep 2003
    Posts
    19
    No the problem lies else where.
    The varchar2 column is of type 'mon-yy' and function based index ca not be created on format 'MON-YY' with to_date.
    It throws an error as below

    create index tcs_mis_manpower_hist_idx6 on tcs_mis_manpower_dist_hist_t(TO_DATE(period,'MON-YY')) TABLESPACE TS_INDX1

    ORA-01743: only pure functions can be indexed

    It can be created successfully with 'MON-YYYY'.But the query cannot have TO_DATE(period,'MON-YY') since period ( a varchar2 column) itself is of type 'MON-YY')
    Preeti

  6. #6
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    how come u said initially its not getting used.

    i thought u had created the index and after that u were testing it.

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    use TO_DATE(period,'MON-YYYY') and it'll work.

    How come you want to do this?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    ... or
    Code:
    TO_DATE(substr(period,1,4)||'20'||substr(period,5,2),'MON-YYYY'))
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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