SELECT 컬럼,
        LTRIM(sys_connect_by_path(컬럼,','),',') AS 컬럼명
FROM  (
        SELECT 컬럼,
        menurole_id,
        row_number() OVER (partition by 그룹컬럼 order by 정렬할컬럼) rn,
        COUNT (*) OVER (partition by 그룹컬럼 ) cnt
        FROM 테이블명
)
WHERE level = cnt
start with rn = 1
connect by prior 그룹컬럼 = 그룹컬럼 and prior rn = rn-1

====================================================================

 세로로 출력되는 값들이 가로로 출력되는걸 확인할수 있다.

컬럼1   컬럼2

1                  나

2                  너

1                  우리

1                  개똥이

컬럼1       컬럼2

1                 나,우리,개똥이

2                 너


출처 : http://kinjsp.pe.kr/board/board_view.kin?boardId=721.0&boardTypeCode=1072&fastFlag=true&numberPerPage=10&pageNumber=0

출처 : http://kinjsp.pe.kr/board/board_view.kin?boardId=721.0&boardTypeCode=1072&fastFlag=true&numberPerPage=10&pageNumber=0

Ⅰ. Index 

인덱스는 테이블이나 클러스터에서 쓰여지는 선택적인 객체로써, 오라클 데이터베이스 테이블내의 원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조입니다. 인덱스는 일련의 엔트리 목록으로 구성되어 있으며, 이러한 엔트리는 테이블의 테이타 행에 사용되는 각각의 키값과 ROWID값을 가집니다. 따라서 특정한 값에 대해 ROWID값을 가지고 접근 하기 때문에 접근속도가 상당히 빠릅니다. 인덱스는 여러 가지 방법으로 구현될 수 있으나 오라클에서는 B*Tree기반의 인덱스를 사용합니다. 왜 이렇게 인덱스에 관해서 장황하게 설명하냐구요? 앞으로 나오는 거의 대부분의 튜닝 과정속에서 index가 중요하게 사용되기 때문입죠. 아울러 어떤이는 “가장 최적의 인덱스를 어떻게 구성할 것인가는 수 많은 애플리케이션을 잘 작성하는 것보다 훨씬중요하다.”라고 하더군요. 인덱스의 ROWID는 16진수 문자열로 표현된 테이블 데이터의 실제 메모리의 물리적인 주소를 저장하고 있는 데이터 타입을 말합니다. 아래는 인덱스를 생성하는 형식입니다.

CREATE [UNIQUE] INDEX index 

        ON table ( 

                column [ASC | DESC], .... 

        ) 

        [CLUSTER cluster] 

        [INITRANS int] 

        [MAXTRANS int] 

        [TABLESPACE tablespace] 

        [STORAGE storage_clause] 

        [PCTFREE int] 

        [NOSORT] 

여기서 column은 인덱스를 생성할 컬럼명을 뜻합니다. 인덱스 생성시 B*Tree의 키 값이 되겠죠,

따라서 SQL문에서 특정 column을 찾고자 할 때 이 키를 가지고 B*Tree 찾게 되므로 중요한 값입니다. 물론 index적용여부 또한 이 키가 결정 할 수도 있으니 키 선정에 많은 신경쓸 필요가 있겠군요. 뒤에서 인덱스 키를 선택하는 방법과 이를 이용한 쿼리 성능 향상 방법에 대해 다룹니다.

따라서 index가 생성되어 있는 경우 이 index를 이용한 검색은 물리주소를 직접 엑세스하기 때문에 일반검색에 비해 훨씬 빠른 속도를 냅니다. 그러니 우리에 목적은 얼마나 잘 이 index를 이용해 검색하느냐가 되겠지요. 그럼 바로 SQL문에서 index를 타지 못하는 경우를 살펴보겠습니다. 여기서 인덱스를 탄다고 하는 말은 인덱스를 이용한 검색이 실행됨을 뜻합니다. 다만 넓은 범위 처리시에는 테이블로의 랜덤 액세스가 증가하여 인덱스를 타는 경우 성능이 저하되기도 합니다. 그러한 경우는 clustering 적용을 검토해 봐야할듯 합니다. 그럼 인덱스를 사용할때 좋은 성능을 내는데도 불구하고 인덱스를 사용하지 못하게 되는 경우를 살펴보겠습니다.

 ◦ 인덱스 사용이 안되는 경우

1) index column이 변형되어 인덱스를 타지 못하는 경우

[예] select * from dept where substr(dname, 1, 3)='abc'; 

여기에서 인덱스로 사용 되어야 할 dname column이 함수로 인해 변형되어, index를 이루는 값을 찾지 못하여 full scan이 발생하는 경우입니다. 여기서 full scan이라함은 테이블의 처음부터 끝까지 순차적인 탐색이 일어나는 것을 뜻합니다. 정확히는 full rage scan이 맞으나 full scan이란 용어도 많이 쓰이므로 구분 없이 쓰겠습니다.

2) index column으로 사용될 column의 값이 부정형인경우 index타지 못한다. 

[예] select * from tmp where job<>'sales'; 

이 경우 역시 B*Tree에서 찾고자 하는 값이 아예 없으므로 인덱스를 타지 못하겠죠.

3)찾을 키가 NULL 혹은 NOT NULL의 경우 index를 타지못한다. 

[예] select * from emp where ename is NOT NULL; 

여기서 NULL인 경우 index는 만들어 지지만 key column엔 첨가되지 못하므로 index를 타지 못합니다, NOT NULL인 경우는 B*Tree에서 찾고자하는 키가 없으므로 역시 index를 타지못합니다. 

4)Optimizer의 취사선택으로 index scan이 발생되지 않는 경우 (‘7890‘만 인덱스로 생성된 경우) 

[예] select * from emp where job like 'ab%' and empno='7890'; 

이 경우는 SQL문이 해석되고 실행되는 과정에서 optimizer가 비용(여기선 탐색시간 혹은 응답시간 등)이 적게드는 키 값을 선택하여 그것을 기본으로 하여 다른 키 값을 찾기 때문에 (이것을 ‘경로를 선택한다’라고 합니다.) 위의 예에선 ‘ab%’에 해당하는 column이 테이블내에서 특정한 부분에 집중되어 있는 경우 optimizer가 이 column을 선택 할 경우 index를 타지 못하게 됩니다.

일단 이러한 경우만 피해간다고 하면 index사용시 상당한 효과를 볼 수 있겠습니다. insert, update, delete시 인덱스를 재구성해야하기 때문이죠 index도 서버에겐 하나의 overhead입니다. 그것을 어떤 상황에 맞게 구성하고 사용하는 것이 중요하겠죠.

 그러면 위와 같은 쿼리문이 꼭 필요한 경우에 인덱스를 적용시키고자 할 경우엔 어떻게 해야 될까요? “인덱스 타도록 강제로 지정해 주면 되지”하시면 정답입니다. 그럼 각각의 경우 어떻게 해결 할지 차례대로 살펴보겠습니다.

 ◦인덱스 미사용 해결

1) index column에 외부적(external)인 변형이 이루어진 경우

◦select * from dept where substr(dname, 1, 3)='abc';

→select * from dept where dname like "abc%";

◦select * from emp where sal*12=1200000;

→select * from emp where sal=1200000/12;

◦select * from emp where TO_CHAR(HIREDATE,'YYMMDD')='940101';

→select * from emp HIREDATE=TO_DATE('940101','YYMMDD');

◦select * from emp where job like 'ab%' and empno='7890';

→??? 앞서 말씀 드렸듯이 이경우는 데이터의 전체적인 분포를 알아야만 optimizer가 어떤 것을 선택 할지 예상이 가능하므로 테이블에 대한 지식이 우선해야 겠습니다.

◦select * from emp where empno between 100 and 200 and NVL(job, 'x')='clerk';

→select * from emp where empno between 100 and 200 and job='clerk';

◦select * from emp where deptno || nob='10salesman'; 

→select * from emp where deptno='10' and job='salesman'; 

[예외] 어디에나 그렇듯이 예외도 있습니다. 여기서 예외란 인덱스를 타지 않도록 만드는 경우가 인덱스를 타는 경우보다 빠른 SQL문을 말합니다. 이러한 방법을 사용하실 때엔 테이블 전체적인 데이터 분포도나 많이 사용되는 쿼리 종류라든지 혹은 자주 검색되는 데이터의 종류 등의 것에 대한 지식이 요구됩니다. 한마디로 테이블을 꿰고 있어야 된다는 말씀. 이러한 경우 index 적용을 피하게 하는 방법을 ‘의도적인 suppressing’ 이라합니다. 

[예]

◦select * from emp where job='manager'; 

→select * from emp where RTRIM(job)='manager'; 

여기서 job이 index로 구성 되있는 경우이고, 사람들이 입력시 왼쪽이나 오른쪽에 공백을 많이 입력하는 경우라면 굳이 index를 타는 것이 full scan보다 빠를 순 없습니다. 따라서 이러한 경우라면 full scan이 빠르겠죠. 

2) index column의 내부적(internal)인 변형시

앞서 살펴보았던 외부적인 변형들의 경우 대개 탐색을 위한 ‘키’값의 변형을 줄이는 방법이 많았던 반면 내부적인 변형은 바로 전에 보았던 suppressing이 주류를 이룹니다. 그럼 예를 보도록 하죠. 

테이블의 구조가 다음과 같다고 하고 아래의 예를 보시기 바랍니다. 

[테이블] 

create table samplet( 

        chr char(10), 

        num nubmer(12,3), 

        var varchar2(20), 

        dat date); 

◦select * from samplet where chr=10; 

→select * from samplet where TO_NUMBER(cha)=10; 

◦select * from samplet where num like '9410%'; 

→select * from samplet where TO_CHAR(num) like '9410%'; 

◦select * from samplet where dat='01-JAN-94'; 

→select * from samplet where dat=TO_DATE('01-JAN-94'); 

왜 설명 안하냐구요? 한번에 몽땅하겠습니다. suppressing의 경우 몇가지 기준이 있습니다. 대개의 경우 아래의 룰을 이용하시면 되겠습니다. 

변경 전 형식 
 변경 후 형식 
 
number=character
 TO_CHAR(number)=character
 
character=number
 TO_NUMBER(character)=number
 
date=character
 date=TO_DATE(character)
 

3) NOT Operator의 경우

◦select 'Not fount!' into :col1 from emp where empno<>'1234'; 

→select 'OK' into:col1 from emp where NOT EXISTS ( 

        select * from emp where empno='1234') 

여기에선 서브쿼리를 이용하여 키를 포함 하는 튜플들을 제외한 값들을 가져오게끔 변형한 예입니다. 이는 검색에 사용되는 키가 not operator인 경우 변형 할 수 있는 한가지 방법이 되겠습니다. 

◦select * from emp where ename like '김%' and job <> 'sales'; 

→(1)select * from emp where ename like '김%‘ 

        MINUS 

        select * from emp b where b.job ='sales'; 

→(2)select * from emp a where a.ename like ‘김%’ and NOT EXISTS( 

        select * from emp b where a.ename=b.ename and b.job='sales'); 

(1)번은 앞에서와 같은 변형방법으로 이것의 검색 횟수는 (자료가 n튜플 이라면) 2n번이 되겠습니다. 그에 비해 (2)번은 둘다 full scan이 일어나서 검색 횟수는 n2이 되겠죠. 

4) NULL, NOT NULL의 경우 

◦select * from emp where ename si not null; 

→select * from emp where ename > ''; 

ename이 문자형일 경우에 사용하는 예입니다.  

◦select * from emp where comm is not null; 

→select * from emp where comm > 0; 

comm이 number형일 경우 사용하는 예입니다. 

◦select * from emp where comm is null; 

→이건 full scan이 필요하겠죠. 아니면 null 값만을 가지고 index를 생성할수도 있으나 그만큼의 null이 있다면 그 외의 경우에서 빼는 방식이 더 빠르겠죠.

많은 사람들이 NULL을 정확하게 이해하지 못하고 사용하기를 꺼려합니다. 물론 그렇지 않은 분들도 많으시겠지만.... ^^;; 일단 하나만 기억 하고 넘어가야 할 것은 NULL이 탐색의 키로 이용될 경우 무조건 full scan이 발생하므로 이용시 많은 주의가 필요합니다. 그럼 NULL대해서 알아보죠. 

NULL은 테이블내에서 공백으로 표시 됩니다. 특징으로는 다음과 같죠 

∘ 어떤 값보다 크지도 않고 작지도 않다. 

∘ 그러므로 어떤 값과 비교될 수 없다. 

∘ 즉, NULL과의 연산결과는 NULL이 된다. 

대부분의 사람들이 세 번째를 놓치게 되는 경우가 많이 있습니다. 

즉 a,b중 하나가 NULL이면 avg(a+b)와 avg(a)+avg(b)는 모두 결과가 NULL이 됩니다. 

그러나 sum(a)의 경우는 NULL값을 참여시키지 않기 때문에 정확한 값이 나오죠. 

 그러면 이러한 NULL을 어떻게 이용 할 것인가? 

일단 조건이 있습니다. 특정한 data type이 테이블 내에서 많은 경우 아울러 full scan이 꼭 필요한 경우 이 data type을 NULL로 지정합니다. NULL은 공백으로 표시 되기 때문에 기억장치에서 공간을 차지 하지 않습니다. 따라서 NULL로 지정한 만큼 공간을 절약할 수 있겠죠. “그게 얼마나 된다고 굳이 NULL로 지정합니까?” 이렇게 물으신다면 만약 테이블 내에서 남여를 구분하는 column이 있다고 가정하면 남과 여중 한 값을 NULL로 한다면 일단 50%는 아끼는 셈이 되죠. 그양이 비록 작을 지라도. 따라서 이 경우에도 손익을 생각하셔서 적용하시면 됩니다. 

또 다음과 같은 경우에 NULL을 사용합니다. 

∘ 미확정 값을 표현하고자 할 때 

∘ 특정 값이 지나치게 많고 나머지 값만 주로 인덱스로 액세스 하고자 할때(위의 경우와 인덱스적용하여 공간과 탐색시간을 줄여주는 방법입니다.) 

∘ 결합인덱스의 구성컬럼이 된다면 NOT NULL로 

∘ 입력 조건값으로 자주 사용되면 NOT NULL로 

마지막의 두가지 경우는 앞으로 나올 예정이므로 따로 설명하지 않겠습니다. 

5) Optimizer의 취사선택으로 index scan이 발생되지 않는 경우를 앞서 살펴 보았습니다. 그러면 어떻게 해야 Optimizer가 제대로 index를 적용하도록 할까요? 이것을 알기위해서 먼저 Optimizer의 역할에 대해서 알아 보겠습니다. 

Optimizer의 목표는 select, update, insert, delete문을 최소한의 프로세싱 시간과 최단 시간의 I/O를 사용하여 실행 시키도록 하는 것이다. 이를 위해 실행계획을 세우고 문장을 실행하기 전에 가장 효과적인 계획을 선택하는 일을 한다. 이를 위해 Optimizer는 실행계획중 규칙기반 접근법(rule-based approach) 또는 비용기반 접근법(cost-based approach)중 하나를 선택한다. 그러나 Optimizer역시 사람이 만든것이라 완벽하게 처리할 수 없다 따라서 만물의 영장인 사람이 정확한 정보를 주어 신속하게 처리하도록 도움을 주어야만 한다. 그럼 앞에서 말한 두가지 접근법에 대해서 알아 보도록 하겠습니다.

①Rule-Based방식 

여러개의 가능한 경로를 찾아서 이미 정해져 있는 Rank를 기준으로 서로의 비용을 비교하고, 이를 토대로 가장 효율적인 것을 선택한다.

옵션 
 액세스경로(Access Path) 
 
1 
 ROWID에 의한 단일 행 접근 
 
2 
 클러스터 조인(Cluster Join)에 의한 단일 행 접근 
 
3 
 Unique key 또는 Primary key를 사용하는 Hash Clushter key에 의한 단일행 접근 
 
4 
 Unique key 또는 Primary key에 의한 단일행 접근 
 
5 
 Cluster Join 
 
6 
 Hash Cluster key 
 
7 
 Indexed Cluster key 
 
8 
 복합키(Composite key) 
 
9 
 단일 컬럼 인덱스(single-column indexes) 
 
10 
 인덱스 컬럼에서의 바운드 범위 조회 
 
11 
 인덱스 컬럼에서의 언바운드 범위 조회 
 
12 
 sort-merge join 
 
13 
 인덱스 컬럼의 MAX 또는 MIN값 
 
14 
 인덱스 컬럼에서의 order by 사용 
 
15 
 full-table scan 
 

②Cost-Based방식 

가장 효율적인 실행계획을 선택하기 위해서 데이커베이스의 통계자료를 사용한다. 오라클 RDBMS사용시 ANALYZE명령어를 사용하면 테이블, 클러스터, 인덱스 등의 통계자료들이 수집, 저장 된다. Cost-Based방식은 이렇게 모인 데이터를 사용한다. 

③ANALYZE명령어 

Cost-Based방식에 사용할 통계 데이터를 모아준다. 또한, 이 명령어는 다음과 같은 목적으로도 사용된다.

Function 
 설   명 
 
통계데이터 수집
 Table, Cluster, Index등에 관련된 자료들을 모아 Cost-Based Optimization에 사용한다. 
 
데이터 무결성 확인
 Table, Cluster, Index등의 무결성 확인 작업을 한다. 
 
Chained-row통계
 Table, Cluster에 있는 데이터 체인행(Chained row)에 대한 통계 자료 수집 
 

그럼 Optimizer가 어떻게 작동하는지 몇가지 예를 들어 보겠습니다. 

∘select * from emp where ename like 'ab%' and empno='7890'; 

→둘다 index일 때 값이 더욱 확실한 empno index만 사용합니다. 

∘select * from emp where ename like 'ab%' and job like 'sa%'; 

→ename or job index중 하나만 사용, 혹은 full scan(이 경우엔 index merge라고 함) 

∘select * from emp where empno > '10'; 

→이와 같은 경우는 특정한 값을 찾는 것이 아니라 테이블 전체를 탐색해야 하므로 full scan선택이 비용이 덜듭니다. 따라서 full scan 선택. 

∘select /* INDEX(emp job_IDX)*/ from emp where ename like 'ab%' and job like 'sa%'; 

→만약 데이터의 분포도나 탐색빈도수가 job index가 월등히 많이 사용된다면 프로그래머가 강제적으로 우선순위를 높여줄수 있는데 이것이 바로 HINT라고 합니다. 형식은 위와 같이 쉽운 편이지만 테이블에 들어가는 데이터들에 대한 지식이 우선되야 하겠지요. 

<Hints에 관하여... 

Hints를 통해서 optimizer에게 알려줄수 있는 정보는 다음과 같습니다. 

-SQL연산을 위한 Cost-based접근 방식의 목표 

-index보다 더 효과적인 Scan방식 

-Join순서 

-병렬연산 순서 

문법 

/*+comment */ →‘+’ 다음에 나오는 내용이 Hints라는 것을 Optimizer에게 알려준다. 

이정도만 이해하고 넘어가도 될 것 같습니다. 

▸Index의 활용 및 적용기준 

“index를 언제 사용 할 것이냐?” 문제가 되겠지요. 절대적인 기준은 아니지만 대개의 경우 적용할 만한 적용 기준은 다음과 같습니다.

-6블록 이상의 테이블에 적용(6블록 이하는 연결고리만) 

-컬럼의 분포도가 10-15%이내인 경우 적용 

-분포도가 범위내이더라도 절대량이 많은 경우에는 단일 테이블 클러스터링을 컴토할 것 

-분포도가 범위 이상이더라도 부분범위 처리를 목적으로 하는 경우에는 적용 

-인덱스만을 사용하여 요구를 해결하고자 하는 경우는 분포도가 나쁘더라도 적용할 수 있음(손익분기점 10-15%이내) 

-질의에서 선택된 column의 값이 동일한 Rows들은 그 Table에 할당된 data block에 균일하게 분산되어 있을 때 

-Table의 row는 질의되는 column에 대하여 불규칙적으로 분포 

-Table에 할당된 각 data block은 최소한 10개의 row를 포함할 때 적용 

-Table은 상당히 작은 수의 column을 가질 때 

-Table에 대한 대부분의 질의들은 비교적 단순한 WHERE절일 때 

-Cache hit ratio는 낮고 operating system cache는 없을 때

<분포도 = 1/컬럼값의 종류 * 100 = 컬럼값의 평균 로우수/테이블의 총 로우수 * 100 

대개 앞의 것을 많이 이용합니다. 예를 들어 5가지 데이터가 들어있는 컬럼의 분포도는 20%이다.

<부분범위 처리란? 

먼저 전체범위 처리에 대해서 알아보면 SQL문에서 group by문이나 order by 문의 경우 테이블의 처음부터 끝까지 전제적으로 정렬이 이루어 져야 한다. 이러한 경우를 전제 범위 처리라 한다. 이와는 반대의 경우를 부분범위 처리라하고 튜닝시 되도록 전제범위처리를 피하는 것이 바람직하다. 

<손익분기점 read하고자 하는 컬럼스/access해야할 컬럼수 * 100 

 손익분기점이 높으면 hit ratio가 낮음을 의미하고, 손익분기점이 낮으면 hit ratio가 높다. 

지금까진 single index만을 살펴 보았습니다. 그러면 결합인덱스에 대해서 알아 보겠습니다. 

결합 인덱스는 말그대로 2개이상(오라클에선 16개까지 가능)의 컬럼을 가지고 index를 생성하는 경우를 말합니니다.  

▸결합인덱스의 장점 

-좋은 분포도 : 나쁜 분포도를 가진 column을 결합한 결합 인덱스가 더 좋은 분포도를 가질 수 있다. 

-저장 공간의 효율성 : 한 질의에 의해 선택된 모든 칼럼이 결합인덱스에 있을 경우, table을 access하지 않고 결합 인덱스 만으로 원하는 값을 가져올 수 있다. 그러나 그만큼의 디스크나 메모리에 부하가 많이 걸릴 것이다. 

[예] select empno from emp where empno='123';  

이 경우는 특별히 테이블을 access하지 않고 index만으로 해당값을 출력할수 있다. 따라서 검색속도가 비약적으로 빨라지는 효과를 볼 수 있습니다. 

▸결합 인덱스의 column선택을 위한 지침  

-각 column의 분포도 보다 결합 인덱스에서 결합된 분포도가 더 좋을 경우 

-여러 질의에서 하나이상의 칼럼값을 가진 칼럼의 동일한 집합을 질의할 경우, 이들 모든 칼럼을 포함하는 결합 인덱스 생성을 고려한다. 

▸결합 인덱스 구성시 column순서 배치를 위한 지침  

-WHERE절에 사용된 칼럼을 선행부분으로 만들기 위한 결합 인덱스를 생성 

-칼럼의 일부가 WHERE절에서 자주 사용될 경우 =>자주 select되는 column을 선행부분올 만들어서 이 column만으로 인덱스를 사용할 수 있도록 한다.  

-모든 칼럼이 WHERE절에서 동일하게 사용되면 =>질의 성능을 개선하기 위하여 CREATE INDEX statement에서 분포도가 좋은 순서대로 배열 

-모든 칼럼이 WHERE절에서 동일하게 자주 사용되지만 데이터가 한 column에 대해 물리적으로 정렬되어 있으면 =>그 column을 결합 인덱스의 첫번째로 구성 

-‘=’을 사용할 땐 선택범위가 좁은 것을 앞에 놓는다.

<in을 이용한 access효율 향상. (in은 ‘=’의 의미를 가지고 있다.) 

예) select * from tab1 where col1='a' and col2 between '111' and '112'; 

→select * from tab1 where col1='a' and col2 in('111', '112'); 

여기서 col2의 값이 적을수록 후자의 속도가 빨라진다. 물론 보통의 경우에도 후자가 빠르다. 

일반적으로 between, like문이 들어가는 SQL문은 in문으로 대체해 준다. 

[주의] 만약 index가 (a+b+c)로 이루어져 있을 경우 where조건에서 a, a+b, a+c, a+b+c가 나오면 index적용이 가능 하지만, b, b+c가 나오면 index적용이 불가능하다. -> B*Tree에서 검색이 불가능 하기 때문에.. 따라서 결합인덱스의 첫 번째 컬럼이 꼭 나와야 index를 적용할 수가 있다. 아울러 대개의 결합인덱스는 최고 5개까지가 적당함. 그럼 간략히 정리를 해보겠습니다. 

<인덱스 선정 절차 

-해당 테이블의 액세스 유형조사 

-대상 컬럼의 선정 및 분포도 분석 

-반복 수행되는 액세스 경로의 해결 

-클러스트링 검토 

-인덱스 컬럼의 조합 및 순서의 결정 

-시험생성 및 테스트 

-수정이 필요한 애플리케이션 조사 및 수정 

-일괄 적용 

<액세스 유형의 조사(설계단계) 

-반복 수행되는 액세스의 형태를 찾는다. 

-분포도가 아주 양호한 컬럼의 찾아 액세스 유형을 찾는다. 

-자주 넓은 범위의 조건이 주여되는 경우를 찾는다. 

-자주 조건절에 사용되는 컬럼들의 액세스 유형을 찾는다. 

-자주 결합되어 사용되는 경우를 찾는다. 

-sort의 유형을 조사한다. 

-통계자료 추출을 위한 액세스 유형을 조사한다. 

<index의 활용(선정기준) 

-분포도가 좋은 컬럼은 단독적으로 생성하여 활용도 향상 

-자주 조합되어 사용되는 경우는 결합인덱스 생성 

-각종 엑세스 경우의 수를 만족할 수 있도록 인덱스간의 역할 분담. 

-가능한 수정이 빈번하지 않는 컬럼 

-기본키 및 외부키(조인의 연결고리가 되는 컬럼) 

-반복수행(loop)되는 조건은 가장 빠른 수행속도를 내게 할 것 

-실제 조사된 액세스 종류를 토대로 선정 및 검증 

<index의 활용(고려사항) 

-새로 추가된 인덱스는 기족 액세스 경로에 영향을 미칠 수 있음 

-지나치게 많은 인덱스는 오버헤드 발생 

-넓은 범위를 인덱스로 처리시 많은 오버헤드 발생 

-Optimizer를 위한 통계데이타를 주기적으로 갱신 

-인덱스의 개수는 테이블의 사용형태에 따라 적절히 생성 

-분포도가 양호한 컬럼도 처리범위에 따라 분포도가 나빠질 수 있음. 

-인덱스 사용원칙을 준수애야 인덱스가 사용됨 

-조인(join)시에 인덱스가 사용여부에 주의 

-데이터 변경시 자주 리빌드해주는 것이 좋다.

Ⅱ. Cluster

 cluster는 테이블에 데이터를 저장하는 방식입니다. 데이터를 저장하는데 있어서 클러스터를 사용할 것인지의 여부는 SQL문장과는 아무런 관계가 없으면, 단지 테이블을 저장하는 방법에 적용됩니다. cluster는 다수 개의 테이블을 하나의 오라클 블록에 저장하는 메커니즘이며, cluster index를 꼭 필요로 합니다. 테이블을 clustering하는 순서는 다음과 같습니다.

①cluster를 생성한다. 

②cluster index를 생성한다. 

③cluster table들을 생성한다. 

그럼 cluster생성 문법을 알아 보겠습니다. 

CREATE CLUSTER cluster( 

        column data_type, ... 

) 

        [PCTUSED int] 

        [PCTFREE int] 

        [INITRANS int] 

        [MAXTRANS int] 

        [SIZE int [K or M]] 

        [TABLESPACE tablespace] 

        [STORAGE storage_clause] 

여기서 주의 해야 할 것은 SIZE로 이것은 하나의 클러스터 키와 그 데이터를 함께 저장하는 데 필요한 스페이스의 크기를 byte단위로 지정한다. 즉 SIZE는 하나의 데이터 블록에 같이 저장될 쉬 있는 클러스터 키의 총수를 결정 짓는 옵션이다. 

[예] 

①create cluster cluster_t1_t2( 

        idnum number(3) 

) 

size 400 

tablespace data1 

storage (initial 30k); 

②create index ind_cluster_t1_t2( 

on cluster cluster_t1_t2 

tablespace idx1; 

③create table t1( 

name varchar2(10), 

hire_date date, 

idnum number(3) 

) 

cluster cluster_t1_t2 (idnum); 

create table t2( 

name varchar2(10), 

idnum number(3) 

) 

cluster cluster_t1_t2 (idnum); 

이렇게 지정하면 테이블 t1과 t2의 데이터는 같은 데이터 블록에 저장됩니다. 그럼 이제 본론으로 들어가겠습니다. 

이해를 쉽게하기 위해 앞에서 보았던 index와 cluster index의 차이 점을 짚어보면 일반 index는 rowid를 가지고 있는 반면 cluster index는 block의 header를 가지고 있습니다. 따라서 보통의 경우엔 일반 index가 빠른 속도를 냅니다. 그러나 중복 데이터를 많이 가지고 있는 컬럼이 있는 테이블의 경우 그 성능이 훨씬 높게 됩니다. 물론 I/O이용이 적으면서 이러한 효과를 볼 수 있다니 얼마나 좋습니까. 따라서 앞에서 다루었던 index와 마찬가지로 cluster 역시 해당 테이블에 대한 정확한 이해를 바탕으로 이루어 져야 합니다. 그럼 정리해 보겠습니다. 

<cluster의 특징 

-지정된 컬럼값의 순서대로 로우를 저장시키는 방법 

-하나 혹은 그 이상의 테이블을 같은 클러스터내 저장 가능 

-엑세스 기법이 아니라 액세스 효율향상을 위한 물리적 저장기법 

-검색 효율을 높여주나 입력, 수정, 삭제시는 부하증가 

-분포도가 넓을 수록 오히려 유리(인덱스의 단점을 해결 5~7배) 

-분포도가 넓은 테이블의 클러스터링은 오히려 저장공간 절약 

마지막 것은 좀 이해가 안되는 부분이요. 이유인 즉슨 오라클 내부에서 동한건에 대해 나머지 값들은 저장이 안된다고 합니다. 따라서 그만큼의 저장 공간이 절약되겠죠. 

<cluster의 활용(선정기준) 

-6블록 이상의 테이블 

-다량의 범위를 자주 액세스 해야 하는 경우 

-인덱스를 사용한 처리가 부담되는 넓은 분포도 

-여러 개의 테이블이 빈번한 조인을 일으킬 때 

-반복 컬럼이 정규화 작업에 의한 어떨수 없이 분할된 경우 

-UNION, DISTICT. ORDER BY, GROUP BY가 빈번한 컬럼이면 고려해 볼 것 

-수정이 자주 발생하지 않는 커럼

-처리 범위가 넓어 문제가 발생되는 경우 단일 테이블 클러스터링 

-조인이 많아 문제가 발생되는 경우는 다중 테이블 클러스터링 

<cluster의 활용(고려사항) 

-데이터 처리(입력, 수정, 삭제)에 오버헤드 발생 주의 

-인덱스로도 충분한 범위는 클러스터링 효과가 없음 

-클러스터 키는 수정이 빈번하지 않을 것 

-각종 액세스형태에 대해 인덱스와 적절한 역할 분담 

-클러스터링은 기존의 인덱스의 수를 감소시킴(인덱스 재구성) 

-클러스터 SIZE 인자가 중요 

-클러스터 키별 로우 수의 편차가 심하지 않을 것 

-클러스터에 데이터 입력시 로우가 적은 테이블부터 실시할 것 

-클러스터링된 테이블 조인시 로우 수의 역순으로 from절에 기술할 것 

-클러스터 키를 첫 번째로 하는 인덱스는 생성하지 말것(optimizer에 의해 선택 문제 발생) 

<index와 cluster의 비교

Ⅲ. VIEW 

 보통 뷰를 이용는 경우는 보안이나 복잡한 쿼리를 피하기 위해 사용 하는 경우가 많이 있습니다. 그럼 과연 튜닝에는 어떻게 쓰일수 있을까요? 일반적으로 튜닝시에는 다음의 두가지 목적을 위해 사용합니다. 

<수행속도 향상을 위한 뷰 

-수행 속도 향상을 위해 미리 튜닝한 select문을 뷰로변환 시켜 사용 

-M:1조인의 연결회수를 감소 시키기 위해 먼저 group by된 뷰를 만들고 그 뷰와 조인을 일으키게 하기 위한 뷰 

-특정 client tool의 문제 해결을 위한 뷰 

-수행 속도에 심한 영향을 주는 넓은 범위 처리나 특정 컬럼의 조건검색을 막아 악성검색을 방지하기 위한 뷰 

-특정한 절차로 수행시키기 위해 뷰의 select list에 suppressing, hint등을 사용한 뷰 

설명이 필요 없으리라 생각됩니다. 

<SQL 기능 향상을 위한 뷰 

-불규칙적인 sort가 필요한 경우 

-group by결과를 다시 가공하고자 하는 경우(group by는 어쩔수 없이 full scan이 발생 합니다. 따라서 그에따른 비용도 많이 듭니다. 따라서 미리 group by결과를 뷰로 가지고 있으면 그만큼의 비용을 절감 할 수 있겠지요) 

-서로 다른 테이블의 group by결과를 같은 줄에 맞추려는 경우 

-ROWNUM을 이용한 특별한 처리 

-각 row에 있는 값들(예:일자)간의 가공처리(예:기간산정) 

-각각의 소계와 그 내역을 하나의 SQL로 처리 

-복잡한 outer join의 해결을 위한 뷰(outer join은 만족하는 레코드뿐만 아니라 만족하지 않는 레코드들도 출력을 해줍니다. 따라서 만족하지 않는 값으로 찾고자 할 경우 full scan이 발생하여 전제 SQL문의 속도를 늦춰 줍니다. 이러한 경우 뷰를 이용하여 만족하지 않는 레코드를 따로 처리해 놓으면 훨씬 빠른 속도를 얻을수 있습니다.) 

-테이블은 row를 가지지 않으나 뷰는 row를 가지도록 한 뷰 

-SQL*From의 execute_query를 활용하기 위한 뷰 

-기타 SQL의 기능 확장을 위해 원하는 임의의 집합이 필요한 경우 

-코드성 테이블을 미리 조인한 후 fact table과 다시 조인한다.(코드성테이블이란 master file과 같은 의미로 삽입이나 삭제가 거의 되지 않고 참조위주의 테이블을 말합니다. 예로 이자율테이블이나 할인율 테이블 같은 것이 되겠죠, 그리고 fact table은 사용자가 입력한 테이터 정보를 모두 가지고 있는 테이블입니다. 보통의 테이블들이 해당 되겠죠. 아울러 덩치도 아주 큽니다.) 

-조인시 데이터 범위가 작은 테이블을 먼저 조인한 다음 점점 큰 순서로 조인하는 것이 훨씬 빠른 속도를 냅니다. 

Ⅳ. 부분범위 처리(Partial Range Scan) 

그럼 일단 그림을 먼저 보겠습니다

<부분범위 처리 방법 

-조건을 만족하느 전제 집합이 아닌 일부분만 access 

-data량이 많아도 performance에 지장이 없고, 오히려 향상 

-index나 cluster를 적절히 활용한 sort의 대체(order by사용을 대체) 

-max처리 

-table은 access하지 않고 index만 사용하도록 유도 

-exists의 활용 

-rownum의 활용 (rownum- 1,2차 가공시 나오는 건수) 

-query를 이원화 하여 일부분씩 scan하도록 유도 

-stored function을 이용 

[예] 

여기서 첫 번째 문장에서는 index가 ymd만으로 이루어졌고, 두 번재 문장에서는 index가 ymd+item으로 이루어 졌다고 가정합니다. 

∘select * from product where ymd='951023' and item like 'ab%' order by ymd, item; 

→select * from product where ymd='951023' and item like 'ab%'; 

sort를대체하는 예 

∘select orddate, custno from orddate between '940101' and '941130' order by orddate desc; 

→select --+index_desc(a orddate) orddate, custno from order1t a where orddate between '940101' and '941130'; 

∘select orddate, custno from order1t where orddept like '7%' order by orddate desc; 

→select --+index_desc(a orddate) orddate, custno from order1t a where orddept like '7%' and orddate <='991231';

<index생성시 아무 옵션도 주지않을 경우 default로 ASC옵션이 적용되어 생성된다. 

따라서 DESC로 출력을 원할 경우 다음 두방법중 하나를 이용하면 된다. 

1. 뒤에서부터 검색하거나 

2. index create시 desc로 생성시켜줘야 속도 향상을 얻을 수 있다. 

max처리하는 예 (전자는 index(dept), 후자는 index(dept+seq)로 index생성 되어 있음) 

∘select max(seq)+1 from product where dept='12300'; 

→select /*+index_desc(a index1)*/ seq+1 from product a where dept='12300' and rownum=1; 

rownum이용 예 

∘select count(*) into:cnt from item_tab where dept='101' and seq>100 ...... 

→select 1 into:cnt from item_tabl where dept='101' and seq>100 and rownum=1... 

1:M join의 부분범위 유도 예 

∘select x.cust_no, x.addr, x.name, ....... from cust x, reqt y where x.cust_no=y.cust_no and x.cust_stat in ('a', 'c', 'f') and y.un_pay >0 group by x.cust_no having sum(y.un_pay) between :val1 and :val2; 

→select cust_no, addr, un_pay, .... from (select cust_no, addr, unpay_sum(cust_no) as un_pay, ......... from cust where cust_stat in ('a', 'c', 'f')) where un_pay between :val1 and :val2;

Ⅴ. 조인 메커니즘의 이해 

조인의 경우 사용히 매우 신중을 기해야 하는 것중의 하나입니다. 특히 조인 컬럼이용되는 컬럼의 선정시 특히 많은 주위를 해야 optimizer의 실행 순서 선택시 우리가 원하는 수행능력을 보장 받을 수 있습니다. 그럼 조인시 수행속도에 영향을 끼치는 것들에 대해서 한가지씩 알아 보겠습니다.

<driving의 영향

<Join순서의 영향

<index의 영향 

양쪽에 index 
 한쪽에 index 
 
① 
 tab1 
 range scan 
 ③ 
 tab1 
 range scan 
 
tab2 
 index scan 
 tab2 
 full scan 
 
② 
 tab1 
 range scan 
 ④ 
 tab1 
 full scan 
 
tab2 
 index scan 
 tab2 
 range scan 
 

①,②의 경우는 일량의 변화가 없다. 

③의 경우 n*m번 비교가 일어난다.(최악의 경우) 이런 경우 ④과 같이 변형하면 조금이나마 성능 향상에 도움이 된다. 

<Join시 유의사항 

-join되는 key column의 data type을갖게 한다. 

-join되는 key column을 index로 만들어 준다.(절대 변형되지 않도록한다.-변형시 full scan발생) 

-두 table간에 join이 맞지 않을 경우(한쪽에만 index가 있는 경우) driving table을 full scan table로 설정하는 것이 최악의 경우를 방지 할 수 있다. 

-두 index에 대한 분포도가 같은 경우 from절의 오른쪽에 있는 table을 driving으로 삼는다. 

-check조건이 되는 테이블의 범위는 넓을 수록 좋다.(사용자에게 테이블의 내용을 빨리 display 할 때 보다 빨리 전송단위 씩 보내줄수 있기 때문에) 

<join과 loop query의 속도 비교. 

-loop query가 빠른 경우 → 부분범위 처리가 발생 할 경우 

-부분범위 처리가 join과 loop query 둘다 발생하면 (전체가 부분범위 처리인 경우) → join이 빠르다. 

-전체 범위 처리가 발생한 이유가 모든 table에 있으면 → join이 빠르다. 

-연결되는 table중 하나를 부분범위로 바꿀수 있다면 → loop query가 빠르다. 

<Loop query? 

말 그대로 query를 loop로 돌려 특정 값을 다음 테이블내에서 찾는 방법입니다. 보통의 SQL문에서 사용하는 경우는 거의 없고 batch 혹은 pl_SQL 사용시 많이 이용됩니다. 

Ⅵ. Trace 

하하 이젠 분석도구라 불리는 놈들을 보겠습니다. 어디가 잘못 됐는지 알아야 고칠거 아닙니까? 따라서 가장 중요하다고 생각합니다. 그런데 어렵더군요. 저 역시 교육 받을 때 이 부분에서 거의 헤맸습니다. ^^;; 여러분은 그러시지 않기를 빕니다.  

<SQL Trace? 

SQL문을 튜닝하기 위해서는 현제의 SQL문이 어떻게 수행되는 가를 정확하게 파악해야 할 필요가 있습니다. 그럼 간단하게 Trace의 사용 목적에 대해서 알아보죠. 

< Trace 파일은 시스템을 튜닝하는데 필요한 아주 유요한 정보(cpu Time, 총수행시간, I/O횟수 등등 아래 자세히 설명)를 제공한다.  

< SQL문의 실행통계를 Session별로 모아서 Trace 파일을 만든다. 

   - SQL Parsing, Execute, Fetch를 수행한 횟수 

   - CPU Time, Elapsed Time(총 경과시간) 

   - Disk(물리적), Memory(논리적) I/O 수행한 횟수 

   - 추출된 Row의 수 

   - 라이브러리 캐쉬 miss 수 

   - Parse Count 

그럼 trace를 한번 만들어 보자구요. 물론 슆지는 않습니다. 또한 Trace를 생성하는 동안 시스템 전체적인 수행성능은 20%~30% 정도 감소합니다. 따라서 운영중인 DB의 경우에는 되도록 사용을 금하기소 되도록 개발용 장비에서 실행하기기 바랍니다. 

먼저 SQL_TRACE를 생성하려면 다음과 같은 파라미터들을 INIT.ORA에 지정해야합니다. 

- CREATE INDEX문에서 NOSORT Option의 사용 

- TIMED_STATISTICS=TRUE → 시간 통계를 모을 수 있게 한다. 

- SQL_TRACE=TRUE → Session을 종료하는 모든 사용자들의 Trace를 수행한다. 

- USER_DUMP_DEST=directory path → SQL_TRACE가 Trace 파일을 저장하는 디렉토리를 지정한다. default는 시스템 덤프(dump) 디레토리이다.(예; oracle_home/rdbms/log) 

-MAX_DUMP_SIZE=number → Trace 파일의 물리적인 크기를 바이트 단위로 지정할 수 있게 한다. 

[주의] SQL_TRACE는 공간이 부족하면, 완전한 출력이 되지 않습니다. 따라서 디스크 공간을 확보하신후 실행 시키싶시오, 아울러 주기적으로 필요 없는 파일들은 삭제해 주어야 합니다. 

[예]  

timed_statistics = true          # if you want timed statistics 

user_dump_dest = /oracle8/app/oracle/product/8.0.3/rdbms/log 

max_dump_file_size = 10240       # limit trace file size to 5 Meg each 

-SQL*Plus에서는 다음과 같이 세션을 초기화시켜야 합니다. 

SQL>alter session set sql_trace=true; 

-optimizer goal의 변경은 다음과 같습니다. 

SQL>alter session set optimizer_goal=rule;(optimizer goal을 정의) 

-RDBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION Produce사용 

SQL>execute RDBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(11,6,TRUE) 

여기서 11은 sid, 6은 Serial number(둘다 v$session에서 확인), TRUE는 Trace enable입니다. 

이렇게 설정해 두면 실행되는 질의문에 대해서 트레이스 파일이 생성된다. 생성되는 위치는 initSID.ora에서 정의한 user_dump_dest 디렉토리에 *.trc형태로 생성됩니다. 

그럼 이제 Trace 파일을 보기만 하면 됩니다. 그러나 생성된 트레이스 파일은(*.trc) 바로 볼 수 없습니다. 시험삼아 한번 vi에디터로 열고 보셔도 물론 됩니다. 그러나 수많은 문자들 가운데서 우리가 원하는 정보를 찾기란 너무 어렵습니다. 그래서 tkprof라는 유틸리티를 사용하여 생성된 트레이스 파일을 분석이 가능한 형식으로 전환해줘야 합니다. 물론 기본적으로 오라클에서 제공하는 유틸리티입니다.

이것은 이미 생성된 트레이스 파일이나 트레이스 파일을 생성하고 있는 중에도 tkprof를 수행시킬 수 있습니다. 트레이스 파일은 SQL문에 대한 실행계획뿐만 아니라 실행시간, 다양한 옵션을 이용하여 분석하기 쉬운 형태 등의 정보를 보여줍니다.

그럼 tkprof란 놈은 어떻게 실행 시킬까요? 

Usage: tkprof tracefile outputfile [explain=user/passwd] [table=schema.tablename] 

            [print=integer] [insert=filename] [sys=yes/no] [sort=option] 

 tracefile : 생성된 트레이스 파일명 

 outputfile : tkprof가 출력하는 텍스트 파일명(디폴트로 확장자가 .prf임) 

 explain=user/passwd : 해당 트레이스 파일이 수행된 세션의 사용자 및 패스워드 

 table=schema.tablename : 실행계획(execution plan)을 저장할 TKPROF 임시 테이블의 이름 

 print=integer : 트레이스 파일별로 출력시킬 SQL문의 수 

 aggregate=yes|no 

 insert=filename : List SQL statements and data inside INSERT statements. 

 sys=yes/no : TKPROF does not list SQL statements run as user SYS. 

 record=filename : Record non-recursive statements found in the trace file. 

 sort=option : Set of zero or more of the following sort options: 

 < sort option의 종류 > 

    prscnt number of times parse was called 

    prscpu cpu time parsing 

    prsela elapsed time parsing 

    prsdsk number of disk reads during parse 

    prsqry number of buffers for consistent read during parse 

    prscu  number of buffers for current read during parse 

    prsmis number of misses in library cache during parse 

    execnt number of execute was called 

    execpu cpu time spent executing 

    exeela elapsed time executing 

    exedsk number of disk reads during execute 

    exeqry number of buffers for consistent read during execute 

    execu  number of buffers for current read during execute 

    exerow number of rows processed during execute 

    exemis number of library cache misses during execute 

    fchcnt number of times fetch was called 

    fchcpu cpu time spent fetching 

    fchela elapsed time fetching 

    fchdsk number of disk reads during fetch 

    fchqry number of buffers for consistent read during fetch 

    fchcu  number of buffers for current read during fetch 

    fchrow number of rows fetched 

    userid userid of user that parsed the cursor 

여기서 SQL문을 실행하는데 걸린 CPU시간을 보여주는 EXECPU가 가장 실용적이다. 만약 init.ora 파라미터를 TIMED_STATISTICS=FALSE로 지정했을 때는 수행 중에 액세스된 블록 수를 보여주는 EXEQRY가 가장 실용적이다. 

[예] tkprof ccdb_ora_1124.trc 1124.txt explain=scott/tiger 

그럼 이제 우리가 눈으로 보기에 꽤 괜찮은 파일이 만들어 집니다.  

- tkprof는 정형화된 리스트(출력파일)를 생성합니다..

- 생성된 파일에는 다음과 같은 내용들을 포함하고 있습니다.

call 
 count 
 cpu 
 elapsed 
 disk 
 query 
 current 
 rows 
 
Parse 
 1 
 0.01 
 0.01 
 0 
 0 
 0 
 0 
 
Execute 
 1 
 0.00 
 0.00 
 0 
 0 
 0 
 0 
 
Fetch 
 1 
 0.00 
 0.02 
 2 
 3 
 0 
 1 
 
total 
 3 
 0.01 
 0.03 
 2 
 3 
 0 
 1 
 

보기 좋게 표로 만들었습니다만 실제론 이렇게 출력되진 않습니다. 그럼 하나 하나 살펴 보지요. 

- parse 

·SQL문이 파싱되는 단계에 대한 통계이다. 새로 파싱을 했거나, 공유 풀에서 찾아 온 것도 포함된다. 

·단, PL/SQL 내에서 반복 수행(Loop)된 SQL이나 PRO*SQL에서 보존커서(Hold cursor)를 지정한 경우에는 한번만 파싱된다. 

- execute 

·SQL문의 실행 단계에 대한 통계이다. UPDATE, INSERT, DELETE 문들은 여기에 수행한 결과가 나타난다. 

· 전체범위 방식으로 처리된 결과가 여러 건인 경우는 주로 여기에 많은 값이 나타나며 fetch에는 아주 적은 값이 나타난다. 

- fetch 

·SQL문이 실해되면서 페치된 통계이다.  

· 부분범위 방식으로 처리된 SELECT문들이나 전체범위 처리를 한 후 한 건을 추출하는 경우(AGGREGATE, 전체집계, Count 등)는 주로 여기에 많은 값들이 나타나고 execute에는 아주 적은 값이 나타난다. 

- count 

·SQL문이 파싱된 횟수, 실행된 횟수, 페치가 수행된 횟수이다.  

- cpu 

·pares, execute, fetch가 실제로 사용한 CPU 시간이다.(1/100초 단위)  

- elapsed 

·작업의 시작에서 종료시까지 실제 소요된 총 시간이다.  

- disk 

·디스크에서 읽혀진 데이타 블록의 수  

- query 

·메모리 내에서 변경되지 않은 블록을 읽거나 다른 세션에 의해 변경 되었으나 아직 Commit되지 않아 복사해 둔 스냅샷 블록을 읽은 블록의 수이다.  

·SELECT문에서는 거의가 여기에 해당하며 UPDATE, DELETE, INSERT 시에는 소량만 발생한다.  

- current 

·현 세션에서 작업한 내용을 Commit하지 않아 오로지 자신에게만 유효한 블록(Dirty Block)을 액섹스한 블록 수이다. 

· 주로 UPDATE, INSERT, DELETE 작업시 많이 발생한다. SELECT 문에서는 거의 없으나 아주 적은 양인 경우가 대부분이다. 

- rows 

·SQL문을 수행한 결과에 의해 최종적으로 액세스된 로우의 수이다. 

·서브쿼리에 의해서 추출된 로우는 제외된다. 

·만약 SUM, AVG, MAX, MIN, COUNT 등의 그룹함수를 사용한 경우라면 큰 의미가 없다. 

☞분석결과의 예  

- execute, fetch의 횟수가 동일하다는 것은 SQL 수행시마다 기본키에 의해 한건씩만 처리되고 있다는 것을 의미한다. 만약 pares가 1인데 execurte와 fetch가 100이라면 루프가 100번 수행되면서(어프리케인션은 한번만 수행되고 SQL은 루프 내에서 반복수행되었다. 왜냐하면, 어플리케이션이 여러번 실행되었다면 비록 SQL이 실제 파싱하지 않고 Shared SQL Area에서 찾아 왔다고 해도 parse의 횟수는 증가되기 때문이다) 보관커서 상태의 SQL이 한 건씩을 추출한 상태이다. 이 경우의 SQL문은 'SELECT ... INTO ...'형식으로 사용되었을 것이다.  

- parse가 1이고 execute가 1이며, fetch가 100이라면 SQL은 단 한번 수행되었고(루프 내에서 수행되지 않았음) 페치만 연속해서 100번을 수행한 것이다. 이 경우의 SQL문은 대개 'DECLARE CURSOR'로 선언한 SQL이 'FETCH ... INTO ...'에 의해 SQLCODE가 '1403'(Date Not Found)일 때까지 수행되었거나 부분범위 처리에 의해 일정 양만큼만 수행하고 멈추었을 때이다. 

- parse : execute : fetch의 비율은 공통 작업이 여러번 수행되면 그 배수로 나타난다. 예를 들면 parse : execute : fetch가 10 : 10 : 1000인 경우는 1 : 1 : 100인 작업이 10번 수행되었다는 것을 의미한다. 

- fetch가 10인데 rows가 100이라면 운반단위가 10인 다중처리(Array Processing)를 사용하여 한번 페치마다 10건의 로우가 추출되었음을 의미한다. 

- 트레이스의 중간부분에 'Misses im library cache during parse : 1'이라는 문장이 있다. 이것은 공유 SQL 영역에서 파상된 결과를 찾지 못하여 실제 파싱작업을 하게 되었다는 것을 의미한다. 

- 최종적으로 추출된 로우의 수는 적으나 많은 CPU 시간이 소요되었다면 이것은 분명히 적절한 액세스 경로로 수해되지 않았음을 의미한다. 

- CPU 시간과 ELAPSED 시간의 차이는 적을수록 좋다. 만약 CPU시간에 비해 ELAPSED 시간이 훨씬 많다면, 그 원인은 다음 중 하나일 가능성이 높다. 즉 주변의 다른 세션에서 많은 부하를 발생시켜 시스템 전체에 부하가 많이 걸려있는 경우나 혹은 어플리케이션의 문제이거나 다량의 데이타 처리에 따른 I/O 병목현상이 발생한 경우 

- disk, query, current의 숫자는 적을수록 좋다. 이 숫자들이 커다는 것은 메모리 공유영역의 적중률(Hit Ratio)이 낮다는 것을 의미한다. 

- Overall totals For All Statements에서 적중률 계산은 다음과 같다. 

 (Execute 'disk' + Fetch 'Disk')/(Execute 'query' + Execute 'current' + Fetch 'query' + Fetch 'Current') * 100 

 이 값이 10%이상이라면 메모리 캐쉬에서 데이타를 찾는 비율(적중률)이 너무 낮은 것이다. 

- 다음은 아주 빠른 응답이 요구되는 온라인 프로세싱 시스템의 경우에서만 적용되는 규칙들이다. 

 모든 Execute 'CPU'가 1초보다 적어야 한다. 

 Parse 'CPU' 시간이 Parse당 0.01초보다 적어야 한다. 

 작은 테이블(200로우 이하)에서만 전체 테이블 스캔이 일어나게 한다. 

 sysdate만 찾아오거나, 오직 연산만 하거나, 'SELECT ... INTO ...'로 값을 복사하는 경우를 위해서 DUAL 테이블들을 불필요하게 사용하는 것은 모두 없앤다. 

 동시에 작업되는 SQL들은 가능한 PL/SQL을 사용한다. 

 조인시에 옵티마이져가 적절한 드라이빙 테이블을 선택하는지를 확인하거나, 여러개의 조건들 중에서 주(드라이빙)가 되는 조건들과 부(체크)가 되는 조건들을 확인한다. 또한 적적한 인덱스가 사용될 수 있는지를 확인하여 주조건의 처리범위가 넓지 않도록 항상 유의한다. 

Ⅶ. Analyze 

이 명령어는 Cost-Based방식에 사용할 통계 데이터를 모아 줍니다.

Function 
 설   명 
 
통계데이터 수집 
 table, Cluster, index 등에 관련된 자료들을 모아 Cost-Based Optimizer에 사용한다. 
 
데이터 무결성확인 
 table, cluster, index등의 무결성 작업을 한다. 
 
Chained-row통계 
 table, cluster에 있는 데이터 체인행(Chained row)에 대한 통계 자료 수집 
 

다음으로 문법을 보죠. 문법은 아래와 같습니다.

ANALYZE object-name operation STATISTICS 

-object → TABLE, INDEX, CLUSTER 중에서 해당되는 오브젝트 종류 

-name → 오브젝트의 이름 

-operation  

· COMPUTE : 각각의 값들을 정확하게 계산 따라서 옵션은 가장 정확한 통계를 얻을 수가 있지만 가장 처리속도가 느린 단점이 있습니다. 

·ESTIMATE : 자료사전의 값과 데이타 견본을 갖고 검사해서 통계를 예상 이 방법은 덜 정확한 정보를 주지만 훨씬 처리속도가 빠릅니다. 

·DELETE : 테이블의 모든 통계정보를 삭제

Analyyze와 관련된 데이터 사전 뷰를 정리 했습니다. 

USER_INDEXS, ALL_INDEXS, DBA_INDEXS 

USER_TABLES, ALL_TABLES, DBA_TABLES 

USER_TAB_COLUMNS, ALL_TAB_COLUMNS, DBA_TAB_COLUMNS 





출처 : http://skyforce.egloos.com/1844944

출처 : Tong - 늘푸른 소나무(松)님의 오라클-SQL통

SQL문 튜닝 개요

Added by honghoekim, last edited by honghoekim on 7월 31, 2009

Labels:

Enter labels to add to this page:   Looking for a label? Just start typing.

1.문제가 있는 SQL 식별

1)느린 프로그램을 식별
2)프로그램에서 SQL을 검토
3)SQL_Trace 및 TKPROF를 사용

• SQL문 식별이 불가능한 경우(SQL이 동적으로 생성되는 경우) SQL TRACE를 사용하여 실행된 SQL을 포함하는 Trace file을 생성한 다음 TKPROF를 사용하여 출력 파일을 생성

2.Optimizer 통계 확인

1)모든 테이블에 대한 통계를 수집

• 모든 SQL문의 실행 계획을 결정하는 CBO는 테이블 및 인덱스에 대해 수집된 통계를 사용함.통계가 수집되지 않았거나 통계가 데이터베이스에 저장된 데이터를 대표하지 못할 경우 Optimizer에 정보가 부족해 최상의 계획을생성할 수 없다.

3.실행 계획 검토

다음 사항을 검토

1)Driving table이 최상의 필터를 가지는 계획이 되어야 한다.
2)각 단계의 조인 순서는 가장 적은 수의 행이 다음 단계로 반환됨을 의미 한다.
3)조인 방식이 반환되는 행 수에 적합.예를 들어 인덱스를 통한 중첩 루프 조인은 많은 행이 반환될 때 최적화되지 않을 수 있다.
4)뷰가 효율적으로 사용 된다.뷰에 대한 access 필요 여부 확인
5)작은 테이블에도 비의도적인 cartesian product가 없다.
6)각 테이블이 효율적으로 액세스 된다.

4.SQL문 재구성

1)AND 및 = 를 사용
2)WHERE절에 변환된 열이 포함되지 않도록 함

• 술어 절 또는 WHERE절에 SQL 함수 사용 금지
  3)복합 모드 표현식을 빼고 암시적인 유형 변환에 유의
  4)각 작업에 대해 별도의 SQL문을 작성
• 다양한 작업에 대해 하나의 SQL문을 사용할 경우 각 작업에 최적화 되지 않은 결과 나옴.굳이 하나의 SQL문을 사용해야 할 경우 UNION ALL 연산자를 사용
  5)서브 쿼리에 대해 IN 대신 EXISTS를 사용
  -선택적 술어가 서브 쿼리에 있는 경우는 IN을 사용.선택적 술어가 상위 쿼리에 있는 경우는 EXISTS를 사용
  6)힌트로 엑세스 경로 및 조언 순서를 제어
  -인덱스를 통해 필요한 행을 얻는 것이 더 효율적인 경우 전체 테이블 스캔 사용 금지,
  더 적은 수의 행을 Fetch하는 다른 인덱스를 대신 사용할 수 있다면 Driving 테이블에서 많은 수의 행을 Fetch하는 인덱스의 사용을 피하자,
  조인 순서에서 나중에 테이블에 적은 수의 행을 조인하는 조인 순서를 선택

5.인덱스 재구성

1)비선택적 인덱스를 제거하여 DML 속도를 높임
2)성능에 중요한 액세스 경로를 인덱스화함
3)기존에 연결된 인덱스에서 열 순서를 변경
4)인덱스에 열을 추가하여 선택성을 높임
5)IOT를 고려

6.시간 경과에 따른 실행 계획 유지 관리


출처 : http://www.javastudy.co.kr:8080/pages/viewpage.action?pageId=786591

• Oracle Database 11g Release 1 (11.1.0.7.0) JDBC Drivers
  ojdbc5.jar (1,890,499 bytes) - Classes for use with JDK 1.5.
  ojdbc6.jar (1,988,051 bytes) - Classes for use with JDK 1.6.
• Oracle Database 11g Release 1 (11.1.0.6.0) JDBC Drivers
  ojdbc5.jar (1,879,860 bytes)
  ojdbc6.jar (1,977,267 bytes)
• Oracle Database 10g Release 2 (10.2.0.4) JDBC Drivers
  classes12.jar (1,609,607 bytes) - for use with JDK 1.2 and JDK 1.3
  ojdbc14.jar (1,555,682 bytes) - classes for use with JDK 1.4 and 1.5
• Oracle Database 10g Release 2 (10.2.0.3) JDBC Drivers
  classes12.jar (1,600,090 bytes) - for use with JDK 1.2 and JDK 1.3
  ojdbc14.jar (1,545,954 bytes) - classes for use with JDK 1.4 and 1.5
• Oracle Database 10g Release 2 (10.2.0.2) JDBC Drivers
  classes12.jar (1,594,191 bytes) - for use with JDK 1.2 and JDK 1.3
  ojdbc14.jar (1,540,457 bytes) - classes for use with JDK 1.4 and 1.5
• Oracle Database 10g Release 2 (10.2.0.1.0) JDBC Drivers
  classes12.jar (1,590,491 bytes) - for use with JDK 1.2 and JDK 1.3
  ojdbc14.jar (1,536,979 bytes) - classes for use with JDK 1.4 and 1.5
• Oracle Database 10g 10.1.0.5 JDBC Drivers
  classes12.jar (1,442,469 bytes) - for use with JDK 1.2 and JDK 1.3
  ojdbc14.jar (1,378,346 bytes) - classes for use with JDK 1.4
• Oracle Database 10g 10.1.0.2 JDBC Drivers
  classes12.jar (1,417,089 bytes) - for use with JDK 1.2 and JDK 1.3
  ojdbc14.jar (1,352,918 bytes) - classes for use with JDK 1.4
• Oracle9i 9.2.0.8 JDBC Drivers
  ojdbc14.jar - JDBC classes (1,212,964 bytes) - For use with JDK 1.4
  classes12.jar - JDBC classes (1,234,433bytes) - For use with JDK 1.2 and JDK 1.3
  classes111.jar - JDBC classes (1,063,074 bytes) - For use with JDK 1.1
• Oracle9i 9.2.0.5 JDBC Drivers
  ojdbc14.jar - JDBC classes (1,200,046 bytes) - For use with JDK 1.4
  classes12.zip - JDBC classes (1,232,604 bytes) - For use with JDK 1.2 and JDK 1.3
  classes111.zip - JDBC classes (1,063,479bytes) - For use with JDK 1.1
• Oracle9i 9.2.0.4 JDBC Drivers
  ojdbc14.jar - JDBC classes (1,187,584 bytes) - For use with JDK 1.4
  classes12.zip - JDBC classes (1,219,950 bytes) - For use with JDK 1.2 and JDK 1.3
  classes111.zip - JDBC classes (1,052,833 bytes) - For use with JDK 1.1
• Oracle9i 9.2.0.3 JDBC Drivers
  ojdbc14.jar - JDBC classes (1,181,679 bytes) - For use with JDK 1.4
  classes12.zip - JDBC classes (1,213,897 bytes) - For use with JDK 1.2 and JDK 1.3
  classes111.zip - JDBC classes (1,048,261 bytes) - For use with JDK 1.1
• Oracle9i 9.2.0.1 JDBC Drivers
  ojdbc14.jar - JDBC classes ( 1,174,976 bytes) - For use with JDK 1.4
  classes12.zip - JDBC classes ( 1,207,068 bytes) - For use with JDK 1.2 and JDK 1.3
  classes111.zip - JDBC classes ( 1,043,528 Bytes) - For use with JDK 1.1
• Oracle9i 9.0.1.4 JDBC Drivers
  classes12.zip - JDBC classes (1,143,559 bytes) - For use with JDK 1.2 and JDK 1.3
  classes111.zip - JDBC classes (988,625 bytes) - For use with JDK 1.1
• Oracle9i 9.0.1 JDBC Drivers
  classes12.zip - JDBC classes( 1,081 kb) - For use with JDK 1.2 and JDK 1.3
  classes111.zip - JDBC classes ( 936 kB) - For use with JDK 1.1
• Oracle8i 8.1.7.1JDBC Drivers
  classes12.zip - JDBC classes ( 1,892 kB) - For use with JDK 1.2
  classes111.zip - JDBC classes ( 1,741 kB)

출처 : http://pantarei.tistory.com/tag/oracle

10G DATAPUMP: REMAP TABLESPACE 기능
===================================

PURPOSE

Example

이 작업은 datapump 임포트에를 통해 수행되며, 다음 세개의 datapump 명령에 의해 소스 스키마로 부터
객체를 추출 할 수 있다.

Table dropped.

SQL> drop table bonus;

Table dropped.

Import는 다음 명령으로 실행 시킨다.

impdp Scott/Scott DIRECTORY=dpump_dir1 DUMPFILE=newtbs.dmp
REMAP_TABLESPACE='USERS':'NEW_USERS'

sun1/home/prod/scmiller> impdp SYSTEM/manager DIRECTORY=dpump_dir1
DUMPFILE=newtbs.dmp REMAP_TABLESPACE='USERS':'NEW_USERS'

Import: Release 10.1.0.2.0 - Production on Tuesday, 23 December, 2003 14:50

Copyright (c) 2003, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produn
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": SYSTEM/******** DIRECTORY=dpump_dir1 D
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/SE_PRE_SCHEMA_PROCOBJACT/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. imported "NEWSCOTT"."DEPT" 5.656 KB 4 rows
. imported "NEWSCOTT"."EMP" 7.820 KB 14 rows
. imported "NEWSCOTT"."SALGRADE" 5.585 KB 5 rows
. imported "NEWSCOTT"."BONUS" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 14:53

sun1/home/prod/scmiller> sqlplus

SQL*Plus: Release 10.1.0.2.0 - Production on Tue Dec 23 14:54:13 2003

Copyright (c) 1982, 2003, Oracle. All rights reserved.

sun1% sqlplus

SQL*Plus: Release 10.1.0.2.0 - Production on Tue Jan 13 12:53:36 2004

Copyright (c) 1982, 2003, Oracle. All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 -
With the Partitioning, OLAP and Data Mining options

SQL> select owner, tablespace_name from dba_segments where owner='SCOTT';

OWNER TABLESPACE_NAME

오라클9i에서 local 접속하던중 ora-12451 에러가 발생했다.

1.cmd -> services.msc
2. OracleOraHome92TNSListener (oracle 9i) 가 시작됐는지 확인.
혹은
3. listener.ora 에서 HOST가 제대로 되어 있는 확인한다

- listener.ora

  1. 오라클 서버에서 클라이어튼의 요청을 듣고, 클라이언트와의 통신 환경을 설정하는 파일

  2. 오라클 서버에 존재

$ORACLE_HOME/network/admin/listener.ora

-------------------------------------------------------------------

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = xxx)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\ora92)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = local)
      (ORACLE_HOME = C:\oracle\ora92)
      (SID_NAME = local)
    )
  )

***리스너 관련 명령어***

c:\>lsnrctl start  리스너 시작하기

c:\>lsnrctl stop  리스너 정지하기

c:\>lsnrctl reload  리스너 재시작하기

c:\>lsnrctl status  리스너 상태보기

c:\>lsnrctl help  도움말

- tnsnames.ora

  1. 오라클 클라이언트 측에서 오라클 서버로 접속시 필요한 프로토콜, 포트번호, 서버주소  등을 설정해주는 파일

  2. 클라이언트에 위치

  3. 예제

      JJS =
          (DESCRIPTION =
                   (ADDRESS_LIST =
                          (ADDRESS = (PROTOCOL = TCP)(HOST = ip주소 )(PORT = 1521))
                    )
                   (CONNECT_DATA =
                          (SERVER = 서버 명)
                          (SERVICE_NAME = db명)
                    )
           )

p.s) listener.ora와 tnsnames.ora파일의 위치는 ORACLE_HOME/network/ADMIN/에 존재

출처 : http://ragew.tistory.com/category/DB노트

### Lock 확인 쿼리
SELECT do.object_name, do.owner, do.object_type,do.owner, vo.xidusn, vo.session_id,
vo.locked_mode
FROM v$locked_object vo , dba_objects do
WHERE vo.object_id = do.object_id ;

####  어떤 object에 어떤 lock이 걸렸는지 확인
SELECT  T1.object_name, DECODE(locked_mode, 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE',  4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', 'UNKNOWN') lock_mode
FROM  dba_objects T1, v$locked_object T2
WHERE T1.object_id = T2.object_id;

#### session 확인
select * from v$session where status = 'ACTIVE'

#### cursor 확인
v$open_cursor

#### 테이블의 lock 확인
SELECT A.SID, A.SERIAL#, B.TYPE, C.OBJECT_NAME
FROM V$SESSION A, V$LOCK B, DBA_OBJECTS C
WHERE A.SID=B.SID
AND B.ID1=C.OBJECT_ID
AND B.TYPE='TM'
AND C.OBJECT_NAME IN ('<테이블이름>');

/*******************************************************************************
* LOCK 관련
*******************************************************************************/
--V$LOCK 을 사용한 잠금 경합 모니터링
SELECT s.username, s.sid, s.serial#, s.logon_time,
  DECODE(l.type, 'TM', 'TABLE LOCK',
         'TX', 'ROW LOCK',
      NULL) "LOCK LEVEL",
  o.owner, o.object_name, o.object_type
FROM v$session s, v$lock l, dba_objects o
WHERE s.sid = l.sid
AND o.object_id = l.id1
AND s.username IS NOT NULL   

--락이 걸린 세션 자세히 알아보기
select a.sid, a.serial#,a.username,a.process,b.object_name,
decode(c.lmode,2,'RS',3,'RX',4,'S',5,'SRX',8,'X','NO') "TABLE LOCK",
decode (a.command,2,'INSERT',3,'SELECT',6,'UPDATE',7,'DELETE',12,'DROP TABLE',26,'LOCK TABLE','UNknown') "SQL",
decode(a.lockwait, NULL,'NO wait','Wait') "STATUS"
from v$session a,dba_objects b, v$lock c
where a.sid=c.sid and b.object_id=c.id1
and c.type='TM'

--락이 걸린 세션 간단히 알아보기
select a.sid, a.serial#, b.type, c.object_name, a.program, a.lockwait,
      a.logon_time, a.process, a.osuser, a.terminal
from v$session a, v$lock b, dba_objects c
where a.sid = b.sid
  and b.id1 = c.object_id
  and b.type = 'TM';

select a.sid, a.serial#, a.username, a.process, b.object_name
from v$session a , dba_objects b, v$lock c
where a.sid=c.sid and b.object_id = c.id1
and c.type = 'TM'

--락이 걸린 세션을 찾아 내어 세션을 죽이려고 해도 죽지 않는 경우
--아래 쿼리문으로 OS단의 PROCESS ID를 찾아내어 OS에서 죽인다
--kill -9 프로세스아이디
select substr(s.username,1,11) "ORACLE USER", p.pid "PROCESS ID",
s.sid "SESSION ID", s.serial#, osuser "OS USER",
p.spid "PROC SPID",s.process "SESS SPID", s.lockwait "LOCK WAIT"
from v$process p, v$session s, v$access a
where a.sid=s.sid and
p.addr=s.paddr and
s.username != 'SYS'

--위 쿼리문의 결과가 있다면 락이 걸린 세션이 있다는것이므로 아래의 쿼리문으로 세션을 죽인다
ALTER SYSTEM KILL SESSION '11,39061'

출처 : http://cocoroworld.com/blog/root/entry/오라클-락lock


출처 : http://cocoroworld.com/blog/root/entry/오라클-락lock

토드(toad)에서 한글이 깨져보일때

ORA-04054: 데이터베이스 링크 'HSCPROD.NEXTVAL'가 존재하지 않습니다
                    
SELECT AAA_SEQ@hscprod.NEXTVAL
  FROM DUAL; (X)

SELECT AAA_SEQ.NEXTVAL@hscprod
  FROM DUAL (O)

SEQUENCE

 CREATE TABLE T1(ID NUMBER(4) CONSTRAINT T1_PK PRIMARY KEY, DATA VARCHAR2(30));

PK에 대해 SEQUENCE를 줄수 있다. 입력시에 아래처럼. 테이블에 시퀀스 하나씩!

 SQL> INSERT INTO T1 VALUES(T1_S.NEXTVAL, 'AA');

1 개의 행이 만들어졌습니다.

 SQL> CREATE SEQUENCE T1_S;

시퀀스가 생성되었습니다.

SQL> SELECT * FROM T1;

        ID DATA
---------- ------------------------------
         1 AA
         2 BB

 세션마다 CURRVAL은 다르지만, NEXTVAL은 같다. 세션에서 NEXTVAL을 사용하게 되면 하나씩 올라가게 된다..

 GAP이 생기면 시퀀스를 쓰지 말아야 하는거죠. 시퀀스대신 SELECT MAX(ID)+1 처럼 해야 된다. <- LOCK이 걸릴수 있기때문에 쓰지 마라.

 ISQL에서는 트랜젝션이 있으니까 상관없긴 하다.

  1  CREATE SEQUENCE DEPT_S
  2  INCREMENT BY 10
  3* START WITH 10
SQL> /

시퀀스가 생성되었습니다.

SQL> SELECT DEPT_S.NEXTVAL FROM DUAL;

   NEXTVAL
----------
        10

SQL> SELECT DEPT_S.NEXTVAL FROM DUAL;

   NEXTVAL
----------
        20

3,5,2,4,6,2,4,6 으로 증가하게 하려면?

 SQL>
  1  CREATE SEQUENCE TS
  2  INCREMENT BY 2
  3  START WITH 3
  4  MAXVALUE 6
  5  MINVALUE 2
  6* CYCLE NOCACHE

시퀀스가 생성되었습니다.

SQL> SELECT TS.NEXTVAL FROM EMP;

   NEXTVAL
----------
         3
         5
         2
         4
         6
         2
         4
         6
         2
         4
         6

   NEXTVAL
----------
         2
         4
         6

14 개의 행이 선택되었습니다.

10.명령어

[2008.06.02] 오라클 내장함수^#

숫자 함수(number function)^#

① ROUND(45.926, 2) --> 45.93 : 소수점 두자리수까지 보여주면서 반올림한다.
② TRUNC(45.926, 2) --> 45.92 : 소수점 두자리까지만 보여주고 나머지는 버린다.
③ MOD(1600,300) --> 100 : 1600을 300으로 나누고 나머지를 리턴한다.
* ROUND예제(WHOLE NUMBER:정수)
SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL
==> 45.92 46 50

* TRUNC예제
SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-1) FROM DUAL
==> 45.92 45(n이 생략된면 일의 자리까지 남기고 버린다.) 40

* SYSTEM 날짜를 갖고 오는 방법.
SELECT sysdate FROM dual

/** 함수 - Number Function **/
--무조건 올림
SELECT CEIL(13.11) FROM DUAL;
SELECT CEIL(13.001) FROM DUAL;

--나머지 구함
SELECT MOD(23, 5) FROM DUAL;
SELECT MOD(57, 145) FROM DUAL;

-- 제곱승
SELECT POWER(3, 2), POWER(3, -2) FROM DUAL;
SELECT POWER(2, 10) FROM DUAL;

--자릿수 지정
SELECT ROUND(345.123, 0) FROM DUAL;
SELECT ROUND(345.123, 2), ROUND(345.123, -1) FROM DUAL;

--지정된 자리까지 잘라내기
SELECT TRUNC(345.123, 1), TRUNC(345.123, 0), TRUNC(345.123, -1) FROM DUAL;
SELECT TRUNC( 345.123 + 0.09, 1 ) FROM DUAL;
SELECT TRUNC( 345.123 + 0.9, 0 ) FROM DUAL;
SELECT TRUNC( 345.123 + 9, -1 ) FROM DUAL;

SELECT SIGN(5.989), SIGN(0), SIGN(-999.098) FROM DUAL;

문자 함수(character function)^#

select ascii('B') from dual;

=> 66

①LOWER( column|expression )

LOWER('String') --> string : 소문자로 변환

②UPPER( column|expression )
UPPER('String') --> STRING : 대문자로 변환

③INITCAP( column|expression )
INITCAP('string') --> String : 첫글자만 대문자이고 나머지글자는 소문자로 변환

④CONCAT( column1|expression1 ,column2|expression2 )
CONCAT('Good','String') --> GoodString : ||와 같은 역할을 한다.

⑤SUBSTR(column|expression, m [,n]) : m값이 음수면 문자값의 끝부터..)
SUBSTR('String',1,3) --> Str : 1번째부터 3개의 문자를 리턴한다.

⑥LENGTH( column|expression )
LENGTH('String') --> 6 : 문자열의 길이를 리턴한다.

⑦INSTR( column|expression, )
INSTR('String','r') --> 3 : 문자열에 'r'이 몇번째 위치하고 있나를 리턴한다.

⑧LPAD( column|expression,n,'string' ) : n 은 전체 길이
LPAD('String',10,'*') --> ****String
: 10자리수중에 모자란 자리수를 '*'로 왼쪽에 채운다.(문자,숫자 가능!!!)

⑨ RPAD('String',10,'*') --> String****
: 10자리수중에 모자란 자리수를 '*'로 오른쪽에 채운다.(문자,숫자 가능!!!)

⑩ LTRIM(' String') --> 'String' : 문자열의 왼쪽 공백을 버린다.

⑪ RTRIM('String ') --> 'String' : 문자령의 오른쪽 공백을 버린다.
* TRIM(leading/tailing/both, trim_character FROM trim_source )

TRIM( 'S' FROM 'SSMITH') --> MITH

/** 함수 - Character Function **/
--특정Character의 아스키값 구하기
SELECT CHR(65) "CHR", ASCII('A') "ASCII" FROM DUAL;
SELECT ASCII( CHR(65) ) FROM DUAL;

SELECT LOWER('My name is LKM') "LOWER", UPPER('My name is LKM') "UPPER" FROM DUAL;

SELECT LPAD('LKM', 10, '*') "LPAD", RPAD('LKM', 10, '*') "RPAD" FROM DUAL;
SELECT LPAD('1234567890', 20, '+') || RPAD('1234567890', 20, '^') "12345678901234567890"
FROM DUAL;
SELECT LPAD('1,234,567', 30, ' ') "LPAD사용으로 30자리 맞춤",
'1,234,567' "단순문자 사용",
1234567 "단순숫자 사용"
FROM DUAL;

SELECT LTRIM(' AAA ') "LTRIM", RTRIM(' AAA ') "RTRIM" FROM DUAL;
SELECT LTRIM( RTRIM( ' A A A ' ) ) "TRIM" FROM DUAL;

SELECT REPLACE('ORACLE', 'A', 'BBB') "REPLACE" FROM DUAL;
SELECT EMP_NAME, REPLACE(EMP_NAME, '이', '박') "이->박"
FROM PERSONNEL
WHERE EMP_NAME LIKE '이%';

SELECT SUBSTR('ORACLE PROJECT', 1, 3) SUBSTR1,
SUBSTR('ORACLE PROJECT', 4, 5) SUBSTR2,
SUBSTR('ORACLE PROJECT', 10) SUBSTR3 FROM DUAL ;
SELECT SUBSTRB('ORACLE PROJECT', 1, 3) SUBSTRB1,
SUBSTRB('ORACLE PROJECT', 4, 5) SUBSTRB2,
SUBSTRB('ORACLE PROJECT', 10) SUBSTRB3 FROM DUAL ;
SELECT SUBSTR('오라클 PROJECT', 1, 3) SUBSTR1,
SUBSTR('오라클 PROJECT', 4, 5) SUBSTR2,
SUBSTR('오라클 PROJECT', 10) SUBSTR3 FROM DUAL ;
SELECT SUBSTRB('오라클 PROJECT', 1, 3) SUBSTRB1,
SUBSTRB('오라클 PROJECT', 4, 5) SUBSTRB2,
SUBSTRB('오라클 PROJECT', 10) SUBSTRB3 FROM DUAL ;

SELECT LENGTH ('ORACLE PROJECT') "LENGTH",
LENGTHB('ORACLE PROJECT') "LENGTHB",
FROM DUAL;
SELECT EMPNO, LENGTH(EMPNO), LENGTHB(EMPNO),
EMP_NAME, LENGTH(EMP_NAME), LENGTHB(EMP_NAME)
FROM PERSONNEL
WHERE EMPNO > '98102';
SELECT HOBBY, LENGTH(HOBBY), LENGTHB(HOBBY)
FROM PERSONNEL
WHERE EMPNO > '98102';

SELECT INSTR ('ORACLE PROJECT', 'R', 1, 1) INSTR1,
INSTR ('ORACLE PROJECT', 'R', 1, 2) INSTR2,
INSTR ('ORACLE PROJECT', 'R', 1, 3) INSTR3
FROM DUAL;
SELECT INSTR ('CORPORATE FLOOR','OR', 3, 2) INSTR,
INSTRB ('CORPORATE FLOOR','OR', 3, 2) INSTRB
FROM DUAL;
SELECT HOBBY,
INSTR (HOBBY, ')', 1, 1) INSTR,
INSTRB(HOBBY, ')', 1, 1) INSTRB
FROM PERSONNEL

WHERE EMPNO > '98102';

날짜와 날짜 처리함수(date, datetime function)^#

date + number : date에 number만큼 후의 날자를 보여준다.
date - number : date에 number만큼 전의 날자를 보여준다.
date1 - date2 : date1에서 date2 까지의 총 일수를 보여준다.( date1+date2는 X )
date1 + 숫자/24 : date1에서 시간을 더해 날짜를 보여준다.

NUMTODSINTERVAL

NUMTODSINTERVAL(n,'char_expr') 함수는 n을 interval day to second로 변환하여 출력한다.
 char_expr은 다음 중의 하나이다.
  ‘DAY’
  ‘HOUR’
  ‘MINUTE’
  ‘SECOND’

MONTHS_BETWEEN('01-SEP-95','11-JAN-94') --> 19.6774194
; 두날짜 사이의 달수를 보여준다.

ADD_MONTHS('11-JAN-94', 6) --> 11-JUL-94
; 날짜에 6개월을 더한 날자를 보여준다.

NEXT_DAY('01-SEP-95','FRIDAY') --> '08-SEP-95'
; 해당일 다음에 오는 FRIDAY의 일자를 보여준다.
('SUNDAY'는 1, 'MONDAY'는 2...이런식으로 숫자를 써줘도 된다.)

LAST_DAY('01-SEP-95') --> '30-SEP-95'
; 해당월의 마지막날자를 보여준다.

ROUND('25-JUL-95','MONTH')--> 01-AUG-95 ROUND('25-JUL-95','YEAR')--> 01-JAN-96
TRUNC('25-JUL-95','MONTH') --> 01-JUL-95 TRUNC('25-JUL-95','YEAR') --> 01-JAN-95

/** 함수 - Date Function **/
SELECT SYSDATE FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') "SYSDATE" FROM DUAL;

SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT TO_CHAR(LAST_DAY(SYSDATE), 'YYYY-MM-DD HH24:MI:SS') "LAST_DAY" FROM DUAL;

SELECT MONTHS_BETWEEN( '2002/01/13', '2002/05/13' ) "MONTHS_BETWEEN (-)",
MONTHS_BETWEEN( '2002/01/13', '2001/11/13' ) "MONTHS_BETWEEN (+)"
FROM DUAL ;
SELECT MONTHS_BETWEEN( '2002/01/13', '2002/01/30' ) "MONTHS_BETWEEN (-)",
MONTHS_BETWEEN( '2002/01/13', '2002/01/01' ) "MONTHS_BETWEEN (+)"
FROM DUAL ;

SELECT ADD_MONTHS(SYSDATE, 1) "ADD_MONTHS (+)",
ADD_MONTHS(SYSDATE, -1) "ADD_MONTHS (-)"
FROM DUAL ;

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
SELECT ADD_MONTHS( '2002/02/28', 12 ) "1년후",
ADD_MONTHS( '2002/02/28', 24 ) "2년후",
ADD_MONTHS( '2002/02/28', 36 ) "3년후"
FROM DUAL ;

SELECT SYSDATE,
NEXT_DAY(SYSDATE, '일요일') "NEXT_DAY 1",
NEXT_DAY(SYSDATE, 1 ) "NEXT_DAY 2"
FROM DUAL;
SELECT SYSDATE,
NEXT_DAY(SYSDATE, '수요일') "NEXT_DAY 1",
NEXT_DAY(SYSDATE, 4 ) "NEXT_DAY 2"
FROM DUAL;

데이터 형변환 함수(conversion function)^#

nlsparams : 십진수, 그룹구분자, 지역 통화 기호, 국제 통화 기호

TO_CHAR(date,['format'],[nlsparams]) : date를 format에 맞게 문자열로 변환한다.

- Date Format Elements
YYYY --> 1999 (년출력) , YEAR --> nineteen ninety-nine (년출력)
MM --> 12 (월출력) , MONTH --> DECEMBER (월출력), MON --> DEC
D --> 요일을 숫자로 리턴한다.(일요일은 1, 월요일은 2...)
DD --> 07 (달의 일출력)
DDD --> 200 (연의 일출력)그 해의 총 몇 일째인가를 리턴한다.
DAY --> MONDAY (요일출력) DY-->MON
CC --> 20 (몇 세기인지를 보여준다.)
WW --> 그 해의 몇 번째 주인가를 리턴한다.
W --> 그 달의 몇 번째 주인가를 리턴한다.

=>  흔히 사용하는 위의 형태는 일상 생활에서 사용되는 몇주 개념적용이 아닌

7일 단위로 끊은 주 개념입니다.

예를 들어 2005년 1월 2일의 경우 일요일로서 일상 생활에서는 2005년의 2주차 입니다.

하지만 to_char('20050102','ww') 의 경우 '2005'년도의 2일째 이기에 1주 로 계산 합니다.

(2006년의 경우 '20060101'이 정확히 일요일이기 때문에 to_char(sysdate,'ww')의 값을 오해 할 수 있습니다. 2005년 또는 2007년 경우를 확인 해 보시면 이해에 도움이 될 것입니다.)

* Element들을 소문자로 쓰면 소문자로 나오고 대문자로 쓰면 대문자로 출력된다.
HH or HH12 or HH24 / MI(0-59분) / SS(0-59초)


* 문자열은 " " 묶어 추가한다 DD " of " MONTH --> 12 of DECEMBER

*숫자 접미어는 숫자를 문자로 표기. TH(4->4TH)/ SP(4->FOUR)/ SPTH or THSP(4->FOURTH)

ddspth : 14-> fothteenth

* / . , : 구두점은 결과에 그대로 출력한다. * 공백, 선행제로를 제거하는 fm요소가 있다.

TO_CHAR(number,'format',[nlsparams]) : number를 format에 맞게 문자열로 변환한다.

- Number Format Elements
9 : 999,999 --> 1,234 $: 부동 달러 기호 $99999 -> $1234
0 : 099999 --> 001234 99.999EEEE -> 1.234E+03 B: 0값을 공백으로
L : L99,999 --> FF1,234 (NLS_CURRENCY에 설정되어있는 값이 L로 보여진다.)

TO_NUMBER(char,['format'],[nlsparams]) : 숫자형태의 문자열을 숫자로 변한한다.
TO_DATE(char,['format'],[nlsparams]):날자형태의 문자열을 format에 맞게 날자형식으로 변환 한다.

/** 함수 - Conversion Function **/
SELECT TO_CHAR(1234567.891) "TO_CHAR1",
TO_CHAR(1234567.891, '999') "TO_CHAR2",
TO_CHAR(1234567.891, '9,999,999') "TO_CHAR3",
TO_CHAR(1234567.891, '0.0000') "TO_CHAR5",
TO_CHAR(1234567.891, '9,999,999.0000') "TO_CHAR6",
TO_CHAR(123, '9,999.00') "TO_CHAR7",
TO_CHAR(123, '9,999.99') "TO_CHAR8"
FROM DUAL ;
SELECT TO_CHAR(1234567.891, '9G999G999') "TO_CHAR3",
TO_CHAR(1234567.891, '0D0000') "TO_CHAR5",
TO_CHAR(1234567.891, '9G999G999D0000') "TO_CHAR6",
TO_CHAR(123, '9G999D00') "TO_CHAR7",
TO_CHAR(123, '9G999D99') "TO_CHAR8"
FROM DUAL ;

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') "TO_CHAR1",
TO_CHAR(SYSDATE, 'YYYY/MM') "TO_CHAR2",
TO_CHAR(SYSDATE, 'YYYY') "TO_CHAR3",
TO_CHAR(SYSDATE, 'DD') "TO_CHAR4",
TO_CHAR(SYSDATE, 'DAY') "TO_CHAR5",
TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') "TO_CHAR6",
TO_CHAR(TO_DATE('20020101','YYYYMMDD'), 'YYYY-MM-DD') "TO_CHAR7",
TO_CHAR(TO_DATE('20020101','YYYYMMDD'), 'YYYYMMDD HHMISS') "TO_CHAR8"
FROM DUAL ;

SELECT TO_NUMBER('123456.9') "TO_NUMBER1",
TO_NUMBER('1234567') "TO_NUMBER2"
FROM DUAL ;
SELECT TO_NUMBER('123,456.9', '999,999.9') "TO_NUMBER1",
TO_NUMBER('1,234,567', '9G999G999') "TO_NUMBER2"
FROM DUAL ;

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT TO_DATE('20020824') "TO_DATE1",
TO_DATE('2002-08-24') "TO_DATE2",
TO_DATE('200208') "TO_DATE3"
FROM DUAL ;
SELECT TO_DATE('20020824', 'YYYYMMDD') "TO_DATE1",
TO_DATE('2002-08-24', 'YYYY-MM-DD') "TO_DATE2",
TO_DATE('200208', 'YYYYMM') "TO_DATE3"
FROM DUAL ;
SELECT TO_DATE('2002/08/24 08:14:06', 'YYYY/MM/DD HH24:MI:SS') "TO_DATE1",
TO_DATE('2002/08/24 08:14:06 오후', 'YYYY/MM/DD HH:MI:SS AM') "TO_DATE2"
FROM DUAL ;

기타함수(miscellaneous single row function)^#

NVL Funcion : 값이 null일 때 설정값을 보여준다.
NVL(number_column, 0) : null일 때 0을 보여준다.
NVL(date_column, '01-JAN-95') : null일 때 '01-JAN-95'를 보여준다.
NVL(character_column, 'Empty') : null일 때 'Empty'를 보여준다.
* column Type과 표현식의 type이 반드시 일치해야 한다.

DECODE Function : CASE or IF-THEN-ELSE 형식과 비슷하다.
*DECODE(col/expression, search1, result1 [,search2,result2,…] [,default])
F1 (F2 (F3 (col,arg1),arg2),arg3)

이중 Decode나 Nvl 그리고, To_데이터형은 너무나도 많이 쓰여서 잘 안 까먹지만....
숫자나 문자관련 함수 잘 안쓰면 찾기가 너무 힘들다는..... '그 뭐시기냐 문자열의 위치 찾아내는거
그 함수 뭐냐?' 라면 '그게 뭔데?'라는 반문이-_- 차라리 instr이 뭐하는거지가 더 쉬운 질문이다-_-
대전 있을때 자료전환건이 있어서... 처리를 하려고 보니 특정칼럼 하나에 잘 쓰이지 않는
두개의 필드값을 탭구분자로 나눠서 때려넣어논 꼴을 본적이 있다. instr, chr, replace로 간단히 해결가능할걸 instr과 chr를 잘 몰라 통째로 엑셀로 받아 수작업을 했다라는....

/** 함수 - ETC **/
SELECT EMPNO, EMP_NAME, HOBBY 취미, WELL 특기
FROM PERSONNEL
WHERE EMPNO BETWEEN '98001' AND '98005';
SELECT EMPNO, EMP_NAME, HOBBY 취미, NVL(WELL, '(없다)') 특기
FROM PERSONNEL
WHERE EMPNO BETWEEN '98001' AND '98005';

SELECT DECODE( '나', '나', '맞다', '아니다' ) "나가 나면 맞다, 아니면 아니다"
FROM DUAL ;
SELECT DECODE( '나', '대명', '아니다',
'너' , '아니다',
'그' , '아니다',
'나' , '맞다',
'모르겠다' ) FROM DUAL ;
SELECT EMPNO,
EMP_NAME,
DECODE (HT_CODE, '1', '현재원', '2', '휴직', '퇴사') HT_CODE
FROM PERSONNEL
WHERE EMPNO BETWEEN '98071' AND '98080';

SELECT GREATEST (132, 33, 45, 90, 60.77) GREATEST,
LEAST (132, 33, 45, 90, 60.77) LEAST
FROM DUAL;
SELECT GREATEST ('이공명', '이대명', '최수미') GREATEST,
LEAST ('이공명', '이대명', '최수미') LEAST
FROM DUAL;

SELECT USERENV('LANGUAGE') "LANGUAGE",
USERENV('TERMINAL') "TERMINAL",
USERENV('SESSIONID') "SESSIONID"
FROM DUAL;

SELECT UID, USER FROM DUAL;

그룹함수 - 집계(Aggregate) 함수^#

/** 함수 - Group Function **/
SELECT AVG(HEIGHT), AVG(WEIGHT) FROM PERSONNEL;

SELECT MAX(EMPNO), MAX(EMP_NAME), MIN(EMPNO), MIN(EMP_NAME) FROM PERSONNEL;
SELECT MAX(HEIGHT), MIN(HEIGHT) FROM PERSONNEL;

SELECT SUM(WEIGHT) FROM PERSONNEL;

SELECT COUNT(*), COUNT(EMPNO), COUNT(JIKCH_CODE) FROM PERSONNEL;

그룹함수 -  분석(Analytic) 함수^#

윈도우(windowing) 분석 함수^#

윈도우 분석함수는 전체 결과 집합 중에서 연속선상에 있는 부분 집합을 대상으로 적용하는 함수의 집합을 말한다.
윈도우 분석함수의 종류는 AVG, COUNT, MAX, MIN,STDDEV,SUM,VARIANCE,FIRST_VALUE,LAST_VALUE 등이 있다.
부분 집합을 결정하기 위한 범위는 CURRENTROW, UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING 등을 지정할 수 있다.

【형식】
   (SUM | AVG | MAX | MIN | COUNT | STDDEV | VARIANCE | FIRST_VALUE | LAST_VALUE)
   ({ | * }) OVER
   ([PARTITION BY [,...]]
   ORDER BY  [collate clause]
   [ASC | DESC] [NULLS FIRST | NULLS LAST] [,...]
   ROWS | RANGE
   {{UNBOUNDED PRECEDING |  PRECEDING} | BETWEEN
   {UNBOUNDED PRECEDING |  PRECEDING}
   AND {CURRENT ROW |  FOLLOWING}}

PseudoColumn을 의미하는 것^#

LPAD, RPAD : 문자열 채우기^#

문법 :

lpad ('string', n [, 'string_pad')
rpad ('string', n [, 'string_pad')

예제 :

select lpad('*', 5, '%') lpad, rpad('%', 5, '*') rpad from dual

결과 :

설명 :

VSIZE : 문자의 길이를 구한다.^#

예제 :

select vsize('가나다'), '가나다' from dual

결과 :

출처 : http://ebizdocs.springnote.com/pages/1250050?print=1

오라클 clob 예제

by kkaok
2003-06-27

Lob type이란?

lob(Large Object) 타입은 대용량 데이터를 저장하기위한 데이터 타입으로 오라클 8버전부터 지원된다.

Lob 타입의 특징

이전에 대용량 처리는 long 타입으로 처리를 할 수 밖에 없었다. 그런데 이 칼럼은 한테이블에 하나만 사용할 수 있으며 최대 크기가 2GB였다. 더군다나 검색을 구현하기가 어려워 포기를 해야 했다. 그래서 이런 문제를 보안하기 위 나온 것이 Lob 타입이다. Lob 타입은 테이블에 여러개의 컬럼을 생성할 수 있으며 최대 크기가 4GB이다. 또한 long 타입보다 훨씬 쉬운 검색기능을 제공한다.

Lob type의 종류

이전에 대용량 처리는 long 타입으로 처리를 할 수 밖에 없었다. 그런데 이 칼럼은 한테이블에 하나만 사용할 수 있으며 최대 크기가 2GB였다. 더군다나 검색을 구현하기가 어려워 포기를 해야 했다. 그래서 이런 문제를 보안하기 위 나온 것이 Lob 타입이다. Lob 타입은 테이블에 여러개의 컬럼을 생성할 수 있으며 최대 크기가 4GB이다. 또한 long 타입보다 훨씬 쉬운 검색기능을 제공한다.

Clob에 저장하기

  1: import java.sql.*;
  2: import java.io.*;
  3: import oracle.sql.*;
  4: import oracle.jdbc.driver.*;
  5: ...
  6: 
  7: public void insertQuery(UploadBoard up,int re_step,int re_level) 
         throws Exception  
  8: {
  9:   ResultSet rs   = null;
 10:   PreparedStatement pstmt = null;
 11:   Connection conn = null;
 12:   String query = "insert into "+up.getTableName()+" (seq,re_step,
           re_level,name,title,pwd,email,readnum,writeday,ip,relativeCnt,
               homepage,imgInfo,content,tag) 
                   values(?,?,?,?,?,?,?,0,sysdate,?,0,?,?,empty_clob(),?)";
오라클 명령어 empty_clob()을 이용해 공간을 확보한다.
 13:   try{
 14:     conn = DBManager.getClobConnection();
 15:     conn.setAutoCommit(false);
CLOB column을 업데이트 하는동안 다른 process의 접근을 막기위해 
setAutoCommit(false)를 반드시 설정해야 한다. 이부분이 가장 중요하다.
 16:     pstmt = conn.prepareStatement(query);
 17:     pstmt.setInt(1,up.getSeq());
 18:     pstmt.setInt(2,re_step);
 19:     pstmt.setInt(3,re_level);
 20:     pstmt.setString(4,up.getName());
 21:     pstmt.setString(5,up.getTitle());
 22:     pstmt.setString(6,up.getPwd());
 23:     pstmt.setString(7,up.getEmail());
 24:     pstmt.setString(8,up.getIp());
 25:     pstmt.setString(9,up.getHomepage());
 26:     pstmt.setString(10,up.getImgInfo());
 27:     pstmt.setString(11,up.getTag());
 28:     pstmt.executeUpdate();
 29:     pstmt.close();
 30:     String query2 = " select /*+ index_desc("+up.getTableName()+
             " "+up.getTableName()+"_indx) */ content from "+
                 up.getTableName()+" where seq = ? for update ";
for update를 이용해 CLOB column을 lock한다.
 31:     pstmt = conn.prepareStatement(query2);
 32:     pstmt.setInt(1,up.getSeq());
 33:     rs = pstmt.executeQuery();
 34:     if(rs.next()) {
 35:       CLOB clob = ((OracleResultSet)rs).getCLOB(1);        
 36:       Writer writer = clob.getCharacterOutputStream();
 37:       Reader src = new CharArrayReader(up.getContent().toCharArray());
 38:       char[] buffer = new char[1024];
 39:       int read = 0;
 40:       while ( (read = src.read(buffer,0,1024)) != -1) {
 41:         writer.write(buffer, 0, read); // write clob.
 42:       }
 43:       src.close();        
 44:       writer.close();
 45:     }
 46:     conn.commit();
 47:     conn.setAutoCommit(true);
CLOB column에 데이터을 저장하였다면 commit()을 실행시키고 
conn.setAutoCommit(true)로 다시 설정한다.

 48:   }finally{
 49:     DBManager.close(rs,pstmt,conn);
 50:   }
 51: }

Clob 불러오기

     ...
  1: public BoardTable getViewData(String tableName,int seq) throws Exception
  2: {
  3:   BoardTable bTable = new BoardTable();
  4:   String query = " select * from "+tableName+" where seq = ? ";
  5:   ResultSet rs = null;
  6:   PreparedStatement pstmt = null;
  7:   Connection conn = null;
  8:   try{
  9:     conn = DBManager.getConnection();
 10:     pstmt = conn.prepareStatement(query);
 11:     pstmt.setInt(1,seq);
 12:     rs = pstmt.executeQuery();
 13:     if (rs.next()) {
 14:       ...
 15:       ...      
 16:       StringBuffer output = new StringBuffer();
 17:       Reader input = rs.getCharacterStream("content");
 18:       char[] buffer = new char[1024];
 19:       int byteRead;
 20:       while((byteRead=input.read(buffer,0,1024))!=-1){
 21:         output.append(buffer,0,byteRead);
 22:       }
 23:       input.close();
 24:       bTable.setContent(output.toString()); 
CLOB 데이터를 불러오기 위해서는 위에서처럼 
rs.getCharacterStream("content")로 불러서 StringBuffer에 담아야 한다.
 25:       ...
 26:       ...
 27:     }
 28:   } finally {
 29:       DBManager.close(rs,pstmt,conn);
 30:   }
 31:   return bTable;
 32: }


 ================================================ 
    * Oracle Community OracleClub.com 
    * http://www.oracleclub.com 
    * http://www.oramaster.net 
    * 운영자 : 김정식 (oramaster _at_ empal.com)
  ================================================