How to Change the ORDER BY When You Can’t Modify the SQL

Is it possible to change the ORDER BY on a query, if the SQL is defined in a wrapped PL/SQL package? A colleague asked me this interesting question today – and the answer was quite simply, Yes and No.

If the SQL already has an ORDER BY, then the answer is No. Fortunately, in our case, the SQL didn’t have any ORDER BY clause:

SELECT a.addressid
FROM address a, addresstype t
WHERE a.locationid = :locationid
AND a.typeid = t.typeid
AND t.typecode = 'STREET';

The plan currently being picked by the CBO (Oracle 9.2.0.8) is:

NESTED LOOPS
   TABLE ACCESS BY ROWID addresstype
      INDEX RANGE SCAN pkx_addresstype
   INDEX RANGE SCAN ix_address

For background, here are the relevant schema details:

TABLE address
(addressid NUMBER PRIMARY KEY
,locationid NUMBER NOT NULL
,typeid NUMBER NOT NULL --FOREIGN KEY to addresstype
);

INDEX idx_address (locationid, typeid, addressid);

TABLE addresstype
(typeid NUMBER PRIMARY KEY
,typecode VARCHAR2(30) NOT NULL
);

typecode is actually unique, but no constraint has been declared to the database.

Because the first step in the plan (the TABLE ACCESS on addresstype) only ever gets one row (in practice), the index range scan on ix_address causes this query to return the rows in ascending order of addressid.

The process that runs this query gets rows one at a time, does some work to “check” it against some other data, and once it finds a “match”, it stops getting more rows.

This design probably seemed like a good idea at the time, but now the business would like this process changed so that in the (rare) occurrence when more than one row would “match”, the process should favour a more recent address rather than an old one. For our purposes, “recent” may be approximated by assuming that bigger addressids are more recent than smaller addressids.

We can’t change the code without a lengthy and costly call to the application vendor; it would be better if we can change it ourselves. We cannot change the PL/SQL package in which this query is defined; so what can we do? Can we change the order in which rows are returned from this query?

The answer is yes – firstly, the query has no ORDER BY, so the order of rows is affected only by the query plan. If we change the query plan, which we can do with a stored outline, the order of rows can be changed.

So in this case, all we have to do is create a stored outline to add an INDEX_DESC hint:

SELECT /*+INDEX_DESC(a)*/ a.addressid
FROM address a, addresstype t
WHERE a.locationid = :locationid
AND a.typeid = t.typeid
AND t.typecode = 'STREET';

This causes the following plan to be used:

NESTED LOOPS
   TABLE ACCESS BY ROWID addresstype
      INDEX RANGE SCAN pkx_addresstype
   INDEX RANGE SCAN DESCENDING ix_address

With the stored outline, the query now returns the rows in descending order of addressid.

NOTE:
If you want to use this approach, it is highly dependant on being able to find a query plan that produces the ordering that you want. This may mean creating a suitable index designed strategically for this purpose, if one doesn’t exist already. Even creating indexes won’t guarantee that you’ll be able to find a plan that gets the order that you want; generally I’d say this approach will only be feasible for relatively simple queries.

AUSOUG Conference 2009 Day One
Ten Years on Oracle

Comments

  1. Hi Gary!Very good question – my first reaction was that it's "obvious" but these things have a habit of not being so obvious in practice.I tried it out and found that in fact, using the INDEX_DESC hint causes the index to be traversed from its "far" end, which for a DESCending index means the values come back in ascending order!My test case:DROP TABLE t1;CREATE TABLE t1 (id NUMBER);INSERT INTO t1 (id)SELECT ROWNUMFROM all_objectsWHERE ROWNUM <= 100ORDER BY dbms_random.value;SELECT id FROM t1;–Expected: random orderCREATE INDEX xdescON t1 (id DESC);BEGIN dbms_stats.gather_table_stats(user,'T1',cascade=>TRUE);END;SELECT /*+INDEX_DESC(t1)*/ idFROM t1WHERE SYS_OP_DESCEND(id)IS NOT NULL;–Expected: ascending orderSELECT /*+INDEX_ASC(t1)*/ idFROM t1WHERE SYS_OP_DESCEND(id)IS NOT NULL;–Expected: descending order

  2. Note: A simpler test case would have had a NOT NULL constraint on t1.id – which would mean the queries don't have to check if the id is null, and the calls to SYS_OP_DESCEND are not required.

  3. Hi,>Is it possible to change the ORDER BY on a query, if the SQL is defined in a wrapped PL/SQL package?Easily in >=10g using dbms_advanced_rewrite

Leave a Reply

Your email address will not be published / Required fields are marked *