| Function based index not used [message #372393] |
Thu, 08 February 2001 11:32  |
Tittom
Messages: 15
Registered: November 2000
|
Junior Member |
|
|
Hi all,
I have a table like this one :
create table t1 (col1 varchar2(50));
and this index :
create index ind_t1_col1 on t1 (lower(col1));
when I execute this sql statement :
select * from t1 where lower(col1) = 'value'
the index is not used (explain plan shows up with a full access)
I use Oracle 8.1.5
Anyone had the same problem or has the solution ?
Thanks
Tittom
|
|
|
|
| Re: Function based index not used [message #372394 is a reply to message #372393] |
Thu, 08 February 2001 12:07  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
maybe try re-analyzing the table. Before you do though, see what the current stats on the table and index are:
SELECT table_name, num_rows FROM user_tables WHERE table_name = 'T1';
SELECT table_name, index_name, last_analyzed, distinct_keys, num_rows, status,funcidx_status
FROM user_indexes WHERE index_name = 'IND_T1_COL1';
If num_rows is null, you have no stats (then RULE based optimizer should kick in. but will it know about the function based index?? not sure).
If num_rows is 0 or small, the analyze was done when there was no or little data. (COST BASED OPTIMIZER wont use the index - too few rows)
If num_rows is correct and high enough to warrant the use of the index it should use it Providing the selectivity of the index is good enough.
There are 2 analyze commands to consider - I don't really know the difference.
analyze table t1 estimate statistics;
analyze table t1 estimate statistics for all indexes;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
