事必歸正

ORACLE 

WHERE column LIKE '%'||#{param}||'%'

MS-SQL

WHERE column LIKE '%'+#{param}+'%'

MY-SQL

WHERE column LIKE CONCAT('%',#{param},'%')

위와 같이 DBMS 별로 column에 param의 값이 포함되어 있는 데이터를 조회할 수 있음

사용자 확인, 사용자 추가

▶ 사용자 확인

mysql> use mysql; mysql> select user, host from user;▶ 사용자 추가

로컬접근 허용 mysql> insert into user(host,user,password,ssl_cipher,x509_issuer,x509_subject) -> values ('localhost','사용자명',password('비밀번호'),'','','');

외부접근 허용 mysql> insert into user(host,user,password,ssl_cipher,x509_issuer,x509_subject) -> values ('%','사용자명',password('비밀번호'),'','','');

▶ 사용자 삭제mysql> drop user 사용자명@호스트;▶ 변경사항 적용mysql> flush privileges;

사용자 데이터베이스 권한 부여, 박탈(삭제)

＊grant 문을 이용하면 flush privileges 를 할 필요가 없다.

▶ 모든 권한 주기mysql> grant all privileges on *.* to 사용자명@호스트 -> identified by '비밀번호' with grant option;▶ 특정 사용자에게 특정 DB의 모든 테이블에 모든 권한 부여하기mysql> grant all privileges on DB명.* to 사용자명@호스트 identified by '비밀번호';▶ 특정 사용자에게 특정 DB의 모든 테이블에 select, insert 권한 부여하기mysql> grant select, insert on DB명.* to 사용자명@호스트 -> identified by '비밀번호';▶ 특정 사용자에게 특정 DB의 특정 테이블의 정해준 컬럼에만 update 권한 부여하기mysql> grant update(컬럼1,컬럼2,컬럼3) on DB명.테이블명 to 사용자명@호스트 -> identified by '비밀번호';▶ 모든 호스트에서 접속가능하고 select 권한만 갖는 사용자를 추가할 땐mysql> grant select on DB명.* to 사용자명@% identified by '비밀번호';▶ ip주소가 192.168.0.으로 시작하는 컴퓨터에서 접속가능한 사용자를 추가할 땐mysql> grant all privileges on *.* to 사용자명@'192.168.0.%' identified by '비밀번호';

▶ 모든 권한 삭제mysql> REVOKE ALL PRIVILEGES ON *.* FROM 사용자명@호스트;▶ usage (삭제하면 로그인조차 안됨) 권한 부여, 삭제

usage 권한 부여 mysql> GRANT USAGE ON *.* TO USER_NAME@HOST IDENTIFIED BY 'USER_NAME'; usage 권한 삭제 mysql> REVOKE USAGE ON *.* FROM USER_NAME@HOST;

▶ 권한 확인하기mysql> SHOW GRANTS FOR 사용자명;

출처: https://blog.opid.kr/237 [opid's document]

CUBRID는 2008년 11월 CUBRID 2008 R1.0을 출시한 이후 2014년 5월 CUBRID 9.3을 출시할 때까지 많은 기능을 추가해 왔습니다. 그 결과 CUBRID는 기본적으로 ANSI SQL-92 표준을 따르는 것 외에 계층 질의, MERGE 문, 분석 함수(analytic function) 등을 추가로 지원하게 되었습니다. SQL:2011 표준까지 발표된 지금, CUBRID를 포함한 주요 DBMS에는 어떤 특징적인 SQL 기능이 있으며, CUBRID 9.3은 이 중에서 어떤 기능들을 지원하고 있을까요? 여기서 '특징적인 SQL'이란 일반적인 SELECT, INSERT, UPDATE, DELETE 외에 좀 더 특수해 보이는(사용 방법이 복잡해 보이지만 잘 알고 쓰면 사용자에게 편할 것 같은) 기능을 제공하는 SQL 구문을 이 글에서 임의로 지칭한 표현입니다.

이 글에서는 특정 DBMS에 국한하지 않고 SQL의 특징적인 기능들을 살펴보고, 해당 기능이 SQL의 어떤 표준에 속하는지, 어떤 DBMS가 해당 기능들을 지원하는지 알아보겠습니다. 이 글에서 다루는 DBMS는 Oracle 12c, SQL Server 2008, MySQL 5.6, CUBRID 9.3입니다.

이 글에서 DBMS별로 비교하는 SQL 기능의 대부분은 다음 페이지의 표에서 가져왔습니다.

• DBMS별 비교표: http://www.sql-workbench.net/dbms_comparison.html

표기 규칙

내용을 살펴보기에 앞서 다음 사항을 염두에 두기 바란다.

• 표에서 기능을 지원함을 나타내는 O 표시에 붙은 (*)는 기능의 일부만 지원함을 의미한다.
• 표에서 기능을 지원하지 않음을 나타내는 X 표시에 붙은 (*)는 지원한다고 보기는 애매하지만 대체 기능이 존재하거나 차기 버전에서 지원 예정임을 의미한다.
• SQL 표준이 아니거나 표준 여부를 파악하지 못한 경우 표준에 대해 명시하지 않았다.

질의문

윈도 함수

OVER 절을 사용하는 윈도 함수(window function)는 SQL:2003 표준으로 제정되었고 SQL:2008 표준에서 확장되었다. 윈도 함수는 각 그룹의 누적, 이동, 중앙 집계를 계산하는 함수이며, 각 그룹에 대해 여러 개의 행을 반환한다는 점이 집계 함수(aggregate function)와 다르다.

CUBRID에서는 분석 함수(analytic function)라고 불리는 함수 중 누적, 이동, 중앙 집계를 계산하는 일부 함수가 윈도 함수에 속하는데, CUBRID 9.3 버전에서는 WINDOW 절을 지원하지 않는다. WINDOW 절은 함수의 집계 대상이 되는 범위 기준을 지정하는 기능을 제공하는데, CUBRID 10.0 버전부터 WINDOW 절을 추가로 지원할 예정이다.

다음은 WINDOW 절 없이 윈도 함수를 사용하는 예이다.

SELECT manager_id, last_name, hire_date, salary,
AVG (salary) OVER (PARTITION BY manager_id ORDER BY hire_date) AS c_mavg
FROM employees
WHERE manager_id BETWEEN 100 AND 102
ORDER BY manager_id, hire_date, salary;

위의 질의는 고용 일자(hire_date)순으로 매니저 ID(manager_id)별로 그룹화(grouping)한 직원에 대해, 급여(salary)를 차례대로 누적하면서 평균을 집계한 결과를 보여준다. PARTITION BY 기준인 매니저 ID(manager_id)별로 집계가 되며, AVG(salary)는 위의 행들만을 누적하면서 평균을 구한다. 즉, 현재 행의 평균을 집계할 때 오직 자신과 위의 행들만 포함한다.

예를 들어, Raphaely의 AVG(salary)에는 위의 De Haan의 salary를 자신의 salary와 더한 후 평균을 계산한다. Kaufling의 AVG(salary)에는 위의 De Haan과 Raphaely의 salary를 자신의 salary와 더한 후 평균을 계산한다. 이 예를 통해, 각 매니저별로 직원 고용 일자의 시간 흐름에 따른 직원 급여 평균의 변화 추이를 확인할 수 있다.

MANAGER_ID LAST_NAME HIRE_DATE SALARY C_MAVG ---------- ------------ --------- -------- ---------- 100 De Haan 01/01/13 17000 17000 100 Raphaely 02/12/07 11000 14000 100 Kaufling 03/05/01 7900 11966.6667 100 Hartstein 04/02/17 13000 12225 100 Weiss 04/07/18 8000 11380 100 Russell 04/10/01 14000 11816.6667 100 Partners 05/01/05 13500 12057.1429 100 Errazuriz 05/03/10 12000 12050 100 Fripp 05/04/10 8200 11622.2222 100 Kochhar 05/09/21 17000 12160 100 Vollman 05/10/10 6500 11645.4545 100 Cambrault 07/10/15 11000 11591.6667 100 Mourgos 07/11/16 5800 11146.1538 100 Zlotkey 08/01/29 10500 11100 101 Mavris 02/06/07 6500 9502.66667 101 Baer 02/06/07 10000 9502.66667 101 Higgins 02/06/07 12008 9502.66667 101 Greenberg 02/08/17 12008 10129 101 Whalen 03/09/17 4400 8983.2 102 Hunold 06/01/03 9000 9000

다음은 WINDOW 절을 사용하는 예이다.

SELECT manager_id, last_name, hire_date, salary,
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
FROM employees
WHERE manager_id BETWEEN 100 AND 102
ORDER BY manager_id, hire_date, salary;

WINDOW 절은 집계 범위를 바로 직전(1 PRECEDING)과 직후(1 FOLLOWING)로 제한하고 있어서, 자신과 직전의 직원, 직후의 직원에 대한 평균을 계산하고 있다.

예를 들어, De Hann의 직전 직원은 없으므로 자신과 직후의 Raphaely의 평균을 계산한다.

Raphaely는 자신과 De Haan, Kaufling의 평균을 계산한다.

MANAGER_ID LAST_NAME HIRE_DATE SALARY C_MAVG ---------- ------------ --------- -------- ---------- 100 De Haan 01/01/13 17000 14000 100 Raphaely 02/12/07 11000 11966.6667 100 Kaufling 03/05/01 7900 10633.3333 100 Hartstein 04/02/17 13000 9633.33333 100 Weiss 04/07/18 8000 11666.6667 100 Russell 04/10/01 14000 11833.3333 100 Partners 05/01/05 13500 13166.6667 100 Errazuriz 05/03/10 12000 11233.3333 100 Fripp 05/04/10 8200 12400 100 Kochhar 05/09/21 17000 10566.6667 100 Vollman 05/10/10 6500 11500 100 Cambrault 07/10/15 11000 7766.66667 100 Mourgos 07/11/16 5800 9100 100 Zlotkey 08/01/29 10500 8150 101 Mavris 02/06/07 6500 8250 101 Baer 02/06/07 10000 9502.66667 101 Higgins 02/06/07 12008 11338.6667 101 Greenberg 02/08/17 12008 9472 101 Whalen 03/09/17 4400 8204 102 Hunold 06/01/03 9000 9000

Common Table Expression

Common Table Expression(CTE)은 SQL:1999 표준으로, 질의 결과가 한 번 이상 사용되는 경우 질의 결과를 부질의(subquery) 형태로 임시로 저장하여, 일반 테이블처럼 여러 곳에서 참조가 가능하게 하는 기능이다. CUBRID는 CTE를 지원하지 않는다.

CTE는 객체로 저장되지 않고 질의 기간 동안에만 지속된다는 점에서 유도 테이블(derived table)과 비슷하다. 유도 테이블과 다른 점은, CTE는 자기 참조(self-referencing)가 가능하고 같은 질의 내에서 여러 번 참조될 수 있다는 점이다. CTE는 한 번의 질의에 여러 번 조인되는 테이블에 대해 접근하는 횟수를 줄이고자 할 때 사용할 수 있다.

다음은 CTE를 사용하는 예이다.

INSERT INTO t2
WITH rn AS (
SELECT rownum rn
FROM dual
CONNECT BY LEVEL <= (SELECT MAX(cases) FROM t1))
SELECT pname
FROM t1, rn
WHERE rn <= cases
ORDER BY pname;

위의 예는 INSERT 문에 WITH 절로 정의한 rn을 SELECT 문에 사용하여 INSERT … SELECT 문을 수행한다.

이렇게 CTE를 사용하면 복잡해지거나 분리될 수밖에 없는 질의도 간단하게 수행할 수 있다.

계층적 질의

계층적 질의(hierarchical query)는 테이블에 포함된 행 간에 수직적 계층 관계가 성립되는 데이터에 대해 각 행을 출력하는 질의이며 SQL 표준은 아니다. CUBRID는 계층적 질의를 지원한다.

계층적 질의의 예는 다음과 같다.

SELECT id, mgrid, name FROM tree CONNECT BY PRIOR id=mgrid ORDER BY id;

위의 질의는 각 직원의 매니저 아이디를 출력한다.

id mgrid name ====================== 1 null Kim 2 null Moy 3 1 Jonas 3 1 Jonas 4 1 Smith 4 1 Smith 5 2 Verma 5 2 Verma 6 2 Foster 6 2 Foster 7 6 Brown 7 6 Brown 7 6 Brown

PIVOT 연산자

PIVOT 연산자는 SQL:2003 표준으로, 특정한 한 열에 포함된 값들을 그룹화한 후 해당 값을 여러 개의 열로 변환하여 출력한다. 즉, 특정 행을 열로 회전(rotation)하는 것이다. CUBRID는 PIVOT을 10.0 버전부터 지원할 예정이다. 참고로, UNPIVOT은 특정 열을 행으로 회전하는 것이며 이 연산자 역시 CUBRID 10.0부터 지원할 예정이다.

다음은 PIVOT을 사용한 질의의 예이다.

SELECT * FROM (SELECT job, deptno, sum(sal) sal FROM emp GROUP BY job, deptno) PIVOT (sum(sal) FOR deptno IN (10, 20, 30, 40));

위의 질의는 행의 값으로 출력되는 deptno의 값을 열로 출력하도록 회전한 것이다.

JOB 10 20 30 40 --------- ---------- ---------- ---------- ---------- CLERK 1300 1900 950 SALESMAN 5600 PRESIDENT 5000 MANAGER 2450 2975 2850 ANALYST 6000

PIVOT 연산자가 없다면 다음과 같이 출력될 것이다.

SELECT job, deptno, sum(sal) sal FROM emp GROUP BY job, deptno; JOB deptno sum(sal) --------- ---------- ---------- CLERK 10 1300 CLERK 20 1900 CLERK 30 950 SALESMAN 30 5600 PRESIDENT 10 5000 MANAGER 10 2450 MANAGER 20 2975 MANAGER 30 2850 ANALYST 20 6000

GROUP BY … ROLLUP

GROUP BY … ROLLUP은 SQL:1999 표준으로, 그룹화된 전체 칼럼 외에 각 칼럼을 기준으로 집계 결과 행을 추가로 출력하는 기능이다. CUBRID는 이 기능을 지원한다.

다음은 GROUP BY … ROLLUP의 예이다.

SELECT dept_no AS a1, name AS a2, avg(sales_amount) AS a3 FROM sales_tbl WHERE sales_amount > 100 GROUP BY a1, a2 WITH ROLLUP;

WITH ROLLUP이 없다면 a1과 a2를 묶은 그룹별 집계만 출력하지만, WITH ROLLUP이 추가되어 a1별 집계, 모든 행에 대한 집계 결과가 추가로 출력되고 있다.

a1 a2 a3 ============================================================= 201 'George' 3.500000000000000e+02 201 'Laura' 5.000000000000000e+02 201 NULL 4.000000000000000e+02 301 'Max' 3.000000000000000e+02 301 NULL 3.000000000000000e+02 501 'Chang' 1.500000000000000e+02 501 'Stephan' 3.000000000000000e+02 501 'Sue' 1.750000000000000e+02 501 NULL 1.900000000000000e+02 NULL NULL 2.750000000000000e+02

WITH ROLLUP이 없다면 다음과 같이 a1별 집계, 모든 행에 대한 집계는 출력에서 제외된다.

SELECT dept_no AS a1, name AS a2, avg(sales_amount) AS a3 FROM sales_tbl WHERE sales_amount > 100 GROUP BY a1, a2; a1 a2 a3 ============================================================== 201 'George' 3.500000000000000e+02 201 'Laura' 5.000000000000000e+02 301 'Max' 3.000000000000000e+02 501 'Chang' 1.500000000000000e+02 501 'Stephan' 3.000000000000000e+02 501 'Sue' 1.750000000000000e+02

참고로, SQL:1999에서 확장된 GROUP BY 표준에는 ROLLUP 외에 CUBE, GROUPING SETS가포함되는데, CUBE와 GROUPING SETS는 CUBRID 10.0에서 지원할 예정이다.

Temporal database

Temporal database는 유효 시간(시간 간격, 시작 시간과 종료 시간)이 메타 데이터로 내장되어 제공되는 데이터베이스이며, SQL:2011 표준이다. CUBRID는 Temporal database를 지원하지 않는다.

Temporal 기능은 Valid Time 또는 Transaction Time을 메타 데이터로 포함한다. Valid Time과 Transaction Time은 기간 정보를 제공하기 위해 각각 시작 시간과 종료 시간을 기록하는데, Valid Time은 응용 프로그램 기간(application time period)을 나타내며 Transaction Time은 시스템 기간(system time period)을 나타낸다.

하나의 테이블은 최대 하나의 Valid Time과 하나의 Transaction Time을 가질 수 있다.

Valid Time은 실제 세계에서 어떤 사건이 사실인 기간이다.

예를 들면, 어떤 사람이 A 동네에 거주하면 거주 시작 시간이 기록되고, 끝 시간은 무한대가 된다. B 동네로 이주하면 A 동네의 이력은 삭제되지 않고 남아 있으며 끝 시간이 업데이트되고, B 동네와 시작 시간이 기록된다. 그리고 그 사람이 죽으면 B 동네의 끝 시간이 업데이트된다.

Jone Doe가 태어나면 다음과 같은 레코드가 기록된다.

Person(John Doe, Smallville, 3-Apr-1975, ∞).

John Doe는 Smallville에서 태어나 거주하다가 BigTown으로 이주하면, Smallville의 종료일은 업데이트되고, Bigtown에 대한 기록이 시작일과 함께 추가된다.

Person(John Doe, Smallville, 3-Apr-1975, 26-Aug-1994). Person(John Doe, Bigtown, 26-Aug-1994, ∞).

John Doe가 사망하면 Bigtown의 종료일이 업데이트된다.

Person(John Doe, Smallville, 3-Apr-1975, 26-Aug-1994). Person(John Doe, Bigtown, 26-Aug-1994, 1-Apr-2001).

위의 기록을 통해 John Doe의 출생과 사망일, John Doe가 거주한 동네와 거주 기간을 알 수 있다.

Transaction Time은 데이터베이스에 저장된 사건이 사실로 간주되는 기간이다. 이를 이용하면, 주어진 시간에 대한 데이터베이스의 상태를 보여주는 질의가 가능하다. Transaction Time 테이블에서 레코드는 절대 삭제되지 않으며, 새 레코드가 추가되거나, 어떤 레코드가 더 이상 존재하지 않음을 표현하기 위해 종료 시간만 업데이트된다.

앞서 Valid Time을 설명할 때 등장한 John Doe를 예로 들어 보자.

Person(John Doe, Smallville, 3-Apr-1975, 26-Aug-1994). Person(John Doe, Bigtown, 26-Aug-1994, 1-Apr-2001).

사실 John Doe는 Bigtown에 거주했다고 기록된 기간 사이에 Beachy에 거주했던 사실이 있다. 그 기간이 포함되면 다음과 같이 기록된다.

Person(John Doe, Smallville, 3-Apr-1975, 26-Aug-1994). Person(John Doe, Bigtown, 26-Aug-1994, 1-Jun-1995). Person(John Doe, Beachy, 1-Jun-1995, 3-Sep-2000). Person(John Doe, Bigtown, 3-Sep-2000, 1-Apr-2001).

하지만 이렇게만 기록된다면 John Doe가 Beachy에 거주했음을(Beachy에 세금을 내지 않으려는 등의 이유로) 숨겼다는 사실을 파악할 수 없다.

한 개인의 주소나 날짜가 잘못되어서 변경해야 되고 그러한 변경 기록을 모두 가지고 있으려면, Transaction Time이 필요하다. Transaction Time은 데이터베이스 내에 변경 이력을 캡처링할 수 있게 한다. Valid Time과 Transaction Time이 같이 포함된 데이터베이스의 내용은 다음과 같다.

Person(John Doe, Smallville, 3-Apr-1975, ∞, 4-Apr-1975, 27-Dec-1994).
Person(John Doe, Smallville, 3-Apr-1975, 26-Aug-1994, 27-Dec-1994, ∞ ).
Person(John Doe, Bigtown, 26-Aug-1994, ∞, 27-Dec-1994, 2-Feb-2001 ).
Person(John Doe, Bigtown, 26-Aug-1994, 1-Jun-1995, 2-Feb-2001, ∞ ).
Person(John Doe, Beachy, 1-Jun-1995, 3-Sep-2000, 2-Feb-2001, ∞ ).
Person(John Doe, Bigtown, 3-Sep-2000, ∞, 2-Feb-2001, 1-Apr-2001 ).
Person(John Doe, Bigtown, 3-Sep-2000, 1-Apr-2001, 1-Apr-2001, ∞ ).

기록되는 순서는 (사람 이름, 도시 이름, Valid-From, Valid-To, Transaction-From, Transaction-To) 이다.

병렬 질의 처리

병렬 질의 처리(parallel query processing) 기능은 하나의 SQL 문장을 멀티 프로세스에서 동시에 수행하게 하는 기능이다. 한 문장을 처리하기 위해 필요한 작업을 여러 개의 서버 프로세스에 나누어서 처리 속도를 높이고자 하는 것이다. CUBRID는 이를 지원하지 않는다.

Oracle 서버는 다음의 질의들에 대해 병렬 질의 처리를 수행할 수 있다.

• SELECT 문
• UPDATE, INSERT, 그리고 DELETE 문의 부질의(subqueries)
• CREATE TABLE ... AS SELECT 문
• CREATE INDEX 문

문자열 집계

문자열 집계(aggregation for strings)는 그룹화되는 칼럼을 기준으로, 여러 개의 행으로 표현되는 다른 칼럼의 값들이 하나의 행에 표현되도록 문자열을 합치는 기능이다. SQL:2008 표준이며 ARRAY_AGG라는 함수 이름으로 정의되어 있다. 특정 숫자 칼럼을 그룹화하여 집계하면 집계된 결과를 한 행에 표현해 주는 것과 마찬가지로, 특정 문자열 칼럼을 그룹화하면 사용자가 명시한 분리자(separator)를 넣어서 하나의 문자열로 병합한다. CUBRID는 GROUP_CONCAT이라는 이름의 함수로 이를 지원한다.

다음은 CUBRID에서 GROUP_CONCAT 함수가 문자열 집계를 보여주는 예이다.

SELECT deptno, GROUP_CONCAT(ename ORDER BY ename SEPARATOR ',') AS employees FROM emp GROUP BY deptno; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 ADAMS,FORD,JONES,SCOTT,SMITH 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

Oracle은 LISTAGG라는 함수를 사용한다.

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees FROM emp GROUP BY deptno; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 ADAMS,FORD,JONES,SCOTT,SMITH 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

정규 표현식(Regular Expression)

정규 표현식 기반 비교

정규 표현식 기반 비교는 비교 연산 시 정규 표현식의 사용이 가능하게 하는 기능으로 SQL:1999 표준이며, CUBRID는 REGEXP 조건 연산자를 이용해 이 기능을 사용할 수 있다.

다음은 CUBRID에서 REGEXP 조건식을 사용하여 정규 표현식 비교를 수행하는 예이다.

SELECT name FROM athlete where name REGEXP '^[a-d]';

위의 결과는 대소문자를 구분하지 않고 a, b, c 혹은 d로 시작되는 name을 반환한다. 대소문자를 구분하려면 "REGEXP BINARY"를 사용한다.

SELECT name FROM athlete where name REGEXP BINARY '^[a-d]';

정규 표현식 기반 서브스트링

정규 표현식 기반 서브스트링(substring)은 전체 문자열에서 매칭되는 특정 문자열만 추출할 때 매칭 조건으로 정규 표현식을 사용한다. CUBRID에서는 지원하지 않는다.

다음은 Oracle에서 REGEXP_SUBSTR 함수를 사용하는 예이다.

SELECT REGEXP_SUBSTR('http://www.example.com/products', 'http://([[:alnum:]]+\.?){3,4}/?') "REGEXP_SUBSTR" FROM dual;

정규 표현식 기반 REPLACE

정규 표현식 기반 REPLACE는 전체 문자열에서 매칭되는 특정 문자열을 교체할 때 매칭 조건으로 정규 표현식을 사용한다. CUBRID에서는 지원하지 않는다.

다음은 Oracle에서 REGEXP_REPLACE 함수를 사용하는 예이다.

SELECT REGEXP_REPLACE('500 Oracle Parkway, Redwood Shores, CA', '( ){2,}', ' ') "REGEXP_REPLACE" FROM dual;

제약 조건(Constraint)

지연된 제약 조건

지연된 제약 조건(deferred constraints)은 질의 수행 시 제약 조건 검사를 트랜잭션 커밋 시점까지 늦추는 기능으로 ANSI SQL-92 표준이다. CUBRID는 지원하지 않는다.

CHECK 제약 조건

CHECK 제약 조건이란, DDL(data definition language)에 제약 조건을 포함해 놓으면 DML(data manipulation language) 질의 수행 시 제약 조건을 검사하는 기능으로 ANSI SQL-92 표준이다. MySQL과 CUBRID는 파싱을 허용하되 실제로 기능이 동작하지는 않는다.

예를 들어 아래와 같이 테이블 생성 구문에 CHECK를 포함해도 테이블이 생성되지만, DML 질의를 수행할 때 이 제약 조건이 적용되지 않으므로 id에 음수 값 입력이 허용된다.

CREATE TABLE person ( id INT CHECK (id > 0), name VARCHAR (30) );

참고로, CUBRID에서 뷰를 생성하는 경우 WITH CHECK OPTION을 사용하여 데이터의 입력을 제한할 수 있다.

인덱싱

Partial Index

partial index는 필터링된 인덱스(filtered index)라고도 하며, 인덱스 생성 시 조건을 명시하여 한정적인 데이터만을 인덱스 대상으로 삼는 것을 말한다. 어떤 조건이 질의에 포함되고 결과 세트(result set)가 필터링된 인덱스의 부분 집합이 될 수 있으면 이 질의에는 필터링된 인덱스가 적용될 수 있다. CUBRID는 필터링된 인덱스를 지원한다.

다음은 CUBRID에서 필터링된 인덱스를 사용하는 예이다.

CREATE TABLE blogtopic ( blogID BIGINT NOT NULL, title VARCHAR(128), author VARCHAR(128), content VARCHAR(8096), postDate TIMESTAMP NOT NULL, deleted SMALLINT DEFAULT 0 ); CREATE INDEX my_filter_index ON blogtopic(postDate) WHERE deleted=0; SELECT * FROM blogtopic USE INDEX (my_filter_index) WHERE postDate>'2010-01-01' AND deleted=0;

내림차순 인덱스

내림차순으로 정렬하여 생성한 인덱스를 내림차순 인덱스(descending index)라고 한다. 특정 칼럼에 대해 내림차순 정렬을 할 때 내림차순 인덱스가 적용될 수 있다. CUBRID는 내림차순 인덱스를 지원한다.

다음과 같이 오름차순과 내림차순을 병합하여 생성할 수도 있다.

CREATE INDEX tbl(a ASC, b DESC);

키가 아닌 칼럼을 포함하는 인덱스

키가 아닌 칼럼을 포함하는 인덱스(index with included columns)란, 키가 아닌 칼럼(non-key column)이지만 검색 결과에 항상 포함되는 칼럼이 단말 노드(leaf node)에 포함되어 있는 인덱스이다. SQL Server에서만 제공되는 기능이며, CUBRID는 이 기능을 지원하지 않는다. SQL Server는 인덱스 단말의 저장 형태에 따라 클러스터형 인덱스와 비클러스터형(non-clustered) 인덱스로 나뉘는데, 키가 아닌 칼럼을 포함하는 인덱스는 비클러스터형 인덱스에서만 사용할 수 있다.

참고로, CUBRID는 인덱스의 키 대상 칼럼이 질의 검색 결과를 모두 포함하는 경우 힙 영역 탐색 없이 인덱스 탐색만으로 질의 결과 값을 출력할 수 있는 커버링 인덱스 기능을 제공한다.

클러스터형 인덱스

클러스터형 인덱스(clustered index)는 인덱스의 단말 노드가 곧 힙 영역(데이터 영역)을 의미하는 인덱스로, 단말 노드에서 힙으로 데이터를 검색하는 과정이 생략된다. 따라서 인덱스 탐색 속도가 비클러스터형(non-clustered) 인덱스보다 빠르지만 삽입/갱신 과정에서 물리적인 정렬을 필요로 하므로 삽입/갱신 연산 비용이 비클러스터형 인덱스보다 크다. CUBRID는 비클러스터형 인덱스만 지원한다.

DML

다중 행 INSERT

다중 행 INSERT(multiple rows insert)는 여러 개의 행을 하나의 질의문으로 처리 가능한 구문으로, ANSI SQL-92 표준이다. CUBRID는 이를 지원한다.

INSERT INTO tbl VALUES (1, 'A'), (2, 'B'), (3, 'C');

MERGE 문

MERGE 문은 하나 또는 그 이상의 원본으로부터 행들을 선택하여 하나의 테이블 또는 뷰로 갱신이나 삽입을 수행하기 위해 사용되는 구문으로, SQL:2003 표준이다. MySQL은 하나 이상의 테이블로부터 하나의 테이블 또는 뷰에 삽입 또는 갱신하는 MERGE 문을 지원하지는 않지만, 단일 테이블에 대해 삽입 또는 갱신이 가능한 INSERT ... ON DUPLICATE KEY UPDATE 문을 지원한다. CUBRID는 MERGE 문과 INSERT ... ON DUPLICATE KEY UPDATE 문을 모두 지원한다.

다음은 MERGE 문의 예이다.

MERGE INTO target_table tt USING source_table st ON (st.a=tt.a AND st.b=tt.b) WHEN MATCHED THEN UPDATE SET tt.c=st.c DELETE WHERE tt.c = 1 WHEN NOT MATCHED THEN INSERT VALUES (st.a, st.b, st.c);

데이터 타입

ENUM 타입

ENUM 타입은 허용하는 문자열을 사용자가 명시하는 타입으로, SQL 표준에는 정의되어 있지 않다. CUBRID는 이를 지원한다.

다음은 CUBRID에서 ENUM 타입을 사용하는 예이다.

CREATE TABLE tbl ( color ENUM ('red', 'yellow', 'blue', 'green') ); INSERT INTO tbl (color) VALUES ('yellow'); INSERT INTO tbl (color) VALUES (1);

BOOLEAN 타입

BOOLEAN 타입은 TRUE 혹은 FALSE 두 가지 값만 갖는 타입으로, SQL:1999 표준이다. CUBRID와 Oracle은 이 타입을 지원하지 않는다.

SQL Server는 BIT 타입이 그 역할을 대신하며, TRUE, FALSE로 리터럴(literal)을 입력하면 1, 0으로 변환된다.

MySQL에서 BOOLEAN 타입은 TINYINT(1) 타입으로 변환된다. 즉, 한 자리수의 숫자를 허용하여 1, 0뿐만 아니라 2 ~ 9도 허용하며, 이는 BOOLEAN의 원래 의미와는 다르므로 정확히 말하자면 BOOLEAN을 지원한다고 보기 어렵다.

CUBRID는 이 타입을 지원하지 않으므로 BIT 타입을 사용하거나 CHAR(1)을 사용하도록 한다. CUBRID의 BIT 타입은 MS SQL처럼 TRUE, FALSE 리터럴을 지원하지는 않는다.

INTERVAL 타입

INTERVAL 타입은 시간 간격을 저장하는 타입으로, ANSI SQL-92에 정의되어 있다. CUBRID는 INTERVAL 타입을 지원하지 않는다.

다음은 Oracle에서 테이블 생성 시 칼럼에 INTERVAL 타입을 명시하는 예이다.

CREATE TABLE test_interval_table ( id NUMBER(10), time_period_1 INTERVAL YEAR TO MONTH, time_period_2 INTERVAL DAY TO SECOND, time_period_3 INTERVAL YEAR (3) TO MONTH, time_period_4 INTERVAL DAY (4) TO SECOND (9) );

DDL

트랜잭션 지원 DDL

트랜잭션 지원 DDL(transactional DDL)은 DDL 문 수행이 트랜잭션에 묶여서 수행할 수 있는 기능을 말한다. 즉, 이 기능이 지원되면 트랜잭션 커밋이 완료되어야만 테이블 생성, 인덱스 생성 등이 완료된다. CUBRID는 이 기능을 지원한다.

계산되는 칼럼

계산되는 칼럼(computed column)이란, 테이블의 칼럼 속성을 계산식으로 정의하는 칼럼이다. 이 칼럼의 계산식에는 칼럼 이름, 상수, 함수, 그리고 이러한 것들이 연산자에 의해 연결된 조합이 될 수 있다. CUBRID는 이를 지원하지 않는다.

다음은 테이블을 생성할 때 계산되는 칼럼을 정의하는 예이다.

ALTER TABLE emp2 ADD (income AS (salary + (salary*commission_pct)));

함수에 의한 칼럼 DEFAULT

칼럼의 DEFAULT 값으로 함수를 사용할 수 있는 기능(functions as column defaults)이다. CUBRID는 이를 지원하지 않는다.

CREATE TABLE yyy( x VARCHAR(10) DEFAULT dbo.uuu() );

SEQUENCE

SEQUENCE는 일련번호 객체를 정의한 것으로, SQL:2003 표준에 정의되어 있다. CUBRID는 이를 SERIAL이라는 구문으로 제공한다.

다음은 CUBRID에서 SERIAL을 정의하는 예이다.

CREATE SERIAL order_no START WITH 10000 INCREMENT BY 2 MAXVALUE 20000;

자동 증가 칼럼

자동 증가 칼럼(auto increment column)은 특정 칼럼의 값을 NULL로 지정하는 경우 일련 번호가 자동으로 부여되도록 하는 속성으로, SQL:2003 표준에 정의되어 있다. CUBRID는 이를 지원한다.

다음은 CUBRID에서 자동 증가 칼럼을 정의하고 사용하는 예이다.

CREATE TABLE tbl (id INT AUTO_INCREMENT, val string) AUTO_INCREMENT = 3; CREATE TABLE t (i INT AUTO_INCREMENT(100, 2));

SYNONYM

SYNONYM 구문은 주로 원격지의 테이블 또는 다른 스키마에 존재하는 테이블의 이름을 줄인 형태로 매핑하여 사용하고자 할 때 쓰는 것이다. 원격지의 위치가 변경되더라도 SYNONYM 원본 이름만 바꾼다면 SYNONYM을 사용하는 기존의 질의문을 그대로 사용할 수 있다는 장점이 있다. CUBRID는 스키마 개념을 지원하지 않기 때문에 이러한 구문이 불필요하다.

다음은 Oracle에서 SYNONYM을 사용하는 예이다.

CREATE SYNONYM offices FOR hr.locations;

무정지 인덱스 생성

무정지 인덱스 생성(non-blocking index creation) 기능은 데이터베이스 운영 중에 인덱스 생성이 가능하도록 하는 기능으로, MySQL에서는 사용자 대부분이 사용하는 MyISAM 엔진이나 InnoDB 엔진에서는 지원하지 않고 오직 클러스터드 에디션(clustered edition)에서만 지원한다. CUBRID는 현재 이를 지원하지 않으나, 10.0 버전부터 인덱스 생성 도중에도 SELECT 질의에 한해 허용할 예정이다.

분할

분할(partitioning)은 특정 키를 기준으로 테이블을 분할하여 생성하는 것으로, 특정 키로 검색하는 경우 분할된 여러 개의 테이블 중 하나로 검색 범위를 제한할 수 있다는 장점이 있다. CUBRID는 분할을 지원한다.

다음은 CUBRID에서 분할 테이블을 생성하는 예이다.

CREATE TABLE participant_part( host_year INT, nation CHAR(3), gold INT, silver INT, bronze INT ) PARTITION BY RANGE (host_year) ( PARTITION before_2000 VALUES LESS THAN (2000), PARTITION before_2008 VALUES LESS THAN (2008) );

Cascading DROP

테이블을 제거(DROP)할 때 이 테이블을 참조하고 있는 테이블들을 같이 제거하는 기능으로SQL:2003 표준이며, CUBRID는 이를 지원하지 않는다.

DDL 트리거

DDL 트리거(trigger)는 DDL 문이 실행될 때마다 사용되는 트리거로, CUBRID는 이를 지원하지 않는다. 일례로, 테이블 생성 내역을 로깅하는 경우에 사용할 수 있다.

TRUNNCATE 트리거

TRUNCATE 트리거는 TRUNCATE 문이 실행될 때마다 사용되는 트리거로, CUBRID는 이를 지원하지 않는다.

기본 키 제약 조건에 이름 명시

CUBRID는 기본 키(primary key) 제약 조건에 이름을 명시하는 것이 가능하다. MySQL의 경우 기본 키 제약 조건 이름은 항상 PRIMARY가 된다.

프로그래밍

저장 프로시저

CUBRID는 저장 프로시저(stored procedure)를 제한적으로 지원한다. CUBRID 저장 프로시저는 구현부를 Java 언어로 정의하여 클래스화한 후, 이를 등록하여 사용한다. 따라서 Java에 익숙한 사용자에게는 저장 프로시저의 구현이 용이하다.

테이블 함수

어떤 함수의 반환 값으로 결과 셋을 받고 싶을 때 테이블 함수(table function)를 사용하면 간단히 해결할 수 있다. 테이블 함수는 여러 개의 행을 반환하며, 특정 함수를 마치 테이블처럼 FROM 절에서 사용할 수 있다. CUBRID는 테이블 함수를 지원하지 않는다.

다음은 Oracle에서 테이블 함수를 정의하고 사용하는 예이다.

CREATE TYPE t_tf_row AS OBJECT ( id NUMBER, description VARCHAR2(50) ); / CREATE TYPE t_tf_tab IS TABLE OF t_tf_row; / -- Build the table function itself. CREATE OR REPLACE FUNCTION get_tab_tf (p_rows IN NUMBER) RETURN t_tf_tab AS l_tab t_tf_tab := t_tf_tab(); BEGIN FOR i IN 1 .. p_rows LOOP l_tab.extend; l_tab(l_tab.last) := t_tf_row(i, 'Description for ' || i); END LOOP; RETURN l_tab; END; / -- Test it. SELECT * FROM TABLE(get_tab_tf(10)) ORDER BY id DESC;

사용자 정의 집계

사용자가 집계 함수를 정의(custom aggregate)하여 사용하는 기능으로, CUBRID는 이를 지원하지 않는다.

문장·행 수준 트리거

트리거는 문장 수준 또는 행 수준으로 제공될 수 있는데, 문장 수준 트리거(statement level trigger)는 문장이 호출될 때만 한 번 수행되며, 행 수준 트리거(row level trigger)는 질의문에 의해 영향을 받는 행 각각에 대해 트리거가 수행된다. CUBRID는 두 가지 타입 모두 제공한다. CUBRID에서는 문장 수준 트리거를 문장 이벤트라고 부르고 행 수준 트리거를 인스턴스 이벤트라고 부른다.

CREATE TRIGGER example BEFORE UPDATE ON history(score) EXECUTE INSERT INTO update_logs VALUES (obj.event_code, obj.score, SYSDATETIME);

위의 예는 history 테이블에서 score 칼럼을 업데이트하기 전에 update_logs 테이블에 수정 이전의 값을 저장하는 트리거이다.

만약 score 칼럼의 첫 번째 행이 갱신되기 직전에 트리거가 단 한 번만 동작되게 하려면 아래와 같이 "STATEMENT UPDATE" 형식을 사용한다.

CREATE TRIGGER example
BEFORE STATEMENT UPDATE ON history(score)
EXECUTE INSERT INTO update_logs VALUES (obj.event_code, obj.score, SYSDATETIME);

내장된 스케줄러

내장된 스케줄러(built-in scheduler)란, 리눅스의 cronjob 명령처럼 스케줄링이 필요한 작업을 데이터베이스에서 직접 수행하는 기능으로, CUBRID는 이를 지원하지 않는다.

뷰(VIEW)

업데이트 가능한 뷰

뷰에서 UPDATE 문 수행이 가능하게 하는 기능(updateable views)으로, CUBRID는 이를 지원한다. SQL-92에서는 원본 테이블이 오직 하나인 뷰만 업데이트가 가능하다는 식으로 업데이트 가능한 조건이 훨씬 제한적이었고, SQL: 2008에서는 뷰의 스키마를 뷰의 기반이 되는 원본 테이블로 역으로 매핑하는 것이 가능하다면 업데이트가 가능하도록 정의되었다.

CUBRID의 경우 2008 R4.x 버전까지는 업데이트 가능한 테이블이나 뷰를 반드시 하나만 포함해야 하며, DISTINCT, UNIQUE 구문을 포함해서는 안 되고, SUM(), AVG()와 같은 집계 함수를 포함하면 안 되는 등의 제한 조건이 존재한다. 9.x 버전에서는 업데이트 가능한 테이블이나 뷰가 2개 이상 포함된 뷰, 즉 조인 뷰에 대해서도 업데이트할 수 있다. 나머지 제약 조건은 2008 R4.x 버전과 동일하다.

WITH CHECK OPTION

뷰를 생성할 때 "WITH CHECK OPTION"을 명시하면, 이후 뷰를 이용하여 값을 갱신할 때 조건에 맞지 않는 값은 갱신할 수 없게 하는 기능이다. CUBRID는 이를 지원한다.

CREATE VIEW b_view AS SELECT * FROM a_tbl WHERE phone IS NOT NULL WITH CHECK OPTION;

위의 뷰에 아래와 같은 UPDATE 문을 수행하면 체크 조건에 위배되므로 에러가 발생한다.

UPDATE b_view SET phone=NULL;

뷰에 트리거 적용

CUBRID에서는 뷰에 트리거를 적용할 수 없다.

유도 테이블이 있는 뷰

CUBRID는 뷰에서 유도 테이블(derived table)을 사용할 수 있다.

CREATE VIEW c_view AS SELECT a.id, b.phone FROM a_tbl a, (SELECT id, phone FROM b_view) b WHERE a.id=b.id;

조인(JOINs)

FULL OUTER JOIN

조인 시 OUTER 조건을 양쪽 테이블에 적용하는 것으로, CUBRID는 이를 지원하지 않는다. CUBRID는 LEFT OUTER JOIN과 RIGHT OUTER JOIN, CROSS JOIN만을 지원한다. CUBRID 10.0에서는 NATURAL JOIN을 추가로 지원할 계획이다.

LATERAL JOIN

LATERAL JOIN은 SQL:2003 표준에 정의되어 있는 구문으로, CUBRID에서 지원하지 않는다. LATERAL JOIN에서 사용하는 'lateral view'는 FROM절에서 앞서 나타난 다른 테이블들을 참조하는 관계를 포함하는 인라인 뷰(inline view)이다. SQL Server는 같은 기능을 위해 LATERAL 연산자 대신 APPLY 연산자를 사용하며, 부분적으로 제약이 있다.

다음은 Oracle에서 수행한 예이다.

SELECT * FROM employees e, (SELECT * FROM departments d WHERE e.department_id = d.department_id); ORA-00904: "E"."DEPARTMENT_ID": invalid identifier

위와 같은 구문은 우측 인라인 뷰에서 바깥쪽의 테이블을 참조할 수 없기 때문에 에러가 발생한다. 그러나, 우측 인라인 뷰에 LATERAL을 정의하면 다음과 같은 구문이 되며, 이는 수행 가능하다.

SELECT * FROM employees e, LATERAL (SELECT * FROM departments d WHERE e.department_id = d.department_id);

JOIN … USING

조인 조건은 보통 ON을 사용하여 지정하는데, 조인 조건이 되는 각 테이블의 칼럼 이름이 동일하면 USING을 사용하여 칼럼 이름만 지정하여, 조건 작성을 단순화할 수 있다. JOIN … USING은 ANSI SQL-92에 정의되어 있다. CUBRID는 조인 문에서 USING을 지원하지 않는다.

다음은 Oracle에서 ON과 USING을 사용하는 예로, 두 개의 구문은 동일한 결과를 출력한다.

SELECT film.title, film.film_id FROM film JOIN film_actor ON (film.film_id = film_actor.film_id) WHERE ... SELECT film.title, film_id FROM film JOIN film_actor USING (film_id) WHERE ...

연산(Operators)

UNION/INTERSECT/EXCEPT

UNION/INTERSECT/EXCEPT는 문장 집합 연산자로 ANSI SQL-92 표준이며, CUBRID는 이들을 모두 지원한다. CUBRID에서 INTERSECT와 INTERSECTION은 서로 동일하며, EXCEPT와 DIFFERENCE도 서로 동일하다.

ORDER BY … NULLS LAST

이 구문은 NULL 값을 정렬할 때 기준을 정하는 것으로, SQL:2003 표준이다. CUBRID는 이를 지원한다.

SELECT * FROM tbl ORDER BY b NULLS FIRST;

CUBRID에서는 NULLS FIRST/LAST를 명시하지 않은 경우 NULL을 모든 값보다 가장 작은 값으로 간주하고 정렬한다. 즉, NULLS FIRST/LAST를 명시하지 않고 ASC로 정렬하는 경우 NULL 값이 앞에 오며(NULLS FIRST) DESC로 정렬하는 경우 NULL 값이 뒤에 온다(NULLS LAST).

마치며

지금까지 SQL의 특징적인 기능들과 이들이 SQL 표준들 중에 어디에 속하는지 살펴보고, Oracle, MySQL, SQL Server 그리고 CUBRID가 해당 기능들을 지원하는지를 살펴보았다. 이 글을 정리하면서, DBMS의 기능을 정의하고 구현하기 위해 그간 참으로 많은 이들의 노력과 고민이 있어왔다는 것을 새삼 느끼게 되었다.

이 글이 DBMS 간 마이그레이션을 진행하거나, DBMS의 특징적인 SQL 기능을 이해하고 사용하는 데 도움이 되기를 바란다.

참고 자료

• DBMS별 비교표: http://www.sql-workbench.net/dbms_comparison.html
• SQL: http://ko.wikipedia.org/wiki/SQL
• SQL:1999 표준: http://en.wikipedia.org/wiki/SQL:1999
• SQL:2003 표준: http://en.wikipedia.org/wiki/SQL:2003
• SQL:2011 표준: http://en.wikipedia.org/wiki/SQL:2011
• 윈도 함수: https://www.simple-talk.com/sql/t-sql-programming/window-functions-in-sql/, http://docs.oracle.com/cd/E11882_01/server.112/e25554/analysis.htm#DWHSG0201
• CTE: http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx
• PIVOT: http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
• Temporal database: https://en.wikipedia.org/wiki/Temporal_database
• 병렬 질의 처리: http://docs.oracle.com/cd/A57673_01/DOC/server/doc/A48506/pqoconce.htm#832
• 문자열 집계: http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php
• 정규 표현식 기반 서브스트링, REPLACE: http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions138.htm#SQLRF06303, http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions137.htm#SQLRF51673
• MERGE: https://ko.wikipedia.org/wiki/Merge_(SQL)
• INTERVAL type: http://www.oracle-base.com/articles/misc/oracle-dates-timestamps-and-intervals.php#interval
• 계산되는 칼럼: http://docs.oracle.com/cd/E11882_01/server.112/e17118/statements_3001.htm#BABIDDJJ
• SYNONYM: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_7001.htm#SQLRF54276
• DDL 트리거: http://docs.oracle.com/cd/B28359_01/server.111/b28318/triggers.htm#CNCPT017
• 테이블 함수: http://www.oracle-base.com/articles/misc/pipelined-table-functions.php#table_functions
• 업데이트 가능한 뷰: http://en.wikipedia.org/wiki/View_(SQL)
• LATERAL JOIN: http://docs.oracle.com/cd/E11882_01/olap.112/e17122/dml_expression.htm#OLADM210
• JOIN … USING: http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_10002.htm#SQLRF55646
• ORDER BY … NULLS LAST: https://en.wikipedia.org/wiki/Order_by

출처 : https://d2.naver.com/helloworld/907716

ANSI ( American National Standards Institute) / 미국표준협회

SQL 공부를 하다가 역사가 궁금해서 찾아보았다. ANSI SQL이라는 용어가 보여 ANSI가 무엇인지 정리하였다. 

SQL은 IBM에서 1970년대 초에 도널드 D. 챔벌린과 레이먼드 F. 보이스가 처음 개발하였다. 초기에는 SEQUEL (Structured English Query Language, 구조 영어 질의어)라는 이름으로 시작하였으며, IBM의 준 관계형 데이터베이스 관리 시스템 시스템 R에 저장된 데이터를 조작하고 수신하기 위해 고안되었다. SEQUEL은 나중에 SQL으로 바뀌었다. 그 까닭은 SEQUEL이 영국의 호커 시들리 항공사의 상표였기 때문이다.

1986년 SQL-86의 명칭(별칭 SQL-87)으로 ANSI에 의한 최초의 표준화가 되었다.

각 나라별 표준협회

일본규격협회(JIS)

미국표준협회(ANSI)

한국표준협회(KS)

유럽(DIN)

여러 데이터베이스들의 SQL들이 제공해주는 특성에 따라 SQL이 다를 수 있다. 하지만 ANSI 표준으로 작성된 SQL은 모든 데이터베이스에서 호환된다. 

그러므로 ANSI SQL 작성하게끔 습관을 들이는 것이 좋다.

[출처] SQL, ANSI ( American National Standards Institute) / 미국표준협회|작성자 돌

Ex. Oracle에서의 쿼리

SELECT a.ename
      ,b.ename
  FROM scott.emp a, scott.emp b
 WHERE a.mgr = b.empno
    
 ANSI SQL

SELECT a.ename
      ,b.ename
  FROM scott.emp a JOIN scott.emp b
    ON a.mgr = b.empno
    

두 쿼리는 같은 출력을 낸다.
   

● ANSI가 표준이고, 모든 DB에서 호환되는데 보통 ANSI를 배우지 않는 이유.

기본적인 데이터 조회 구문(JOIN)은 ANSI SQL로 작성 가능하나, ANSI를 벗어나게 되는 경우는 DBMS에서 제공하는 함수를 사용할 때 이다.

대부분 테이블에 저장된 데이터를 그대로 보지 않고 가공(문자열 잘라내기, 숫자 계산, 포맷 지정, 데이터 암 복호화 등)해서 보는 경우가

많다. 이러한 경우 내장된 함수 또는 별도로 개발한 함수 등을 이용해서 데이터를 가공하여 보여준다.

특히 자주 사용되는 것은 윈도우 함수(분석 함수)이며 ORACLE에서 제일 많이 제공하고 있다.

| Redis란?

레디스(Redis)는 고성능 key-value 저장소로서 리스트, 해시, 셋 정렬된 셋 등 여러 형식의 자료구조를 지원하는 NoSQL입니다. 메모리에 상주하면서 RDBMS의 캐시 솔루션으로서 주로 사용되며 라인, 삼성전자, 네이버, Stackoverflow, 인스타그램 등 여러 IT 대기업에서도 사용하는 검증된 오픈소스 솔루션입니다. 

제가 일하는 회사에서도 Redis를 쓰고 있으며 RDBMS의 read부하를 줄이기 위해 Redis 클러스터 형태로 운영하고 있습니다. 

| Redis 사용용도

Redis는 Message Queue, Shared Memory, Remote Dictionary 용도로 사용할 수 있습니다. 특히 Remote Dictionary로서 RDBMS의 캐시 솔루션으로 사용 용도가 굉장히 높다고 생각하고 있습니다. RDBMS에서 SELECT 쿼리문을 날려 특정 데이터들을 FETCH했을 때, RDBMS의 구조상 DISK에서 데이터를 꺼내오는 데 Memory에서 읽어들이는 것보다 천배 가량 더 느리기 때문입니다. 

이 때 Redis같은 유연한 자료구조를 가지는 인메모리 Key-value 솔루션을 사용하여 DB 부하의 Read 연산의 부하를 분산시키는 데 적용하는 것은 바람직한 것 같습니다. (DB 캐시 적중률을 높이면 되겠지만 어느 정도 한계가 있다고 생각합니다) 

| Redis 특징

■  Key-Value Store

레디스는 거대한 맵(Map) 데이터 저장소입니다. Key와 value가 매핑된 단순한 맵 데이터 저장소로서 데이터를 레디스에 쉽고 편하게 읽고 쓸 수 있습니다. 장점은 익히기 쉽고 직관적인 데 있고 단점은 Key-value 형태로 저장된 데이터를 레디스 자체내에서 처리하는 것이 어렵다는 점입니다.

■ 다양한 데이터 타입

Key로 참조되는 Value 타입을 다양하게 지정하여 저장할 수 있습니다. List, String, Set, Sorted set 등 여러 데이터를 저정하여 손쉽고 편리하게 데이터를 저장할 수 있습니다.

■  Persistence

Redis는 데이터를 disk에 저장할 수 있습니다. 따라서 Redis는 서버가 shutdown된 후에 restart 하더라도 disk에 저장해놓은 데이터를 다시 읽어서 데이터가 유실되지 않습니다. redis의 데이터를 disk에 저장하는 방식은 snapshot, AOF 방식이 있습니다.

Snapshot : 스냅샷은 RDB에서도 사용하고 있는 어떤 특정 시점의 데이터를 DISK에 옮겨담는 방식을 뜻합니다. Blocking 방식의 SAVE와 Non-blocking 방식의 BGSAVE 방식이 있습니다.

AOF : Redis의 모든 write/update 연산 자체를 모두 log 파일에 기록하는 형태입니다. 서버가 재시작할 시 write/update를 순차적으로 재실행, 데이터를 복구합니다.

레디스 공식문서에서의 권장사항은 RDBMS의 rollback 시스템같이 두 방식을 혼용해서 사용하는 것입니다. 주기적으로 snapshot으로 벡업하고 다음 snapshot까지의 저장을 AOF 방식으로 수행하는 것이죠.

■ ANSI C로 작성

C언어로 작성되어 Java와 같이 가상머신 위에서 동작하는 언어에서 발생하는 성능 문제에 대해 자유롭습니다. 곧바로 기계어로 동작하지 않고 어떤 가상의 머신 위에서 인터프리터된 언어로 가동하는 경우에는 가비지컬렉션(Garbage Collection) 동작에 따른 성능 문제가 발생할 수 밖에 없습니다. 하지만 C언어로 작성된 Redis는 이런 이슈에 대해 자유롭습니다. 

■ 서버 측 복제 및 샤딩을 지원

읽기 성능 증대를 위한 서버 측 복제를 지원합니다와 쓰기 성능 증대를 위한 클라이언트 측 샤딩을 지원합니다.

| Redis 아키텍처

■  Redis Topology

레디스는 아래 그림과 같이 Master-slave 형태로 데이터를 복제해서 운영할 수 있습니다. 이 master-slave 간의 복제는 non-blocking 상태로 이루어집니다.

■ Redis Sharding

레디스에서 데이터를 샤딩하여 레디스의 read성능을 높일 수 있습니다. 예로들어 #1~#999, #1000~#1999 ID 형태로 데이터를 나누어서 데이터의 용량을 확장하고 각 서버에 있는 Redis의 부하를 나누어 줄일 수 있습니다. 

■ Redis Cluster

레디스는 이전에는 Clustering을 지원하지 않았지만 Clustering을 지원하면서 대부분의 회사가 Redis를 클러스터로 묶어서 가용성 및 안정성있는 캐시 매니져로서 사용하고 있습니다. Single Instance로서 레디스를 사용할 때는 Sharding이나 Topology로서 커버해야했던 부분을 Clustering을 이용함으로서 어플리케이션을 설계하는 데 좀 더 수월해졌다고 볼 수 있습니다.

| 사용시 주의할 점

■  장애가 났을 경우 그에 대비한 운영 플랜이 세워줘야 함

Redis는 인메모리 데이터 저장소로서 서버에 장애가 났을 경우 데이터 유실이 발생합니다. 따라서 위의 Snapshot과 AOF 기능을 통한 복구 시나리오가 제대로 세워져 있어야 데이터 유실에 대비한 사고에 대처할 수 있습니다.

■  캐시 솔루션으로 사용할 시 잘못된 데이터가 캐시되는 것을 방지, 예방해야 함

회사에서 Redis를 운영 중 전에 개발자의 실수로 작성된 로직으로 캐시 데이터가 잘못 캐싱되어 올바르지 않은 데이터가 FETCH되어 한동안 데이터가 꼬이는 일이 있었습니다. 레디스와 캐싱하고자 하는 데이터 저장소의 데이터가 서로 일치하는 지 주기적인 모니터링과 이를 방지하기 위한 사내 솔루션을 개발하는 것이 좋습니다

출처: https://engkimbs.tistory.com/869 [새로비]