Oracle 11g R2의 신기능(SQL)
세 가지 테마로 거듭난 Oracle 11g R2의 신기능(SQL)
Oracle 11g가 발표된 지 약 2년만인 지난 9월, 공식적으로 Oracle 11g Release 2(이하 R2)가 발표되었다. 이번 릴리즈의 테마는 Consolidate(통합), Compress(압축), Control(컨트롤)로, 이 단어에 이번 릴리즈의 모든 기능들이 함축적으로 반영되어 있다. 필자가 일단 11g R2를 설치하고 new feature 문서를 살펴본 결과, 위의 테마에 담긴 뜻이 하나씩 떠오르기 시작했다.
지금부터 필자가 이해한 의미를 바탕으로 11g R2의 기능을 설명한다.
11g R2에서는 지난해 ‘오라클 오픈 월드(Oracle Open World)’에서 발표했던 DW전용 머신인 Exadata Storage 서버에 대한 지원을 강화하기 위해 Advaned Compress를 비롯한 DW 기능이 강화됐고 클라우드 컴퓨팅 지원 기능과 자가 데이터베이스 튜닝 및 관리 기능이 강화되었다.
11g R2의 발표
이렇듯 오라클 데이터베이스의 신규 릴리즈(Release) 발표는 여타 DBMS의 제품 업그레이드 버전 수준과는 사뭇 다른 상징적인 의미를 가진다. R2의 발표는 현업 IT 담당자들에게는 과거 릴리즈 버전 때부터 이어져온 신규 버전을 적용해도 되는 안정된 시점이라는(terminal 버전) 의미로서 받아들여지고 있다.
뿐만 아니라 마치 버전 업그레이드와 같이 오랜 시간 동안 신규 기능을 추가하고 안정화 단계를 거치기 때문에 마치 신규 버전과 같은 많은 변화와 기능적인 특징을 나타내기도 한다.
심지어는 R2용의 New Feature 가이드만으로도 마치 새로운 버전이 발표된 것과 같은 기능적인 특색으로 가득 차 있다. 이에 따라 현재 몇몇 소수 사이트에서만 도입된 11g를 제외하고 안정성을 이유로 현재 10g를 주로 도입해 사용하고 있는 사이트에서 R2가 발표되고 난 이후 시점부터는 안정성이 검증된 11g로 버전 업그레이드를 수행하는 사례가 증가할 것으로 예상된다. 이 글에서는 New Feature의 중요 변화 중에서 우선 SQL에 관련된 부분만을 골라 설명하기로 한다.
중복키 무시 힌트(IGNORE_ROW_ON_DUPKEY_INDEX)
‘INSERT INTO 테이블A SELECT .. FROM 테이블B’로 데이터 입력 시에 기존 데이터와 UNIQUE 키에 위배된 중복된 로우가 발생할 시에는 작업 전체가 중복키 에러를 발생시키면서 실패한다.
따라서 대량의 배치로 INSERT 시에는 이러한 UNIQUE 에러가 발생하는 것을 막기 위해 로우 단위 패치를 통하거나 중복 체크를 로직에서 수행한 후에 작업했다. 이번에 신규로 추가되는 IGNORE_ROW_ON_DUPKEY_INDEX 힌트를 사용한다면 키가 중복될 경우 에러가 발생되지 않고 KEY에 중복되지 않은 로우만을 입력할 수 있다.
INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(A, PK_EMP) */ INTO EMP A SELECT * FROM EMP_T;
기존 10g에서부터 지원하는 ERROR LOGGING 기능을 이용해 처리했던 부분이 이 힌트 사용으로 부분 대체되는 역할을 수행한다. 다만 SINGLE INSERT 문에만 적용되고 UPDATE, DELETE, MERGE, MULTI INSERT 구문에서는 지원하지 않는다는 것과 APPEND, PARALLEL 힌트를 함께 사용하면 이 2개의 힌트가 무시되어 <화면 1>의 실행계획에서 나타나듯이 처리 속도가 빠른 DIRECT PATH LOAD(버퍼를 경유하지 않은 처리)가 아닌 CONVENTIONAL 모드만 지원하는 것이 아쉬운 점 가운데 하나이다.
업그레이드된 Analytic Function 2.0
Oracle 8i 버전부터 지원하기 시작한 데이터 분석용의 Analytic Function 군(MOVING AGGREGATE, RANKING, LEAD/LAG COMPARISION 등 비즈니스 분야에서 자주 행해지는 여러 가지 형태의 분석에 유용하게 활용될 수 있는 SQL Function)은 종래의 많은 라인의 쿼리를 줄여주고 이를 응용해 다양한 형태의 분석용 데이터를 추출해 낼 수 있는 개발 단계에서는 없어서는 안 될 중요한 함수들로 자리 잡았다. 이번 R2에서 새롭게 추가되거나 업그레이드된 함수와 옵션들이 눈에 띈다. 이 업그레이드된 Analytic Function 2.0들은 다음과 같다.
LISTAGG
함수의 파라미터로 지정한 컬럼과 구분자로 각각의 그룹핑된 컬럼을 order by 절에 지정된 순으로 하나의 값으로 합쳐준다. 함수 이름 그대로 LIST를 AGGREGATE 해주는 함수이다.
[표현식]
LISTAGG(컬럼, 구분값) WITHIN GROUP( ORDER BY 절)
[예]
SELECT DEPTNO, LISTAGG(ename, ‘; ‘) WITHIN GROUP (ORDER BY hiredate, ename) “Emp_list”, MIN(hiredate) “Earliest” FROM emp GROUP BY DEPTNO;
예전에 로우 단위의 데이터를 가로로 나열하기 위해 decode 함수를 사용해서 열로 가공하거나 또는 Function을 생성해서 해당 컬럼으로 호출하는 방법 등을 통해 pivot 쿼리 형태로 힘들게 구현해 봤던 유저라면 위의 함수의 등장이 반가울 것이다. 각 그룹핑되는 로우 수가 동적인 경우에 사용하는 recursive SQL의 sys_connect_by_path() 함수를 이용하는 방법보다는 훨씬 더 간단해졌다.
NTH_VALUE
NTH_VALUE 함수는 함수의 적용 그룹과 순서를 정하는 ANALYTIC 절에 기술된 windowing 범위 중에서 함수에 기술한 값을 기준으로 N 번째의 로우 값을 리턴한다. 다음의 예를 살펴보자.
SELECT DEPTNO, EMPNO, MIN(sal), NTH_VALUE(MIN(sal), 2) OVER (PARTITION BY deptno ORDER BY empno ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) NV FROM EMP GROUP BY DEPTNO, EMPNO;
IGNORE NULLS 옵션 LAG, LEAD 함수 지원
설명하기 전에 먼저 아래의 쿼리를 살펴보자. 이는 해당 값 중 LAST_VALUE 함수를 사용한 windowing 범위 내에서 가장 큰 값을 가져오는 SQL 문이다.
SELECT DEPTNO, ENAME, SAL, LAST_VALUE(ENAME) OVER(PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM EMP;
위의 쿼리를 수행하면 부서에서 가장 큰 SAL을 받는 마지막 사원명을 CURRENT 로우에서 가져올 수 있다. 다만 다음 결과에서 10번 부서에서 내가 의도했던 바와 다르게 SALARY가 NULL인 KING을 가져왔다. 당연하겠지만 DB에서 NULL이 가장 큰 값으로 인식하기 때문에 이를 가공해주기 위해 NVL로 치환해주거나 ORDER 절 위에 NULLS FIRST와 NULLS LAST라는 옵션 값으로 NULL 값의 정렬순서를 인위적으로 가공해야 했다.
10g부터는 IGNORE NULLS라는 옵션을 함수 내에 LAST_VALUE(COLUMN IGNORE NULLS) 형식과 같이 옵션 절을 지정해 NULL인 로우를 제외하고 가져올 수 있기 때문에 유용한 옵션으로 사용될 수 있다.
11g R2부터는 이러한 옵션 절의 위치가 기존 방식에다가 아래와 같은 형식으로 표현이 추가되었다. 새로 바뀐 아래 방식이 DB2나 기타 벤더에서 지원하는 ANSI 표현식이기도 하다. ANSI 표준에 맞지 않는 함수의 파라미터 안에 입력하는 방식은 하위 버전 호환을 위해 사용 가능하지만 더 이상 기술적인 업그레이드는 없을 예정이다.
FIRST_VALUE(expr) IGNORE NULLS OVER (analytic clause)
11g R2부터는 위의 함수뿐만 아니라 현재 CURRENT ROW 기준으로 특정 offset 위치의 로우를 가져올 수 있는 LEAD(), LAG() 함수에도 적용할 수 있다.
SELECT DEPTNO, ENAME, SAL, LAG(SAL) IGNORE NULLS OVER(PARTITION BY DEPTNO ORDER BY SAL) LEAD_VAL FROM EMP; ;
새롭게 추가된 Analytic Function과 옵션들을 잘 사용한다면 복잡한 형태의 쿼리를 더욱 간단하게 해주고 성능 또한 향상시켜주는 역할을 수행할 것이다.
RECURSIVE WITH 절
필자가 마소 3월호에 기고했던 ‘DBMS 3종 세트’라는 글에서 각각의 RDBMS 벤더들이 서로의 기능들을 모방하거나 비슷한 방향으로 흘러갈 것이라고 얘기한 바 있다. SQL 문도 Oracle 9i에서 ANSI 문법을 지원하게 되면서 특수 기능 중에서 ANSI 문법을 따르지 않는 몇 개의 SQL 문 형태도 향후에는 ANSI를 지원하게 될 것이라고 예상했었다.
이번 버전에서는 다른 RDBMS와는 다른 형식으로 표현되는 오라클의 계층형 구조를 표현하는 RECURSIVE SQL을 ANSI 형태로 바꿀 수 있다. WITH 절을 이용한 SUBQUERY FACTORING 절을 이용해 QUERY BLOCK 2개를 참조하는 SQL 문을 지원한다. 이 부분은 MS SQL Server나 DB2와 구현 원리가 거의 동일하다. <리스트 1>의 구현 SQL 문의 예를 살펴보자.
그 밖의 중요변경 사항
● FLASHBACK을 이용한 DDL 복구
다음의 추가적인 DDL 문을 FLASHBACK DATA ARCHI VE를 이용해 복구할 수 있다.
- 컬럼에 대한 ADD, DROP, RENAME
- 파티션 DROP, TRUNCATE
- 테이블 TRUNCATE , RENAME
- 제약조건 ADD, DROP, RENAME, MODIFY
● IN-MEMROY PARALLEL 실행
병렬 실행 시에 BUFFER CACHE를 이용할지 안 할지를 오라클이 판단.
● 기존보다 업그레이드된 Advanced TABLE COMPRESSION 기술
● 병렬 쿼리시에 자동 DOP(Degree of parallelism)
● Edition-base Redefinition
별도의 업그레이드 환경 설정 없이 데이터베이스 애플리케이션을 온라인에서 업그레이드할 수 있음.
● Exadata Storage Server를 위한 Hybrid Columnar Compression 기술
DW 기능의 강화
지금까지 11g R2의 대략적인 SQL에 대한 변화와 중요변경 사항을 살펴봤다. 필자가 느끼는 이번 릴리즈의 특징은 대용량 데이터, 즉 DW용의 기능들이 상당히 강화되었다는 데 있다. 이는 작년에 발표한 DW용 하드웨어 결합 제품인 Exadata Storage Server와 이 글을 쓰는 동안 발표된 썬과의 합병 이후에 나온 하드웨어 결합 제품인 Exadata Database Machine Version 2와도 무관하지 않으리라 생각된다. 데이터 사이즈가 하드웨어 발전 속도를 앞서서 폭발적으로 증가하고 있는 상황에서 이러한 대용량 데이터베이스 지원 기술은 앞으로도 더욱 증가하리라 생각된다.
출처 : http://m.cafe.daum.net/bestheli/hVLb/51?q=D_XACT.v2tFDE0&