Scramble ORDER BY? Use subquerys and computed expressions

A friend of mine sent me an email with some curious things regarding ORDER BY and a subquery. Basically this:

create table users (id int, name varchar(50), pass varchar(50));
insert into users (id, name, pass) values (1, 'hugo', 'pass123');
insert into users (id, name, pass) values (2, 'martin', 'pass345');
insert into users (id, name, pass) values (3, 'jony', 'pass678');

Let’s apply some querys on this table with ORDER BY clause:

SQL> SELECT id, name, pass FROM users ORDER BY 1;
ID NAME PASS
---------- ---------- ----------
1 hugo pass123
2 martin pass345
3 jony pass678

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 201 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 3 | 201 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| USERS | 3 | 201 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------

Nothing new, FTS on table and then ORDER BY. Now let’s try the following:

SQL> SELECT id, name, pass FROM users 
 ORDER BY (SELECT (CASE WHEN ( 1 = 1 ) THEN 1 
ELSE 1/(SELECT 0 FROM DUAL) END) FROM DUAL);

ID NAME PASS
---------- ---------- ----------
 1 hugo pass123
 3 jony pass678
 2 martin pass345

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 201 | 4 (25)| 00:00:01 |
| 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 2 | SORT ORDER BY | | 3 | 201 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| USERS | 3 | 201 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------

As you can see the result is different and no criteria (?) is defined for ORDER BY clause, since the evaluation of FAST DUAL (subquery) is made after the ORDER BY according to the explain plan. If you are expecting the same result as the previous query you are wrong. Another variation of this behaviour is simply archived by:

SELECT id, name, pass FROM users ORDER BY (SELECT 1 FROM DUAL);
ID NAME PASS 
---------- ---------- ----------
1 hugo pass123
3 jony pass678
2 martin pass345

Same result, as exactly the same explain plan. DUAL is evaluated after the ORDER BY clause. That might suggest that is DUAL table making some tricks. Let’s try another thing:

create table i_have_one (id int);
insert into i_have_one (id) values (1);

SQL> select id, name, pass from users ORDER by (select id from i_have_one);

ID NAME PASS
---------- ---------- ---------- 
1 hugo pass123 
3 jony pass678 
2 martin pass345

-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 201 | 4 (25)| 00:00:01
| 1 | TABLE ACCESS FULL | I_HAVE_ONE | 1 | 13 | 3 (0)| 00:00:01
| 2 | SORT ORDER BY | | 3 | 201 | 4 (25)| 00:00:01
| 3 | TABLE ACCESS FULL| USERS | 3 | 201 | 3 (0)| 00:00:01|
|-------------------------------------------------------------------

Again, every thing you throw in a subquery after ORDER BY will be evaluated after the execution of that clause, preventing any expected result.
Enough of subquerys, let’s try computed expressions (not functions):

select id, name, pass from users ORDER by 2-1;

ID NAME PASS
---------- ---------- ----------
1 hugo pass123
2 martin pass345
3 jony pass678

Execution Plan
Plan hash value: 3461732445
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 201 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| USERS | 3 | 201 | 3 (0)| 00:00:01
|---------------------------------------------------------------------------

On this particular case, ORDER BY is not even considered by CBO (same as ORDER BY NULL) as you can see in explain plan. You can try more combinations and if you have free time try to under your hat.

Have a nice weekend 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s