| function based index? [message #19030] |
Tue, 26 February 2002 11:52  |
novice
Messages: 5
Registered: February 2001
|
Junior Member |
|
|
I used the following command to create a table and index.
create table testtable (name varchar2(10),
age number 3);
create index testindex on testtable (upper(name));
When I tried to use the below sql command to select records, I can only get "TOM", but could not get "Tom"
select /*+index(testindex)*/ name, age from testtable where name='TOM';
If I also want to get "Tom", what should I do?
Another question is, how can I enforce oracle to use the index I specify? As I understanding, Oracle may ignore the index hints.
Thanks very much!
|
|
|
|
| Re: function based index? [message #19031 is a reply to message #19030] |
Tue, 26 February 2002 13:23  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Since you created your function-based index on upper(name), you need to query on that as well:
where upper(name) = 'TOM';
This query will return both 'TOM' and 'Tom'.
Oracle will use your hint if it is applicable - in your case, it did not use your hint because your where clause did not even match the index expression. In general though, analyze your tables and don't rely on hinting.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
