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 Tue, 25 Nov 2003 03:58:10 -0500, Andrew Hardy wrote: A 10000 row table may be considered small by the engine if there are many rows on a single page, if so, the engine is likely performing a table scan to identify the rows and sorting. Have you tried running under SET EXPLAIN ON; to see the query plan and verify the perceived behavior? You could try SET OPTIMIZATION FIRST_ROWS; this will force the engine to optimize the query for returning the initial rows as quickly as possible, this is more likely to use an index that matches the ORDER BY clause. But wait, there's more: > 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 Ummmmm. The oid column is an integer? According to below it contains small numbers. So, why is the query above comparing it to a string that is apparently a file path? This will cause the engine to convert the values of oid to strings for comparison. Since ALL of the strings "1", "2", etc. are lexicographically greater than any string that begins with "\\15" (character Octal(15)), the filter is selecting all rows and so the sequential scan followed by a physical sort is the BEST query path, and that is the behavior you are experiencing. Art S. Kagel > 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