Dharmakumar Gajendran wrote:What's happening here is for ORDER BY 1 the query returns the result fast. But while sorting other than 1st column its taking more time.
So you know how to sort. I'd just recommend creating a view returning the base data and then using the pagination query just with the view. It would simplify the things out.
Sorting by first column is fast most probably because there is an index on that column (I guess the ID is a primary key, and therefore it is indexed). You need to add an index on other columns you want to sort by. If you'll run this query with several different orderings, you'll need to create an index for every ordering you plan to use. If you want to sort by expression, you might use function-based index.
Additionally, I'd say that the
/*+ FIRST_ROWS(25) */ hint should appear in the outermost select, not in the inner one. But if your query is fast enough (when sorting by
orders.id, that is), the query plan is certainly good and moving the hint should not change anything.
And finally, calling PL/SQL functions from an SQL is a bit slower than pure SQL. I don't know what your functions do, but if they could be converted to pure SQL, you might get better response times. In either case (SQL or PL/SQL - assuming the functions are deterministic) a materialized view might help too, but we're getting rather far from the original topic now.