Blog612

두 자리 연도(yy)(1)	네 자리 연도(yyyy)	표준	입/출력(3)
-	0 또는 100(1, 2)	기본값	mon dd yyyy hh:miAM(또는 PM)
1	101	미국	mm/dd/yyyy
2	102	ANSI	yy.mm.dd
3	103	영국/프랑스	dd/mm/yy
4	104	독일	dd.mm.yy
5	105	이탈리아	dd-mm-yy
6	106 (1)	-	dd mon yy
7	107 (1)	-	Mon dd, yy
8	108	-	hh:mi:ss
-	9 또는 109(1, 2)	기본값 + 밀리초	mon dd yyyy hh:mi:ss:mmmAM(또는 PM)
10	110	미국	mm-dd-yy
11	111	일본	yy/mm/dd
12	112	ISO	yymmdd
-	13 또는 113(1, 2)	유럽 기본값 + 밀리초	dd mon yyyy hh:mi:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)
-	20 또는 120(2)	ODBC 표준	yyyy-mm-dd hh:mi:ss(24h)
-	21 또는 121(2)	ODBC 표준 (밀리초 포함)	yyyy-mm-dd hh:mi:ss.mmm(24h)
-	126 (4)	ISO8601	yyyy-mm-ddThh:mi:ss.mmm(공백 없이)
	127(6, 7)	ISO8601 (Z 표준 시간대)	yyyy-mm-ddThh:mi:ss.mmmZ(공백 없이)
-	130 (1, 2)	회교식(5)	dd mon yyyy hh:mi:ss:mmmAM
-	131 (2)	회교식(5)	dd/mm/yy hh:mi:ss:mmmAM

¹ 이러한 스타일 값은 비결정적 결과를 반환합니다. 모든 (yy)(두 자리 연도) 스타일과 (yyyy)(네 자리 연도) 스타일의 하위 집합을 포함합니다.

² 기본값(style이 0 또는 100, 9 또는 109, 13 또는 113, 20 또는 120, 21 또는 121인 경우)은 항상 네 자리 연도(yyyy)를 반환합니다.

³ datetime으로 변환할 때의 입력이며 문자 데이터로 변환할 때의 출력입니다.

⁴ XML용으로 고안되었습니다. datetime 또는 smalldatetime을 문자 데이터로 변환하는 경우 출력 형식은 앞의 표에서 설명한 것과 같습니다.

⁵ 회교식 달력 시스템에는 여러 가지 형태가 있는데 SQL Server 2005에서는 쿠웨이트 알고리즘을 사용합니다.

⁶ 문자 데이터를 datetime 또는 smalldatetime 형식으로 형변환할 때만 지원됩니다. 날짜 또는 시간 구성 요소만 나타내는 문자 데이터를 datetime 또는 smalldatetime 데이터 형식으로 형변환하면 지정되지 않은 시간 구성 요소는 00:00:00.000으로 설정되고 지정되지 않은 날짜 구성 요소는 1900-01-01로 설정됩니다.

⁷ 선택적인 표준 시간대 표시 Z를 사용하면 표준 시간대 정보가 있는 XML datetime 값을 표준 시간대가 없는 SQL Server datetime 값에 쉽게 매핑할 수 있습니다. Z는 표준 시간대 UTC-0을 표시합니다. 다른 표준 시간대는 + 또는 - 방향의 HH:MM 오프셋으로 나타냅니다. 예: 2006-12-12T23:45:12-08:00.

smalldatetime을 문자 데이터로 변환할 때는 초나 밀리초가 포함된 스타일이 해당 위치에 0으로 표시됩니다. datetime 또는 smalldatetime 값을 변환할 때는 알맞은 char 또는 varchar 데이터 형식 길이를 사용하여 필요 없는 날짜 부분을 잘라낼 수 있습니다.

--------------------------------------
From. http://blog.naver.com/mininuke7303/50023815808

 SELECT U.TABLESPACE_NAME AS "TableSpace"
         , SUM(U.BYTES) / 1024000 AS "Size (MB)"
         , (SUM(U.BYTES) - SUM(NVL(F.BYTES,0))) / 1024000 AS  "Used (MB)"
         , TRUNC(100 - (SUM(NVL(F.BYTES,0)) / SUM(U.BYTES)) * 100,2) "Used (%)"
         , (SUM(NVL(F.BYTES,0))) / 1024000 "Free (MB)"
         , TRUNC((SUM(NVL(F.BYTES,0)) / SUM(U.BYTES)) * 100,2) "Free (%)"
  FROM DBA_FREE_SPACE F, DBA_DATA_FILES U
 WHERE F.FILE_ID(+) = U.FILE_ID
 GROUP BY U.TABLESPACE_NAME
 ORDER BY U.TABLESPACE_NAME;

SYSTEM 계정으로 실행해야 함.
해당 테이블 스페이스의 모든 블럭들을 합산하여 계산.

오라클 데이터베이스를 설치한 후 자동으로 생성되는 사용자 계정은 다음과 같다.

SYS : 오라클 슈퍼 사용자로 데이터베이스에서 발생하는 모든 문제를 처리할 수 있는 권한을 가짐.
SYSTEM : 오라클 데이터베이스를 유지. 보수할 때 사용하는 사용자.
                 SYS와 달리 데이터베이스를 생성할 권한을 가지지 않음.
SCOTT : 샘플 사용자. 일명 수컷호랑이

1. 오라클 사용자 생성하기

오라클 사용자를 생성하기 위해서는 사용자 암호에 관련된 사항, 권한에 관련된 사항, 저장 구조에 대한 사항과 자원 사용에 대한 사항 등 네 가지 사항을 고려해야 한다.

2. 사용자 암호 설정하기

생성하는 오라클 사용자의 암호는 [IDENTIFIED BY] 절에 의해 설정한다. 예를 들어 TEST 사용자를 생성하고 암호를 TEST123 으로 설정해 보자.....

 SQL> CREATE USER TEST IDENTIFIED BY TEST123;

3. 사용자 암호 변경하기

사용자의 암호를 변경하기 위해서는 ALTER USER 문을 사용하고 사용자를 삭제하려면 DROP USER 문을 사용한다.
만일 사용자가 만들어 놓은 모든 테이블, 인덱스, 뷰 등을 함께 삭제하려면 다음과 같이 CASCADE 절을 사용한다.

 SQL> ALTER USER TEST IDEDTIFIED BY TEST456;      //사용자 암호를 변경

 SQL> DROP USER TEST CASCADE;        //사용자를 삭제한다.

4. 사용자 암호 잠그기/만료하기

데이터베이스 사용자의 암호를 보호하기 위해 암호를 강제로 만료(expire)시키거나 사용자 게정의 사용을 제한할 필요가 있다.
사용자 암호를 만료시키거나 잠글 수 있는 권한은 DBA(데이터베이스 관리자)만이 가지고 있으므로 반드시 SYS 사용자 또는 SYSTEM 사용자로 로그인 해야 한다.
사용자 암호를 만료시키기 위한 구문은 다음과 같다.
사용자 암호를 만료시킨 후 사용자가 데이터베이스에 접속을 시도하면 새로운 암호를 요구한다.

 SQL> ALTER USER TEST

          PASSWORD EXPIRE;                 // 사용자 암호 만료시키기

 SQL> CONNECT TEST/TEST123;

          TEST에 대한 암호를 변경합니다.
          새로운 암호 : *****
          새로운 암호를 다시 입력합니다. : ******
          암호가 변경되었습니다.
          연결 되었습니다.

5. 사용자 프로파일 관리

프로파일(profile) 객체를 사용하면 좀더 체계적이고 다양한 옵션을 사용하여 암호에 대한 보안 기능을 강화할수 있다.
프로파일은 CREATE PROFILE 구뭉을 사용하여 작성한다. CREATE PROFILE 구문의 형식은 다음과 같다.

 SQL> CREATE PROFILE 프로파일이름 LIMIT

                    [RESOURCE_PARAMETER 값]
                    [PASWORD_PARAMETER 값];

SQL> CREATE PROFILE TEST_PROFILE LIMIT

                        FAILED_LOGIN_ATTEMPTS 5
                        PASSWORD_LOCK_TIME 7
                        PASSWORD_LIFE_TIME 30
                        PASSWORD_GRACE_TIME 3

위 예제에서 패스워드를 입력할 때 5회(FAILED_LOGIN_ATTEMPTS)를 실패하면 더 이상 입력할 수 없도록 하고, 그 후 7일간(PASSWORD_LOCK_TIME)은 패스워드를 입력할 수 없도록 했다. 또한 패스워드의 유호 일수(PASSWORD_LIFE_TIME)인 30일이 지나면 3일(PASSWORD_GRACE_TIME) 동안 경고 메시지를 표시하도록 설정 하였다.
생성한 프로파일의 변경은 ALTER PROFILE 문을, 삭제는 DROP PROFILE 문을 사용하며 형식은 다음과 같다.

 ALTER PROFILE 프로파일이름 LIMIT

        [RESOURCE_PARAMETER 값]
        [PASWORD_PARAMETER 값];

 DROP PROFILE 프로파일이름 [CASCADE];

6. 데이터베이스 권한과 역할

오라클 데이터베이스 시스템 권한은 GRANT 문을 사용하여 사용자 또는 데이터베이스 관리자 역할을 부여할 수 있다.

 GRANT [시스템권한 | ALL] TO [사용자 | 역할(ROLE) | PUBLIC]
            WITH ADMIN OPTION ;

GRANT 명령문의 PUBLIC 옵션과 WITH ADMIN OPTION 옵션에 대해 알아보자.
PUBLIC 옵션은 시스템 권한이나 개체 권한을 데이터베이스에 생성되어 있는 모든 사용자에게 부여할 때 사용하는 옵션이다.
사용자에게 권한을 부여할 때 WITH ADMIN OPTION 옵션을 사용하면 사용자가 데이터베이스 관리자가 아님에도 불구하고 자신이
가지는 권한을 다른 사람에게 부여할 수 있다.

(1) SYSTEM 사용자로 접속하고 새로운 사용자(TEST)를 생성하자.

 SQL> CONNECT SYSTEM/MANAGER ;

(2) TEST 사용자에게 CREATE TABLE 권한을 부여하자.

 SQL> GRANT CREATE TABLE TO TEST ;

(3) CREATE SESSION 권한 설정

SQL> GRANT CREATE SESSION TO TEST ;

(4) 시스템 권한의 철회는 REVOKE 문을 사용하여 수행한다.

SQL> REVOKE UPDATE ANY TABLE FROM PUBLIC ;

7. 권한 조회하기

사용자 또는 데이터베이스 역할에 부여된 개체 권한은 데이터 사전 뷰를 사용하여 조회가 가능하다.
데이터 사전 뷰는 데이터베이스만이 조회할 수 있는 뷰와 일반 사용자 개체 권한 뷰 그리고 모든 사용자가 조회할 수 있는 개체 권한 뷰 등이 있다.

(1) DBA_TAB_PRIVS 를 사용한 권한 조회

SQL> SELECT * FROM DBA_TAB_PRIVS WHERE OWNER='SCOTT' ;

(2) DBA_COL_PRIVS 을 사용한 권한 조회

SQL> SELECT * FROM DBA_COL_PRIVS WHERE OWNER='SCOTT' ;

8. 역할

사용자가 데이터베이스에 접속하여 특정 작업을 수행하려면 특정 작업에 대한 권한을 가지고 있어야 한다.
그러나 오라클 데이터베이스 권한은 종류가 다양하므로 각 사용자에게 모두 부여하는 것은 많은 시간이 소요될 뿐만 아니라 사용자가 가지고 있는 권한에 대한 관리가 어렵기 때문에 이를 해결하기 위하여 특정 사용 목적에 따라 권한들을 한데 묶어놓았는데, 이를 역할(ROLE) 이라 한다.

사용자에게 오라클의 역할을 부여하기 위해서는 GRANT 문을, 철회하기 위해서는 REVOKE 문을 사용한다.

 GRANT 역할(ROLE) TO 사용자 ;            // 사용자 역할을 부여

 SQL> GRANT CONNECT, RESOURCE TO TEST ;

 REVOKE 역할(ROLE) FROM 사용자 ;     // 사용자 역할을 철회

 SQL> REVOKE CONNECT, RESOYRCE FROM TEST ;

오라클 데이터베이스 생성 시 제공되는 역할

CONNECT     :  데이터베이스에 접속할 수 있는 권한들을 가진 역할
                      SQLBSQ 스크립트에 의해 생성됨.

RESOURCE   :  오라클 데이터베이스의 기본 개체(테이블, 뷰, 인덱스 등)를 생성. 변경.
                      삭제할 수 있는 권한을 가지는 역할.
                      QLBSQ 스크립트에 의해 생성됨.

DBA              :  오라클 데이터베이스를 관리하기 위해 필요한 권한들을 가진 역할로
                       124개의 권한이 부여되어 있음.
                       SQL.BSQ 스크립트에 의해 생성됨.
                       WITH ADMIN OPTION 을 가진 모든 시스템 권한들이 포함.

SYSDBA        :  데이터베이스 시작과 종료 및 관리를 하기 위해 필요한 역할.

SYSOPER     :  SYSDBA 권한과 데이터베이스를 생성할 수 있는 역할.

* 오라클 데이터베이스 역할 생성하기

역할의 생성은 CREATE ROLE 문을 사용하고, 형식은 다음과 같다.

CREATE ROLE 역할이름

 [NOT IDENTIFIED | IDENTIFIED BY [암호 | EXTERNALLY] ;

하나의 역할을 생성하기 위해서는 다음 3단계의 작업이 필요하다.

- 역할 생성하기
- 역할에 권한 부여하기
- 사용자에게 역할 지정하기

SQL> CREATE ROLE ROLE_KONG ;          // 역할 생성하기

SQL> GRANT CREATE SESSION TO ROLE_KONG ;      

SQL> GRANT SELECT, UPDATE, DELETE ON EMP TO ROLE_KONG ;   // 역할에 권한 부여하기

SQL> GRANT ROLE_KONG TO KONG ;          // 사용자에게 역할 지정하기

9. 역할 활성/비활성 시키기

오라클 데이터베이스 역할에 의해 부여된 권한들은 SET ROLE 문을 사용자에게 활성 시키거나 비활성시킬수 있다.

SQL> SET ROLE ROLE-KONG ;  // ROLE_KONG 역할 이외에 다른 모든 역할들이 비활성 된다.

SQL> SET ROLE NONE ;           // EMP_MANAGER 역할을 제외한 모든 역할을 활성시킨다.

10. 디폴트 역할 설정하기

디폴트(DEFAULT) 역할은 사용자가 오라클 데이터베이스에 접속할 때 자동으로 활성화되는 역할이다.

ALTER USER 사용자이름 DEFAULT ROLE 역할이름

               ALL [EXCEPT 역할이름]

               NONE ;

SQL> ALTER USER KONG DEFAULT ROLE EMP_MANAGER   // ALTER USER 문을 사용하여 사용자(KONG)에게 EMP_MANAGER 역할을 디폴트 역할로 지정.

11. 역할 조회하기

특정 역할을 부여받은 사용자나 권한 등의 정보를 조회하기 위해서 다음과 같은 데이터 사전 뷰를 사용할 수 있다.

역할 조회에 사용하는 데이터 사전 뷰

DBA_SYS_PRIVS        :  사용자와 역할에 부여된 모든 권한 정보 조회.
USER_ROLE_PRIVS    :  사용자에게 부여된 역할 조회.
ROLES_SYS_PRIVS    :  역할에 부여된 시스템 권한 조회에 사용.
ROLE_TAB_PRIVS      :  역할에 부여된 테이블 개체의 권한 정보 조회.
ROLE_ROLE_PRIVS    :  역할에 부여된 다른 역할 조회.
SESSION_ROLE         :  현재 사용자의 세션에서 활성화되어 있는 역할 조회.
DBA_ROLES              :  데이터베이스 내에 존재하는 모든 역할 조회.

SQL> SELECT * FROM USER_ROLE_PRIVS ;

From. http://blog.daum.net/sowebpro

근래에 많은 기업들의 데이터베이스가 대용량화 되면서 이를 효과적으로 관리할 수 있는 방안을 찾는 것이 관리자들의 주요 업무가 됐다. 이를 위한 매우 효과적인 방안 가운데 하나가 파티셔닝이다.

일반적으로 단순한 명령어 위주로만 알려져 있지만 실제 현장에서 접하는 파티셔닝의 효용은 그 이상이다. 익숙한 개념이지만 그동안 제대로 알지 못했던 파티셔닝의 의미와 대표적인 활용 사례를 살펴보자.

필자는 많은 현장 사이트에서 대용량의 가치 있는 데이터들이 놀라운 능력을 보유하고 있는 데이터베이스 안에서 사용자의 무지로 인해 방치돼 있거나 잘못 사용되고 있어 역효과를 일으키는 모습을 많이 보아 왔다. 예를 들어 총 테이블 건수 1억 건이 넘는 상황에서 우리가 어떤 형태로든 건드려야 할 부분이 약 10% 정도라고 할 때 그 테이블 전체를 읽지 않고 1000만 건만 읽을 수 있게 해야 하는 것이 당연하지만 실제로는 그렇지 못한 경우를 많이 보아 왔다.

어떻게 처리해야겠다는 생각도 없이 무조건 명령어(command)부터 날리는 것이다. 그렇다면 필요한 테이블 만을 다루려면 어떻게 해야 할까. 이를 위해 필요한 개념이 바로 테이블 파티셔닝(Table Partitioning)이다.

파티셔닝은 지난 강좌에서 살펴본 사항들과 함께 어떤 자동화된 툴로 절대 해결할 수 없는 부분으로 실제로 어떤 상황에서 파티셔닝이 필요하다고 정형화된 법칙은 없다. 중소 용량의 데이터베이스에서도 상황에 따라 꼭 사용해야 하는 경우가 있고, 초대용량의 경우 파티셔닝을 쓰지 않으면 시스템 자체가 관리되지 않을 수도 있다(필자 역시 컨설팅을 하면서 이 파티셔닝을 이용해 많은 시스템을 효율적으로 운영할 수 있다는 것을 직간접적으로 체험한 바 있다).

그러나 대부분의 파티셔닝 관련 자료들은 형식적으로 파티셔닝의 종류를 나열하고 스크립트 정도를 언급하는 수준이다. 이런 식의 접근은 한계가 명확하다.

오히려 파티셔닝을 올바르게 이용하기 위해서는 먼저 데이터베이스 액세스 방식의 정확한 차이와 장단점 그리고 파티션을 이용한 풀 스캔(full scan)에 대해 정확하게 이해할 필요가 있다. 파티셔닝은 일종의 기능일 뿐이어서 스캔에 대한 정확한 이해없이는 이를 사용할 이유도, 어떻게 사용해야 할지도 전혀 알 수가 없다. 각 스캔 방식의 장단점을 알고 어떤 상황에서 어떤 스캔 방법이 유리한 지를 명확하게 이해해야 그에 대한 보완책으로서 파티셔닝의 개념이 보이기 시작한다.

파티셔닝 세계 입문
대용량 테이블이나 인덱스를 파티셔닝한다는 것은 하나의 Object를 여러 개의 세그먼트로 나눈다는 의미이다. 즉 하나의 테이블이나 인덱스가 동일한 논리적 속성을 가진 여러 개의 단위(partition)로 나누어져 각각이 PCTFREE, PCTUSED, INITRANS, MAXTRANS, TABLESPACE, STORAGE PARAMETER 등 별도의 물리적 속성을 갖는 것이다.

특히 관리해야 할 데이터가 늘어나면 성능과 스토리지 관점에서 문제가 생길 수 있는데, 이를 해결할 수 있는 효율적인 방법 가운데 하나가 곧 파티셔닝이다. 파티셔닝은 보통 다음과 같은 장점을 갖고 있다.

오라클 DBMS에서 제공하는 파티셔닝 방식에는 레인지(range) 파티셔닝, 해시(hash) 파티셔닝, 리스트(list) 파티셔닝, 컴포지트(composite) 파티셔닝(레인지-해시, 레인지-리스트) 등이 있다.

특정 컬럼 값을 기준으로 분할하는 레인지 파티셔닝
레인지 파티셔닝은 어떤 특정 컬럼의 정렬 값을 기준으로 분할하는 것이다. 주로 순차적인(historical) 데이터를 관리하는 테이블에 많이 사용된다. 예를 들면 ‘가입계약’이라는 테이블이 있고 여기에 몇 년 동안의 데이터가 쌓여 있다면, 보통 5년치 데이터만 관리하고 이 가운데 자주 액세스하는 하는 것은 최근 1～2년 정도가 일반적이다.

따라서 이를 년별, 월별로 파티셔닝하고 애플리케이션의 SQL을 조정해 전체 데이터가 아닌 최근 정보를 가지고 있는 파티션만 액세스하도록 하면 전체 데이터베이스의 성능을 향상시킬 수 있다. 일부 사례의 경우 가입계약_1999, 가입계약_2000처럼 월별 또는 년별로 테이블을 따로 만들어 사용하기도 했지만 실제로 쓰는 데 불편한 점이 많고 액세스하는 SQL이 복잡해지는 단점이 있다. 다음은 레인지 파티션을 만드는 DDL(Data Definition Language) 스크립트다.

CREATE TABLE CONTRACT
(I_YYYYMMDD VARCHAR2(8), I_CUSTOMER VARCHAR2(9), …… )
TABLESPACE TBS1
STORAGE (INITIAL 2M NEXT 2M PCTINCREASE 0)
PARTITION BY RANGE (I_YYYYMMDD)
(PARTITION PAR_200307 VALUES LESS THAN (‘20030801’),
PARTITION PAR_200308 VALUES LESS THAN (‘20030901’), …… )

PARTITION BY RANGE (COLUMN_LIST)는 특정 컬럼을 기준으로 파티셔닝을 할 것인지를 결정하는 것이고, VALUES LESS THAN (VALUE_LIST)는 해당 파티션이 어느 범위에 포함될 것인지 상한을 정하는 것이다. PARTITION BY RANGE에 나타나는 COLUMN_LIST를 파티셔닝 컬럼이라고 하며 이 값이 파티셔닝 키를 형성한다.

파티셔닝 컬럼은 결합 인덱스처럼 최대 16개까지 지정할 수 있다. VALUESS LESS THAN에 나타나는 VALUE_LIST는 파티셔닝 컬럼들의 상한 값으로, 여기 지정된 값보다 작은 값만을 저장하겠다는 의미이다. 이런 스크립트에서 지정한 물리적 속성들은 각 파티션들이 생성될 때 개별적으로 물리적 속성을 지정하지 않으면 각 파티션들은 이러한 속성 값을 적용 받게 된다.

오직 성능 향상, 해시 파티셔닝
해시 파티셔닝은 특정 컬럼 값에 해시 함수를 적용해 분할하는 방식으로, 데이터의 관리 목적보다는 성능 향상에 초점을 맞춘 개념이다. 레인지 파티셔닝은 각 범위에 따라 데이터 양이 일정치 않아 분포도가 일정치 않은 단점이 있는데, 해시 파티셔닝을 이런 단점을 보완해 일정한 분포를 가진 파티션으로 나누고, 균등한 분포도를 가질 수 있도록 조율해 병렬 프로세싱으로 성능을 높인다. 실제로 분포도를 정의하기 어려운 테이블을 파티셔닝을 할 때 많이 이용하고 2의 배수 개수로 파티셔닝하는 것이 일반적이다.

해시 파티셔닝으로 구분된 파티션들은 동일한 논리, 물리적 속성을 가지다(단 테이블스페이스(tablespace)는 유일하게 파티션별로 지정할 수 있다). 또한 레인지 파티션과 달리 각 파티션에 지정된 값들을 DBMS가 결정하므로 각 파티션에 어떤 값들이 들어 있는지를 알 수 없다. 그러나 대용량의 분포도가 일정치 않은 테이블을 마이그레이션할 때는 프로그램 병렬 방식과 함께 유용하게 사용할 수 있다. 다음은 해시 파티션을 만드는 DDL 스크립트이다.

CREATE TABLE CONTRACT
( SERIAL NUMBER, CODE VARCHAR2(4), ……)
TABLESPACE TBS1
STORAGE (INITIAL 2M NEXT 2M PCTINCREASE 0)
PARTITION BY HASH(SERIAL)
(PARTITION PAR_HASH_1 TABLESPACE TBS2,
PARTITION PAR_HASH_2 TABLESPACE TBS3, ……)

함께 쓰일 때 더욱 강력한 리스트 파티셔닝
리스트 파티셔닝은 특정 컬럼의 특정 값을 기준으로 파티셔닝을 하는 방식이다. 주로 이질적인(distinct) 값이 많지 않고 분포도가 비슷하며 다양한 SQL의 액세스 패스에서 해당 컬럼의 조건이 많이 들어오는 경우 유용하게 사용된다. 예를 들어 ‘서비스 계약’이라는 테이블이 있고 서비스를 최초 가입한 대리점을 ‘가입 대리점’, 변경사항을 처리한 대리점을 ‘처리 대리점’이라고 한다면 모든 서비스의 가입, 해지, 전환 등의 처리 데이터에는 이 두 대리점이 존재한다. 테이블 구조를 보면 다음과 같다.

CREATE TABLE SERVICE_CONTRACT
(I_YYYYMMDD VARCHAR2(8), I_CUSTOMER VARCHAR2(6),
I_DLR_IND VARCHAR2(2), I_DEALER VARCHAR2(6), ……)

즉 I_DLR_IND(대리점 구분)라는 컬럼이 존재하고 ‘A’일 때는 ‘가입 대리점’, ‘S’일 때는 ‘처리 대리점“이라고 할 때 대부분의 조회 패턴에는 가입 대리점 또는 처리 대리점에 해당하는 값이 들어오기 마련이다. 이럴 때 I_DLR_IND로 리스트 파티셔닝을 한다면 어떨까. 즉 집합의 서브 타입을 분류할 때 리스트 파티션은 매우 유용하다. 지금 예로 든 것은 단편적인 것에 불과하지만 리스트 파티셔닝의 위력은 강력하다. 특히 컴포지트 파티션에서 레인지 파티션과 함께 사용하면 전체 데이터베이스의 성능을 크게 향상시킬수 있다. 다음은 리스트 파티션을 만드는 DDL 스크립트이다.

CREATE TABLE SERVICE_CONTRACT
(I_YYYYMMDD VARCHAR2(8), I_CUSTOMER VARCHAR2(6),
I_DLR_IND VARCHAR2(2), I_DEALER VARCHAR2(6), …….)
TABLESPACE TBS1
STORAGE (INITIAL 2M NEXT 2M PCTINCREASE 0)
PARTITION BY LIST (I_DLR_IND)
(PARTITION PAR_A VALUES (‘A’), PARTITION PAR_S VALUES (‘S’))

PARTITION BY LIST에 나타나는 COLUMN_LIST는 파티셔닝 컬럼으로 파티션 키에 해당하고(단 단일 컬럼만 지정할 수 있다), VALUESS LESS THAN에 나타나는 VALUE_LIST는 파티셔닝 컬럼들의 값이다. 여기에 나타낸 값에 해당하는 행들을 저장하겠다는 의미가 된다.

레인지의 장점을 그대로, 레인지-해시 컴포지트 파티셔닝
레인지-해시 컴포지트 파티셔닝은 레인지 방식을 사용해 데이터를 파티셔닝하고 각각의 파티션 내에서 해시 방식으로 서브 파트셔닝을 하는 방식이다. 서브 파티션이 독립된 세그먼트가 되는 것이 특징으로, 다음과 같은 장점이 있다.

레인지 파티션에서 해당 테이블이 단지 논리적인 구조이고 실제 데이터는 파티셔닝된 세그먼트에 저장됐던 것처럼 컴포지트 파티션에서도 해당 테이블과 파티셔닝된 테이블은 단지 파티셔닝을 위한 논리적인 구조일 뿐이다. 데이터는 가장 하위에 위치한 서브 파티션 영역에 저장된다. 다음은 레인지-해시 컴포지트 파티션을 생성하는 DDL 스크립트이다. PARTITION BY RANGE (I_YYYYMMDD)에 의해 레인지로 파티션을 한 후 SUBPARTITION BY HASH에 의해 서브 파티셔닝을 수행했음을 알 수 있다.

CREATE TABE TB_RANGE_HASH
(I_YYYYMMDD VARCHAR2(8), I_SERIAL NUMBER, SALE_PRICE NUMBER, ……)
TABLESPACE TBS1
STORAGE (INITIAL 2M NEXT 2M PCTINCREASE 0)
PARTITION BY RANGE (I_YYYYMMDD)
SUBPARTITION BY HASH (I_SERIAL)
(PARTITION SALES_1997 VALUES LESS THAN (‘19980101’)
(SUBPARTITION SALES_1997_Q1 TABLESPACE TBS2,
SUBPARTITION SALES_1997_Q2 TABLESPACE TBS3), ……)

레인지-리스트 컴포지트 파티셔닝
레인지-리스트 컴포지트 파티셔닝은 레인지 방식을 사용해 데이터를 파티셔닝하고 각 파티션 안에서 리스트 방식을 이용해 서브 파티셔닝하는 방식이다(이때 서브 파티션은 독립된 세그먼트가 된다). 레인지-리스트 컴포지트 파티션은 레인지-해시 컴포지트 파티션과 비슷하지만 서브 파티션이 리스트 파티션이라는 점이 다르다. 실제 업무에서는 레인지-해시보다 유용한 면이 많다. 다음은 레인지-리스트 컴포지트 파티션을 생성하는 DDL 스크립트이다.

CREATE TABLE TB_RANGE_LIST (
I_YYYYMMDD VARCHAR2(8), I_AGR_IND VARCHAR2(2), I_DELAER VARCHAR2(6), …….)
TABLESPACE TBS1
STORAGE (INITIAL 2M NEXT 2M PCTINCREASE 0 MAXEXTENTS UNLIMITED)
PARTITION BY RANGE (I_YYYYMMDD)
SUBPARTITION BY LIST (I_AGR_IND)
(PARTITION PAR_1997 VALUES LESS THAN (‘19980101’)
(SUBPARTITION PAR_1997_A VALUES (‘A’), SUBPARTITION PAR_1997_A VALUES (‘S’)),
……)

파티션된 인덱스의 참뜻
‘파티션된 인덱스(partitioned index)’라고 하면 대부분의 개발자들은 로컬 인덱스를 떠올린다. 또한 파티션된 테이블에서만 쓰이는 것으로 생각한다. 그러나 이것은 명백한 오산이다. 파티션된 인덱스는 파티션된 테이블과 별개의 것으로, 단지 많은 상호 연관을 갖고 있을 뿐이다. 파티션된 인덱스는 문자 그대로 인덱스를 파티셔닝한 것으로, 해당 테이블이 파티션된 테이블이든 파티션되지 않은(non-partitioned) 테이블이든 상관없이 만들 수 있다.

예를 들면 ‘EMP’ 테이블의 크기가 상당히 크고 파티션되지 않은 일반 테이블일 경우 다음과 같은 과정을 통해 파티션된 인덱스를 만들 수 있다. 이를 ‘Global Prefixed Partitioned Index’라고 부르는데, 파티션 인덱스와 마찬가지로 대용량 데이터 환경에서 성능을 높이고 관리를 편리하게 하기 위해서다.

CREATE INDEX EMP_IDX1 ON EMP (DEPTNO)
GLOBAL
PARTITION BY RANGE (DEPTNO)
(PARTITION PAR_10 VALUES LESS THAN (‘20’) TABLESPACE TBS1,
PARTITION PAR_20 VALUES LESS THAN (‘30’) TABLESPACE TBS2,
PARTITION PAR_30 VALUES LESS THAN (‘40’) TABLESPACE TBS3,
PARTITION PAR_40 VALUES LESS THAN (‘50’) TABLESPACE TBS4,
PARTITION PAR_MAX VALUES LESS THAN (MAXVALUE) TABLESPACE TBS5)

파티션된 인덱스가 유용한 이유는, 앞서 파티션의 개념에서 설명한 것처럼 하나의 인덱스를 여러 개의 독립적인 물리 속성을 가진 세그먼트로 나누어 생성, 관리할 수 있기 때문이다. 오라클 DBMS에서 제공하는 인덱스는 글로벌/로컬 인덱스와 Prefixed/Non-Prefixed 인덱스로 분류된다.

파티션된 인덱스와 일반 인덱스 사이의 차이점은 파티션 테이블과 일반 테이블의 그것과 동일하다. 인덱스는 인덱스 컬럼과 Rowid 순으로 값이 정렬되는데, 이런 특성은 파티션 인덱스에서도 동일하다. 많은 개발자들이 파티션된 인덱스는 전체 테이블 값이 정렬되지 않는다고 생각하지 하지만 이것은 사실과 다르다. 글로벌 파티션된 인덱스의 경우 테이블에 대해 값 정렬이 보장돼 있으며, 인덱스도 파티션별로 독립적으로 관리할 수 있다. 두 가지 방식의 차이는 <그림 1>과 같다.

<그림 1> 파티션된 인덱스와 파티션되지 않은 인덱스의 차이

파티션되지 않은 인덱스는 하나의 루트(root) 노드에서 리프(leaf) 노드까지 전체적인 밸런스를 유지하는 구조이고, 파티션 인덱스는 파티션 별로 독립적인 루트 노드와 리프 노드를 갖고 있음을 알 수 있다. 따라서 파티션되지 않으면 대용량 테이블에서는 글로벌 인덱스의 깊이(depth)가 매우 깊어질 수 있는 단점이 있다.

반면 파티션된 인덱스는 각 파티션별 깊이가 일반 인덱스의 깊이보다 얕고 인덱스도 파티션 별로 할 수 있어 병렬 프로세싱을 이용한 인덱스 관리에 매우 효과적이다.

그렇다면 글로벌 인덱스와 로컬 인덱스는 어떤 차이가 있는 것일까? 많은 개발자들이 파티션됐는지 여부로 판단하지만 이것은 잘못된 생각이다. 앞서 설명한 것처럼 글로벌 인덱스도 파티셔닝할 수 있으며, 이를 파티션별로 관리할 수도 있다. 글로벌 인덱스와 로컬 인덱스의 가장 큰 차이는 ‘정렬’이다. 즉 글로벌 인덱스는 테이블 전체에 대해 인덱스된 컬럼과 Rowid 순으로 정렬되고, 로컬 인덱스는 해당 파티션 내에서만 인덱스된 컬럼과 Rowid 순으로 정렬된다.

또한 로컬 인덱스는 ‘Local’이라는 말에서 알 수 있듯이 지역적인 인덱스로, 해당 테이블(base table)의 파티션 키로 파티셔닝된 인덱스다. 일반적으로 로컬 인덱스의 구성 컬럼에 반드시 파티션 키가 포함돼야 가능한 것으로 알려져 있지만 로컬 인덱스에는 파티션 키가 포함되어 있지 않아도 사용할 수 있다. 다음 예제를 보자. PACKAGE_DLR_IDX1 인덱스의 구성 컬럼에 테이블 파티션 키인 I_DLR_IND가 포함되지 않아도 검색조건에 I_DLR_IND = ‘C’라는 검색 조건이 있기 때문에 해당 파티션의 로컬 인덱스를 이용하는 것을 알 수 있다.

select

*from PACKAGE_DLR

where i_package = ‘AAA’ and i_dlr_ind = ‘C’

Operation Object Name PStart PStop SELECT STATEMENT Hint=CHOOSE TABLE ACCESS BY LOCAL INDEX ROWIDPACKAGE_DLR 3 3 INDEX RANGE SCAN PACKAGE_DLR_IDX 3 3

글로벌 인덱스는 전역적인 인덱스로, 기본적으로는 파티션되지 않은 인덱스이다. 대부분의 개발자들은 글로벌 인덱스를 파티셔닝해 사용할 생각을 하지 못하는데, 대용량 테이블에서 인덱스 관리의 효율성을 높이고 인덱스 검색 성능을 높이기 위해서는 이를 파티셔닝하는 것이 좋다. 글로벌 인덱스는 기본 테이블의 파티션 키와 무관하게 파티셔닝하는 것으로 설사 기본 테이블의 파티션 키로 글로벌 인덱스를 파티셔닝했다고 해도 로컬 인덱스처럼 동일파티셔닝(equipartitioning)된 개념이 아니므로 테이블 DDL시 전체 인덱스를 다시 생성해야 한다.

그렇다면 글로벌 파티션 인덱스의 인덱스 컬럼 값은 어떻게 전체 테이블에 대해 정렬을 보장하는 것일까. 예를 들어 5000만 건의 파티션되지 않은 EMP 테이블을 부서번호에 따라 파티셔닝했다고 가정하면 다음과 같다.

CREATE INDEX EMP_IDX1 ON EMP (DEPTNO)
GLOBAL
PARTITION BY RAGE (DEPTNO)
(PARTITION PAR_10 VALUES LESS THAN (‘20’) TABLESPACE TBS1,
PARTITION PAR_20 VALUES LESS THAN (‘30’) TABLESPACE TBS2,
PARTITION PAR_30 VALUES LESS THAN (‘40’) TABLESPACE TBS3,
PARTITION PAR_40 VALUES LESS THAN (‘50’) TABLESPACE TBS4,
PARTITION PAR_MAX VALUES LESS THAN (‘MAXVALE’) TABLESPACE TBS2,

<그림 2> Global Prefixed Partitioned 인덱스

<그림 2>는 Global Prefixed Partitioned 인덱스의 구조다. Prefixed와 Non-Prefixed는 인덱스 파티셔닝 키가 인덱스의 선두 컬럼으로 오는가 그렇지 않은가의 차이가 있다. <그림 2>에서도 ‘Prefixed’란 인덱스의 파티션 키(DEPTNO)가 인덱스 선두 컬럼(DEPTNO)이 되는 것을 알 수 있다. 글로벌 인덱스의 경우 모든 인덱스 컬럼 값이 정렬돼 있다. 각 인덱스 파티션의 루트 블럭(root block)에 들어가는 값들이 인덱스 파티션에 따라 정렬되기 때문에 자연적으로 리프 블럭(leaf block)에 들어가는 모든 값들도 정렬되는 것이다. 반면 Global Non-Prefixed 인덱스를 파티셔닝하면 레인지 파티셔닝 방식으로만 가능하다. 이것은 정렬 때문인데, 레인지 파티션은 정렬 기능을 이용해 파티셔닝 키 자체를 생성하는데 반해 다른 파티셔닝 방식은 정렬과 상관없이 수행하기 때문이다.

로컬 인덱스는 Prefixed 인덱스와 Non-Prefixed 인덱스를 모두 지원한다. 로컬 인덱스는 기본적으로 현재 테이블의 파티션 키가 인덱스의 파티션 키가 되기 때문에 인덱스 컬럼에 현재 테이블의 파티션 키가 포함되지 않아도 인덱스를 생성할 수 있다. 또한 인덱스 컬럼 값의 정렬이 전체 테이블에 대해 보장된 것도 아니기 때문에 인덱스 파티션 키가 인덱스의 선두 컬럼이 될 필요가 없다. 또한 Non-Partitioned 인덱스이든 파티션 인덱스든 상관없이 인덱스를 이용하고자 할 때는 무조건 인덱스 파티션 키를 조회해야 하는 글로벌 인덱스와 달리 로컬 인덱스는 조회 검색조건에 파티션 키가 들어올 수도 있고 들어오지 않을 수도 있다.

대용량 DB 테이블과 인덱스 전략
파티션 인덱스 전략은 파티션 테이블과 밀접하게 연관되어 수립해야 하지만 여기서는 파티션 인덱스를 위주로 이야기를 풀어본다. 먼저 인덱스 크기에 대한 논의는 기본적으로 테이블보다는 훨씬 작게 생성, 관리하는 것이 원칙이다. 따라서 중소 용량의 데이터베이스 환경에서는 파티션 인덱스의 유용성을 따질 필요가 없다. 단 중소 용량의 데이터 환경일 경우에서도 테이블이 파티셔닝돼 있다면 파티션 인덱스를 고려해야 한다. 또한 기본적으로 파티션되지 않은 인덱스(일반 인덱스) 전략을 기본으로 해 테이블이 파티셔닝 된 경우와 인덱스를 파티셔닝했을 때의 장점을 비교해 보아야 한다.

먼저 테이블 파티션 키가 항상 ‘=’로 들어오는 경우 또는 파티션 범위가 크지 않은 경우에는 로컬 인덱스가 최상이다. 인덱스 컬럼의 순서와 구성은 액세스 패스에 따라 생성하면 되지만 최대한 가볍게 생성하는 것이 좋다. 기본 테이블의 파티션 키는 반드시 포함될 필요가 없으나, 테이블이 레인지 파티션이고 한 파티션 범위 안에서 파티션 키의 분포도가 좋을 경우 이를 포함하는 것을 고려해 볼만하다. 이렇게 하면 각 파티션당 인덱스가 파티션되지 않았을 때보다 가벼워지고 데이터 마이그레이션을 할 때도 테이블 파티션과 인덱스 파티션이 동일하므로 exchange, add, drop, split 등 파티션별 관리도 용이하다.

또한 빠른 응답 시간을 요구하는 환경에서 대용량 파티션 테이블의 조회 조건에 파티션 키가 들어오지 않을 가능성이 있다면 파티션 글로벌 인덱스를 고려해 볼만하다. 이렇게 하면 파티션되지 않은 글로벌 인덱스와 달리 레인지 파티션 별로 인덱스가 가벼워지는 장점이 있고, 레인지 파티션 별로 인덱스 split와 rebuild 명령을 독립적으로 수행할 수 있다. 컬럼 분포도에 따른 파티셔닝이나 민감한(critical)한 상수 레인지에 대해서는 파티션을 독립적으로 생성해 인덱스 크기를 줄임으로써 인덱스 검색 시간을 줄일 수 있는 이점도 있다.

exchange는 파티션된 테이블의 특정 파티션과 파티션되지 않은 일반 테이블 간의 구조를 서로 바꾸는 것으로, 대용량의 파티션된 테이블을 관리하는 데 상당한 효과가 있다. <그림 2>와 같이 데이터가 없는 새로운 데이터 테이블과 데이터가 들어 있는 파티션 2를 exchange하면 파티션 2에 해당하는 디렉토리 정보가 새로운 데이터로 바뀌고 새 테이블 데이터에는 데이터가 들어간다. 이것은 실제 데이터가 이동하는 것이 아니라 데이터를 저장하는 테이블 정보만을 업데이트하는 것이다. 한 가지 주의할 점은 exchange하고자 하는 파티션과 테이블의 구조가 같아야 하고 속성들의 특성도 같아야 한다는 사실이다.
exchange의 기본적인 문법은 다음과 같다.

<그림 3> 대용량 DB에서 exchange 작업

한편 파티션된 대용량 테이블에 split 함수를 실행하면 많은 시간이 걸린다. 이럴 때 exchange 기능을 이용하면 빠르고 안전하게 작업할 수 있다. <그림 4>에서 보는 것처럼 split를 해야 하는 파티션을 exchange에 의해 빈 공간으로 만든 다음 split을 하고 다시 데이터를 채우기 위해 split하는 것이다. 이렇게 하면 대용량의 데이터라도 매우 빠른 시간내에 split 작업을 수행할 수 있다.

<그림 4> 대용량 DB에서 split 작업

한편 대부분의 DBA들과 개발자들은 동일한 테이블을 생성할 때 create table ～ as select 구문을 이용한다. 대용량의 데이터일 경우 parallel 옵션을 줘 생성하기도 한다. 만약 1억 건의 테이블을 그대로 생성한다고 할 때 어떤 방법이 효과적일까. 이렇게 파티션된 대용량 테이블을 생성할 때는 exchange, program parallel 방법을 사용하는 것이 바람직하다.

<그림 5> 동일 테이블을 만들 때

<그림 5>는 이 과정을 도식화한 것이다. 먼저 생성할 TB_PART_1 테이블의 빈껍데기를 만든다. 대용량의 파티션된 테이블의 파티션 각각을 create table ～ as select 구문의 parallel 옵션을 이용해 각 테이블로 생성한다. 이후 미리 생성해 놓은 TB_PART_1 테이블의 파티션과 만들어 놓은 테이블들을 exchange하는 것이다. 이때 파티션별로 200105.sql, 200106.sql, 200107.sql…… 형식으로 만들어 놓고 이 프로그램들을 동시에 실행하면(program parallel) 극적인 효과를 볼 수 있다.

이번엔 데이터 마이그레이션에 대해 살펴 보자. 원격으로 데이터를 옮겨야 할 때 보통 database link를 이용한다. 네트워크를 통해 데이터를 옮기면 직렬(serial)로 데이터가 이동되므로 속도가 현저하게 떨어지기 때문이다. 따라서 소스 테이블을 파티셔닝하고 해당 파티션을 액세스하는 프로그램을 각각 띄워 병렬 프로세싱을 하게 되면 매우 빠른 속도로 데이터를 옮길 수 있다.

소스 테이블을 파티셔닝할 수 있는 상황이라면 테이블의 분포를 보고 레인지나 리스트 방식으로 파티셔닝할 수 있고, 일정한 분포가 존재하지 않는 테이블이라면 해시 파티셔닝으로 분포도를 고르게 나눈 다음 해당 파티션을 읽는 뷰를 액세스해 데이터를 옮기는 것이 좋다.

예를 들어 다음은 중대형 정도 크기인 약 2700만 건의 회원 테이블을 옮기는 DDL 스크립트다. 앞서 언급한 대로 이를 바로 database link를 이용해 처리하면 네트워크의 속도가 떨어져 엄청난 시간이 소요된다. 그러나 이것을 일반 테이블을 여러 개로 파티션을 나누어서 파티션과 병렬 처리하면 성능이 크게 향상된다. 작업 순서는 다음과 같다.

이제 다음과 같이 소스 테이블 뷰 생성한 후

다음과 같이 프로그램 패러럴(program parallel) 작업을 동시에 실행한다.

이것은 단적인 예에 지나지 않는다. 활용할 수 있는 사례는 얼마든지 있을 것이다. 한편 인덱스는 전체 데이터에 대해 해당 컬럼의 값으로 정렬하기 때문에 대용량 테이블의 경우 create, rebuild 명령을 실행할 때 많은 시간이 필요하다. 이때 파티션된 인덱스를 만들면 인덱스의 생성과 관리를 더 활용적으로 할 수 있다. 다음은 파티션된 인덱스를 Unusable로 생성한 사례다(로컬/글로벌 파티션된 인덱스).

먼저 파티션 인덱스를 ‘unusable’ 옵션을 이용해 생성한다. 실제 데이터를 정렬해 만드는 것이 아니라 일종의 껍데기를 만드는 과정이다. 이제 앞서 살펴본 병렬 처리를 이용해 여러 파티션을 동시에 rebuild를 하면 대용량 데이터라도 빠른 시간에 인덱스를 생성할 수 있다.

이제 파티션별로 index1.sql, index2.sql 등을 독립적으로 병렬 실행한다.

지금까지 테이블 파티셔닝에 대해 다뤄봤다. 자동화된 성능관리 툴로 커버할 수 없는 영역을 살펴보고 있으나 가장 중요한 것은 데이터베이스 액세스 개념에 대해 정확하게 이해하는 것이다. 많은 사람들이 파티셔닝을 알고 있지만 정확하게 사용하고 있지 못하는 현실이 아타까울 때가 많다. 그러나 이 점은 역설적으로 파티셔닝의 매력이기도 하다. 노력하는 데이터베이스 관리자 만이 도전해 볼 수 있는 영역이 바로 ‘파티셔닝’ 분야이기 때문이다.

FROM 호석

*오라클 힌트 SAMPLE
-------------------------------------------------
select   /*+ index( idx_col_1 ) */
    name, age, hobby
from     member
-------------------------------------------------

*오라클 힌트 사용표

INDEX ACCESS OPERATION 관련 HINT
HINT	내용	사용법
INDEX	INDEX를 순차적으로 스캔	INDEX(TABLE명, INDEX명)
INDEX_DESC	INDEX를 역순으로 스캔	INDEX_DESC(TABLE명, INDEX명)
INDEX_FFS	INDEX FAST FULL SCAN	INDEX_FFS(TABLE명, INDEX명)
PARALLEL_INDEX	INDEX PARALLEL SCAN	PARALLEL_INDEX(TABLE명,INDEX명)
NOPARALLEL_INDEX	INDEX PARALLEL SCAN 제한	NOPARALLEL_INDEX(TABLE명,INDEX명)
AND_EQUALS	INDEX MERGE 수행	AND_EQUALS(INDEX_NAME, INDEX_NAME)
FULL	FULL SCAN	FULL(TALBE명)
JOIN ACCESS OPERATION 관련 HINT
HINT	내용	사용법
USE_NL	NESTED LOOP JOIN	USE_NL(TABLE1, TABLE2)
USE_MERGE	SORT MERGE JOIN	USE_MERGE(TABBLE1, TABLE2)
USE_HASH	HASH JOIN	USE_HASH(TABLE1, TABLE2)
HASH_AJ	HASH ANTIJOIN	HASH_AJ(TABLE1, TABLE2)
HASH_SJ	HASH SEMIJOIN	HASH_SJ(TABLE1, TABLE2)
NL_AJ	NESTED LOOP ANTI JOIN	NL_AJ(TABLE1, TABLE2)
NL_SJ	NESTED LOOP SEMIJOIN	NL_SJ(TABLE1, TABLE2)
MERGE_AJ	SORT MERGE ANTIJOIN	MERGE_AJ(TABLE1, TABLE2)
MERGE_SJ	SORT MERGE SEMIJOIN	MERGE_SJ(TABLE1, TABLE2)
JOIN시 DRIVING 순서 결정 HINT
HINT	내용
ORDERED	FROM 절의 앞에서부터 DRIVING
DRIVING	해당 테이블을 먼저 DRIVING- driving(table)
기타 힌트
HINT	내용
append	insert 시 direct loading
parallel	select, insert 시 여러 개의 프로세스로 수행- parallel(table, 개수)
cache	데이터를 메모리에 caching
nocache	데이터를 메모리에 caching하지 않음
push_subq	subquery를 먼저 수행
rewrite	query rewrite 수행
norewrite	query rewrite 를  수행 못함
use_concat	in절을 concatenation access operation으로 수행
use_expand	in절을 concatenation access operation으로 수행 못하게 함
merge	view merging 수행
no_merge	view merging 수행 못하게 함

FROM OKJSP 대용량데이터베이스 스터디, Quick님 제공 [오라클 힌트 사용예 ] / dong6245

1. 정의
인덱스란 테이블 내의 한 개 또는 여러 개의 컬럼 값과 그 값을 포함하고 있는 행의 논리적 주소를 연관시켜 만들어지는 별도의 저장 구조이다. 
해당 데이터의 검색을 위해 전체 테이블을 읽지 않고 해당 행의 주소를 통하여 직접 접근한다.
생성된 인덱스는 경우에 따라서 하나 이상 사용되기도 하고 전혀 사용되지 않을 수도 있다.
Optimizer에 의해 판단되는 액세스 경로는 주어진 조건, 인덱스의 구성, 통계정보, 클러스터링, Select List, 사용자의 코딩, 힌트(Hint), Optimizer Mode등에 따라서 다양하게 나타나며 이 결정된 액세스 경로에 따라 수행 속도가 좌우된다.
그러므로 양호한 액세스 경로를 보장 받으려면 Optimizer의 액세스 생성 원리에 대한 이해를 바탕으로 좋은 액세스 경로가 생성될 수 있도록 테이블을 설계하고, 적절하고 종합적인 경우를 대비한 인덱스의 지정, 넓은 범위의 처리나 조인의 효율성을 향상하기 위한 클러스터링, 인덱스 적용 원칙에 맞는 SQL의 코딩, 효율적인 SQL의 구사, 통계자료의 주기적인 재생성, 힌트나 Suppressing 기능을 활용하여 Optimizer를 제어하는 등의 노력이 필요하다.

2. 절차

a. 기본 인덱스 설계 (설계 단계)
    1)PK, FK 인덱스 생성
    2)주요 화면에 대한 SQL 구문을 분석하여 인덱스 설계

b. 인덱스 추가 설계 (개발 단계)
    1)SQL 구문 정리 및 조건절 분석
    2)인덱스 적용시 고려 사항
    3)인덱스 크기 산정

c. 인덱스 개선(튜닝)
    1)문제되는 테이블의  SQL 구문 수집
    2)각 SQL이 사용하는 인덱스를 조사하여 개선안 도출
    3)인덱스 개선안 테스트 및 적용

d. 기타 인덱스 활용
   1)Descending Index
   2)Reverse-Key Index
   3)Function-Based Index
   4)Bitmap Index
   5)Index-Organized Table

2.1 기본 인덱스 설계 (설계 단계)

설계단계에서는 데이터가 없고 화면의 내용이 변하므로 정확한 인덱스를 설계할 수 없다.
따라서 변하지 않는 중요 인덱스만을 선정한 후, 80% 이상 개발이 완료된 시점에 인덱스를 재 선정한다.

1)PK, FK 인덱스 생성
-.인덱스 선정에 있어서 기본적으로 Primary Key는 반드시 포함시킨다.
-.Foreign Key는 Foreign Key Constraint를 부여한 경우 Foreign Key 인덱스를 생성한다. (PK와 중복되는 경우는 PK 사용)

2)주요 화면에 대한 SQL 구문을 분석하여 인덱스 설계
-.주요 화면과 SQL 구문 선정 과정은 고객/업무담당자와 함께 한다.
-.자주 조합되어 사용되는 컬럼: 결합인덱스를 생성하는 것이 효율적임.
-.기본키 및 외래키 등 조인의 연결고리가 되는 컬럼
-.선택이나 조인의 기준 컬럼(SQL문의 Where절)
-.ORDER BY,  GROUP BY,  UNION,  DISTINCT 및 SORT조작 시 사용되는 컬럼
-.SQL 구문에 대한 인덱스 선정작업은 구축 작업 완료 후 재작업 한다.
-.분석/설계 단계는 인덱스 크기를 별도로 산정하기보다는 산정된 테이블 크기의 50%를 사용한다.

2.2 인덱스 추가 설계 (개발 단계)

1)SQL 구문 정리 및 조건절 분석
-.코딩된 SQL문을 발췌, 정리하여 SQL 구문을 프로그램별 테이블별로 아래 예와 같이 작성하고, 작성된 내용을 이용하여 조건절을 분석하고, 인덱스 설계서를 작성하여 기본 인덱스 설계와 비교하고 빠진 인덱스를 생성한다
-.조사 대상 프로그램에 대하여 사용하는 SQL 구문 FROM절과 WHERE절을 사용된 그대로 기입한다. (가능하면 전체 프로그램을 대상으로 함)
-.컬럼명은 조건절에서 사용하는 모양 그대로 작성되어야 한다. 즉 컬럼에 함수를 사용했을 경우 함수를 사용한 모습 그대로 작성한다.
-.비교 연산자는 인덱스 생성시 가장 중요한 고려 사항이므로 정확하게 기재한다.
-.A UNION B, A MINUS B, A INTERSECT B 등과 같은 연산은 A와 B를 독립된 SQL문으로 간주한다.
-.서브 쿼리와 Inline View는 독립된 SQL문으로 간주하여 정리한다.
-.WHERE절에 ORDER BY, GROUP BY절 다음의 컬럼들을 순서대로 기입한다

2)인덱스 적용시 고려 사항
-.자주 조합되어 사용되는 경우에는 결합 인덱스를 생성하되, 컬럼 순서 선정에 주의한다.
   -항상 사용되는 것이 첫번째 컬럼
   -항상 사용되는 것이 많다면 ‘=’로 사용되는 것이 첫번째 컬럼
   -분포도가 좋은 컬럼을 우선
   -자주 사용되는 정렬 순서 고려
-.인덱스의 개수는 테이블의 사용 형태에 따라 적절하게 생성하여야 하며, 지나치게 많은 인덱스는 오버헤드를 발생시키므로 특별한 경우가 아니면 5개는 넘지 않도록 한다.
-.중복이 대단히 많은 컬럼은 인덱스에서 제외한다.
-.길이가 40 byte 이상인 컬럼에 대한 인덱스는 피한다.
-.빈번하게 변경되는 컬럼에 대해서는 인덱스 생성을 피한다.
-.정기적으로 Batch 처리를 위해서 필요로 하는 인덱스는 Batch 기간 동안에 생성하고 사용 후 삭제함으로써 인덱스를 유지하기 위한 부하를 최소화하여 온라인 처리의 성능을 향상 시킬 수 있다.
-.인덱스 테이블과 데이터 테이블 I/O수의 합계가 전체 테이블을 스캔하는 I/O수보다 적은 경우에만 성능이 향상된다.

3)인덱스 크기 산정
-.테이블 크기 산정시와 같은 방법으로 별도로 구성된 빈 테이블을 사용하여 인덱스 길이를 구한다.
-.헤더 사이즈를 감안하여 인덱스의 row 평균길이를 구한다.
-.데이터 건수(테이블과 같음)와 길이를 바탕으로, 계산 공식이 반영된 Excel 양식을 이용하여 자동으로 산정한다.

2.3인덱스 개선 ( 튜닝 )

1)문제되는 테이블의 SQL 구문 수집
-.문제되는 테이블을 선정한다.
   -가장 많이 사용되는 테이블 선정
   -데이터 양이 많고 증가량이 큰 테이블 선정
   -인덱스가 너무 많은 테이블 선정 ( 5 ~ 10개 이상 )
   -한 컬럼이 여러 인덱스에 사용되는 테이블 선정
   -고객 의견 반영
-.테이블이 사용하는 SQL 구문을 수집한다.
  (SQL구문 ID 부여 및 중요도 체크)

2)각 SQL이 사용하는 인덱스를 조사하여 개선안 도출
-.SQL 구문의 PLAN 조사 ( autotrace 또는 tkprof 이용 )
-.SQL 구문별 사용 인덱스 컬럼 조사하여 Matrix 작성
   -2006년 3월7일자 인덱스matrix.. 참조(글목록 참조)
-.Matrix를 이용하여 인덱스 개선안 도출
   -사용 빈도수가 적은 인덱스 삭제
   -인덱스 컬럼 순서 조정으로 더 효율적인 경우 순서 조정
   -Full Table Scan 하는 SQL 구문은 인덱스 생성을 고려
   -비슷한 인덱스 병합

3)인덱스 개선안 테스트 및 적용
-.인덱스 개선을 위한 테스트 환경을 마련하여 개선안 테스트
-.개선 전후의 수행시간을 비교하여 개선안 조정
-.인덱스 개선안이 많고 모두 적용하기가 무리이면 확실한 개선 사항이 도출되는 인덱스부터 순차적으로 적용
-.개선안 적용 후 모니터링

2.4기타 인덱스 활용

1)Descending Index
-.DB2와의 호환성을 위해서 Oracle 7점대 버전에서도 “DESC”라는 syntax가 제공되었지만, 이는 실제로 Descending으로 생성되는 것은 아니었다.
-.Oracle 8i부터는 실제로 Index를 Descending으로 생성할 수 있다.

2)Reverse-Key Index
-.Reverse-Key Index란 Index 구성 컬럼의 순서는 그대로 유지하면서 각 컬럼의 데이터(ROWID 제외)를 바이트 단위로 역순으로 인덱싱하는 것을 말한다. (Oracle 8.0부터 적용 가능함)
-.이미 생성된 인덱스의 경우에도 Rebuild를 통하여 변경할 수 있다
-.장점
   ·적은 수의 leaf block에 변경 작업이 집중되는 경우 발생할 수 있는 성능의 저하 회피 가능
   ·Ascending한 자료 축적이 일어나고 낮은 value들이 삭제되어지는 테이블의 경우, 불균형(skewed) 인덱스를 방지함.
   ·RAC 환경에서, 서로 다른 인스턴스에서 같은 Index Block에 Insert 작업시 발생하는 ping을 줄일 수 있음
-.단점
   ·Range Scan 불가능
   ·비트맵 인덱스에 적용 불가
   ·Index-Organized Table에 적용 불가

3)Function-Based Index
-.일반적인 경우, WHERE 조건에서 인덱스 컬럼에 연산을 사용하게되면 인덱스를 사용할 수 없다. Oracle8i부터는 조건절에서의 연산이 필수적인 경우에 인덱스를 사용하려면 Function-Based Index 로 해결할 수 있다.
-.Function-Based Index를 사용하려면 다음과 같이 System Privilege를 부여해야 한다.
   GRANT QUERY REWRITE TO user_name ;
 생성 방법 :
   CREATE INDEX IX_AAA01_02
          ON TB_AAA01 ( REVENUE – COST ) ;
 생성된 index 사용 :
   ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE ;
   SELECT ORDID
      FROM  TB_AAA01
    WHERE ( REVENUE – COST ) > 1000 ;
   ALTER SESSION SET QUERY_REWRITE_ENABLED = FALSE ;
-.Function-Based Index는 Bitmap Index로도 생성 가능하다.

4)Bitmap Index
-.비트맵 인덱스는 Index 컬럼의 각 데이터 종류별로 Bitmap Set을 구성하여 Bit 값으로 Row의 위치를 관리하는 것으로, Oracle 7.3 버전부터 사용 가능하며 Oracle8i에서는 Local Index에 한해서 파티션도 가능하다.
-.컬럼의 분포도에 따라 Bitmap Set 개수가 결정되므로 컬럼의 분포도가 양호할수록 저장 공간이 많이 필요하다. (분포도가 불량일수록 유리함. )
-.Bit 연산으로 인해 Row단위가 아닌 Block-Level Lock이 발생하므로 DML 작업이 적은 업무에 적용하도록 한다.
-.분포도가 불량하고 다양한 컬럼과 OR 연산을 많이 하는 경우에 사용한다.
-.컬럼의 종류가 10 ~ 5개 이하인 경우에 적용한다. ( 성별, 코드, 등급, 유무 등의 컬럼 )
-. “=”, IN ( ) 연산에 유리하며, 범위( <,>,BETWEEN) 연산 및 패턴 조회(LIKE)시에는 인덱스를 사용할 수 없다.
-.대용량 배치 작업시 disable 상태에서 작업하는 것이 권고된다.
-.Bitmap Index의 저장 공간 측정
   -Index 컬럼의 Unique 정도에 따라 크기가 다르므로 실제 값의 종류(Cardinality)가 중요함.
   -Unique 컬럼을 Bitmap Index로 설정할 경우 B*-tree Index보다  1.5 ~ 1.7배 더 많이 소요되고 값의 종류가 1/1000인 경우에는 B*-tree Index 보다 0.15 ~ 0.10 배 정도 적게 소요됨. (추정치)
   -Bitmap Index 크기 = ( A x 150 ) x (B*TREE 인덱스 크기)
 가정) A : 컬럼 분포도 ( 1 / 컬럼 종류 수 )
      분포도 1/1000 기준으로 0.15배 적용 ( 0.15 x 1,000 = 150 )
      B*-tree 인덱스 크기 : 정상적인 Index 산정 크기

5)Index-Organized Table
Oracle 8에 추가된 기능으로, 데이터의 물리적 저장 방식이 Primary Key 를 기준으로 B*-tree 형태로 Row가 저장되는 것이다. 따라서 Index-Organized Table은 ROWID 정보를 갖지 않는다
-.장점
   ·별도의 PK Index의 저장 공간이 필요 없음.
   ·PK 컬럼에 대한 Range Scan, =, IN 연산시 빠름.
   ·Table Full Scan시 데이터가 Ordering 되어 출력됨
-.제약사항
   ·Primary Key가 반드시 있어야 하며, 별도의 Unique Index 추가 불가능. (8.0에서는 Non-unique  Index도 추가 불가능)
   ·Clustering, 병렬 처리, 복제, 분산 불가. (Partition은 가능)
   ·LONG 컬럼 저장 불가. (8i에서는 LOB은 저장할 수 있지만, 이 경우 Partition을 할 수 없음)
   ·Insert 혹은 PK 컬럼 Update시 B*-tree에 대한 부하 발생
-.적용범위
   ·코드 테이블과 같이 수정이 별로 없고 PK 컬럼으로만 Access하는 경우 권고

FROM 네이버 까페 어딘가에서 스리슬쩍~

오라클 정규식을 찾던중에 레퍼런스 페이지에서 발견~
10g부터 지원이 된다하니.... 9i까지는 삽질의 세계로~

Writing Better SQL Using Regular Expressions

By Alice Rischert

The regular expressions feature in Oracle Database 10g is a powerful tool for manipulating textual data

A new feature of Oracle Database 10g vastly improves your ability to search and manipulate character data. This feature, regular expressions, is a notation for describing textual patterns. It has long been available in many programming languages and a number of UNIX utilities.

Oracle's implementation of regular expressions comes in the form of various SQL functions and a WHERE clause operator. If you are unfamiliar with regular expressions, this article can give you a glimpse into this new and extremely powerful yet seemingly cryptic capability. Readers already familiar with regular expressions can gain an understanding of how to apply this functionality within the context of the Oracle SQL language.

What Is a Regular Expression?

A regular expression comprises one or more character literals and/or metacharacters. In its simplest format, a regular expression can consist only of character literals, such as the regular expression cat. It is read as the letter c followed by the letters a and t and this pattern matches strings such as cat, location, and catalog. Metacharacters provide algorithms that specify how Oracle should process the characters that make up a regular expression. When you understand the meaning of the various metacharacters, you will see that regular expressions are powerful for isolating and replacing specific textual data.

Data validation, identification of duplicate word occurrences, detection of extraneous white spaces, or parsing of strings are just some of the many uses of regular expressions. You can apply them in order to validate the formats of phone numbers, zip codes, email addresses, Social Security numbers, IP addresses, filenames and pathnames, and so on. Furthermore, you can locate patterns such as HTML tags, numbers, dates, or anything that fits any pattern within any textual data and replace them with other patterns.

Using Regular Expressions With Oracle Database 10g

To harness the power of regular expressions, you can exploit the newly introduced Oracle SQL REGEXP_LIKE operator and the REGEXP_INSTR, REGEXP_SUBSTR, and REGEXP_REPLACE functions. You will see how this new functionality supplements the existing LIKE operator and the INSTR, SUBSTR, and REPLACE functions. In fact, they are similar to the existing operator and functions but now offer powerful pattern-matching capabilities. The searched data can be simple strings or large volumes of text stored in the database character columns. Regular expressions let you search, replace, and validate data in ways you have never thought of before, with a high degree of flexibility.

Basic Examples of Regular Expressions

Before using the new functionality, you need to understand the meaning of some of the metacharacters. The period (.) matches any character (except newline) in a regular expression. For example, the regular expression a.b matches a string containing the letter a, followed by any other single character (except newline), followed by the letter b. The strings axb, xaybx, and abba are matches because this pattern is buried in the string. If you want to exactly match a three-letter string in which the line begins with a and ends with b, you must anchor the regular expression. The caret (^) metacharacter indicates the start of a line, and the dollar symbol ($) designates the end of the line (see Table 1). Therefore, the regular expression ^a.b$ matches the strings aab, abb, or axb. To contrast this approach with the familiar pattern matching available with the LIKE operator, you can express such a pattern as a_b, where the underbar (_) is the one-character wildcard.

By default, an individual character or character list in a regular expression matches just once. To indicate multiple occurrences of a character in a regular expression, you apply a quantifier, also called a repetition operator. If you want a match that starts with the letter a and ends with the letter b, your regular expression looks like this: ^a.*b$. The * metacharacter repeats the preceding match any metacharacter (.) zero, one, or more times. The equivalent pattern with the LIKE operator is a%b, with the percent (%) indicating zero, one, or multiple occurrences of any character.

Table 2 shows the complete list of repetition operators. Notice that it contains specific repetition choices that allow more flexibility than the existing LIKE wildcard characters. If you use parentheses around an expression, effectively creating a subexpression, the subexpression can be repeated a certain number of times. For example, the regular expression b(an)*a matches ba, bana, banana, yourbananasplit, and so on.

Oracle's regular expression implementation supports the POSIX (Portable Operating System Interface) character classes, as listed in Table 3. This means that you can be very specific about the type of character you are looking for. Imagine writing a LIKE condition that looks only for nonalphabetic characters—the resulting WHERE clause could easily become very complex.

The POSIX character class must be enclosed by a character list indicated by square brackets ([]). For example, the regular expression [[:lower:]] matches a lowercase character and [[:lower:]]{5} matches five consecutive lowercase characters.

Besides the POSIX character classes, you can place individual characters in a character list. For example, the regular expression ^ab[cd]ef$ matches the strings abcef and abdef. Either c or d must be chosen.

Most metacharacters inside a character list are understood as literals, with the exception of the caret (^) and the hyphen (-). Regular expressions appear complicated because some metacharacters have multiple meanings, depending on the context. The ^ is just one such metacharacter. If you use it as the first character inside a character list, it means the negation of a character list. Therefore, [^[:digit:]] looks for a pattern consisting of any nondigit character whereas ^[[:digit:]] looks for matches that start with a digit. The hyphen (-) indicates a range; the regular expression [a-m] matches any of the letters a through letter m. But it means the literal hyphen if it is the first character in a character list such as in [-afg].

One of the previous examples introduced the use of parentheses to create a subexpression; they allow you to enter alternates separated by the vertical bar (|) alteration metacharacter.

For example, the regular expression t(a|e|i)n allows three possible alternate characters between the letters t and n. Matches include words such as tan, ten, tin, and Pakistan but not teen, mountain, or tune. Alternatively, the regular expression t(a|e|i)n can also be expressed as a character list, which is t[aei]n. Table 4 summarizes these metacharacters. Although more metacharacters exist, this brief overview is sufficient for understanding the regular expressions this article uses.

The REGEXP_LIKE Operator

The REGEXP_LIKE operator introduces you to regular expression functionality when applied within the Oracle database. Table 5 lists the syntax of REGEXP_LIKE.

The following SQL query's WHERE clause shows the REGEXP_LIKE operator, which searches the ZIP column for a pattern that satisfies the regular expression [^[:digit:]]. It will retrieve those rows in the ZIPCODE table for which the ZIP column values contain any character that is not a numeric digit.

SELECT zip
  FROM zipcode
 WHERE REGEXP_LIKE(zip, '[^[:digit:]]')
ZIP
-----
ab123
123xy
007ab
abcxy

This example of a regular expression consists only of metacharacters—more specifically the POSIX character class digit delimited by colons and square brackets. The second set of brackets (as in [^[:digit:]]) encloses a character class list. As previously mentioned, this is required because you can use POSIX character classes only for constructing a character list.

The REGEXP_INSTR Function

This function returns the starting position of a pattern, so it works much like the familiar INSTR function. The syntax of the new REGEXP_INSTR function is shown in Table 6. The main difference between the two functions is that REGEXP_INSTR lets you specify a pattern instead of a specific search string; thus providing greater versatility. The next example uses REGEXP_INSTR to return the starting position of the five-digit zip-code pattern within the string Joe Smith, 10045 Berry Lane, San Joseph, CA 91234. If the regular expression is written as [[:digit:]]{5}, you will get the house number's starting position instead of the zip code's, because 10045 is the first occurrence of five consecutive digits. Therefore, you must anchor the expression to the end of the line, as indicated with the $ metacharacter, and the function will display the starting position of the zip code regardless of the number of digits for the house number.

SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234',
       '[[:digit:]]{5}$')
       AS rx_instr
  FROM dual
  RX_INSTR
----------
        45

Writing More Complex Patterns

Let's expand on the zip code pattern of the previous example to include an optional four digits. Your pattern may now look like this: [[:digit:]]{5}(-[[:digit:]]{4})?$. If your source string ends in either the 5-digit zip code or the 5-digit + 4 zip-code format, you'll be able to show the pattern's starting position.

SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234-1234',
       ' [[:digit:]]{5}(-[[:digit:]]{4})?$')
    AS starts_at
  FROM dual
 STARTS_AT
----------
        44

In this example the parenthesized subexpression (-[[:digit:]]{4}) is repeated zero or one times, as indicated by the ? repetition operator. Again, attempting to use the traditional SQL functions to accomplish the same result poses a challenge even to SQL experts. To better explain the various components of the regular expression example, Table 7 contains a description of the individual literals and metacharacters.

The REGEXP_SUBSTRFunction

The REGEXP_SUBSTR function, much like the SUBSTR function, extracts part of a string. Table 8 displays the syntax of the new function. In the following example, the string that matches the pattern , [^,]*, is returned. The regular expression searches for a comma followed by a space; then zero or more characters that are not commas, as indicated by [^,]*; and lastly looks for another comma. The pattern will look somewhat similar to a comma-separated values string.

SELECT REGEXP_SUBSTR('first field, second field , third field',
       ', [^,]*,')
  FROM dual
REGEXP_SUBSTR('FIR
------------------
, second field   ,

The REGEXP_REPLACE Function

Let's first look at the traditional REPLACE SQL function, which substitutes one string with another. Assume your data has extraneous spaces in the text and you would like to replace them with a single space. With the REPLACE function, you need to list exactly how many spaces you want to replace. However, the number of extra spaces may not be the same everywhere in the text. The next example has three spaces between Joe and Smith. The REPLACE function's parameter specifies that two spaces should be replaced with one space. In this case, the result leaves an extra space where there were three spaces in the original string between Joe and Smith.

SELECT REPLACE('Joe   Smith','  ', ' ')
       AS replace
  FROM dual
REPLACE
---------
Joe Smith

The REGEXP_REPLACE function takes the substitution a step further; the syntax is listed in Table 9. The following query replaces any two or more spaces with a single space. The ( ) subexpression contains a single space, which can be repeated two or more times, as indicated by {2,}.

SELECT REGEXP_REPLACE('Joe   Smith',
       '( ){2,}', ' ')
       AS RX_REPLACE
  FROM dual
RX_REPLACE
----------
Joe Smith

Backreferences A useful feature of regular expressions is the ability to store subexpressions for reuse later; this is also called backreferencing (summarized in Table 10). It allows sophisticated replace capabilities such as swapping patterns in new positions or indicating repeated word or letter occurrences. The matched part of the subexpression is stored in a temporary buffer. The buffer is numbered from left to right and accessed with the \digit notation, where digit is a number between 1 and 9 and matches the digit-th subexpression, as indicated by a set of parentheses. The next example shows the name Ellen Hildi Smith transformed to Smith, Ellen Hildi, by referring to the individual subexpressions by number. SELECT REGEXP_REPLACE(        'Ellen Hildi Smith',        '(.*) (.*) (.*)', '\3, \1 \2')   FROM dual REGEXP_REPLACE('EL ------------------ Smith, Ellen Hildi The SQL statement shows three individual subexpressions enclosed by parentheses. Each individual subexpression consists of a match any metacharacter (.) followed by the * metacharacter, indicating that any character (except newline) must be matched zero or more times. A space separates each subexpression and must be matched as well. The parentheses create subexpressions that capture the values and can be referenced with \digit. The first subexpression is assigned \1, the second \2, and so on. These backreferences are used in the last parameter of this function (\3, \1 \2), which effectively returns the replacement substrings and places them in the desired format (including comma and spaces). Table 11 details the individual components of this regular expression. Backreferences are useful for replacing, formatting, and substituting values, and you can apply them to find adjacent occurrences of values. The next example shows use of the REGEP_SUBSTR function to find any duplicate occurrences of alphanumeric values separated by a space. The displayed result shows the substring that identifies the duplicated words is. SELECT REGEXP_SUBSTR(        'The final test is is the implementation',        '([[:alnum:]]+)([[:space:]]+)\1') AS substr   FROM dual SUBSTR ------ is is The Match Parameter Option You may have noticed that the regular expression operator and functions contain an optional match parameter. This parameter controls case-sensitivity, matching of the newline character, and retaining multiline inputs. Practical Applications for Regular Expressions You can use regular expressions not only in queries but also anywhere you can use a SQL operator or function, such as in the PL/SQL language. You can write triggers that take advantage of regular expression functionality in order to validate, generate, or extract values. The next example illustrates how you can apply the REGEXP_LIKE operator in a column check constraint for data validation. It checks for the correct Social Security number format upon insert or update. Social Security numbers in such formats as 123-45-6789 and 123456789 are acceptable values for this column constraint. Valid data must begin with three digits, followed by a hyphen, two more digits and a hyphen, and lastly another four digits. The alternate expression allows only nine consecutive digits. The vertical bar symbol (|) separates the individual choices. ALTER TABLE students ADD CONSTRAINT stud_ssn_ck CHECK (REGEXP_LIKE(ssn, '^([[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4}|[[:digit:]]{9})$')) Leading or trailing characters are not acceptable, as indicated by ^ and $. Make sure your regular expression does not split across multiple lines or contain any extraneous spaces unless you want them to be part of the pattern and matched accordingly. Table 12 explains the individual components of this regular expression example. Next Steps Visit the Oracle Database 10g page: /products/database/oracle10g/index.html Comparing Regular Expressions to Existing Functionality Regular expressions have several advantages over the familiar LIKE operator and INSTR, SUBSTR, and REPLACE functions. These traditional SQL functions have no facility for matching patterns. Only the LIKE operator performs matching of characters, through the use of the % and _ wildcards, but LIKE does not support repetitions of expressions, complex alternations, ranges of characters, characters lists, POSIX character classes, and so on. Furthermore, the new regular expression functions allow detection of duplicate word occurrences and swapping of patterns. The examples here offer you a glimpse into the world of regular expressions and on how you can apply them in your applications. A Solid Addition to Your Toolkit Because they help solve complex problems, regular expressions are very powerful. Some of the functionality of regular expressions is difficult to duplicate by using traditional SQL functions. When you've learned the basic building blocks of this somewhat cryptic language, regular expressions will become an indispensable part of your toolkit in the context of not only SQL but also other programming languages. Although trial and error are sometimes necessary to get your individual pattern right, the elegance and power of regular expressions is indisputable. Alice Rischert (ar280@yahoo.com) chairs the Database Application Development and Design track at Columbia University's Computer Technology and Application Program. She is the author of the Oracle SQL Interactive Workbook 2nd edition (Prentice Hall, 2002) and the forthcoming Oracle SQL by Example (Prentice Hall, 2003). Rischert has over 15 years of experience as a database architect, DBA, and project leader for Fortune 100 companies and she has worked with Oracle since version 5. Table 1: Anchoring Metacharacters Metacharacter Description ^ Anchor the expression to the start of a line $ Anchor the expression to the end of a line Table 2: Quantifiers, or Repetition Operators Quantifier Description * Match 0 or more times ? Match 0 or 1 time + Match 1 or more times {m} Match exactly m times {m,} Match at least m times {m, n} Match at least m times but no more than n times Table 3: Predefined POSIX Character Classes Character Class Description [:alpha:] Alphabetic characters [:lower:] Lowercase alphabetic characters [:upper:] Uppercase alphabetic characters [:digit:] Numeric digits [:alnum:] Alphanumeric characters [:space:] Space characters (nonprinting), such as carriage return, newline, vertical tab, and form feed [:punct:] Punctuation characters [:cntrl:] Control characters (nonprinting) [:print:] Printable characters Table 4: Alternate Matching and Grouping of Expressions Metacharacter Description | Alternation Separates alternates, often used with grouping operator () ( ) Group Groups subexpression into a unit for alternations, for quantifiers, or for backreferencing (see "Backreferences" section) [char] Character list Indicates a character list; most metacharacters inside a character list are understood as literals, with the exception of character classes, and the ^ and - metacharacters Table 5: The REGEXP_LIKE Operator Syntax Description REGEXP_LIKE(source_string, pattern [, match_parameter]) source_string supports character datatypes (CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2, and NCLOB but not LONG). The pattern parameter is another name for the regular expression. match_parameter allows optional parameters such as handling the newline character, retaining multiline formatting, and providing control over case-sensitivity. Table 6: The REGEXP_INSTR Function Syntax Description REGEXP_INSTR(source_string, pattern [, start_position [, occurrence [, return_option [, match_parameter]]]]) This function looks for a pattern and returns the first position of the pattern. Optionally, you can indicate the start_position you want to begin the search. The occurrence parameter defaults to 1 unless you indicate that you are looking for a subsequent occurrence. The default value of the return_option is 0, which returns the starting position of the pattern; a value of 1 returns the starting position of the next character following the match. Table 7: Explanation of 5-digit + 4 Zip-Code Expression Syntax Description   Empty space that must be matched [:digit:] POSIX numeric digit class ] End of character list {5} Repeat exactly five occurrences of the character list ( Start of subexpression - A literal hyphen, because it is not a range metacharacter inside a character list [ Start of character list [:digit:] POSIX [:digit:] class [ Start of character list ] End of character list {4} Repeat exactly four occurrences of the character list ) Closing parenthesis, to end the subexpression ? The ? quantifier matches the grouped subexpression 0 or 1 time thus making the 4-digit code optional $ Anchoring metacharacter, to indicate the end of the line Table 8: The REGEXP_SUBSTR Function Syntax Description REGEXP_SUBSTR(source_string, pattern [, position [, occurrence [, match_parameter]]]) The REGEXP_SUBSTR function returns the substring that matches the pattern. Table 9: The REGEXP_REPLACE Function Syntax Description REGEXP_REPLACE(source_string, pattern [, replace_string [, position [,occurrence, [match_parameter]]]]) This function replaces the matching pattern with a specified replace_string, allowing complex search-and-replace operations. Table 10: Backreference Metacharacter Metacharacter Description \digit Backslash Followed by a digit between 1 and 9, the backslash matches the preceding digit-th parenthesized subexpression. (Note: The backslash has another meaning in regular expressions; depending on the context it can also mean the Escape character Table 11: Explanation of Pattern-Swap Regular Expression Regular-Expression Item Description ( Start of first subexpression . Match any single character except a newline * Repetition operator, matches previous . metacharacter 0 to n times ) End of first subexpression; result of the match is captured in \1 (In this example, it's Ellen.)   Empty space that needs to be present ( Start of the second subexpression . Match any single character except a newline * Repetition operator matches the previous . metacharacter 0 to n times ) End of second subexpression; result of this match is captured in \2 (In this example, it stores Hildi.)   Empty space ( Start of third subexpression . Match any single character except a newline * Repetition operator matches . metacharacter 0 to n times ) End of third subexpression; result of this match is captured in \3 (In this example, it holds Smith.) Table 12: Explanation of the Social Security Number Regular Expression Regular-Expression Item Description ^ Start of line character (Regular expression cannot have any leading characters before the match.) ( Start subexpression and list alternates separated by the | metacharacter [ Start of character list [:digit:] POSIX numeric digit class ] End of character list {3} Repeat exactly three occurrences of character list - A hyphen [ Start of character list [:digit:] POSIX numeric digit class ] End of character list {2} Repeat exactly two occurrences of character list - Another hyphen [ Start of character list [:digit:] POSIX numeric digit class ] End of character list {4} Repeat exactly four occurrences of character list | Alternation metacharacter; ends the first choice and starts the next alternate expression [ Start of character list [:digit:] POSIX numeric digit class. ] End of character list {9} Repeat exactly nine occurrences of character list ) Ending parenthesis, to close the subexpression group used for alternation $ Anchoring metacharacter, to indicate the end of the line; no extra characters can follow the pattern

Math Functions
Function	Oracle	SQL Server
Absolute value	ABS	ABS
Arc cosine	ACOS	ACOS
Arc sine	ASIN	ASIN
Arc tangent of n	ATAN	ATAN
Arc tangent of n and m	ATAN2	ATN2
Smallest integer >= value	CEIL	CEILING
Cosine	COS	COS
Hyperbolic cosine	COSH	COT
Exponential value	EXP	EXP
Round down to nearest integer	FLOOR	FLOOR
Natural logarithm	LN	LOG
Logarithm, any base	LOG(N)	N/A
Logarithm, base 10	LOG(10)	LOG10
Modulus (remainder)	MOD	USE MODULO (%) OPERATOR
Power	POWER	POWER
Random number	N/A	RAND
Round	ROUND	ROUND
Sign of number	SIGN	SIGN
Sine	SIN	SIN
Hyperbolic sine	SINH	N/A
Square root	SQRT	SQRT
Tangent	TAN	TAN
Hyperbolic tangent	TANH	N/A
Truncate	TRUNC	N/A
Highest number in list	GREATEST	N/A
Lowest number in list	LEAST	N/A
Convert number if NULL	NVL	ISNULL
Standard deviation	STDDEV	STDEV
Variance	VARIANCE	VAR

String Functions
Function	Oracle	SQL Server
Convert character to ASCII	ASCII	ASCII
String concatenate	CONCAT	(expression + expression)
Convert ASCII to character	CHR	CHAR
Return starting point of character in character string (from left)	INSTR	CHARINDEX
Convert characters to lowercase	LOWER	LOWER
Convert characters to uppercase	UPPER	UPPER
Pad left side of character string	LPAD	N/A
Remove leading blank spaces	LTRIM	LTRIM
Remove trailing blank spaces	RTRIM	RTRIM
Starting point of pattern in character string	INSTR	PATINDEX
Repeat character string multiple times	RPAD	REPLICATE
Phonetic representation of character string	SOUNDEX	SOUNDEX
String of repeated spaces	RPAD	SPACE
Character data converted from numeric data	TO_CHAR	STR
Substring	SUBSTR	SUBSTRING
Replace characters	REPLACE	STUFF
Capitalize first letter of each word in string	INITCAP	N/A
Translate character string	TRANSLATE	N/A
Length of character string	LENGTH	DATALENGTH or LEN
Greatest character string in list	GREATEST	N/A
Least character string in list	LEAST	N/A
Convert string if NULL	NVL	ISNULL

Date Functions
Function	Oracle	SQL Server
Date addition	(use +)	DATEADD
Date subtraction	(use -)	DATEDIFF
Last day of month	LAST_DAY	N/A
Time zone conversion	NEW_TIME	N/A
First weekday after date	NEXT_DAY	N/A
Convert date to string	TO_CHAR	DATENAME
Convert date to number	TO_NUMBER(TO_CHAR())	DATEPART
Convert string to date	TO_DATE	CAST
Get current date and time	SYSDATE	GETDATE()

출처 : http://www.sql-ref.com/

웹 애플리케이션은 일반적으로 사용자에게 많은 양의 정보를 제공하기 위해 페이징 기법을 사용한다. 예를 들어 인터넷 검색엔진은 사용자의 쿼리 결과로 대용량의 결과값을 반환한다. 이 때 검색엔진이 한번에 결과값 전체를 반환하게 되면 결과값을 받는 클라이언트측 시스템에 과부하가 발생 할 수 있다.
하지만 페이징 기법을 사용하게 되면, 반환되는 결과값을 클라이언트측과 서버측 양쪽에서 관리할 수 있을 만큼의 고정된 크기의 블록으로 구분하여 한 번에 이동시키는 정보의 양을 줄일 수 있다. 애플리케이션에서는 한 번 에 소수의 레코드만 사용자에게 보내게 되며, 결과값 중에서 사용자가 필요로 하는 정보만 반환하게 된다.

페이징 기법을 사용하면 데이터를 사용자가 좀 더 이해하고 표현하기 쉽게 해 줄 뿐만 아니라, 대량의 정보를 조회하고 표현하기 위해서 시스템에 불필요한 과부하가 발생하여 결국 시스템의 성능에 악영향을 미치지 않도록 통제하기 때문에 전체적인 시스템 성능을 향상시키게 된다.
정상적으로 시스템에 반환된 결과값 레코드가 페이징되었다면, 검색엔진을 사용하는 사용자는 대부분 맨 처음 한 페이지 또는 일부 페이지만 조회하게 될 것이다.

불행하게도 많은 프로그래머들이 페이징 관련해서 성능측면에서 매우 중요한 고려사항에 대해서 잘 모르고 있다.
IIS와 SQL Server를 사용하는 환경에서는, AbsolutePage, PageSize, PageCount와 같은, 표준 ADO RecordSet 페이징 기능을 사용하는 것이 가장 일반적인 페이징 기법이다.
소량(수십 또는 수백 레코드 정도)의 데이터에 대해서는 이러한 기능을 사용하면 정상적으로 동작하고, 시스템의 성능측면에서도 부하를 발생시키지 않는다. 하지만 레코드 수가 증가하게 되면, 이러한 기능을 사용하게 되면 효율성이 감소하게 되고 전체적인 애플리케이션의 성능에 악영향을 미치게 된다.

대량의 발주정보를 조회해야 하는 구매조달관련 애플리케이션, 수 천명의 회원이 동시에 접속하는 미팅 웹사이트, 고객의 검색조건에 따라 수백 개의 상품의 정보를 표시해야 하는 대규모 전자상거래 웹사이트와 같이, 대용량 데이터를 처리해야 하는 애플리케이션의 경우에는 좀 더 개선된 서버측 페이징 기술이 필요하게 된다. 이번 호의 기사에서는 수백만 개의 행정보를 포함하는 테이블에서도 사용할 수 있는 페이징 기법에 대해서 소개하고자 한다.


ADO RecordSet 페이징 기법의 한계


대용량의 레코드를 페이징하기 위해서 ADO RecordSet의 페이징기법을 사용할 때 발생하는 문제의 원인은 ADO에서 데이터를 처리하는 방법 때문이다.
ADO 기술구조에서는 데이터베이스에서 정보를 조회하기 위해 서 조회의 대상이 되는 데이터에 대한 포인터를 관리해야 할 필요가 있다.
데이터에 대한 포인터를 커서 라고 하며, 클라이언트측(예를 들어 ASP 페이지)에서는 각 레코드를 건별로 조회하게 된다.

ADO RecordSet 개체는 서버측 커서(기본값)와 클라이언트측 커서 유형을 지원한다.
서버측 커서를 사용하게 되면 모든 데이터는 그대로 SQL Server에 두고, 해당 데이터가 필요한 시점에 순서에 따라 각 레코드를 조회하게 된다.
클라이언트측 커서를 사용하게 되면 필요한 모든 데이터를 클라이인트로 전송한 다음, 클라이언트측 커서를 사용하여 클라이언트측 버퍼 메모리에 있는 데이터를 레코드별로 조회하게 된다.
검색엔진 예제에서처럼 쿼리의 결과값 중에서 일부분만을 표시하거나 사용해야 하는 경우라면 SQL 서버가 클라이언트에서 요청하는 페이지만 전송하고 전체 결과값 중 나머지 레코드는 데이터베이스 서버에 그대로 남겨두게 되는, 서버측 커서를 사용하는 것이 효율적이다.
서버측 커서를 사용하게 되면 클라이언트로 전송되는 레코드 수가 특정 페이지를 구성하는 20~30 레코드 정도로 제한된다는 것이다.

PageCount 와 같은 일부 레코드셋 페이징 기능을 사용하기 위해서는 클라이언트측 커서를 사용해야 한다.
클라이언트 커서를 사용할 수 있도록 ADO를 설정하기 위해서는 RecordSet의 ClientLocation 속성을 adUseServer에서 adUseClient로 변경해 주면 된다.
[리스트 1]의 VB 코드는 RecordSet 개체에서 클라이언트측 커서와 서버측 커서를 사용하는 방법에 대한 예제가 나타나 있다. ClientLocation 속성을 asUseClient 로 변경하게 되면, 사용자 쿼리의 결과로 반환되는 데이터에서 필요로 하는 페이지 수를 판단하기 위해 결과값 전체가 클라이언트로 전송된다.

예를 들어 데이터베이스로부터 5000 레코드를 반환하는 쿼리를 실행했다고 가정하자.
애플리케이션에서 서버측 커서를 사용하게 되면 반환되는 레코드를 한 페이지당 20 레코드씩으로 페이징하고, 사용자가 1 페이지만 보고 있는 경우라면 애플리케이션에서는 클라이언트로 맨 처음 페이지를 구성하는 20 레코드만을 전송하면 된다.
그 다음 사용자가 두번째 페이지로 이동하면 애플리케이션에서는 21~40번 레코드만 클라이언트로 전송하게된다. 반면에 클라이언트측 커서를 사용하게 되면 ADO에서는 비록 사용자가 단지 첫 페이지에 해당하는 20 레코드만 필요한 경우라도 5000 레코드 전체를 클라이언트로 전송하게 된다.
이렇게 전체 레코드를 전송하게 되면 결과값이 사용자에게 나타나는 시간이 지연되게 되고, 반환되는 레코드수가 매우 많은 경우에는 성능에 심각한 악영향을 미칠 수 있다.

다른 페이징 기법

ADO RecordSet 페이징 기법과 관련한 문제가 애플리케이션의 성능에 영향을 미치게 되었기 때문에 필자는 수천 레코드를 페이징해야 하는 웹 애플리케이션에서 사용할 수 있는 다른 페이징 기법을 찾아 보았다.

SQL 서버의 인덱스를 활용하여 전체 결과집합 중에서 상위의 레코드를 선택하는 방법을 소개하고자 한다.

다음은 Northwind 데이터베이스의 Orders 테이블에서 상위 10개의 레코드를 선택하는 쿼리이다.

SELECT TOP 10 * FROM Orders

위의 구문을 활용하면 전체 결과집합에서 10 개의 레코드 단위로 결과값이 반환되게 할 수 있다.
주어진 페이지에 해당하는 레코드만 선택하기 위해서는, 한 페이지에 몇 개의 레코드를 포함시킬 것인지 결정하고, 실제 사용자가 몇 번째 페이지의 정보를 조회하기를 원하는지에 대한 페이지 카운트를 알고 있어야 한다.
예를 들어 한 페이지에 10 개의 레코드가 포함되고, 사용자가 전체 결과값 집합 중에서 3 페이지를 조회하고자 하는 경우라면 다음과 같은 쿼리를 사용하면 된다.

SELECT TOP 10 * FROM Orders WHERE OrderID NOT IN (SELECT TOP 20 OrderID FROM Orders)

위의 쿼리는 맨 처음 20 개의 레코드 이후에 존재하는 10개의 레코드, 즉 21~30번까지의 레코드를 반환한다.
위의 쿼리를 절차코드로 일반화하게 되면 다음과 같이 표현할 수 있다.

SELECT TOP page_size * FROM Orders WHERE OrderID NOT IN (SELECT TOP (page_size * (current_page -
1)) OrderID FROM Orders)

위의 쿼리는 대량의 레코드를 반환하는 경우라도 잘 동작하지만, 반환할 페이지의 숫자가 많아질수록 전체적인 성능은 감소하게 된다.
문제의 원인은 IN 연산자에 포함되는 쿼리의 결과값이 많아지면서 비효율성이 증가하기 때문이다. 예를 들어 한 페이지당 10개의 레코드를 반환하는 결과집합 중에서 500번째 페이지를 조회하기 위해 쿼리를 실행하게 되면, IN 연산자의 대상이 되는 서브쿼리에는 다음과 같은 문장이 포함되게 된다.

(SELECT TOP 4990 OrderID FROM Orders)

마지막 10개의 레코드를 조회하기 위해 서버에서는 4990 개의 OrderID와 각 OrderID를 비교해야만 한다. 불필요한 비교작업이 많이 발생하게 된다.
물론 SQL Server의 경우에는 이러한 경우 데이터를 좀 더 빠르고 효율적인 방법으로 검색하기 위해서 인덱스를 사용하여 빠르게 쿼리를 처리하게 된다.
(OrderID가 기본키로 설정되어 있기 때문에, SQL Server는 기본적으로 인덱스를 사용하게 된다.)
조회하고자 하는 페이지 수가 증가함에 따라 성능면에서 느려지게 된다고 하더라도 이러한 조회성능의 감소현상은 조회하고자 하는 페이지 수가 매우 큰 경우에만 인식할 수 있게 된다.
이러한 점증적인 성능의 감소현상은 대부분의 경우 사용자가 맨 처음부분의 일부 페이지만 조회하게 되고, 맨 처음부분의 일부 페이지를 조회 할 경우에는 매우 빠른 성능을 보장할 수 있기 때문에 크게 중요한 관심의 대상이 되지 않는다.
애플리케이션이션에서 적절하게 데이터를 정렬하고 필터링한다면 사용자는 찾고자 하는 정보를 거의 한 두 페이지 이내에서 찾게 된다. 만약 맨 처음 부분의 페이지에서 필요로 하는 데이터를 찾지 못한 경우에는 대부분의 사용자는 예제에서처럼 500 페이지까지 원하는 데이터를 찾고자 계속 다음 페이지를 찾아보는 것이 아니라 다른 정렬 및 필터링 조건으로 새로운 쿼리를 하게 된다.

앞에서 언급한 것과 같이 검색작업을 수행할 때, 테이블의 기본키가 어떤 컬럼에 설정되었는지가 매우 중요한 역할을 하게 된다.
기본키에는 각 레코드를 유일하게 식별할 수 있는 컬럼이 포함되어야 하며, 쿼리를 실행할 때 기본키를 기준으로 해당 레코드를 쿼리의 결과값으로 선택할 것인지 무시할 것인지를 판단하게 된다.
앞의 일반화된 쿼리에는 정렬 및 기본키에 관련한 WHEHE 절이나 ORDER BY 절 내용이 누락되어 있다. 목록 2 에는 이러한 요소를 포함시킨 일반화된 쿼리가 나타나 있다.

정렬은 일반적으로는 비효율적인 작업이며, 쿼리의 성능을 저하시키는 원인이 되기도 한다.
SQL 서버의 경우, 인덱스가 설정된 컬럼에 대해서는 매우 효율적으로 정렬 및 필터링 작업을 할 수 있고, SQL 서버가 항상 기본키 컬럼에는 인덱스를 생성하기 때문에 쿼리를 좀 더 최적화하여 빠르게 실행한다.
기본키에 설정된 인덱스를 최대한 활용하기 위해서, [리스트 3]과 같이 테이블의 전체 컬럼을 선택하지 않고, 먼저 기본키의 조건으로 대상이 되는 레코드를 검색한 다음에 결과값에 포함되어 있는 기본키값으로 다시 해당 레코드에 대한 전체 컬럼을 찾아오게 할 수도 있다.
테이블에 기본키 인덱스가 설정되어 있고, 인덱스가 설정된 필드에 대해서만 정렬 및 필터링 작업을 하게 되면, 조회하고자 하는 레코드를 찾기 위해 인덱스 페이지만을 사용하게 된다. 이렇게 쿼리의 결과값에 필요한 전체 필드가 인덱스에 포함되어 있는 경우를 커버된 인덱스라고 한다.
동일한 쿼리를 실행시킨 경우라도 해당 쿼리가 커버된 인덱스를 사용하는 경우가 일반 테이블에 대해서 쿼리하는 경우보다 더 빠르게 된다. 맨 마지막으로 선택된 결과값에 해당하는 나머지 정보를 조회하기 위한 작업을 수행할 때에는(즉, SELECT * 부분), 기본키 인덱스를 사용하여 SQL Server가 해당 인덱스를 바로 찾을 수 있기 때문에 매우 효율적으로 쿼리를 수행하게 된다.

[리스트3]에 나타나 있는 쿼리는 결과집합 중에서 특정 페이지를 매우 효율적이고, 단순한 방법으로 조회하게 된다. 물론, GROUP BY나 HAVING 절을 추가하여 사용할 수도 있다.
쿼리를 좀 더 단순화하기 위해서 [리스트 4]와 같이 SELECT_WITH_PAGING 라는 저장 프로시저를 생성하여, 기능을 캡슐화하였다.

SELECT_WITH_PAGING 저장프로시저에는 fields_to_return (string), primary_key (string), table_name (string),
page_number (integer, default 1), page_size (integer), get_record_count (true/false), filter_conditions (string),
sort_columns (string), group_by (string)와 같은 매개변수(데이터형)가 입력되게 된다.

예를 들어 한 페이지당 레코드 수를 10개로 지정하고 Northwind 데이터베이스의 Order 테이블으로부터 주문일자로 정렬하여 CustomerID, ShipName을 조회한 다음, 결과값 중 세번째 페이지를 조회하기 위해서는 다음 문장을 실행하면 된다.

EXEC SELECT_WITH_PAGING 'CustomerID, ShipName', 'OrderID', 'Northwind.dbo.Orders', 3, 10,
1, '', 'OrderDate'

위의 쿼리를 실행하게 되면 입력된 조건에 따라 필터링되어 반환되는 전체 레코드 수가 두번째 레코드셋으로 반환된다. 전체 레코드 수는 사용자에게 전체 페이지 수를 표시하려고 할 때 유용하게 사용되며, 대부분의 경우 사용자는 단순하게 이전페이지 또는 다음페이지로 표시되는 것보다는 전체 페이지 수 중에서 현재 조회하고 있는 페이지의 번호를 표시하는 방법을 더 선호한다.
조건에 해당하는 전체 레코드 수를 조회하기 위해서 필자는 여섯번째 매개변수를 1로 설정하였다.
만약 여섯번째 매개변수를 설정하지 않으면 데이터베이스로부터 페이지당 레코드로 제한된 10개의 레코드만을 반환하게 되기 때문에 전체 레코드 수가 몇 개인지는 알 수 없게 된다.

table_name 매개변수에는 두 개 또는 그 이상의 테이블에 대한 조인을 설정하는 문장이 올 수도 있고, 필요에 따라 서브쿼리도 올 수 있다.
예를 들어, 다음 두 문장은 table_name 매개변수에 모두 사용될 수 있다.

'Northwind.dbo.Orders A JOIN Northwind.dbo.Customers B ON A.CustomerID = B.CustomerID' '(SELECT *
FROM Northwind.dbo.Orders WHERE OrderDate > ''8/1/1996'') AS tbl'

이번 호의 기사에서 소개한 페이징 기법은 레코드의 수가 매우 많은 경우에 서버측 페이징을 처리하기 위해 매우 단순하고, 효율적으로 사용할 수 있으며, 필자의 경우에는 수백만 행이 포함되어 있는 테이블에 대해서도 사용한 경험이 있다. 예를 들어 2천 5백만 레코드가 있는 테이블에 대해서 맨 처음 일부 페이지를 조회하는 쿼리를 실행할 때, ADO RecordSet 페이징 기법을 사용했을 때에는 거의 40초가 걸렸으나 이번 호에 소개한 저장프로시저를 사용한 경우에는 1초로 수행시간을 단축할 수 있었다.
이처럼 이번 호에 소개한 페이징 기법은 조회의 대상이 되는 데이터양이 많아서 ADO RecordSet 페이징 기법을 사용하게 되면 성능상 문제가 발생할 수 있는 상황에서 유용한 대안으로 사용할 수 있다.


[리스트 1] ClientLocation 속성을 변경하는 코드
Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
' 연결 생성
Set objConn = New ADODB.Connection
objConn.Open "Driver=SQL Server; Server=localhost; Database=Northwind"
Set objRS = New ADODB.Recordset
　　　' 클라이언트측 커서를 사용하게 하는 옵션
objRS.CursorLocation = adUseClient
　　　' 서버측 커서를 사용하게 하는 옵션
　　　objRS.CursorLocation = adUseServer
objRS.Open "SELECT * FROM Orders", objConn, adOpenStatic,
adLockOptimistic
　　　' 이 문장은 페이징을 위해서 필요하지만, 서버측 커서를 사용하는 경우에는 에러의 원인이 된다.
　　　Debug.Print "Total records: " & objRS.RecordCount

[리스트 2] 정렬을 위한 조건절을 지정한 일반화된 페이징 쿼리
SELECT TOP page_size * FROM table WHERE primary_key NOT IN
(SELECT TOP page_size * (page_number - 1) primary_key FROM table
WHERE filter_conditions
ORDER BY sort_field)
AND filter_criteria
ORDER BY sort_field



[리스트 3] 기본키에 검색조건을 먼저 설정하는 일반화 쿼리
SELECT * FROM table WHERE primary key IN
(SELECT TOP page_size primary_key FROM table
WHERE primary_key NOT IN

(SELECT TOP page_size * (page_number - 1) primary_key FROM table
WHERE filter_conditions ORDER BY sort_field) AND filter_criteria
ORDER BY sort_field)
ORDER BY sort_field


[리스트 4] SELECT_WITH_PAGING 저장 프로시저
CREATE PROCEDURE SELECT_WITH_PAGING (
@strFields varchar(4000),
@strPK varchar(100),
@strTables varchar(4000),
@intPageNo int = 1,
@intPageSize int = NULL,
@blnGetRecordCount bit = 0,
@strFilter varchar(8000) = NULL,
@strSort varchar(8000) = NULL,
@strGroup varchar(8000) = NULL)
/* 매개변수에 따라 반환되는 결과값을 특정 페이지로 정의하거나 전체 행을 모두 반환할 수 있도록 설정한
다. */
AS
DECLARE @blnBringAllRecords bit
DECLARE @strPageNo varchar(50)
DECLARE @strPageSize varchar(50)
DECLARE @strSkippedRows varchar(50)
DECLARE @strFilterCriteria varchar(8000)
DECLARE @strSimpleFilter varchar(8000)
DECLARE @strSortCriteria varchar(8000)
DECLARE @strGroupCriteria varchar(8000)
DECLARE @intRecordcount int
DECLARE @intPagecount int
/* 페이징 조건 정규화 의미있는 페이징 조건이 입력되지 않은 경우, 페이징하지 않고 좀 더 효율적인 방법으
로 쿼리를 실행시키기 위해 blnBringAllRecords 플래그를 사용 */
IF @intPageNo < 1
SET @intPageNo = 1
SET @strPageNo = CONVERT(varchar(50), @intPageNo)
IF @intPageSize IS NULL OR @intPageSize < 1 ?- 페이징하지 않고 전체 행을 반환
SET @blnBringAllRecords = 1
ELSE
BEGIN
SET @blnBringAllRecords = 0
SET @strPageSize = CONVERT(varchar(50), @intPageSize)
SET @strPageNo = CONVERT(varchar(50), @intPageNo)
SET @strSkippedRows = CONVERT(varchar(50), @intPageSize * (@intPageNo - 1))
END
/* 정렬 및 필터링 조건 정규화 정렬 및 필터링 조건이 지정되지 않으면, 필터링이나 정렬작업이 수행되지 않
도록 하여 쿼리의 성능을 향상시킴.*/
IF @strFilter IS NOT NULL AND @strFilter != ''
BEGIN
SET @strFilterCriteria = ' WHERE ' + @strFilter + ' '
SET @strSimpleFilter = ' AND ' + @strFilter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilterCriteria = ''
END
IF @strSort IS NOT NULL AND @strSort != ''
SET @strSortCriteria = ' ORDER BY ' + @strSort + ' '
ELSE
SET @strSortCriteria = ''
IF @strGroup IS NOT NULL AND @strGroup != ''
SET @strGroupCriteria = 'GROUP BY' + @strGroup + ' '
ELSE
SET @strGroupCriteria = ''
/* 실제 조회작업을 시작 */
IF @blnBringAllRecords = 1 -- 페이징 하지 않고 단순한 SELECT 문장만을 실행
BEGIN

EXEC (
'SELECT ' + @strFields + 'FROM' + @strTables + @strFilterCriteria +
@strGroupCriteria + @strSortCriteria
)
END -- 전체 레코드를 반환.
ELSE -- 지정된 페이지를 반환
BEGIN
IF @intPageNo = 1 -- 맨 처음 페이지를 찾기 때문에 서브쿼리가 없어서 가장 효율적으로 실

행된다.
EXEC (
'SELECT TOP' + @strPageSize + ' ' + @strFields + 'FROM' + @strTables +
@strFilterCriteria + @strGroupCriteria + @strSortCriteria
)
ELSE -- 특정 페이지를 선택하기 위해 서브쿼리 구조를 실행한다.
EXEC (
'SELECT' + @strFields + 'FROM' + @strTables + 'WHERE' + @strPK + 'IN' + '
(SELECT TOP' + @strPageSize + ' ' + @strPK + 'FROM' + @strTables +
' WHERE' + @strPK + 'NOT IN' + '
(SELECT TOP' + @strSkippedRows + ' ' + @strPK + 'FROM' + @strTables +
@strFilterCriteria + @strGroupCriteria + @strSortCriteria + ') ' +
@strSimpleFilter +
@strGroupCriteria +
@strSortCriteria + ') ' +
@strGroupCriteria +
@strSortCriteria
)
END -- 특정 페이지를 지정한 경우
/* 전체 레코드 수를 반환하도록 지정된 경우 */
IF @blnGetRecordCount = 1
IF @strGroupCriteria != ''
EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM' +
@strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)
)
ELSE
EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM' + @strTables + @strFilterCriteria

+ @strGroupCriteria)
GO

출처 : SQL2000 매거진 (Andrew Rosca )