Function Based Index Creation

[preetikate]

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?

[Srinivas_Sharma]

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

[preetikate]

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

[Srinivas_Sharma]

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

[preetikate]

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')

[Srinivas_Sharma]

how come u said initially its not getting used.

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

[slimdave]

use TO_DATE(period,'MON-YYYY') and it'll work.

How come you want to do this?

[slimdave]

... or

TO_DATE(substr(period,1,4)||'20'||substr(period,5,2),'MON-YYYY'))