Power of Oracle's NVL and Decode function

Suppose I have the following data in a table as shown below. R_ID is related to ID.
A
.....B
.....C
.....D
E
.....F
.....G
.....H
I
.....J
.....K
.....L
.....M

SQL> select * from example order by ID;

ID D R_ID
---------- - ----------
1 A
2 E
3 I
4 B 1
5 C 1
6 D 1
7 F 2
8 G 2
9 H 2
10 J 3
11 K 3
12 L 3
13 M 3

It has been asked to order by both ID and R_ID. The following output is required.


ID D R_ID
---------- - ----------
1 A
4 B 1
5 C 1
6 D 1
2 E
7 F 2
8 G 2
9 H 2
3 I
10 J 3
11 K 3

ID D R_ID
---------- - ----------
12 L 3
13 M 3

There are may ways to achieve this from which two are as follows.

1) Decode function

SQL> select id,details D,decode(r,id,null,r) R_ID
2 from
3 (select id,details,decode(r_id,null,id,r_id) r from example)
4 order by r,id


ID D R_ID
---------- - ----------
1 A
4 B 1
5 C 1
6 D 1
2 E
7 F 2
8 G 2
9 H 2
3 I
10 J 3
11 K 3

ID D R_ID
---------- - ----------
12 L 3
13 M 3

In the inner query null value of R_ID is being replaced by corresponding ID column value. In the outer query reverse is being done. If ID and r are equal then null is assigned.

2) NVL function


SQL> select * from example
2 order by nvl(r_id,id),id


ID D R_ID
---------- - ----------
1 A
4 B 1
5 C 1
6 D 1
2 E
7 F 2
8 G 2
9 H 2
3 I
10 J 3
11 K 3

ID D R_ID
---------- - ----------
12 L 3
13 M 3

This method is very simple. The null values of R_ID are being replaced by ID value and then these are used for ordering purpose.


Second method is efficient than first one as nvl is being called only once while in the first method decode function is being called two times. I found this ordering to be interesting.

References:
1) Decode function
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96540/functions33a.htm#1017439
2) NVL function
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96540/functions88a.htm#91645