-
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
-
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
-
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
-
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
-
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
-
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?
-
... or
Code:
TO_DATE(substr(period,1,4)||'20'||substr(period,5,2),'MON-YYYY'))
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|