Manual Optimisation

[Forward to Manual Optimisation part 2]

Warning: The following note supplies an example of a mechanism that I believe to be safe in Oracle 8i, 9i, and 10g (except when hidden parameter _optimizer_ignore_hints is set to true) and is probably safe in 11g.

However, the SQL shown is expected to return data in a given order without a final “order by” clause, and it is Tom Kyte’s opinion  that it would be dangerous to use this code on a production system.  

I agree with Tom’s argument – to the degree that any hinted code or any code that depends on side-effects of the current mechanics should be treated with great caution and its use should be documented, kept to a minimum, and checked regularly.

The following question appeared in a recent post on the Oracle DBA Forum.

We have a developer here who thinks that DBAs can make things happen. His query is against a single table and his result set is around 300,000 records and he wants to sort this by two columns (transaction_id desc, id desc) where id is the primary key in the table. The query does not take a long time but the sort part is the problem. Is this doable or not. His query is:

select  *
from    event
where   log_id = 1
and     date_posted > trunc(sysdate-1)
order by
        transaction_id desc,
        id desc
;

Before you get too busy trying to speed up such a query, one of the important questions you have to ask in response is: “Why does anyone want to retrieve 300,000 rows from the database?”

There are valid reasons for doing so, of course. You may, for example, be driving a statistical analysis package that needs to acquire large volumes of raw data (though the order by then looks a little suspect); but no-one reads 300,000 lines of a report and if the front-end code is acquiring the data to summarise it then it’s probably better to summarise it in the database.

Let us assume, however, that (a) the front-end really does need to the get this data, (b) it does need to appear in sorted order, and (c) it is a relatively small extract from a much larger dataset. Is there any way to make this more efficient.

The main problem we have to face is that we have a range-based predicate that gets a lot of data combined with an order by clause that uses a different set of columns from the predicate list. This makes it difficult to optimize the query through a simple index. (If we could change the second predicate to date_posted = trunc(sysdate-1) then an index on (log_id, date_posted, transaction_id desc, id desc) would allow the optimizer to do an index range scan, access the table, and return the data in order without sorting).

Note (2nd May) – from this point onwards in the text I managed to change from ‘date_posted >’ to ‘transaction_date >=’ without spotting that I had done so. Given a conversation about it that appears in the comments, I have left this change in place.

We might be lucky, of course. If there is a strong correlation between log_id and transaction_date such that most of the data with log_id=1 also had transaction_date >= trunc(sysdate – 1), then we might get a reasonable response time by creating the index (log_id, transaction_id desc, id desc) – allowing Oracle to use an index range scan to access the data in the right order picking up a little more data (we hope) than we need, then discarding the (few, we hope) rows which fail the test on transaction_date.

Since the stated problem is one of sorting (rather than accessing) the data we won’t consider the options for creating a ‘fat index’ (see Book Reviews for an explanation of this term).

So what does that leave us with? We have been told that it is the sorting that is the problem – so possibly the number of columns in that “select *” is large making the total volume of data (although “only” 300,000 rows) very large. Is there any way we could minimise the sorting if we can’t build a “perfect” index ?

The answer is yes – although it may require a little lateral thinking.

In this case, we will need a slightly smaller and simpler index (no descending columns) than the four column index shown above: (log_id, date_posted, transaction_id, id) but we’re not going to use it in the standard way. Instead we’re going to take a two-pass approach to the problem. We start with the following SQL:

select
        rowid   rid
from
        t1
where
        log_id = 1
and     transaction_date >= trunc(sysdate - 1)
order by
        transaction_id  desc,
        id              desc
;

This gets us the rowids of all the rows that our main query wants, and because of the order by clause in this query, if we visited the rows in the order of our result set, we would be visiting them in exactly the order that would satisfy our original query.

Although we are still performing a sort operation we are sorting the smallest possible set of values that we could get away with, and once we have the rowids in order we can use them to visit the table with no further sorting. So all we have to do is stick this initial query into a non-mergeable  inline view:

em
rem     Script:         index_pass.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2008
rem 

select
        /*+
                qb_name(main)
                leading (ptr@main t1@main)
                use_nl(@main t1@main)
                rowid(@main t1@main)
        */
        t1.*
from
        (
        select
                /*+
                        no_merge
                        qb_name(rowids)
                        no_eliminate_oby(@rowids)
                        index(@rowids t1(log_id, transaction_date, transaction_id, id))
                */
                rowid   rid
        from
                t1
        where
                log_id = 1
        and     transaction_date >= trunc(sysdate - 1)
        order by
                transaction_id  desc,
                id              desc
        )       ptr,
        t1
where
        t1.rowid = ptr.rid
;

This query gets us the data we want, in the order we want, with the smallest possible sort. The cost/benefit analysis we have to do is simple: does the random access to the table that this execution path gives us result in less work than any other mechanism that gets the data and sorts entire rows. The answer to that question depends on the data volume and distribution.

You’ll notice the great stack of 10g hints – I have used this type of code on 8i and 9i in the past, but I’ve always put in a lot of hints to minimise the risk of Oracle doing something “clever” to spoil my plan. 10g introduces cost-based query transformation – and has far more options for unwrapping any clever tricks you introduce to SQL; so if you’re going to try messing about with “manual” optimisation you do need a lot of hints to block every new feature that might cause problems. (Highlighted following note 18 and its link to AskTom – see also “Rules for Hinting”)

The most important hint in this example is probably the no_eliminate_oby hint, which I have described in the past. Without this hint you may find that the optimizer decides that the order by clause adds no value to the inline view, and eliminates it – while still honouring the no_merge hint.

The execution plan you get from this query, running under 10.2.0.3 in my case, is as follows:

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  3400 |  1470K|  3420   (1)| 00:00:42 |
|   1 |  NESTED LOOPS               |       |  3400 |  1470K|  3420   (1)| 00:00:42 |
|   2 |   VIEW                      |       |  3400 | 40800 |    17   (6)| 00:00:01 |
|   3 |    SORT ORDER BY            |       |  3400 |   106K|    17   (6)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN        | T1_I1 |  3400 |   106K|    16   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY USER ROWID| T1    |     1 |   431 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("LOG_ID"=1 AND "TRANSACTION_DATE">=TRUNC(SYSDATE@!-1) AND
              "TRANSACTION_DATE" IS NOT NULL)

As you can see the view operator tells us that we’ve created an ordered list from the inline view and then, for each rowid in the list, accessed the t1 table by “user” rowid – just as we wanted.

So – when you can’t do what you want with simple SQL, remember that you can always decompose a query into steps to reduce the size of the ‘hard work’, then use an intermediate result set (possibly joined back to the original table, as in my example) to collect the rest of the data. It’s a technique I have used successfuly to suppy short-term fixes to performance issues that some of my clients have had on their web-based applications.

[Forward to Manual Optimisation part 2]