댓글 쓰기 권한이 없습니다. 로그인 하시겠습니까?
PRACTICE
2012.02.12 01:41
WMSYS.WM_CONCAT vs ListAgg
댓글 1조회 수 30708
WMSYS.WM_CONCAT vs ListAgg
출처 : https://forums.oracle.com/forums/thread.jspa?threadID=1088158
difference1
wmsys.wm_concat allows distinct option.
ListAgg does not allows it.
create table diffT(sortKey,Val) as
select 1,'aa' from dual union all
select 2,'bb' from dual union all
select 3,'aa' from dual union all
select 4,'dd' from dual;
col concatV for a20
select wmsys.wm_concat(distinct Val) as concatV from diffT;
concatV
--------
aa,bb,dd
*************************************************************************
difference2
ListAgg allows to decide string concat order.
wmsys.wm_concat does not allows it.
select ListAgg(Val,',')
within group(order by sortKey desc) as concatV
from diffT;
CONCATV
------------
dd,aa,bb,aa
*************************************************************************
difference3
ListAgg allows to decide delimiter.
wmsys.wm_concat does not allows it.
select ListAgg(Val,'***')
within group(order by sortKey desc) as concatV
from diffT;
CONCATV
-----------------
dd***aa***bb***aa
*************************************************************************
difference4
wmsys.wm_concat allows to be used OLAP function with order by
ListAgg does not allows it.
ListAgg allows only OLAP function without order by.
select sortKey,wmsys.wm_concat(Val)
over(order by sortKey) as concatV
from diffT;
SORTKEY CONCATV
------- -----------
1 aa
2 aa,bb
3 aa,bb,aa
4 aa,bb,aa,dd
*************************************************************************
difference5
wmsys.wm_concat allows to be used KEEP
ListAgg does not allows it.
select wmsys.wm_concat(Val)
Keep(Dense_Rank First order by Val) as concatV
from diffT;
CONCATV
-------
aa,aa
| 번호 | 분류 | 제목 | 글쓴이 | 날짜 |
|---|---|---|---|---|
| 9 | PRACTICE |
SQL*Plus에서 &(Ampersand)/특수문자/Escape 사용법
 |
김형조 | 2013.09.27 |
| 8 | FUNCTIONS | 함수목록 | 김형조 | 2012.02.21 |
| 7 | DBA_ | DBA HIST Views - 11.2.0.1 | 김형조 | 2012.02.16 |
| 6 | PRACTICE | Not Exists 를 이용한 차집합 데이터 Insert | 관리자 | 2012.02.12 |
| 5 | PRACTICE | 열->행 변환 사례 3 | 관리자 | 2012.02.12 |
| » | PRACTICE | WMSYS.WM_CONCAT vs ListAgg [1] | 관리자 | 2012.02.12 |
| 3 | PRACTICE | 오라클 여러 행을 1개의 문자열로 SELECT | 관리자 | 2012.02.12 |
| 2 | PRACTICE | 행 -> 열 변환 사례 2 | 관리자 | 2012.02.12 |
| 1 | PRACTICE | 열->행 변환 사례 1 | 관리자 | 2012.02.12 |
Copyright 2003-2012. All rights reserved. @XE
-- 현재 설정된 parameter 리트스
-- ----------------------------------------------
-- --------------------------
-- Oracle Workspace manager
-- --------------------------
-- [checking installed module]
-- select comp_id, version, status from dba_registry where comp_id='OWM'
-- select * from dba_users where username='WMSYS'
-- select * from dba_objects where object_name='DBMS_WM'
-- select * from dba_objects where object_name='WM_CONCAT'
select x.name,x.isdefault,x.value,x.display_value,
x.isses_modifiable,x.issys_modifiable,x.isinstance_modifiable,
x.ismodified,
y.available_values,
x.description
from v$parameter x,
(select name,wmsys.wm_concat(value) as available_values
from v$parameter_valid_values
where rownum >=1
group by name) y
where x.name=y.name(+)
and x.name='optimizer_features_enable'
order by x.name
-- --------------------------
-- 11g New features - ListAgg
-- --------------------------
select x.name,x.isdefault,x.value,x.display_value,
x.isses_modifiable,x.issys_modifiable,x.isinstance_modifiable,
x.ismodified,
x.description,
y.available_values
from v$parameter x,
(select name,listagg(value,', ')
within group (order by value desc) as available_values
from v$parameter_valid_values
where rownum>=1
group by name) y
where x.name=y.name(+)
-- and x.name='optimizer_features_enable'
order by x.name