Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Converting Strings to ASCII codes to increase search performance
On Feb 19, 8:07 am, "Frank van Bortel" <frank.van.bor..._at_gmail.com>
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.
>
> 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 -- Received on Mon Feb 19 2007 - 09:20:16 CST