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 계정으로 실행해야 함.
해당 테이블 스페이스의 모든 블럭들을 합산하여 계산.

'Source Storage > DataBase' 카테고리의 다른 글

MS-SQL Convert 함수  (0) 2010.04.22
오라클 사용자 관리하기  (0) 2008.09.08
Oracle Table Partitioning  (0) 2008.04.22
ORACLE HINT RULE  (0) 2008.04.04
ORACLE TABLE INDEX RULE  (0) 2008.03.27

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

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 스크립트에 의해 생성됨.

      포함되는 시스템 권한 : ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LIKE,
                                      CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM,
                                      CREATE TABLE, CREATE VIEW

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

       포함되는 시스템 권한 : CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR,
                                       CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE,
                                       CREATE TRIGGER, CREATE TYPE

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

'Source Storage > DataBase' 카테고리의 다른 글

MS-SQL Convert 함수  (0) 2010.04.22
오라클 테이블 스페이스 사용량 분석  (0) 2010.03.29
Oracle Table Partitioning  (0) 2008.04.22
ORACLE HINT RULE  (0) 2008.04.04
ORACLE TABLE INDEX RULE  (0) 2008.03.27
근래에 많은 기업들의 데이터베이스가 대용량화 되면서 이를 효과적으로 관리할 수 있는 방안을 찾는 것이 관리자들의 주요 업무가 됐다. 이를 위한 매우 효과적인 방안 가운데 하나가 파티셔닝이다.

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

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

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

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

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

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

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

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

◆ 데이터 액세스시(특히 풀 스캔시) 액세스의 범위를 줄여 성능을 향상시킨다.
◆ 물리적으로 여러 영역으로 파티셔닝해 전체 데이터의 훼손 가능성이 줄어들고 데이터 가용성이 향상된다.
◆ 각 파티션별로 백업, 복구 작업을 할 수 있다.
◆ 테이블의 파티션 단위로 디스크 I/O를 분산해 부하를 줄일 수 있다.

오라클 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의 기본적인 문법은 다음과 같다.

Alter table Tb_Partition
Exchange partition par_200306
With table Tb_Exchange
(Without validation Including indexes)

사용자 삽입 이미지
<그림 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를 이용해 처리하면 네트워크의 속도가 떨어져 엄청난 시간이 소요된다. 그러나 이것을 일반 테이블을 여러 개로 파티션을 나누어서 파티션과 병렬 처리하면 성능이 크게 향상된다. 작업 순서는 다음과 같다.

create table t_cust_hash
storage (initial 5M next 5M pctincrease 0)
partition by hash(mem_no)
(
partition par_hash_1 TABLESPACE TS_DATA,
partition par_hash_2 TABLESPACE TS_DATA,
partition par_hash_3 TABLESPACE TS_DATA,
partition par_hash_4 TABLESPACE TS_DATA,
partition par_hash_6 TABLESPACE TS_DATA,
partition par_hash_7 TABLESPACE TS_DATA,
partition par_hash_8 TABLESPACE TS_DATA,
partition par_hash_9 TABLESPACE TS_DATA,
partition par_hash_10 TABLESPACE TS_DATA,
)
nologging
as
select /*+ parallel(x 10) */ * from t_cust x

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

create or replace view t_cust_1
as select * from t_cust_hash partition (par_hash_1);

create or replace view t_cust_2
as select * from t_cust_hash partition (par_hash_2);

create or replace view t_cust_3
as select * from t_cust_hash partition (par_hash_3)

……

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

T_cust_1.sql
create table t_cust_1
storage (initial 5M next 5M pctincrease 0)
nologging
tablespace njh
as
select /*+ parallel(x 4) */ * from t_cust_1@remote x;

T_cust_2.sql
create table t_cust_2
storage (initial 5M next 5M pctincrease 0)
nologging
tablespace njh
as
select /*+ parallel(x 4) */ * from t_cust_2@remote x

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

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

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)
UNUSABLE;

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

ALTER INDEX EMP_IDX1 REBUILD PARTITION PAR_10 PARALLEL 4; ---‘ index1.sql
ALTER INDEX EMP_IDX1 REBUILD PARTITION PAR_20 PARALLEL 4; ---‘ index2.sql
ALTER INDEX EMP_IDX1 REBUILD PARTITION PAR_30 PARALLEL 4; ---‘ index3.sql
ALTER INDEX EMP_IDX1 REBUILD PARTITION PAR_40 PARALLEL 4; ---‘ index4.sql
ALTER INDEX EMP_IDX1 REBUILD PARTITION PAR_MAX PARALLEL 4; ---‘ index5.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/

+ Recent posts