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 (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.
Thank you for your cooperation.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sending to informix-list