列转行函数listagg

一个开发同事遇到一条慢SQL，大概需要1分钟执行完毕，SQL目的是通过wmsys.wm_concat实现列转行。这条SQL如果把wmsys.wm_concat函数去掉，耗时只需3秒钟。可见，性能瓶颈来自于这wmsys.wm_concat函数。通过查找，发现了它的替代函数listagg，且性能好于wmsys.wm_concat。

列转行函数有两种：

• wmsys.wm_concat 这个函数已经在博客中写过（http://blog.chinaunix.net/uid-23284114-id-4463240.html）
  
• listagg (column,[,]) within group (order by ) [over (partition by  )] 。语法如下，具体说明见
  

两者区别：

• wmsys.wm_concat 返回的结果是clob类型，返回默认在列后加上逗号。
  
• listagg函数在11g中推出，返回结果是varchar2(4000)，超过长度ORA-01489: result of string concatenation is too long错误。返回结果不带逗号，需要在SQL中手动添加||','。
  

两者性能：

listagg函数性能好于wmsys.wm_concat 。下面的案例会做性能比较。

案例中，wmsys.wm_concat逻辑读是36，而listagg只有22。

--测试环境11gR2

--ZT_USR_IDCARD 1141行

 

--wmsys.wm_concat

SELECT wmsys.wm_concat(t.status) FROM (SELECT status FROM ZT_USR_IDCARD order by status) t;

 

Elapsed: 00:00:00.02

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1481251159

 

--------------------------------------------------------------------------------------

| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |               |     1 |    12 |     8  (13)| 00:00:01 |

|   1 |  SORT AGGREGATE      |               |     1 |    12 |            |          |

|   2 |   VIEW               |               |  1054 | 12648 |     8  (13)| 00:00:01 |

|   3 |    SORT ORDER BY     |               |  1054 |  2108 |     8  (13)| 00:00:01 |

|   4 |     TABLE ACCESS FULL| ZT_USR_IDCARD |  1054 |  2108 |     7   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

         12  db block gets

         24  consistent gets

          0  physical reads

          0  redo size

       1247  bytes sent via SQL*Net to client

        949  bytes received via SQL*Net from client

          5  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

      

        

--listagg

SELECT listagg(status||',') within group (order by status) FROM ZT_USR_IDCARD;        

 

Elapsed: 00:00:00.01

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2916943040

 

------------------------------------------------------------------------------------

| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |               |     1 |     2 |     7   (0)| 00:00:01 |

|   1 |  SORT GROUP BY     |               |     1 |     2 |            |          |

|   2 |   TABLE ACCESS FULL| ZT_USR_IDCARD |  1054 |  2108 |     7   (0)| 00:00:01 |

------------------------------------------------------------------------------------

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

         22  consistent gets

          0  physical reads

          0  redo size

       2854  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

转载请注明：
十字螺丝钉
http://blog.chinaunix.net/uid/23284114.html
QQ:463725310
E-MAIL:houora#gmail.com(#请自行替换为@)