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, one is instantaneous.
The explain output for this query is as follows:
QUERY:
------
SELECT {+INDEX(user_file_read_table, oid_index)} reader, filename,
readStatus FROM user_file_read_table WHERE oid = '\\0BProfile3/68\\06cdsftp';
DIRECTIVES FOLLOWED:
INDEX ( user_file_read_table oid_index )
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 1
Estimated # of Rows Returned: 2
1) root.user_file_read_table: INDEX PATH
(1) Index Keys: oid (Serial, fragments: ALL)
Lower Index Filter: root.equal(root.user_file_read_table.oid
,'\\0BProfile3/68\\06cdsftp' )
UDRs in query:
--------------
UDR id : 28150
UDR name: equal
Argument # 2 needs casting
Argument casting function id : 28148
Argument casting function name: asnoctetstring382_input
UDR id : 28149
UDR name: compare
Why does the following query 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. This seems likely cos returning rows from the end of
the order takes longer
than returning rows from the start of the order! But Why is it doing it
this way ?
The explain output for this one is as follows:
QUERY:
------
SELECT {+INDEX(user_file_read_table, oid_index)} first 1 oid, reader,
fileName, readStatus FROM user_file_read_table WHERE oid >
'\\0BProfile3/67\\06cdsftp' ORDER BY oid
DIRECTIVES FOLLOWED:
INDEX ( user_file_read_table oid_index )
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 2
Estimated # of Rows Returned: 2
1) root.user_file_read_table: INDEX PATH
Filters: informix.greaterthan(root.user_file_read_table.oid
,'\\0BProfile3/67\\06cdsftp' )
(1) Index Keys: oid (Serial, fragments: ALL)
UDRs in query:
--------------
UDR id : -404
UDR name: greaterthan
Argument # 1 needs casting
Argument casting function id : 28147
Argument casting function name: asnoctetstring382_output
UDR id : 28149
UDR name: compare
Is there a more specific way I can tell the server to just get the next row
along from '\\0BProfile3/67\\06cdsftp',
so that it uses the index better and doesn't try anything excessive.
The oid value provided, of which I want the next, will also always be an
existing oid, don't know if this helps.
Can any-one help ?
Andrew H.
sending to informix-list