I've moved...

This blog now has a new home - please update your shortcuts and readers to: www.jeffkemponoracle.com. Sorry for any inconvenience!

Wednesday, December 23, 2009

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.

[Full Article]

4 comments:

  1. What is the effect of making the index descending ?
    http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_510a.htm#2071531

    ReplyDelete
  2. 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 ROWNUM
    FROM all_objects
    WHERE ROWNUM <= 100
    ORDER BY dbms_random.value;
    SELECT id FROM t1;
    --Expected: random order
    CREATE INDEX xdesc
    ON t1 (id DESC);
    BEGIN dbms_stats.gather_table_stats
    (user,'T1',cascade=>TRUE);
    END;
    SELECT /*+INDEX_DESC(t1)*/ id
    FROM t1
    WHERE SYS_OP_DESCEND(id)
    IS NOT NULL;
    --Expected: ascending order
    SELECT /*+INDEX_ASC(t1)*/ id
    FROM t1
    WHERE SYS_OP_DESCEND(id)
    IS NOT NULL;
    --Expected: descending order

    ReplyDelete
  3. 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.

    ReplyDelete
  4. 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

    ReplyDelete

Note: only a member of this blog may post a comment.