RE: Correct use of an automatically created index on a primary ke y

Does any one know how to prevent implicit creation of an index when setting the primary key for a table. Andrew H. ----- Forwarded by Andrew Hardy/MAIN/MC1 on 14/10/2003 12:55 ----- |---------+----------------------------> | | Andrew Hardy | | | | | | 14/10/2003 12:18 | | | | |---------+----------------------------> >--------------------------------------------------------------------------------------------------------------------------------------------------| | | | To: informix-list@iiug.org | | cc: | | Subject: RE: Correct use of an automatically created index on a primary ke y | >--------------------------------------------------------------------------------------------------------------------------------------------------| Sorry to be a pain, I'm a real newbie. IRO the suggestion below, is this the kind of thing ? CREATE TABLE user_file_read( oid AsnObjectId, userId integer, reader VARCHAR(255), fileName VARCHAR(255), readStatus integer) LOCK MODE ROW; CREATE INDEX oid_index ON user_file_read (oid ASC) FRAGMENT BY EXPRESSION ___ IN ___, ___ IN ___; ALTER TABLE user_file_read ADD CONSTRAINT PRIMARY KEY (oid); UPDATE STATISTICS; Then I understand now, that it should use the index automatically for an order by, so INSERT {+INDEX(oid_index)} INTO user_file_read_table ( oid, userId, reader, fileName, readStatus) VALUES ( '99.99.99.99.99.99..99.99', 13, 'hardya', 'testDir1/testDir2/testFile', 1) SELECT * FROM user_file_read_table order by oid Many thanks, ----- Forwarded by Andrew Hardy/MAIN/MC1 on 14/10/2003 11:59 ----- |---------+--------------------------------> | | "Marley, Peter" | | | <peter.marley@acco-eu| | | rope.co.uk> | | | | | | 14/10/2003 11:29 | | | | |---------+--------------------------------> >--------------------------------------------------------------------------------------------------------------------------------------------------| | | | To: "'Andrew Hardy'" <Andrew.Hardy@marconi.com> | | cc: | | Subject: RE: Correct use of an automatically created index on a primary ke y | >--------------------------------------------------------------------------------------------------------------------------------------------------| Andrew When creating indexes it is best to first create the index using whatever fragmentation you want. Then add the primary key. If an already index exists on the primary key column it will use that, instead of creating another index with a system defined name. So you end up with 1) an index with your naming convention and 2) the correct fragmentation strategy for the index. Then apply an update statistics to the table to let the optimiser know where the data is and size of table and distribution of data etc. When querying the data you do not normally need to specify index names. The WHERE clause or ORDER BY clause will hint the optimzer to use the correct index. Also. Depending on the size of the table (hence the update statistics) the optimiser may decide not to use the index as it has decided that a sequential scan is quicker All the best Peter __________________________________ Peter Marley DBA, Acco UK Tel: +44 (0)1296732228 Fax: +44 (0)1296732203 email: peter.marley@acco-europe.co.uk -----Original Message----- From: Andrew Hardy [mailto:Andrew.Hardy@marconi.com] Sent: 14 October 2003 10:55 To: informix-list@iiug.org Subject: Correct use of an automatically created index on a primary key Please excuse me for anything stupid . I am very new to Informix and to SQL at this level. I need to know how to correctly use an automatically created index on a primary key, or if I need to remove it and replace it with one of my own, and how I may do that. Andrew H. Detail =========================================================== I have the following table, which potentially contains 10,000 records, and I think I need an index to do my inserts and my retrievals, because I need to quickly retrive the next row in order of the primary key field (oid). I create the table like this CREATE TABLE user_file_read( oid AsnObjectId, userId integer, reader VARCHAR(255), fileName VARCHAR(255), readStatus integer, PRIMARY KEY (oid) ) LOCK MODE ROW; AsnObjectId is a user defined type for which the server knows about functions for comparison etc and for which the client registers. This appears to work successfully and was written for us by Informix. Then I try to create the index like this CREATE INDEX oid_index ON user_file_read (oid); I get error -350 'Index already exists on column'. So I find out what the index is, it's ' 2828_63' (with a leading space) and appears not to change after table creation, then I do my inserts and selects like these examples. Clearly in the long term the index name ought not to be hard coded. SELECT {+INDEX(' 2828_63')} * FROM user_file_read_table order by oid And for the insert, this is an example INSERT {+INDEX(' 2828_63 )} INTO user_file_read_table ( oid, userId, reader, fileName, readStatus) VALUES ( '99.99.99.99.99.99..99.99', 13, 'hardya', 'testDir1/testDir2/testFile', 1) My inserts are successful. My select is successful and correct and I can do next on that to travers in oid order, but the execution of the select with 10,000 records is currently taking about 30 seconds. A straight forward unordered next is instant. I thought that using the index would imnprovce performance. I must be doing something worng, but I get no clues, because I get no errors. sending to informix-list ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ This e-mail and any attachments thereto may contain information which is confidential and/or protected by intellectual property rights and are intended for the sole use of the recipient(s) named above. Any use of the information contained herein (includi

This is a no-go. Primary constraint is implemented using that index. All you can do is create table without primary key, create index and later alter table to use that index in a primary key. Gorazd "Andrew Hardy" <Andrew.Hardy@marconi.com> wrote in message news:bmgp7h$59h$1@terabinaries.xmission.com... > > > Does any one know how to prevent implicit creation of an index when setting > the primary key for a table. > > Andrew H. > > > ----- Forwarded by Andrew Hardy/MAIN/MC1 on 14/10/2003 12:55 ----- > |---------+----------------------------> > | | Andrew Hardy | > | | | > | | 14/10/2003 12:18 | > | | | > |---------+----------------------------> > >--------------------------------------------------------------------------- -----------------------------------------------------------------------| > | | > | To: informix-list@iiug.org | > | cc: | > | Subject: RE: Correct use of an automatically created index on a primary ke y | > >--------------------------------------------------------------------------- -----------------------------------------------------------------------| > > > > Sorry to be a pain, I'm a real newbie. > > IRO the suggestion below, is this the kind of thing ? > > CREATE TABLE user_file_read( oid AsnObjectId, userId integer, reader > VARCHAR(255), fileName VARCHAR(255), readStatus integer) LOCK MODE > ROW;> > CREATE INDEX oid_index ON user_file_read (oid ASC) FRAGMENT BY > EXPRESSION ___ IN ___, ___ IN ___;> > ALTER TABLE user_file_read ADD CONSTRAINT PRIMARY KEY (oid);> > UPDATE STATISTICS;> > Then I understand now, that it should use the index automatically for an > order by, so > > INSERT {+INDEX(oid_index)} INTO user_file_read_table ( oid, userId, > reader, fileName, readStatus) VALUES ( '99.99.99.99.99.99..99.99', > 13, 'hardya', 'testDir1/testDir2/testFile', 1) > > SELECT * FROM user_file_read_table order by oid> > Many thanks, > > ----- Forwarded by Andrew Hardy/MAIN/MC1 on 14/10/2003 11:59 ----- > |---------+--------------------------------> > | | "Marley, Peter" | > | | <peter.marley@acco-eu| > | | rope.co.uk> | > | | | > | | 14/10/2003 11:29 | > | | | > |---------+--------------------------------> > >--------------------------------------------------------------------------- -----------------------------------------------------------------------| > | | > | To: "'Andrew Hardy'" <Andrew.Hardy@marconi.com> | > | cc: | > | Subject: RE: Correct use of an automatically created index on a primary ke y | > >--------------------------------------------------------------------------- -----------------------------------------------------------------------| > > > > > Andrew > > > When creating indexes it is best to first create the index using whatever > fragmentation you want. Then add the primary key. If an already index > exists on the primary key column it will use that, instead of creating > another index with a system defined name. So you end up with 1) an index > with your naming convention and 2) the correct fragmentation strategy for > the index. > > > Then apply an update statistics to the table to let the optimiser know > where the data is and size of table and distribution of data etc. > > > When querying the data you do not normally need to specify index names. The > WHERE clause or ORDER BY clause will hint the optimzer to use the correct > index. Also. Depending on the size of the table (hence the update > statistics) the optimiser may decide not to use the index as it has decided > that a sequential scan is quicker > > > > > > All the best > Peter > __________________________________ > Peter Marley > DBA, Acco UK > Tel: +44 (0)1296732228 > Fax: +44 (0)1296732203 > email: peter.marley@acco-europe.co.uk > > > -----Original Message----- > From: Andrew Hardy [mailto:Andrew.Hardy@marconi.com] > Sent: 14 October 2003 10:55 > To: informix-list@iiug.org > Subject: Correct use of an automatically created index on a primary > key > > > Please excuse me for anything stupid . I am very new to Informix and to > SQL at this level. > > > I need to know how to correctly use an automatically created index on a > primary key, or if I need to remove it and replace it with one of my own, > and how I may do that. > > > Andrew H. > > > > > > > Detail > =========================================================== > > > I have the following table, which potentially contains 10,000 records, and > I think I need an index to do my inserts and my retrievals, because I need > to quickly retrive the next row in order of the primary key field (oid). > > > I create the table like this > > > CREATE TABLE user_file_read( oid AsnObjectId, userId integer, reader > VARCHAR(255), fileName VARCHAR(255), readStatus integer, PRIMARY KEY (oid) > ) LOCK MODE ROW;> > > AsnObjectId is a user defined type for which the server knows about > functions for comparison etc and for which the client registers. This > appears to work successfully and was written for us by Informix. > > > Then I try to create the index like this > > > CREATE INDEX oid_index ON user_file_read (oid);> > > I get error -350 'Index already exists on column'. So I find out what the > > index is, it's ' 2828_63' (with a leading space) and appears not to change > after table creation, then I do my inserts and selects like these examples. > > Clearly in the long term the index name ought not to be hard coded. > > > SELECT {+INDEX(' 2828_63')} * FROM user_file_read_table order by oid > > > And for the insert, this is an example > > > INSERT {+INDEX(' 2828_63 )} INTO user_file_read_table ( oid, userId, > reader, fileName, readStatus) VALUES ( '99.99.99.99.99.99..99.99', 13, > 'hardya', 'testDir1/testDir2/testFile', 1) > > > My inserts are successful. > > > My select is successful and correct and I can do next on that to travers in > > oid order, but the execution of the select with 10,000 records is currently > > taking about 30 seconds. A straight forward unordered next is instant. I > thought that using the index would imnprovce performance. I must be doing > something worng, but I get no clues, because I get no errors. > > > > > > > sending to informix-list > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~

On Tue, 14 Oct 2003 12:56:16 +0100, "Andrew Hardy" <Andrew.Hardy@marconi.com> wrote: > > >Does any one know how to prevent implicit creation of an index when setting >the primary key for a table. > >Andrew H. > Far as I know, it's automatic.

On Tue, 14 Oct 2003 07:56:16 -0400, Andrew Hardy wrote: Yes explicitely create a unique index on the primary key columns in the same order you will be listing them in the constraint. Then the constraint will use the existing index instead of creating its own. Art S. Kagel > Does any one know how to prevent implicit creation of an index when setting > the primary key for a table. > > Andrew H. > > > ----- Forwarded by Andrew Hardy/MAIN/MC1 on 14/10/2003 12:55 ----- > |---------+----------------------------> | | Andrew Hardy > | | | | | | 14/10/2003 > 12:18 | | | | > |---------+----------------------------> > >--------------------------------------------------------------------------------------------------------------------------------------------------| > | > | | > To: informix-list@iiug.org > | | > cc: > | | > Subject: RE: Correct use of an automatically created index on a primary ke > y | > >--------------------------------------------------------------------------------------------------------------------------------------------------| > > > > Sorry to be a pain, I'm a real newbie. > > IRO the suggestion below, is this the kind of thing ? > > CREATE TABLE user_file_read( oid AsnObjectId, userId integer, reader > VARCHAR(255), fileName VARCHAR(255), readStatus integer) LOCK MODE ROW;> > CREATE INDEX oid_index ON user_file_read (oid ASC) FRAGMENT BY > EXPRESSION ___ IN ___, ___ IN ___;> > ALTER TABLE user_file_read ADD CONSTRAINT PRIMARY KEY (oid);> > UPDATE STATISTICS;> > Then I understand now, that it should use the index automatically for an order > by, so > > INSERT {+INDEX(oid_index)} INTO user_file_read_table ( oid, userId, > reader, fileName, readStatus) VALUES ( '99.99.99.99.99.99..99.99', 13, > 'hardya', 'testDir1/testDir2/testFile', 1) > > SELECT * FROM user_file_read_table order by oid> > Many thanks, > > ----- Forwarded by Andrew Hardy/MAIN/MC1 on 14/10/2003 11:59 ----- > |---------+--------------------------------> | | "Marley, > Peter" | | | <peter.marley@acco-eu| | | > rope.co.uk> | | | | | > | 14/10/2003 11:29 | | | > | |---------+--------------------------------> > >--------------------------------------------------------------------------------------------------------------------------------------------------| > | > | | > To: "'Andrew Hardy'" <Andrew.Hardy@marconi.com> > | | > cc: > | | > Subject: RE: Correct use of an automatically created index on a primary ke > y | > >--------------------------------------------------------------------------------------------------------------------------------------------------| > > > > > Andrew > > > When creating indexes it is best to first create the index using whatever > fragmentation you want. Then add the primary key. If an already index exists > on the primary key column it will use that, instead of creating another index > with a system defined name. So you end up with 1) an index with your naming > convention and 2) the correct fragmentation strategy for the index. > > > Then apply an update statistics to the table to let the optimiser know where > the data is and size of table and distribution of data etc. > > > When querying the data you do not normally need to specify index names. The > WHERE clause or ORDER BY clause will hint the optimzer to use the correct > index. Also. Depending on the size of the table (hence the update statistics) > the optimiser may decide not to use the index as it has decided that a > sequential scan is quicker > > > > > > All the best > Peter > __________________________________ > Peter Marley > DBA, Acco UK > Tel: +44 (0)1296732228 > Fax: +44 (0)1296732203 > email: peter.marley@acco-europe.co.uk > > > -----Original Message----- > From: Andrew Hardy [mailto:Andrew.Hardy@marconi.com] Sent: 14 October 2003 > 10:55 > To: informix-list@iiug.org > Subject: Correct use of an automatically created index on a primary key > > > Please excuse me for anything stupid . I am very new to Informix and to SQL > at this level. > > > I need to know how to correctly use an automatically created index on a > primary key, or if I need to remove it and replace it with one of my own, and > how I may do that. > > > Andrew H. > > > > > > > Detail > =========================================================== > > > I have the following table, which potentially contains 10,000 records, and I > think I need an index to do my inserts and my retrievals, because I need to > quickly retrive the next row in order of the primary key field (oid). > > > I create the table like this > > > CREATE TABLE user_file_read( oid AsnObjectId, userId integer, reader > VARCHAR(255), fileName VARCHAR(255), readStatus integer, PRIMARY KEY (oid) ) > LOCK MODE ROW;> > > AsnObjectId is a user defined type for which the server knows about functions > for comparison etc and for which the client registers. This appears to work > successfully and was written for us by Informix. > > > Then I try to create the index like this > > > CREATE INDEX oid_index ON user_file_read (oid);> > > I get error -350 'Index already exists on column'. So I find out what the > > index is, it's ' 2828_63' (with a leading space) and appears not to change > after table creation, then I do my inserts and selects like these examples. > > Clearly in the long term the index name ought not to be hard coded. > > > SELECT {+INDEX(' 2828_63')} * FROM user_file_read_table order by oid > > > And for the insert, this is an example > > > INSERT {+INDEX(' 2828_63 )} INTO user_file_read_table ( oid, userId, reader, > fileName, readStatus) VALUES ( '99.99.99.99.99.99..99.99', 13, 'hardya', > 'testDir1/testDir2/testFile', 1) > > > My inserts are successful. > > > My select is successful and correct and I can do next on that to travers in > > oid order, but the execution of the select with 10,000 records is currently > > taking about 30 seconds. A straight forward unordered next is instant. I > thought that using the index would imnprovce performance. I must be doing > something worng, but I get no clues, because

Andrew Hardy wrote: > Does any one know how to prevent implicit creation of an index when > setting the primary key for a table. Create the appropriate index explicitly first? > ----- Forwarded by Andrew Hardy/MAIN/MC1 on 14/10/2003 12:55 ----- > |---------+----------------------------> > | | Andrew Hardy | > | | | > | | 14/10/2003 12:18 | > | | | > |---------+----------------------------> > Sorry to be a pain, I'm a real newbie. It's OK, I see you use Notes -- I feel your pain. > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > > > This e-mail and any attachments thereto may contain > information which is confidential and/or protected by > intellectual property rights and are intended for the > sole use of the recipient(s) named above. Any use of > the information contained herein (including, but not > limited to, total or partial reproduction, communication > or distribution in any form) or the taking of any action > in reliance on the contents, by persons other than the > designated recipient(s) is strictly prohibited. > > If you have received this e-mail in error, please notify > the sender either by telephone or by e-mail and delete > the material from any computer. No I frickin' won't! -- Ciao, The Obnoxious One "Ogni uomo mi guarda come se fossi una testa di cazzo"