Home » RDBMS Server » Performance Tuning » For second Name oracle is not using function Index
For second Name oracle is not using function Index [message #65685] Fri, 03 December 2004 22:15 Go to next message
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 Go to previous message
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
Previous Topic: DB Optimization
Next Topic: Execution Plan of Query For Second Name Oracle is not using Index
Goto Forum:
  


Current Time: Thu Mar 28 03:56:54 CDT 2024