Function Based Index Creation
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?
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.
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
u need to have it to TRUSTED
Alter Session SET QUERY_REWRITE_INTEGRITY = TRUSTED
there r restrictions, if u have complex joins it may not use index scan.
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')
how come u said initially its not getting used.
i thought u had created the index and after that u were testing it.
use TO_DATE(period,'MON-YYYY') and it'll work.
How come you want to do this?
Click Here to Expand Forum to Full Width