Re: No primary keys

Try creating 2 tables both with a single column in them of the same size. Make this the unique primary index on one table and leave it unindexed on the other then do a bcheck on each of the index files ......... strange how they both have the same number of indexes ...... oh but hang on you didn't create one of them ......... gosh ... must be some spirit or genie that did it. Spooky. Yours ---- Earle A Long (Senior Informix DBA) SINGLEPOINT UK LTD "Jonathan Leffler" <jleffler@earthlink.net> wrote in message news:3A5BFFD3.424C4CB@earthlink.net... > Earle A Long wrote: > > > No Jonathan I'm not thinking of rowid I'm thinking of an additional index. > > OK; then I respectfully suggest you are wrong. > > > > Like I said if you do not specify a primary index then informix will create > > one of type serial. This is true all standard engines I've worked on. It may > > or may not be true with Online. When I get chance I'll check it unless > > somebody knows for certain like maybe our illustrious lords and masters at > > Informix inc.. > > OK; it may be true of all the versions of Standard Engine that you've worked on, > but it has not (I regret to contradict you) been the case in any of the versions > I've worked on, which range from 1.10.00 through 2.10.03x (when it wasn't really > called Standard Engine) via 4.00, 4.1x, 5.0x, 5.1x, to 7.24. That leaves a few > versions (6.00, 7.0x, 7.1x, 7.2{0,1,2,3}) with wriggle room, but I would be > rather surprised if the feature was added in any of those versions only to be > dropped in the 7.24 version. The 7.25 version of SE, using 7.25 C-ISAM, is not > supposed to have any such feature in it, but I don't actually have it installed > on any of my machines so I could be wrong (but I'd have to enter a bug against > it if I were; it was not in the specification). Oh, and the pre-SQL Informix > 3.30 product didn't do it either. And no version of OnLine that I've worked > with (from Turbo 1.10.03x through OnLine 4.00, 4.1x, 5.0x, 5.10, 6.00, 7.1x, > 7.2x, 8.3x, 9.0x, 9.1x or 9.2x) does this either. > > I'm not sure whether Illustra ever did this -- or Ardent, RedBrick or > Cloudscape; I am not pontificating on those DBMS. Were any of those the > "illustrious lords and masters" to whom you were referring. I'm not sure who > else currently working at Informix you'd be thinking of. > > As someone else pointed out, the C-ISAM files used with SE have a peculiar index > 0 which provides ROWID access -- and which is why the average C-ISAM file > created by a generic C-ISAM program is typically not suited for use with SE. > This would show up as an index in bcheck or secheck. I am still trying to be > charitable and think this is misleading you, Earle. However, if you choose to > think otherwise (either that I'm being uncharitable or that this is not what you > are thinking of), then I cannot stop you doing so and I won't argue the point > much further. I've made my views known. > > > > "Jonathan Leffler" <jleffler@informix.com> wrote: > > > Earle A Long wrote: > > > > My understanding is that if you do not define a unique primary key to a > > > > database table then informix will create one of type serial which does not > > > > > > show in the tables list of columns. > > > > > > No, this is not what happens, but there is just enough semblance of > > > truth in the comment that some explanation is in order rather than a > > > simple denial. > > > > > > I think Earle is thinking of the ROWID which, in SE, is similar to > > > SERIAL in some respects. However, the ROWID is there in non-fragmented > > > tables regardless of whether there is a primary key specified. The > > > ROWID is a pseudo-column which is not actually stored in the data; it is > > > a record number in SE and a slot address in OnLine. It can be used to > > > find a unique record, regardless of duplicates or not. However, you are > > > strongly counselled not to use ROWID, and it is critical that you never, > > > ever store a ROWID in a permanent table in the database. > > > > > > If there is no unique constraint specified on a table, Informix allows > > > duplicate rows to be inserted into that table. It is difficult to do > > > anything with just one of the duplicate rows using pure SQL (SQL that > > > does not reference ROWID values). In my view, every table, regardless > > > of size, should have a primary key constraint. > > > > > > > "Wayne Hansford" <hansford@salemleasing.com> wrote: > > > > > What does Informix do if there are no primary key or foriegn key > > > > > attributes defined in the database schema ? Will Informix try to build > > a > > > > > primary key Index based on as many fields as it thinks it needs for > > record > > > > > uniqueness ? Is this process expensive in terms of compute time ? > > -- > Jonathan Leffler (jleffler@earthlink.net, jleffler@informix.com) > Guardian of DBD::Informix 1.00.PC1 -- see http://www.cpan.org/ > #include <disclaimer.h> > > >

Earle A Long wrote: > Try creating 2 tables both with a single column in them of the same size. > Make this the unique primary index on one table and leave it unindexed on > the other then do a bcheck on each of the index files ... Like this? Anubis JL: dbaccess - - <<! create database pkindexes; create table table1 (pk integer not null primary key); create table table2 (pk integer not null);! Database created. Table created. Table created. Anubis JL: ls pkindexes.dbs/tab* pkindexes.dbs/table00100.dat pkindexes.dbs/table00101.dat pkindexes.dbs/table00100.idx pkindexes.dbs/table00101.idx Anubis JL: bcheck pkindexes.dbs/table*.idx BCHECK C-ISAM B-tree Checker version 5.08.UD1 Copyright (C) 1981-1996 Informix Software, Inc. Software Serial Number JLS#R970530 C-ISAM File: pkindexes.dbs/table00100.idx Checking dictionary and file sizes. Index file node size = 1024 Current C-ISAM index file node size = 1024 Checking data file records. Checking indexes and key descriptions. Index 1 = unique key 0 index node(s) used -- 1 index b-tree level(s) used Index 2 = unique key (0,4,2) 1 index node(s) used -- 1 index b-tree level(s) used Checking data record and index node free lists. 3 index node(s) used, 0 free -- 0 data record(s) used, 0 free C-ISAM File: pkindexes.dbs/table00101.idx Checking dictionary and file sizes. Index file node size = 1024 Current C-ISAM index file node size = 1024 Checking data file records. Checking indexes and key descriptions. Index 1 = unique key 0 index node(s) used -- 1 index b-tree level(s) used Checking data record and index node free lists. 2 index node(s) used, 0 free -- 0 data record(s) used, 0 free Anubis JL: As you can see, one table has one index, the unique key index, and the other has two indexes, the unique key index and an index actually on the data in the table. Index 1 is the special index which has been alluded to several times in this discussion; it enables the rowid access. Further, I can insert 201 rows into each table using: Anubis JL: for i in $(range 1900 2100) > do > echo "insert into table1 values($i);" > echo "insert into table2 values($i);" > done | dbaccess pkindexes - >/dev/null 2>&1 Anubis JL: ls -l pkindexes.dbs/tabl* -rw-rw---- 1 jleffler informix 1024 Jan 10 11:47 pkindexes.dbs/table00100.dat -rw-rw---- 1 jleffler informix 5120 Jan 10 11:47 pkindexes.dbs/table00100.idx -rw-rw---- 1 jleffler informix 1024 Jan 10 11:47 pkindexes.dbs/table00101.dat -rw-rw---- 1 jleffler informix 2048 Jan 10 11:47 pkindexes.dbs/table00101.idx Anubis JL: bcheck pkindexes.dbs/tabl*.idx BCHECK C-ISAM B-tree Checker version 5.08.UD1 Copyright (C) 1981-1996 Informix Software, Inc. Software Serial Number JLS#R970530 C-ISAM File: pkindexes.dbs/table00100.idx Checking dictionary and file sizes. Index file node size = 1024 Current C-ISAM index file node size = 1024 Checking data file records. Checking indexes and key descriptions. Index 1 = unique key 0 index node(s) used -- 1 index b-tree level(s) used Index 2 = unique key (0,4,2) 3 index node(s) used -- 2 index b-tree level(s) used Checking data record and index node free lists. 5 index node(s) used, 0 free -- 201 data record(s) used, 3 free C-ISAM File: pkindexes.dbs/table00101.idx Checking dictionary and file sizes. Index file node size = 1024 Current C-ISAM index file node size = 1024 Checking data file records. Checking indexes and key descriptions. Index 1 = unique key 0 index node(s) used -- 1 index b-tree level(s) used Checking data record and index node free lists. 2 index node(s) used, 0 free -- 201 data record(s) used, 3 free Anubis JL: Note that the index file for table2 (table00101.idx) has not grown at all, whereas the index file for table1 (table00100.idx) has grown and is using more disk space. I added 4000 more rows of data, and the index file for table2 is still 2048 bytes, but the index file for table1 is 37888 bytes (bigger than the data file at 21504 bytes, which isn't unexpected given that the only data in the data file is the number which is being indexed). The data files are the same size - in fact, byte for byte identical. > ... strange how > they both have the same number of indexes ...... oh but hang on you didn't > create one of them ......... gosh ... must be some spirit or genie that did > it. Spooky. They don't have the same number of indexes. Can you show the equivalent results for your system? Note that this is using SE 5.08.UD1 on Solaris 7. I would expect the same results with any other version of SE you care to lay your hands on. Absent proof to the contrary, I regard this as QED - quod erat demonstrandum. That which was to be demonstrated has been demonstrated. In SE, when you create a table with no explicit index, no index is created. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"

I not sure I followed your statement, would you mind verifying I understood you correctectly? Are you saying you did the following create table abc{ abc_no serial not null, primary key(abc) ); create table abc2{ abc2_no serial not null ); and there were indexes on both tables? Will In article <PV076.474$IX3.15327@NewsReader>, "Earle A Long" <earle@btclick.com> wrote: > Try creating 2 tables both with a single column in them of the same size. > Make this the unique primary index on one table and leave it unindexed on > the other then do a bcheck on each of the index files ......... strange how > they both have the same number of indexes ...... oh but hang on you didn't > create one of them ......... gosh ... must be some spirit or genie that did > it. Spooky. > > Yours > ---- > Earle A Long (Senior Informix DBA) > SINGLEPOINT UK LTD > > "Jonathan Leffler" <jleffler@earthlink.net> wrote in message > news:3A5BFFD3.424C4CB@earthlink.net... > > Earle A Long wrote: > > > > > No Jonathan I'm not thinking of rowid I'm thinking of an additional > index. > > > > OK; then I respectfully suggest you are wrong. > > > > > > > Like I said if you do not specify a primary index then informix will > create > > > one of type serial. This is true all standard engines I've worked on. It > may > > > or may not be true with Online. When I get chance I'll check it unless > > > somebody knows for certain like maybe our illustrious lords and masters > at > > > Informix inc.. > > > > OK; it may be true of all the versions of Standard Engine that you've > worked on, > > but it has not (I regret to contradict you) been the case in any of the > versions > > I've worked on, which range from 1.10.00 through 2.10.03x (when it wasn't > really > > called Standard Engine) via 4.00, 4.1x, 5.0x, 5.1x, to 7.24. That leaves > a few > > versions (6.00, 7.0x, 7.1x, 7.2{0,1,2,3}) with wriggle room, but I would > be > > rather surprised if the feature was added in any of those versions only to > be > > dropped in the 7.24 version. The 7.25 version of SE, using 7.25 C- ISAM, > is not > > supposed to have any such feature in it, but I don't actually have it > installed > > on any of my machines so I could be wrong (but I'd have to enter a bug > against > > it if I were; it was not in the specification). Oh, and the pre-SQL > Informix > > 3.30 product didn't do it either. And no version of OnLine that I've > worked > > with (from Turbo 1.10.03x through OnLine 4.00, 4.1x, 5.0x, 5.10, 6.00, > 7.1x, > > 7.2x, 8.3x, 9.0x, 9.1x or 9.2x) does this either. > > > > I'm not sure whether Illustra ever did this -- or Ardent, RedBrick or > > Cloudscape; I am not pontificating on those DBMS. Were any of those the > > "illustrious lords and masters" to whom you were referring. I'm not sure > who > > else currently working at Informix you'd be thinking of. > > > > As someone else pointed out, the C-ISAM files used with SE have a peculiar > index > > 0 which provides ROWID access -- and which is why the average C- ISAM file > > created by a generic C-ISAM program is typically not suited for use with > SE. > > This would show up as an index in bcheck or secheck. I am still trying to > be > > charitable and think this is misleading you, Earle. However, if you > choose to > > think otherwise (either that I'm being uncharitable or that this is not > what you > > are thinking of), then I cannot stop you doing so and I won't argue the > point > > much further. I've made my views known. > > > > > > > "Jonathan Leffler" <jleffler@informix.com> wrote: > > > > Earle A Long wrote: > > > > > My understanding is that if you do not define a unique primary key > to a > > > > > database table then informix will create one of type serial which > does not > > > > > > > > show in the tables list of columns. > > > > > > > > No, this is not what happens, but there is just enough semblance of > > > > truth in the comment that some explanation is in order rather than a > > > > simple denial. > > > > > > > > I think Earle is thinking of the ROWID which, in SE, is similar to > > > > SERIAL in some respects. However, the ROWID is there in > non-fragmented > > > > tables regardless of whether there is a primary key specified. The > > > > ROWID is a pseudo-column which is not actually stored in the data; it > is > > > > a record number in SE and a slot address in OnLine. It can be used to > > > > find a unique record, regardless of duplicates or not. However, you > are > > > > strongly counselled not to use ROWID, and it is critical that you > never, > > > > ever store a ROWID in a permanent table in the database. > > > > > > > > If there is no unique constraint specified on a table, Informix allows > > > > duplicate rows to be inserted into that table. It is difficult to do > > > > anything with just one of the duplicate rows using pure SQL (SQL that > > > > does not reference ROWID values). In my view, every table, regardless > > > > of size, should have a primary key constraint. > > > > > > > > > "Wayne Hansford" <hansford@salemleasing.com> wrote: > > > > > > What does Informix do if there are no primary key or foriegn key > > > > > > attributes defined in the database schema ? Will Informix try to > build > > > a > > > > > > primary key Index based on as many fields as it thinks it needs > for > > > record > > > > > > uniqueness ? Is this process expensive in terms of compute time ? > > > > -- > > Jonathan Leffler (jleffler@earthlink.net, jleffler@informix.com) > > Guardian of DBD::Informix 1.00.PC1 -- see http://www.cpan.org/ > > #include <disclaimer.h> > > > > > > > > Sent via Deja.com http://www.deja.com/

Jonathan Leffler wrote in message <3A5CBF8D.56053DFE@informix.com>... > >Like this? <SNIP> >Index 1 is the special index which has been alluded to several times in >this discussion; it enables the rowid access. Further, I can insert 201 >rows into each table using: > >Note that the index file for table2 (table00101.idx) has not grown at >all, whereas the index file for table1 (table00100.idx) has grown and is >using more disk space. I added 4000 more rows of data, and the index >file for table2 is still 2048 bytes, >but the index file for table1 is 37888 bytes (bigger than the data file >at 21504 bytes, which isn't unexpected given that the only data in the >data file is the number which is being indexed). The data files are the >same size - in fact, byte for byte identical. > >Absent proof to the contrary, I regard this as QED - quod erat >demonstrandum. That which was to be demonstrated has been >demonstrated. In SE, when you create a table with no explicit index, no >index is created. > So, to totally clarify for the dummies and the extremely rusty ex-SE users, the first index mentioned is virtual, and doesn't really consume any space, and really, no index style activity is carried out when fetching via rowid? Because I always thought that an SE rowid was the ACTUAL row number of the row - hence the fact that they start out strictly sequentially from 1, with holes only appearing in the sequence due to deleted records. It would appear that the so called index 1 is really only some sort of report saying how many true rows they are, and perhaps reporting on the health of the trivial yet different internal data structures that are related to plain old record access. Might I at this point also provoke another religious war by asking "why the hell would anyone use SE anyway?" -- "Just think, next time I shoot someone, I could be arrested" - Frank Drebin, Naked Gun.

No and Yes Jonathan (Lord and Master), Your bcheck shows that Informix has created an index of it's own as I believe I said in my first response. Although it would appear that you cannot stop it creating that index by specifying your own unique primary index key any longer. Now this may well be what is now referred to as ROWID but in earlier versions I seem to remember that if you created a table without a primary key you got exactly what you demonstrated ie. bcheck showing 1 index but if you then specified a single field of type serial indexed as primary then bcheck again showed only one index. Although I will hold my hands up and say that it is a long long time ago when I last came across this and have since made sure that all my tables have a primary key defined in the schema (forgive my use of an antiquated phrase but it fits). On a side line Jonathan if you have been at Informix that long that you have worked on the original Informix Versions dbschema, dbstatus, dbbuild etc etc then you may remember that I worked at the DHSS on secondment for some time writing and helping them write their Personell System in 3.3, then converting it to SQL and getting Informix to get Informix Turbo working properly. Ahhh the good old days of MPM II, Concurrent CPM, Unix Version 1. When mutiuser meant > 1 user. Yours -- Earle A Long (Senior Informix DBA) SINGLEPOINT UK LTD "Jonathan Leffler" <jleffler@informix.com> wrote in message news:3A5CBF8D.56053DFE@informix.com... > Earle A Long wrote: > > Try creating 2 tables both with a single column in them of the same size. > > Make this the unique primary index on one table and leave it unindexed on > > the other then do a bcheck on each of the index files ... > > Like this? > > Anubis JL: dbaccess - - <<! > create database pkindexes; > create table table1 (pk integer not null primary key); > create table table2 (pk integer not null);> ! > > Database created. > > > Table created. > > > Table created. > > > Anubis JL: ls pkindexes.dbs/tab* > pkindexes.dbs/table00100.dat pkindexes.dbs/table00101.dat > pkindexes.dbs/table00100.idx pkindexes.dbs/table00101.idx > Anubis JL: bcheck pkindexes.dbs/table*.idx > > BCHECK C-ISAM B-tree Checker version 5.08.UD1 > Copyright (C) 1981-1996 Informix Software, Inc. > Software Serial Number JLS#R970530 > > > C-ISAM File: pkindexes.dbs/table00100.idx > > Checking dictionary and file sizes. > Index file node size = 1024 > Current C-ISAM index file node size = 1024 > Checking data file records. > Checking indexes and key descriptions. > Index 1 = unique key > 0 index node(s) used -- 1 index b-tree level(s) used > Index 2 = unique key (0,4,2) > 1 index node(s) used -- 1 index b-tree level(s) used > Checking data record and index node free lists. > 3 index node(s) used, 0 free -- 0 data record(s) used, 0 free > > > C-ISAM File: pkindexes.dbs/table00101.idx > > Checking dictionary and file sizes. > Index file node size = 1024 > Current C-ISAM index file node size = 1024 > Checking data file records. > Checking indexes and key descriptions. > Index 1 = unique key > 0 index node(s) used -- 1 index b-tree level(s) used > Checking data record and index node free lists. > 2 index node(s) used, 0 free -- 0 data record(s) used, 0 free > > Anubis JL: > > As you can see, one table has one index, the unique key index, and the > other has two indexes, the unique key index and an index actually on the > data in the table. > > Index 1 is the special index which has been alluded to several times in > this discussion; it enables the rowid access. Further, I can insert 201 > rows into > each table using: > > Anubis JL: for i in $(range 1900 2100) > > do > > echo "insert into table1 values($i);" > > echo "insert into table2 values($i);" > > done | dbaccess pkindexes - >/dev/null 2>&1 > Anubis JL: ls -l pkindexes.dbs/tabl* > -rw-rw---- 1 jleffler informix 1024 Jan 10 11:47 > pkindexes.dbs/table00100.dat > -rw-rw---- 1 jleffler informix 5120 Jan 10 11:47 > pkindexes.dbs/table00100.idx > -rw-rw---- 1 jleffler informix 1024 Jan 10 11:47 > pkindexes.dbs/table00101.dat > -rw-rw---- 1 jleffler informix 2048 Jan 10 11:47 > pkindexes.dbs/table00101.idx > Anubis JL: bcheck pkindexes.dbs/tabl*.idx > > BCHECK C-ISAM B-tree Checker version 5.08.UD1 > Copyright (C) 1981-1996 Informix Software, Inc. > Software Serial Number JLS#R970530 > > > C-ISAM File: pkindexes.dbs/table00100.idx > > Checking dictionary and file sizes. > Index file node size = 1024 > Current C-ISAM index file node size = 1024 > Checking data file records. > Checking indexes and key descriptions. > Index 1 = unique key > 0 index node(s) used -- 1 index b-tree level(s) used > Index 2 = unique key (0,4,2) > 3 index node(s) used -- 2 index b-tree level(s) used > Checking data record and index node free lists. > 5 index node(s) used, 0 free -- 201 data record(s) used, 3 free > > > C-ISAM File: pkindexes.dbs/table00101.idx > > Checking dictionary and file sizes. > Index file node size = 1024 > Current C-ISAM index file node size = 1024 > Checking data file records. > Checking indexes and key descriptions. > Index 1 = unique key > 0 index node(s) used -- 1 index b-tree level(s) used > Checking data record and index node free lists. > 2 index node(s) used, 0 free -- 201 data record(s) used, 3 free > > > Anubis JL: > > Note that the index file for table2 (table00101.idx) has not grown at > all, whereas the index file for table1 (table00100.idx) has grown and is > using more disk space. I added 4000 more rows of data, and the index > file for table2 is still 2048 bytes, > but the index file for table1 is 37888 bytes (bigger than the data file > at 21504 bytes, which isn't unexpected given that the only data in the > data file is the number which is being indexed). The data files are the > same size - in fact, byte for byte identical. > > > ... strange how > > they both have the same number of indexes ...... oh but hang on you didn't > > create one of them ......... gosh ... must be some spirit or genie that did > > it. Spooky. > > They don't have the same number of indexes. Can you show the equivalent > results for your system? > > Note that this is using SE 5.08.UD1 on Solaris 7. I would expect the > same results with any other version of SE you care to lay your hands on. > > Absent proof to the contrary, I regard this as QED - quod erat > demonstrandum. That which was to be demonstrated has been > demonstrated. In SE, when you create a table with no explicit index, no > index is created. > > -- > Yours, > Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> > Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN > "I don't suffer from insanity; I enjoy every minute of it!" >