A few days ago I posted an example of SQL that could be used to reduce the impact of sorting a large volume of data by sorting the smallest possible subset of the data with its rowids, and then joining back to the original table by rowid.
This produced a few comments, backed by Tom Kyte, about the dangers of depending on (a) SQL returning data in order without a final “order by” clause, (b) the exact and unchangeable use of hints, and (c) an assumption that internal mechanisms would not change.
It’s worth saying a little more about these issues, but I thought I’d start with the background to the SQL that appeared in the previous post as it’s actually derived from a generic strategy that I’ve used a couple of times as a temporary performance fix for Web-based applications.
The basic requirement for many Web-based reporting systems is to be able to run “page-based” reports, which means the ability to respond efficiently to queries like: “return rows 21 to 40 of an ordered set” - searches of Google or Amazon give you the general principle of the need for this type of pagination.
The mechanism of using a couple of “rownum” predicates against an inline view is quite well known as a way of optimising this type of page-based access; but it usually requires you to build a suitable index to support the underlying query. For example:
select
v2.id,
v2.small_vc
from
(
select
v1.id,
v1.small_vc,
rownum rn
from
(
select
t1.id,
t1.small_vc
from
t1
where
t1.rep = 100
order by
t1.id
) v1
where
rownum <= 20 -- First N rows, typically a bind variable
) v2
where
v2.rn >= 11 -- Last M rows, typically a bind variable
order by
v2.rn
;
Execution Plan (10.2.0.3 - dbms_xplan.display_cursor() edited to remove columns)
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id| Operation | Name | E-Rows | A-Rows | Buffers | Used-Mem |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| 1| SORT ORDER BY | | 20 | 10 | 4 | 2048 (0)|
|* 2| VIEW | | 20 | 10 | 4 | |
|* 3| COUNT STOPKEY | | | 20 | 4 | |
| 4| VIEW | | 21 | 20 | 4 | |
| 5| TABLE ACCESS BY INDEX ROWID| T1 | 21 | 20 | 4 | |
|* 6| INDEX RANGE SCAN | T1_PK | | 20 | 3 | |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Predicate Information (identified by operation id):
- - - - - - - - - - - - - - - - - - - - - - - - - -
2 - filter(”V2″.”RN”>=11)
3 - filter(ROWNUM<=20)
6 - access(”T1″.”REP”=100)
For the best performance both the “where” clause and “order by” clause have to be “captured” in a single index, where the “where” clause is an equality condition against the leading column(s) of the index, and the “order by” clause follows the next columns of the index definition.
If you can’t match these requirements, your query will have to collect all the data identified by the “where” clause and sort it according to the “order by” clause before restricting the result set to the single “page” that you wanted. If you do meet the requirements, the optimizer is able to produce a plan that can avoid some (most) of the work by “pushing” the row limit inside the index range scan, as indicated above.
So what do you do when you have to handle a query that does something a little more awkward. Here’s an example of how to minimise the overheads:
select
/*+
qb_name(main)
leading(ptr@main t1@main)
use_nl(@main t1@main)
*/
t1.*
from
(
select
/*+ qb_name(last_M) */
rid, rn
from (
select
/*+ qb_name(first_N) */
rid,
rownum rn
from
(
select
/*+ qb_name(rowids) */
rowid rid
from
t1
where
log_id = 1
and transaction_date >= trunc(sysdate - 1)
order by
transaction_id desc,
id desc
)
where
rownum <= 500
)
where rn >= 481
) ptr,
t1 t1
where
t1.rowid = ptr.rid
order by
ptr.rn
;
The driving query doesn’t acquire all the data it needs, it acquires the minimum necessary data, which (in this case) is just the set of rowids for the target data, and sorts that minimum set according to the required ordering clause.
Once you have the sorted set of rowids, you can do the usual “get_N / discard_M” trick with the rownum to get the rowids for the page you want - and with these rowids you can do an efficient join back to the “real” data, accessing exactly the required rows in the most efficient way possible.
In these circumstances, ending the statement with an “order by” clause that repeats the ordering implied by the earlier “order by” clause will ensure that the final result is ordered correctly with only a small increase in the work load … even if you think the “order by” seems to be unnecessary.
But I’ve had to include some hints in the final join back to make sure that the optimizer does something that I know to be sensible. The example shows the stage where we’tr trying to get rows 481 to 500 of the underlying report - and in this case the optimizer can use the 500 that appears in the first “rownum” predicate as part of its cost calculation; but it can’t handle the 481 that appears in the second “concealed” rownum predicate. So the optimizer’s estimate of cardinality for this query is 500, despite the fact that we know that the size of the result is going to be 20.
The result of this is that (with my data set) a point came as I paged through the data where the optimizer switched from the nested loop with rowid access (low cardinality path) to a hash join that scanned the t1 table (high cardinality path) - completely defeating the point of the complex query.
My clever query depends on hints to do what I know to be the right thing all the time, every time.
But how much of a surprise is that ? Go back to the simpler example at the start of this page. Although we’ve written a query that should obviously walk through an index to pick up 20 rows at a time in the right order, it is perfectly feasible for the optimizer to ignore the index when optimising this query, especially if you are after (say) rows 201 to 220 … so even in this simple case you really need to include at least one hint (viz: to use the apporpriate index) to make sure that the path doesn´t go wrong.
The point I want to make, of course, is this - any time you want “page-based” SQL to operate with maximum efficiency, you are trying to do something that the optimizer has not been programmed to do, for reasons that only you can see. So you will have to supply some hints to block any execution paths that might be inefficient. The argument that my original SQL depends on hints to work efficiently is not a sufficiently powerful argument to stop you from using it - it’s just a reminder that (as with all code that’s hinted) you have to document and manage the code properly.
In the original article a more important criticism of the query I showed was that it depended on assumptions about the implementation of a particular join mechanism. That is a much more powerful criticism - and one that I shall address in my next note on this topic.
[Back to Manual Optimisation part 1]
