Re: Select Performance Using Order By on Index

theo@rasdev.rascal.co.za (T. Kramer 794-1472) wrote: The OR predicate in the 1st query will most likely cause a table scan. Check your explain output. Regards, Rafal. -------------------------------------------------------------------------------------------------------------------------------- Rafal Czerniawski Dataspace Consulting Pty Ltd rafal@dataspace.com.au Checkout our Database and Data Managment Resources page: http://www.dataspace.com.au/resource.html -------------------------------------------------------------------------------------------------------------------------------- >Hi, >I have a peformance problem when using select and order by on a multi field >index that, hopefully, some of you SQL gurus can help me with. >The problem is as follows: >I need to select and fetch forwards in the natural index order where the index >contains multiple segments on a single data table. To illustrate the point >and for the sake of simplicity I will use numeric fields as follows: > field1 field2 field3 > ----------------------------- > 1 5 1 > 1 5 2 > 1 6 0 > 1 6 1 > 1 6 2 > 1 6 3 > 2 4 3 >The index I have created is unique and consists of all three fields in order. >The query that I use is as follows: >SELECT * FROM find WHERE > find_field1 = 1 AND find_field2 = 5 AND find_field3 >= 1 OR > find_field1 = 1 AND find_field2 > 5 OR > find_field1 > 1 >ORDER BY find_field1, find_field2, find_field3 >Doing this query on small tables gives reasonable performance, however, if I >do the query on large tables (approx 30000 records) and the required record >is towards the end of the index the select becomes unacceptably slow. Note that >the extent sizes on the table are correctly set up. >Changing the query to the following: >SELECT * FROM find WHERE > find_field1 >= AND find_field2 >= 5 AND find_field3 >= 1 >ORDER BY find_field1, find_field2, find_field3 >provides intantaneous response (so I do know that informix can do it) yet provides >an incorrect result set when scrolling forwards ie. the third and last record >are no longer part of the result set. Comparing the cost using 'explain' also >gives an entire different picture for the two queries ie. the first query is >much more expensive than the second, yet I feel that the second should be more >expensive as records in the natural index sequence have to be skipped. Note that >in both cases informix reports that it does use the index. >The questions I have are as follows: >1. Why is this the case on a naturally ordered index? >2. What can I do to improve the select performance with the result set > being what I require? >Thanks in advance, >Theo Kramer >theo@rascal.co.za