[Oracle]Sys_Connect_By_Path를 대신할 XMLAGG(XMLELEMENT)

출처: 웅의 Oracle 정리노트

Sys_Connect_By_Path를 대신할 XMLAGG(XMLELEMENT)

 | Oracle Tip 2009/07/27 09:55

Posted by 몸과 정신에 대한 혁신 Drilldown

xmlagg는 sys_connect_by_path와 같이 여러 row의 특정 컬럼의 값 한 row로 바꿔주는 xml함수이다.
xml함수가 처리할 수 있는 DataType은 xml DataType이어야 하고 그래서 일반적으로 쓰이는 string, number DataType을 일단 xml DataType으로 변환해 주어야 한다.

create table testt(col1 varchar2(10), col2 number);
insert into testt values('빨강',10);
insert into testt values('파랑',20);
insert into testt values('노랑',30);
commit;

1. XMLELEMENT함수 : char, varchar, number DatavType을 xml DatavType으로 바꿔준다.
 - 첫번째 파라미터(필수) : xml element tag의 명칭을 지정한다. 컬럼명이나 쌍따옴표로 묶은 리터럴문자가 올 수 있다. 컬럼명이 온다고 컬럼의 데이터가 표시되는 것은 아니다. 리터럴문자처럼 처리된다. Null은 올 수 없다.

WOONGASM:WOONG >
  1  select xmlelement("Color",col1) from testt;

XMLELEMENT("COLOR",COL1)
--------------------------------------------------
<Color>빨강</Color>
<Color>파랑</Color>
<Color>노랑</Color>

WOONGASM:WOONG >
  1  select xmlelement("number",col2) from testt;

XMLELEMENT("NUMBER",COL2)
--------------------------------------------------
<number>10</number>
<number>20</number>
<number>30</number>

 - 두번째 파라미터(옵션) : xml element의 컬럼을 지정한다.(상수표현 및 서브쿼리도 가능)

WOONGASM:WOONG >
  1  select xmlelement("cons",'상수') from testt;

XMLELEMENT("CONS",'상수')
-------------------------------------------------
<cons>상수</cons>
<cons>상수</cons>
<cons>상수</cons>

WOONGASM:WOONG >
  1  select xmlelement("Rank",(select count(*) from testt where a.col2 <= col2) ) from testt a;

XMLELEMENT("RANK",(SELECTCOUNT(*)FROMTESTTWHEREA.COL2<=COL2))
-----------------------------------------------------------------------------------------------
<Rank>3</Rank>
<Rank>2</Rank>
<Rank>1</Rank>

 - 세번째 파라미터 이후(옵션) : 세번째 파라미터 이후는 concat와 같은 역할을 한다(여러개의 컬럼 또는 상수를 붙여쓸 수 있다.)

WOONGASM:WOONG >
  1  select xmlelement("cons",col1, ' concat ',col2) from testt;

XMLELEMENT("CONS",COL1,'CONCAT',COL2)
----------------------------------------------------------------
<cons>빨강 concat 10</cons>
<cons>파랑 concat 20</cons>
<cons>노랑 concat 30</cons>

WOONGASM:WOONG >
  1  select xmlelement("cons",'상수1 ','상수2 ','상수3 ') from testt;

XMLELEMENT("CONS",'상수1','상수2','상수3')
---------------------------------------------------------------------
<cons>상수1 상수2 상수3 </cons>
<cons>상수1 상수2 상수3 </cons>
<cons>상수1 상수2 상수3 </cons>

2. XMLAGG 함수 : 여러 줄로 표시된 xml element를  한 줄로 합쳐주는 xml함수이다.
 - XML ELEMENT의 순서를 정하기 위하여 order by 정렬기준컬럼( 또는 식)을 이용할 수 있고 생략 시 rowid순으로 표시된다.

WOONGASM:WOONG >
  1  select xmlagg(xmlelement("Color", col1) order by col2) from testt;

XMLAGG(XMLELEMENT("COLOR",COL1)ORDERBYCOL2)
------------------------------------------------------------------------
<Color>빨강</Color><Color>파랑</Color><Color>노랑</Color>

WOONGASM:WOONG >
  1  select xmlagg(xmlelement("Color",col1) order by rowid) from testt;

XMLAGG(XMLELEMENT("COLOR",COL1)ORDERBYROWID)
------------------------------------------------------------------------
<Color>빨강</Color><Color>파랑</Color><Color>노랑</Color>

WOONGASM:WOONG >
  1  select xmlagg(xmlelement("Color",col1) ) from testt;

XMLAGG(XMLELEMENT("COLOR",COL1))
----------------------------------------------------------
<Color>빨강</Color><Color>파랑</Color><Color>노랑</Color>

 - XMLAGG는 xml element노드에서 텍스트를 추출할 수 있는 method Extract를 가지고 있는데 추출을 위한 인자로 Root로터 거리를 인자로 받는다.
   ( extract('//text()')의 의미는 첫번째 슬레시는 루트를 두번째 각 슬레시는 그 다음 단계의 element노드를 의미하고 그 단계의 text를 추출함을 지정한다. )

WOONGASM:WOONG >
  1  select xmlagg(xmlelement("Color",col1) order by col2).extract('//text()')
  2  , dump(xmlagg(xmlelement("Color",col1) order by col2).extract('//text()')) from testt;

XMLAGG(XMLELEMENT("COLOR",COL1)ORDERBYCOL2).EXTRACT('//TEXT()')
--------------------------------------------------------------------------------------------------------------
DUMP(XMLAGG(XMLELEMENT("COLOR",COL1)ORDERBYCOL2).EXTRACT('//TEXT()'))
--------------------------------------------------------------------------------------------------------------
빨강파랑노랑
Typ=58 Len=48: 68,143,185,12,104,139,44,246,168,56,31,246,220,227,34,246,0,0,0,4,0,4,0,0,0,4,0,0,1,0,4,0,148,1
08,92,12,0,0,0,0,68,143,185,12,144,139,44,246

 - 마지막으로 xml DataType을 문자나 숫자형으로 변환하기위한 sub method를 제공하는데 .GetStringVal() or .GetNumberVal()이다. 
   ( 직관적으로 String 또는 Number형으로 가져오라는 뜻임을 알 수 있다. )

WOONGASM:WOONG >
  1  select xmlagg(xmlelement("Color",col1) order by col2).extract('//text()').getstringval()
  2  , dump(xmlagg(xmlelement("Color",col1) order by col2).extract('//text()').getstringval()) from testt;

XMLAGG(XMLELEMENT("COLOR",COL1)ORDERBYCOL2).EXTRACT('//TEXT()').GETSTRINGVAL()
----------------------------------------------------------------------------------------------------------
DUMP(XMLAGG(XMLELEMENT("COLOR",COL1)ORDERBYCOL2).EXTRACT('//TEXT()').GETSTRINGVAL())
----------------------------------------------------------------------------------------------------------
빨강파랑노랑
Typ=1 Len=12: 187,161,176,173,198,196,182,251,179,235,182,251

이전 다수의 row에 존재하는 특정컬럼을 한 row로 합쳐서 보여주기 위해 sys_connect_by_path와 start with ~ connect by를 사용하여 불필요한 row증가와 합침등 복잡한 오퍼레이션을 했왔으나 xml함수를 이용하는 것이 보다 깔끔하고 성능 상 유리한 SQL을 작성할 수 있다는 것을 알 수 있다.

WOONGASM:WOONG > --이전 sys_connect_by_path 사용 시 
  1  select ltrim(max(sys_connect_by_path(col1,',')),',')
  2    from (
  3          select col1
  4               , row_number() over (order by col2) rn
  5            from testt
  6         )
  7  start with rn = 1
  8  connect by prior rn = rn -1
  9  /

LTRIM(MAX(SYS_CONNECT_BY_PATH(COL1,',')),',')
----------------------------------------------------------
빨강,파랑,노랑

WOONGASM:WOONG > 
  1  select * from table(dbms_xplan.display_cursor(null,null,'allstats typical'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M  |
------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE            |       |      2 |00:00:01.68 |      56 |       |       |         |
|*  2 |   CONNECT BY WITH FILTERING|       |      6 |00:00:01.55 |      56 |  9216 |  9216 |  4/0/0  |
|*  3 |    FILTER                  |       |      2 |00:00:00.04 |      14 |       |       |         |
|   4 |     COUNT                  |       |      6 |00:00:00.04 |      14 |       |       |         |
|   5 |      VIEW                  |       |      6 |00:00:00.03 |      14 |       |       |         |
|   6 |       WINDOW SORT          |       |      6 |00:00:00.03 |      14 |  2048 |  2048 |  2/0/0  |
|   7 |        TABLE ACCESS FULL   | TESTT |      6 |00:00:00.01 |      14 |       |       |         |
|*  8 |    HASH JOIN               |       |      4 |00:00:01.49 |      42 |  1066K|  1066K|  2/0/0  |
|   9 |     CONNECT BY PUMP        |       |      6 |00:00:00.01 |       0 |       |       |         |
|  10 |     COUNT                  |       |     18 |00:00:01.18 |      42 |       |       |         |
|  11 |      VIEW                  |       |     18 |00:00:01.18 |      42 |       |       |         |
|  12 |       WINDOW SORT          |       |     18 |00:00:00.02 |      42 |  2048 |  2048 |  2/0/0  |
|  13 |        TABLE ACCESS FULL   | TESTT |     18 |00:00:00.01 |      42 |       |       |         |
|  14 |    COUNT                   |       |      0 |00:00:00.01 |       0 |       |       |         |
|  15 |     VIEW                   |       |      0 |00:00:00.01 |       0 |       |       |         |
|  16 |      WINDOW SORT           |       |      0 |00:00:00.01 |       0 | 73728 | 73728 |         |
|  17 |       TABLE ACCESS FULL    | TESTT |      0 |00:00:00.01 |       0 |       |       |         |
------------------------------------------------------------------------------------------------------

WOONGASM:WOONG > -- XML 함수사용 시
  1  select rtrim(xmlagg(xmlelement("Color",col1,',') order by col2).extract('//text()').getstringval(),',') from testt;

RTRIM(XMLAGG(XMLELEMENT("COLOR",COL1,',')ORDERBYCOL2).EXTRACT('//TEXT()').GETSTRINGVAL(),',')
-----------------------------------------------------------------------------------------------
빨강,파랑,노랑

WOONGASM:WOONG >
  1  select * from table(dbms_xplan.display_cursor(null,null,'allstats  typical'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name  | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
-----------------------------------------------------------------------------------------------
|   1 |  SORT GROUP BY     |       |      3 |00:00:00.72 |      21 | 73728 | 73728 |          |
|   2 |   TABLE ACCESS FULL| TESTT |      9 |00:00:00.04 |      21 |       |       |          |
-------------------------------------------------------------------