I have a numeric string column in oracle with or without leading zeros samples:
00000000056
5755
0123938784579343
00000000333
984454
The issue is that partial Search operations using like are very slow
select account_number from accounts where account_number like %57%
one solution is to restrict the search to exact match only and add an additional integer column that will represent the numeric value for exact matches.
At this point I am not sure we can add an additional column,
Do you guys have any other ideas?
Is it possible to tell Oracle to index the numeric string column as an integer value so we can do exact numeric match on it?
for example query on value :00000000333
will be:
select account_number from accounts where account_number = '333'
While ignoring the leading zeros.
I can use regex_like and ignore them, but i am afraid its going to be slow.
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire