| query not usinf index on concatenated fields [message #10933] |
Tue, 24 February 2004 23:39  |
shad
Messages: 50
Registered: October 2000
|
Member |
|
|
Hi there
ŕ I have an index created on the concat(REF_NO,REF_NO_CHECK).
I run the following query which for some reason does not use the existing index. The query does a full tables scan on the employers table.
SELECT EMPLOYERS.TRADE_NAME
FROM EMPLOYERS
WHERE EMPLOYERS.REF_NO || EMPLOYERS.REF_NO_CHECK = :b1
Is there away for me try and force this query to use the index. By the way I have also already tried to force it using a hint but that did not work.
Regards
Shad
|
|
|
|
|
|
| Re: query not usinf index on concatenated fields [message #10957 is a reply to message #10933] |
Wed, 25 February 2004 09:29  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
The index will only be used if your search expression matches the index. You have a composite index and a search expression that concatenates the two columns - very different.
If you need to use an index, you will need to build a function-based index on that expression:
create index employers_ndx on employers (ref_no || ref_no_check);
Please read the documentation for other settings required for function-based indexes:
FBI requirements
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
