In this topic we compare the two methods for distributing row values across columns left to right discussed earlier, PIVOT and MAX DECODE, with two other methods sometimes used, LEAD and MODEL. At the end of this topic a Performance Comparison of all techniques is given.
Test Data
The following test data will be used in each solution presented.
desc t6 Name Null? Type ---------------------------------------------- -------- ------------------------------- GROUP_KEY VARCHAR2(10) VAL VARCHAR2(10) break on group_key duplicates skip 1 select * from t6 where group_key in ( 'G0','G1','G2') order by group_key, val ;
GROUP_KEY VAL ---------- ---------- G0 a0 G0 a1 G0 a2 G0 a3 G0 a4 G1 a0 G1 a1 G1 a2 G1 a3 G1 a4 G2 a0 G2 a1 G2 a2 G2 a3 G2 a4
column number format 999,999,999 select count(*) as total_rows , count( distinct group_key ) as distinct_group_keys , 5 as rows_per_group_key from t6 ;
TOTAL_ROWS DISTINCT_GROUP_KEYS ROWS_PER_GROUP_KEY
---------- ------------------- ------------------
100000 20000 5
Each test will return a result set that looks like this.
GROUP_KEY COL_0 COL_1 COL_2 ---------- ---------- ---------- ---------- G0 a0 a1 a2 G0 a3 a4 G1 a0 a1 a2 G1 a3 a4 G2 a0 a1 a2 G2 a3 a4 ...
PIVOT
First we test the PIVOT solution presented in Distributing Left to Right .
PIVOT:
select
group_key ,
col_0 ,
col_1 ,
col_2
from
( select
group_key,
mod( row_number() over ( partition by group_key order by val ) - 1, 3 )
as pivot_key ,
trunc
( ( row_number() over ( partition by group_key order by val ) - 1 ) / 3 )
as row_num ,
val
from t6
) t6a pivot
( max(val)
for pivot_key in
( 0 as col_0 ,
1 as col_1 ,
2 as col_2
)
)
order by group_key, row_num ;
Test Complete
GROUP_KEY COL_0 COL_1 COL_2 ---------- ---------- ---------- ---------- G0 a0 a1 a2 G0 a3 a4 G1 a0 a1 a2 G1 a3 a4 G2 a0 a1 a2 G2 a3 a4 ...
MAX DECODE
Next we test the MAX DECODE solution presented in Distributing Left to Right .
MAX DECODE:
select
group_key ,
max( decode( pivot_key, 0, val, null ) ) col_0 ,
max( decode( pivot_key, 1, val, null ) ) col_1 ,
max( decode( pivot_key, 2, val, null ) ) col_2
from
( select
group_key,
mod( row_number() over ( partition by group_key order by val ) - 1, 3 )
as pivot_key ,
trunc
( ( row_number() over ( partition by group_key order by val ) - 1 ) / 3 )
as row_num ,
val
from t6
) t6a
group by group_key, row_num
order by group_key, row_num ;
Test Complete
GROUP_KEY COL_0 COL_1 COL_2 ---------- ---------- ---------- ---------- G0 a0 a1 a2 G0 a3 a4 G1 a0 a1 a2 G1 a3 a4 G2 a0 a1 a2 G2 a3 a4 ...
LEAD Solution
This method uses the LEAD function.
LEAD:
select group_key, col_0, col_1, col_2
from
( select
group_key ,
pivot_key ,
val as col_0 ,
lead( val, 1 )
over( partition by group_key, row_num order by val )
as col_1,
lead( val, 2 )
over( partition by group_key, row_num order by val )
as col_2
from
( select
group_key,
trunc
( ( row_number() over ( partition by group_key order by val ) - 1 ) / 3 )
as row_num ,
mod( row_number() over ( partition by group_key order by val ) - 1, 3 )
as pivot_key ,
val
from t6
) t6a
) t6b
where pivot_key = 0
order by group_key ;
Test Complete
GROUP_KEY COL_0 COL_1 COL_2 ---------- ---------- ---------- ---------- G0 a0 a1 a2 G0 a3 a4 G1 a0 a1 a2 G1 a3 a4 G2 a0 a1 a2 G2 a3 a4 ...
MODEL Solution
This method uses the MODEL clause of the SELECT command (see SQL Features Tutorials: MODEL Clause if you are unfamiliar with MODEL).
MODEL:
select group_key, col_0, col_1, col_2
from t6
model
return updated rows
partition by ( group_key )
dimension by
(
trunc
( ( row_number() over ( partition by group_key order by val ) - 1 ) / 3 )
as row_num ,
mod( row_number() over ( partition by group_key order by val ) - 1, 3 )
as pivot_key
)
measures( val as col_0, val as col_1, val as col_2 )
rules upsert
(
col_1[any,0] = col_0[cv(),1] ,
col_2[any,0] = col_0[cv(),2]
)
order by group_key, row_num ;
Test Complete
GROUP_KEY COL_0 COL_1 COL_2 ---------- ---------- ---------- ---------- G0 a0 a1 a2 G0 a3 a4 G1 a0 a1 a2 G1 a3 a4 G2 a0 a1 a2 G2 a3 a4 ...
Performance Comparison
The following table shows database statistics where values for one method differ by more than 100 from another method.
Metric PIVOT MAX DECODE LEAD MODEL
-------------------------- --------- ---------- --------- ---------
Elapsed Time (1/100 sec) 369 348 350 353
Latch Gets Total 6,746 6,602 7,158 6,866
session pga memory max 7,471,104 7,405,568 7,405,568 7,405,568
table scan rows gotten 123,928 100,000 100,000 100,000
session pga memory -196,608 -196,608 -262,144 -262,144
See Statistics Descriptions and Latches and Internal Locks for metric descriptions.
Note: The tests were performed using SQL*Plus against an Oracle 11g Enterprise Edition Release 11.1.0.6.0 database running on Oracle Enterprise Linux 5. The tests were performed on a single CPU machine. On multi-CPU machines the MODEL solution may perform better than reported here.