Tuning Order by when SELECT clause has data from multiple tables
Hi ,
I have a sql query that needs tuning. The bulk of the time it spends is in SORTING of data. The critical part of its execution plan looks like this:
PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------------------------------- | 1 | SORT ORDER BY | | 1 | 59 | 17674 |00:00:45.07 | 173K| 16411 | 7920K| 1111K| 7039K (0)| |* 2 | FILTER | | 1 | | 17674 |00:00:18.88 | 77291 | 6903 | | | | |* 3 | INDEX RANGE SCAN | MSG_INST_TEST7 | 1 | 471 | 40673 |00:00:01.84 | 2110 | 2110 | | | | |* 4 | INDEX RANGE SCAN | MSG_USER_TEST9 | 40638 | 2 | 22999 |00:00:16.77 | 75076 | 4765 | | | | |* 5 | INDEX RANGE SCAN | PRC_INST_TEST3 | 35 | 1 | 0 |00:00:00.12 | 105 | 28 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------
Now the problem is that the query also has several scalar subqueries, where depending on the outcome it gets data from other tables and not just from one table. In this case, can anything be done to tune the "SORT ORDER BY" clause? I have attached part of the execution plan that i pasted here.
0