Re: Performance question - multiple Number or one String field ??

Hi Matt,

In this case, hashing is only useful to you if this codes are put in order, and, you only intend to consult then in that order. Because hashing will construct a key value, based on a hash function, that drive the queries. So, if the column store the codes 1, 2, 3, 4, 5, and so on, on positions 1o., 2o., 3o., 4o., 5o., etc., the content of this field can be "001002003004005". Considering that you convert the numbers to string adjusting at right.

The queries must observe this order, to hashing works properly. So...

Select <fields>
  from Table
  where key_column = '001002003004005'

will work.

But, if you need to get the rows where the 3o. code is equal to 3, or things like that, the implementation of separate columns is the best way to do. And you can also create separate indexes to each type of query (i.e. for c1, c2 and c5; or c5, c6 and c20, etc.) you want.

Other way of implement this, however, is the tree structure. If not all code fields are filled at each row inserted (i.e. c1=2342, c2=NULL, c3=48574, c4=NULL, and so on) this way is much efficient, because it will save disk space. In this case, there are one row for each code with the pointer to the parent row (if is not the first row) and the queries must be designed with de CONNECT BY option.

I hope this help, and sorry about my poor english.

Regis Vaz
regis.vaz_at_zaz.com.br

Matt Randle wrote:

> I am writing a system that needs to store potentialy millions of 'Codes'.
> Each code has 20 elements where each element is represented by a long
> integer. As I see it, the two ways of storing this are either,
>
> 1. 20 seperate number(5) columns.
> 2. One 100 byte string column.
>
> Given the massive size of the table which would be the quickest structure
> for lookups of single codes ??. My feeling is the string field would be
> quicker as it allows hashing ??
>
> Thanks in advance,
>
> Matt.
Received on Tue Apr 06 1999 - 19:35:29 CDT