Question about index performance for a "...where x > 'y' order by

SELECT {+INDEX(user_file_read_table, oid_index)} first 1 oid, reader, fileName, readStatus FROM user_file_read_table WHERE oid > '\\15tesdir1/testdir2/4998\\06hardya' ORDER BY oid I have the above sql statement. An ascending index has been successfully created on the opaque type oid field. I have a 10,000 row table for testing, which in order of appearance has the first oid starting in the middle of the table and ascending to the end of the table, the next in oid order is then the first record in order of appearance continuing to the middle. A miniture example would be as follows Rows in order of appearance 1. oid = 5, filename = "xxx", ... 2. oid = 6, filename = "xxx", ... 3. oid = 7, filename = "xxx", ... 4. oid = 8, filename = "xxx", ... 5. oid = 1, filename = "xxx", ... 6. oid = 2, filename = "xxx", ... 7. oid = 3, filename = "xxx", ... 8. oid = 4, filename = "xxx", ... This index appeared to create ok. CREATE UNIQUE INDEX oid_index ON %s (oid ASC);ALTER TABLE %s ADD CONSTRAINT PRIMARY KEY (oid); And had all the functions it needed. Now, if I execute the above statement to get the first row (in oid order ) after a specified oid, I get the right answer, what ever oid I specify or what ever I put into the database. BUT the oids I specify nearer the top end of the oid range, the longer the query takes, which suggests to me that the index may be being used for ordering, but not for the greater than bit of the statement. I have tried this statement different ways around, but am still unable to improve the performance. Does any one have any ideas ? Many thanks, Andrew H sending to informix-list

On Fri, 21 Nov 2003 16:45:08 +0000, "Andrew Hardy" <Andrew.Hardy@marconi.com> wrote: > > > >SELECT {+INDEX(user_file_read_table, oid_index)} first 1 >oid, reader, fileName, readStatus FROM user_file_read_table >WHERE oid > '\\15tesdir1/testdir2/4998\\06hardya' >ORDER BY oid > > >I have the above sql statement. An ascending index has been successfully >created on the opaque type oid field. > >I have a 10,000 row table for testing, which in order of appearance has the >first oid starting in the middle of the table and ascending to the end of >the table, the next in oid order is then the first record in order of >appearance continuing to the middle. > >A miniture example would be as follows > >Rows in order of appearance >1. oid = 5, filename = "xxx", ... >2. oid = 6, filename = "xxx", ... >3. oid = 7, filename = "xxx", ... >4. oid = 8, filename = "xxx", ... >5. oid = 1, filename = "xxx", ... >6. oid = 2, filename = "xxx", ... >7. oid = 3, filename = "xxx", ... >8. oid = 4, filename = "xxx", ... > >This index appeared to create ok. > >CREATE UNIQUE INDEX oid_index ON %s (oid ASC);>ALTER TABLE %s ADD CONSTRAINT PRIMARY KEY (oid); > >And had all the functions it needed. > >Now, if I execute the above statement to get the first row (in oid order ) >after a specified oid, I get the right answer, what ever oid I specify or >what ever I put into the database. > >BUT the oids I specify nearer the top end of the oid range, the longer the >query takes, which suggests to me that the index may be being used for >ordering, but not for the greater than bit of the statement. > >I have tried this statement different ways around, but am still unable to >improve the performance. > >Does any one have any ideas ? > Did you update stats for the given table after the index was created??