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

On 19 feb, 12:53, "spacedog" <s1m0nc...@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.

Anyway - do whatever you want, but test it, measure it, and
look at it again. Only if you find proof, based on numbers,
implement your solution.

On Feb 19, 8:07 am, "Frank van Bortel" <frank.van.bor...@gmail.com>
wrote:
> On 19 feb, 12:53, "spacedog" <s1m0nc...@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.
>
> Anyway - do whatever you want, but test it, measure it, and
> look at it again. Only if you find proof, based on numbers,
> implement your solution.

The comparison operation is going to be a very small part of the
time. If is the search for the rows where a great deal more time will
be spent. Make sure the search conditions are properly indexed.

In general I have seen tests comparing numeric keys to varchar2 keys
and the performance advantage of the numeric key over the character
key is minute. It takes a fairly hefty amount of data to see the
benefit. In general it is not worth the conversion.

HTH -- Mark D Powell --

Frank van Bortel wrote:
> On 19 feb, 12:53, "spacedog" <s1m0nc...@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@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org