Re: Converting Strings to ASCII codes to increase search performance

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 21 Feb 2007 19:23:23 -0800
Message-ID: <1172114602.133926@bubbleator.drizzle.com>

Frank van Bortel wrote:

> On 19 feb, 12:53, "spacedog" <s1m0nc..._at_hotmail.com> wrote:

>> Hi,
>> We have a system that contains 50M customer records. We need to
>> repeatedly search this table to see if the new customer is actually a
>> new customer or has been a customer of ours in the past.
>> Some of the development guys have come up with the idea of converting
>> some of the fields to their ASCII code representation as they think
>> this will be quicker that doing straight string comparisons.
>> I have been trying to do some research around this and can't find
>> anything that supports this.
>> Has anyone come across this in the past or have any ideas.
>>
>> Thanks
>> Simon

> 
> Probably the myth, that searches on numeric fields would
> be faster than on character fields.

I should test this some day but I'd expect you are correct:

SQL> SELECT dump('Dan Morgan'), dump(0123456789) FROM dual;

DUMP('DANMORGAN')

---

DUMP(123456789)

---

Typ=96 Len=10: 68,97,110,32,77,111,114,103,97,110 Typ=2 Len=6: 197,2,24,46,68,90

It sure all looks like numbers to me.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Wed Feb 21 2007 - 21:23:23 CST