RE: Correct use of an automatically created index on a primary ke
You've got the syntax wrong on the optimizer directive. Try:
{+INDEX(TableName ColumnName)}
Also, if you update statistics on the table per the performance guide
recommendations, the optimizer would use the index if appropriate.
Regards,
Bill
> -----Original Message-----
> From: Andrew Hardy [SMTP:Andrew.Hardy@marconi.com]
> Sent: Tuesday, October 14, 2003 5:55 AM
> 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
sending to informix-list