vendredi 11 septembre 2015

Oracle numeric string column and indexing

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