getting the next row along ordered by an indexed field

oid is an opaque type with comparison and equality functions, the oid_index is a successfully created ascending index on oid. with 10,000 records, The following query is instantaneous: SELECT {+INDEX(user_file_read_table oid_index)} oid, reader, fileName, readStatus FROM user_file_read_table ORDER BY oid So is this one: SELECT {+INDEX(user_file_read_table oid_index)} oid, reader, fileName, readStatus FROM user_file_read_table WHERE oid = '\\15tesdir1/testdir2/4000\\06hardya' Why does this one take so long, when I just want to get the very next row along and there is an index. Maybe It's ordering, and THEN searching though all the rows, cos rows from the end of the order take longer to return than rows from the start of the order! But Why ? SET OPTIMIZATION FIRST_ROWS; SELECT {+INDEX(user_file_read_table oid_index)} first 1 oid, reader, fileName, readStatus FROM user_file_read_table WHERE oid > '\\15tesdir1/testdir2/4000\\06hardya' ORDER BY oid Is there a more specific way I can tell the server to just get the next row along from '\\15tesdir1/testdir2/4000\\06hardya', so that it uses the index better and doesn't try anything excessive. The oid value provided, of which I want the next, will always also be an existing oid, don't know if this helps. Can any-one help ? Andrew H. sending to informix-list

On Tue, 02 Dec 2003 06:08:57 -0500, Andrew Hardy wrote: What do the SET EXPLAIN outputs say about the three queries? Art S. Kagel > oid is an opaque type with comparison and equality functions, the oid_index is > a successfully created ascending index on oid. > > with 10,000 records, > > The following query is instantaneous: > > SELECT {+INDEX(user_file_read_table oid_index)} oid, reader, fileName, > readStatus FROM user_file_read_table ORDER BY oid > > So is this one: > > SELECT {+INDEX(user_file_read_table oid_index)} oid, reader, fileName, > readStatus FROM user_file_read_table WHERE oid = > '\\15tesdir1/testdir2/4000\\06hardya' > > Why does this one take so long, when I just want to get the very next row > along and there is an index. Maybe It's ordering, and THEN searching though > all the rows, cos rows from the end of the order take longer to return than > rows from the start of the order! But Why ? > > SET OPTIMIZATION FIRST_ROWS; > SELECT {+INDEX(user_file_read_table oid_index)} first 1 oid, reader, fileName, > readStatus FROM user_file_read_table WHERE oid > > '\\15tesdir1/testdir2/4000\\06hardya' ORDER BY oid > > Is there a more specific way I can tell the server to just get the next row > along from '\\15tesdir1/testdir2/4000\\06hardya', so that it uses the index > better and doesn't try anything excessive. The oid value provided, of which I > want the next, will always also be an existing oid, don't know if this helps. > > Can any-one help ? > > Andrew H. > > > sending to informix-list