| For second Name oracle is not using function Index [message #65685] |
Fri, 03 December 2004 22:15  |
Gurinder Mann
Messages: 7
Registered: October 2004
|
Junior Member |
|
|
Dear Sir/Madam
I have function index on a column called name when i write query
: where UPPER(NAME) LIKE 'AHE% HUS_%'
OR
: where UPPER(NAME) LIKE 'AHE%'
THEN IT IS USING INDEX ON THE COLUMN CALLED NAME
BUT WHEN I WRITE THE SAME QUERY
: where UPPER(NAME) LIKE 'AHE% HUS%'
THEN IT IS DOING FULL TABLE SCAN
WHAT PARAMETER IS REQUIRED TO CHANGE IN ORACLE DATABASE BECAUSE MY QUERY IS IN EXE SO I CAN NOT CHANGE THE QUERY SO PLEASE HELP BE TO COME OUT OF THIS PROBLEM.
THANX AND REGARDS
GURINDER SINGH MANN
|
|
|
|
| Re: For second Name oracle is not using function Index [message #65688 is a reply to message #65685] |
Sun, 05 December 2004 05:26  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
If you have an index on the column NAME, the optimizer will NOT use the index if you only have a where clause like
"where upper(name) like 'AHE%'"
Name is used in a function and therefor cannot be used in the index !
Please post the entire query and the plans, both for 'AHE%' as for 'AHE% HUS%'
hth
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
