一个分组显示的sql语句(二)
QUE | | 2 | 6 | 4 (25)| 00:00:
01 |
| 26 | TABLE ACCESS FULL | TEST2 | 5 | 15 | 3 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=:B1 AND "RN1" IS NULL)
3 - filter("ID"=:B1 AND "RN1" IS NULL)
10 - access("RN1"=NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
56 consistent gets
0 physical reads
0 redo size
541 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
21 sorts (memory) www.2cto.com
0 sorts (disk)
2 rows processed
第二种方法：利用wmsys.wm_concat
select id,wmsys.wm_concat(name) name from test2 group by id;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3598114683
www.2cto.com
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 20 | 4 (25)| 00:00:01 |
| 1 | SORT GROUP BY | | 2 | 20 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TEST2 | 5 | 50 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
535 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory) www.2cto.com
0 sorts (disk)
2 rows processed
这种方法更好理解：
单用wmsys.wm_concat是这样显示的
SQL> select wmsys.wm_concat(name) name from test2;
NAME
----------------------------------------
stefanie,google,sina,baidu,microsoft
再加上分组group by就得到了需要的结果，而且7个逻辑读比上面的那个56个逻辑读的效率也要更高。
作者 paololiu