Re: Q: NUMBER OF DUPLICATE IN
>From: timothy.hood@sltrib.com (Timothy Hood)
>Date: Fri, 21 Jul 1995 17:14:00 GMT
>X-Informix-List-Id: <list.6999>
>
>S> From: swahl.str@sni.de (Sabina Wahl)
> > Subject: Q: Number of duplicate indices with same contents?
>
>S> is it true, that max number of duplicate indices with same content is 32000?
> > I have a large database (Informix-Online 6.0), and some indices have not
> > always a content (its blank).
>
>65536 (or maybe 65535). However, keep in mind that indices with highly
>repeated values tend to become poor performers.
I am delighted to be be able to tell you that empirical evidence shows
that 6.00.UE1 SE does not have a limit at 64k on the number of rows in
a duplicate index. I demonstrated this by using the scripts below, which
are hardly rocket science SQL.
-- k1.sql --
CREATE TABLE t1 (j INTEGER NOT NULL);
CREATE INDEX p1 ON t1(j);
CREATE TABLE t2 (j INTEGER NOT NULL);
CREATE INDEX p2 ON t2(j);
INSERT INTO t1 VALUES(1);
-- k2.sql --
INSERT INTO t2 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t2;
SELECT COUNT(*) FROM t1;
-- k3.sql --
input k2.sql; -- 2
input k2.sql; -- 4
input k2.sql; -- 8
input k2.sql; -- 16
input k2.sql; -- 32
input k2.sql; -- 64
input k2.sql; -- 128
input k2.sql; -- 256
input k2.sql; -- 512
input k2.sql; -- 1024
input k2.sql; -- 2048
input k2.sql; -- 4096
input k2.sql; -- 8192
input k2.sql; -- 16384
input k2.sql; -- 32768
input k2.sql; -- 65536
input k2.sql; -- 131072
When I ran it, of course, I got a quite different sequence of numbers
because the code did not delete the prior contents of t2. I interrupted
the script after it had produced the output:
2
5
13
34
89
233
610
1597
4181
10946
28657
75025
196418
These are even terms of a Fibonacci Sequence (counting 0 as the zeroth
term): 0 1 2 3 5 8 13 21 34 ...
I performed the same test with 4.12.UC1 SE and stopped it after the 75025
output. I believe the 64k limit was removed with 4.00; it may have been as
late as 4.10 -- but it has gone. I have also performed the same test with
OnLine 6.00.UE1 and again encountered no problem. It was also quite a bit
faster, I might add.
Having said that, there was at one time such a limitation, but that was
removed quite a while ago.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
PS: It only took about 15 minutes (including a coffee stop) and about
3 MB of disk space to demonstrate this -- couldn't you have done the
same?