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