PRACTICE
2012.02.12 01:41

WMSYS.WM_CONCAT vs ListAgg

댓글 1조회 수 26444
?

단축키

이전 문서

다음 문서

+ - Up Down Comment Print
?

단축키

이전 문서

다음 문서

+ - Up Down Comment Print

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

  • 김형조 2012.02.16 01:18
    -- ----------------------------------------------
    -- 현재 설정된 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

번호 분류 제목 글쓴이 날짜
9 PRACTICE 행 -> 열 변환 사례 2   관리자 2012.02.12
8 FUNCTIONS 함수목록   김형조 2012.02.21
7 PRACTICE 오라클 여러 행을 1개의 문자열로 SELECT   관리자 2012.02.12
6 PRACTICE 열->행 변환 사례 3   관리자 2012.02.12
5 PRACTICE 열->행 변환 사례 1   관리자 2012.02.12
» PRACTICE WMSYS.WM_CONCAT vs ListAgg [1]   관리자 2012.02.12
3 PRACTICE SQL*Plus에서 &(Ampersand)/특수문자/Escape 사용법  secret 김형조 2013.09.27
2 PRACTICE Not Exists 를 이용한 차집합 데이터 Insert   관리자 2012.02.12
1 DBA_ DBA HIST Views - 11.2.0.1   김형조 2012.02.16
Board Pagination ‹ Prev 1 Next ›
/ 1
Designed by hikaru100

나눔글꼴 설치 안내


이 PC에는 나눔글꼴이 설치되어 있지 않습니다.

이 사이트를 나눔글꼴로 보기 위해서는
나눔글꼴을 설치해야 합니다.

설치 취소

SketchBook5,스케치북5

SketchBook5,스케치북5

SketchBook5,스케치북5

SketchBook5,스케치북5

O R A C L E L A B 로그인 :)