On Sat, 12 Jan 2008, Francesco Saverio Giudice wrote:
> Hello Friends,
> I have a database that has 3 numeric fields likes:
> N1 N 16 0
> N2 N 16 0
> N3 N 16 0
> and I need to make a lot of indexes like
> INDEX ON Str(n1)+Str(n2)+Str(n3) TAG "n1n2n3" TO myindex
> INDEX ON Str(n2)+Str(n1)+Str(n3) TAG "n2n1n3" TO myindex ADDITIVE
> INDEX ON Str(n3)+Str(n2)+Str(n1) TAG "n3n2n1" TO myindex ADDITIVE
> INDEX ON Str(n1)+Str(n3)+Str(n2) TAG "n1n3n2" TO myindex ADDITIVE
> INDEX ON Str(n2)+Str(n3)+Str(n1) TAG "n2n3n1" TO myindex ADDITIVE
> INDEX ON Str(n3)+Str(n1)+Str(n2) TAG "n3n1n2" TO myindex ADDITIVE
> INDEX ON Str(n1)+Str(n2) TAG "n1n2" TO myindex UNIQUE
> ADDITIVE
> INDEX ON Str(n2)+Str(n1) TAG "n2n1" TO myindex UNIQUE
> ADDITIVE
> INDEX ON Str(n1)+Str(n3) TAG "n1n3" TO myindex UNIQUE
> ADDITIVE
> etc.
> but I would like to reduce index keys dimension. Is there an algorithm that
> give me compact indexes ?
CDX will compress such indexes by default but if you want to compress
also single key then you will have to convert numeric value to
binary form.
> I have thought to convert numeric decimal values in hex values, but I have
> to store same 16 chars for every fields. Or am I in wrong ?
Nearly 16. Let's calculate it more precisely:
? log(10^16)/log(2) => 53.15
So you need at least 54 bits to hold such number. In hex encoding
each number holds information about 4 bits it means that you need
at least INT((54+3)/4) => 14 hex digits (it can be also calculated
as log(10^16)/log(16) => 13.29) As you can see you will not save
too much using hex conversion.
If you do not use national collation then you can use binary big
endian form and in such case for each number you will need 7 bytes
(log(10^16)/log(256) => 6.64). You can write your own function
for such conversion:
HB_FUNC( HASHDEC16 )
{
LONGLONG llValue = hb_parnll( 1 );
char * buffer[ 8 ];
/* if you are using possitive only number */
HB_PUT_BE_UINT64( buffer, llValue );
hb_retclen( buffer, 7 );
}
and then create index on HASHDEC16(n1)+HASHDEC16(n2)+HASHDEC16(n3)
You will reduce the key size from 48 to 21.
But if you are using national collation then you should also
convert 'buffer' above to respect modified byte order. You can
create static byte table for such conversion to not reduce speed.
Unfortunately now we do not have an option to mark index as binary.
I'll add such functionality in the future.
best regards,
Przemek
_______________________________________________
Harbour mailing list
Harbour@harbour-project.org
http://lists.harbour-project.org/mailman/listinfo/harbour
