Re: Q: NUMBER OF DUPLICATE IN
Jonathan Leffler (johnl@informix.com) wrote:
: >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?
+----------------------------------------------------------+
Hy !
Tables with a lot of duplicates and indexed should have
been unique. You have create an index with the fields
create index new on table ( duplicate field, unique field) ;This is much faster then an big duplicate Index. The best
unique fied is a serial field.
--
Bye
+----------------------------------------------------------+
| Email : cze@sup.css.de Christoph Zehentmaier |
| |
| Voice : +049 661 939226 Computer Software Studio GmbH |
| Fax : +049 661 939252 Justus-Liebig-Str. 2 |
| Data : +049 661 939253 36093 Kuenzell / Germany |
+----------------------------------------------------------+