June 1, 2011 at 12:43 am
Hi i want to compare two varchar column strings and my table consists of Billions of records so i cannot go with matching of data using equality.so can anyone suggest what is the best way to compare two stings with this huge data set.Moreover i can not run Fuzzy Logic also.
Thanks
Vineet Bhargava
Thanks And Regards
Vineet Bhargava
vineetbhargav@gmail.com
June 1, 2011 at 4:03 am
With a table that large, do you have any partitioning on it?
June 1, 2011 at 9:31 am
I'm not sure I understand the question completely. Could you please give us a (small) example of the data, and a sample query?
If you are saying you want to search using a LIKE '%STRING%' comparison, it is going to produce a table scan. Sorry.
If you are looking for certain keywords out of string of words, you might consider full text indexing.
Otherwise, you could write a query to manufacture another table that contained your primary tables's key and each of the possible character combinations and index that table. Something like this.
Source
---------------------------------------------
Paradimethylaminobenzaldehayde
New table
----------------------------------------------
Paradimethylaminobenzaldehayde
aradimethylaminobenzaldehayde
radimethylaminobenzaldehayde
adimethylaminobenzaldehayde
dimethylaminobenzaldehayde
imethylaminobenzaldehayde
methylaminobenzaldehayde
etc etc etc
This would allow you to index the built table and do LIKE 'STRING%' searches that would take advantage of the index (unlike '%STRING%'). Given the size of your table, creating and maintaining this table might prove impractical, but if your data is fairly static and you have the disk space available, it might be doable.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply