진리 블로그

public class Test implements  KeyListener {  .......

//이 두개를 동시에 해줘야 텝이벤트가 먹는다...
  id_tf.addKeyListener(this);
  id_tf.setFocusTraversalKeysEnabled(false);

 

//다음과 같이 걸어주면 된다.

 public void keyPressed(KeyEvent e) {
  if(e.getKeyCode()==KeyEvent.VK_TAB && e.getSource()==id_tf){
       pw_tf.requestFocus();
  }
  
  if(e.getKeyCode()==KeyEvent.VK_TAB && e.getSource()==pw_tf){
       login_bt.requestFocus();
  }
 }
 
 public void keyTyped(KeyEvent e) {
 }
 
 public void keyReleased(KeyEvent e) {
 }

"관계형 데이터베이스에서 효율적인 데이터연결" 네번째로 "Outer Join의 원리 및 활용방안"에 대해서 알아보자.

1. 개념 및 원리

Outer Join은 정상적으로 조인 조건을 만족하지 못하는 행들을 보기 위해 사용된다. 즉 어떤 집합을 기준으로 해서 조인되는 다른 집합과의 연결에 실패했더라도 그 결과를 추출하는 조인을 말한다.

 

Outer Join의 연산자는 (+)로 조인시킬 값이 없는 조인측에 (+)를 위치시킨다.

(+)기호는 where절 어디에도 올 수 있으며 Oracle 8i 까지는 한쪽에만 올 수 있었으며 Oracle 9i이후에는 Full Outer Join이라는 절이 이용하여 양쪽 아우터 조인이 가능해졌다.

2. Outer Join 실패

 

아래에 2)번 쿼리의 실행 계획을 확인해보면 분명 Outer Join을 수행한 것으로 나오나 Outer Join을 수행하지 않은 1)번 쿼리와 동일한 결과가 나온다.

 

이유를 보면 연결조건은 Outer join을 하였으므로 두개의 집합은 당연히 Outer join이 되었다. 그러나 d.deptno = 40인 Row들이 조인에는 성공했으나 emp2의 집합은 모두 null 값을 가지게 되므로 마지막 e.empno=7288의 조건은 만족하지않기 때문에 filter로 걸러진다.

이러한 Outer Join실패를 해결하기 위해서는 OuterJoin이 되는 집합에 모두 (+)기호를 붙여 주거나 Outer Join된 결과를 보존하는 하기 위해 null을 조건에 포함 시키는 방법이 있다.

 

3. Outer Join 활용(특이사례)

3.1 Minus 대신 조인으로 처리

집합A에서 집합 B에 있는 것을 빼는 개념으로 Outer Join을 사용할 수 있다.

보통 minus 개념은 minus 구분을 사용하거나 not exists / not in을 사용한다. 그러나 minus의 경우 양쪽집합을 Sort하는 부하가 생기고 not exists / not in의 경우 메인 쿼리의 집합이 많은 경우나 대용량의 경우 메인쿼리에서 서브 쿼리로 가는 랜덤 액세스가 많이 생기는 비효율이 발생한다.

 

이런 비효율을 해결하기 위해 집합 A와 집합B를 outer join한후 inline view로 묶고 B.COL IS NULL을 값을 구하면 (A-B)의 값을 얻을 수 있다.

대용량 데이터의 경우 집합A와 집합B의 Outer Join시 /*+ Use_hash(A B)*/을 사용하면 Nested Loop의 랜덤 액세스의 비효율을 제거할 수 있다.

 

3.2 2개 이상 집합의 Outer Join A = B(+) and B = C(+)

2개 이상의 집합으로 연속적인 Outer Join을 하는경우 대부분의 사용자들은 원집한 보존과 데이터 표시에 있어서 혼돈을 하는 경우가 있다.

아래와 같은 테이블 3개를 Outer Join해야 하는 경우를 예를 들어보자

 

연속적인 Outer Join을 다음과 같이 단계별 조일 결과 집합으로 다음과 같이 쉽게 이해할 수 있다.

 

우선 집합 A와 집합 B의 Outer Join에서 1차 집합을 구한다. B에는 1값이 없으므로 null채워져서 나오고 B의 5값은 A에 없는 값이으로 조인에 실패하여 제외된다.

다음 이 결과집합과 집합 C와 Outer Join을 한다. 집합 C의 5, 6은 1차 집합에 존재하지 않으므로 조인에 실패하여 제외 되고 1차 집합의 4가 집합 C에는 없으므로 Null채워져 나오고 1차집합의 null 값은 2차 집합에 null값으로 대체되어 나온다.

위 결과를 종합해 볼 때 2개이상의 집합으로 Outer Join을 연속으로 하는 경우 최초 결과 집합 A의 개수를 보존하고, 집합 A, B, C 모두 존재하는 경우에만 값이나오고 그렇지 않은 경우에는 Null로 채워지는걸 알수 있다.

3.3. Outer join에서 In / or절 사용 해결 방안

 

Outer Join에서 In /Or절 사용시 에러를 발생 하는 문제는 decode를 사용하여 각각의 조건에 대해 상수값을 지정하고 밖에서 조건별 상수 값을 비교하는 방법으로 해결할 수 있다.

아래 쿼리는 job이 'CLERK'이나 'MANAGER'인 경우는 1을 그렇지 않은 경우에는 2를 내보내고 decode 결과 값이 1인 경우에만 조인에 성공하므로 IN ('CLERK', 'MANAGER')와 같은 결과를 얻을 수 있다.

 

3.4 SubQuery OuterJoin의 해결방안

 

두집합 집합사이에서의 Outer Join은 에러를 발생시킨다. Oracle 8i부터는 다음과 같은 방법으로 해결할 수 있다. 이런경우 결과는 집합이지만 집합이 아니라것을 옵티마이저를 속이기 위해 집합의 결과에 영향을 미치지 않는 문자열을 Concat 시키면 옵티마이저는 집합의 Outer Join이 아니라 상수와의 Outer Join으로 인식하고 에러를 발생시키지 않고 Outer Join의 결과를 정확히 나타낸다.

 

3.5 Access Path의 고정

Outer Join으로 Nested Loop Join을 실행할 때에는 Outer Join여부가 조인의 순서에 영향을 미치므로 주의해야 한다.

위 그림에서 확인할 수 있는 것처럼 TAB1이 먼저 액세스 하여 TAB2를 Outer Join하는 경우 우리가 원하는 결과를 얻을 수 있다.

 

Outer Join은 조인후 성공한 집합을 만들 때 조인에 실패한 경우에도 무조건 성공으로 리턴 시킨다. 그러나 아래 그림처럼 반대 방향으로 조인했을 때 TAB2가 먼저 수행하고 TAB1를 연결하려 한다면 TAB2에 없는 TAB1의 집합을 액세스 하는 것은 논리적으로 불가능하다.

 

그러므로 Nested Loop 조인에서 Outer Join을 수행하는 경우 Outer 되는쪽이 나중에 수행될수 밖에 없어 드라이빙 집합이 고정되어 있으므로 액세스 패스에 주의하여 쿼리를 작성해야 한다.

4. Full Outer 조인

4.1 Oracle 9i 이전의 Full Outer Join 해결 방안

Oracle 9i이전에는 어떤 집합이 Outer Join될 때 비교되는 상대집합은 반드시 하나야만 Outer Join이 가능했었다. 그래서 이런경우 상대집합이 하나이상인 경우 각각을 기준으로 Outer Join을 한 후에 union으로 묶는 방법으로 각 집합에 대해 서로 조인에 실패한 부분도 결과에 포함시켜 얻을 수 있다.

다음은 소속된 사원이 없는 부서와 사원이 소속부서과 부서테이블에 없는 경우 보고자하는 쿼리 이다.

 

4.2 Oracle 9i 에서 Full Outer Join 사용하기

Full Outer Join을 각각 다른 방향으로 Outer Join을 한 후 union을 하면 같은 집합을 2번 읽어야 하는 비효율과 union 하면서 생기는 sort의 부하가 있다. Oracle 9i부터는 Full Outer Join이라는 ANSI 표준 SQL이 지원된다.

위의 쿼리를 Full Outer로 변경하면 다음과 같다.

 

ANSI 표준 SQL을 사용하는 Full Outer Join에는 On절과 Where절에 각각 상수조건에 들어 갈 수 있다.

1) On절에 상수조건이 있는 경우

 

위와 같이 On 절에 상수조건이 들어와 Full Outer Join을 하는 경우 emp 테이블에 deptno가 40인 사원만 dname과 deptno 값이 들어오고 나머지 deptno를 갖는 emp테이블의 값들은 null값으로 매치된다.

Dept테이블 역시 40이외에 다른 deptno로 매치되는 emp에 값이 존재하더라도 D.DEPTNO = 40 조건 때문에 emp쪽 컬럼 값들이 null로 매치가 된다.

On절에 상수조건이 있는 경우 조인조건으로 사용되는걸 알 수 있다.

2) Where에 상수조건이 있는 경우

where절에 조건에 있는 경우는 일반적인 Outer Join에서와 마찬가지로 조인후 걸러지는 체크 조건 값으로 사용된다.

 

위 쿼리는 full outer join후 다시 deptno가 40이거나 emp가 가지고 있는 deptno가 dept에 없는 데이터를 결과로 낸다. 여기서 is null 조건을 빼는 경우 d.deptno= 40만 가지고 equal join한 결과와 같은 값을 내므로 is null이 없는 경우 outer join에서 (+)기호를 생략한 결과와 같다고 할 수 있다.

또한 where d.deptnp(+) = 40 로 사용한 경우 ORA-25156: 이전 방식의 포괄조인(+)은 ANSI 조인과 함께 사용할 수 없음 이라는 에러 메시지가 나오므로 주의해야 한다.

이상으로 Outer Join의 원리와 활용방안에 대해서 알아보았다. 다음 연재는 단계별 데이터의 연결방법에 대해서 알아볼 것이다.

출처 : http://www.oracleclub.com/article/5375    저자 : 유경진

이번에 오라클로 인터페이스 작업을 하게 됬다. Job으로 프로시져를 실행시키는 작업을 해야 할 것 같아서 테스트를 진행해 봤다.

win 7 x86 + 오라클 10g + 토드 9.0.1 으로 작업했다.
하단 스크립트들은 모두 토드의 마법사로 만든 것이다. 엑셀에 정리한 내용을 옮겨왔더니 무지막지하게 보기 싫어져 버렸다.

테스트 시나리오
테스트를 진행하기 전 가상의 시나리오를 생성한다.

1. 테이블을 생성한다.
테이블명은 test_schedule
필드구성은 일련번호, 비고, 입력일시로 간단하게 구성
일련번호는 시퀀스이고 시퀀스명은 SQ_TEST_SCHEDULE 임
sql 스크립트를 백업한다.

2. 프로시져 생성
프로시져명은 sp_test_schedule_ins
test_schedule 테이블에 insert 하는 프로시져
비고는 고정 텍스트
입력일시에는 입력 당시의 일시(sysdate)
프로시져를 실행하여 데이터 처리가 정상인지 확인한다.

3. 오라클 스케줄러 잡을 구성한다.
1분 6회만 실행되도록 처리한다.
10초에 한번씩 sp_test_schedule_ins 를 호출한다.

4. 결과를 확인한다.

시퀀스 스크립트

CREATE SEQUENCE LIMS_YS.SQ_TEST_SCHEDULE
START WITH 1
INCREMENT BY 1
MINVALUE 1
NOCACHE
NOCYCLE
NOORDER

테이블 스크립트

CREATE TABLE LIMS_YS.test_schedule
(
seq NUMBER,
memo VARCHAR2(10) DEFAULT '입력',
ins_date DATE DEFAULT sysdate
)

LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;

COMMENT ON COLUMN LIMS_YS.test_schedule.seq IS '일련번호';
COMMENT ON COLUMN LIMS_YS.test_schedule.memo IS '비고';
COMMENT ON COLUMN LIMS_YS.test_schedule.ins_date IS '입력일시';

ALTER TABLE LIMS_YS.test_schedule ADD (
CONSTRAINT test_schedule_PK
PRIMARY KEY
(seq));

프로시져 스크립트

CREATE OR REPLACE PROCEDURE sp_test_schedule_ins IS

/******************************************************************************
NAME: sp_test_schedule_ins
PURPOSE: 오라클 job 테스트용, test_schedule 테이블에 데이터 insert

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
Created this procedure.

NOTES:

Automatically available Auto Replace Keywords:
Object Name: sp_test_schedule_ins
Sysdate:
Date and Time:
Username:
Table Name: test_schedule

******************************************************************************/
BEGIN
insert into test_schedule values(sq_test_schedule.nextVal, '비고', sysdate);
-- EXCEPTION
-- WHEN NO_DATA_FOUND THEN
-- NULL;
-- WHEN OTHERS THEN
-- Consider logging the error and then re-raise
-- RAISE;
END sp_test_schedule_ins;

Job 스크립트

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'LIMS_YS.SP_TEST_SCHEDULE_INS;'
,next_date => to_date('01-01-4000 00:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE)+1/8640'
,no_parse => TRUE
);
SYS.DBMS_JOB.BROKEN
(job => X,
broken => TRUE);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/

commit;

테스트 결과

테스트 결과서
테스트 진행을 위한 스크립트를 모두 생성한 후 진행된 테스트 결과를 기술한다.
1. Job 실행결과
	select * from test_schedule where rownum <= 10
	order by seq desc
	결과
	SEQ	MEMO	INS_DATE
	67	비고	2011-04-13 오전 11:42:05
	66	비고	2011-04-13 오전 11:41:55
	65	비고	2011-04-13 오전 11:41:45
	64	비고	2011-04-13 오전 11:41:35
	63	비고	2011-04-13 오전 11:41:25
	62	비고	2011-04-13 오전 11:41:15
	61	비고	2011-04-13 오전 11:41:05
	60	비고	2011-04-13 오전 11:40:55
	59	비고	2011-04-13 오전 11:40:45
	58	비고	2011-04-13 오전 11:40:35
2. 결과
	Job을 실행하면 10초에 한번씩 데이터가 insert 되는 것을 확인함

문제점 및 해결방안

문제점

1. 오라클의 Job 패키지는 원하는 시간에 정확히 실행이 되지 않는다.
몇 초정도 뒤로 밀려서 실행이 되는 경우가 많은데 짧은 주기로 수행을
해야하는 조건에서는 문제가 될 수 있다.
수행 주기가 10초 단위가 지속적으로 유지되지 못하고 10~14초 사이를
왔다갔다 하는 현상이 발생할 수 있다.
예) 12:00:00 에 실행되야 하는 Job이 실제로는 12:00:04 에 실행된다.

2. Job 수행이 16번 실패하면 Broken 상태가 되어 관리자가 명시적으로
Run 명령을 수행해야 관련 Job이 다시 수행된다.

3. 수행해야 할 업무가 많을 경우(많은 인터페이스 작업과 같은) 아주 많은
Job을 만들어내야 하기 때문에 관리의 어려움이 있을 수 있다.


해결방법

1. 수행 주기가 민감하지 않다면 큰 문제는 아님
최소 단위를 분으로 하고 Trunc() 함수를 사용하여 분단위를 잘 맞추면
정확한 시간에 실행이 된다고 함

2. Broken 상태를 체크하여 Run 시켜주는 작업을 할 수 있음
관련 문서를 찾아 그에 맞게 작업해야 함

3. Job 관리 문서를 작성
Job을 오라클의 스케줄러로 관리하여 스케줄러만 관리하면 되도록 구성



출처 : http://findfun.tistory.com/entry/오라클-Job을-테스트하자

인덱스를 이용해야만 성능은 향상되는가

우리가 SQL을 작성하면서 성능을 보장하기 위해 가장 먼저 무엇을 고려하는가? 가장 먼저 고려하는 사항은 인덱스일 것이다. 많은 경우에 작성한 SQL에 대해 인덱스를 생성한다면 성능을 보장 받을 수 있다고 생각하게 된다. 과연, 인덱스만 생성한다면 해당 SQL의 성능을 보장할 수 있겠는가? 어떤 SQL은 인덱스 때문에 성능이 엄청 저하될 수 있는 것이 현실이다. 이러한 경우는 경험해본 사람이라면 쉽게 이해할 수 있을 것이다.

그렇다면 어떤 경우의 SQL에는 인덱스가 필요하고 어떤 경우의 SQL에는 인덱스가 필요하지 않은 것일까? 인덱스를 이용하여 성능을 최적화하기 위해서 어떤 컬럼으로 인덱스를 구성할 것인가에 대한 것보다도 해당 SQL이 인덱스를 이용해야 할지 아니면 해당 SQL이 인덱스를 이용하면 안 되는지에 대한 정확한 기준이 필요하다. SQL을 작성하는 사람들은 이러한 기준을 가지고 있는가? 아마도 많은 사람들이 이러한 기준을 가지고 있지 않을 것이다. 이러한 문제는 SQL을 작성하는 개발자들만의 문제가 아니다.

우리가 많이 사용하는 툴들은 해당 SQL의 실행 계획에서 인덱스를 이용하지 못하는 경우에 빨간색을 표시하게 된다. 이와 같은 현상이 마치 무조건 문제인 것처럼 보이게 만들어 무조건 인덱스를 생성하게 만드는 경우도 많다. 이제부터 우리는 어떤 SQL은 인덱스를 이용하고 어떤 SQL은 인덱스를 이용해서는 안 되는지에 대해 정확하게 구분해야 할 것이다.

첫 번째로 인덱스를 이용해서는 안 되는 SQL에 대해 확인해 보자. 어떤 SQL이 인덱스를 이용하면 안 되는지에 대해 언급하기 전에 하나의 예제를 확인해 보자. 어떤 사이트를 지원 했을 때의 일이다. 개발 담당자는 매일 저녁 9시부터 1시간 동안 야간 통계 작업을 수행한 후 SQL의 수행 결과를 확인하고 퇴근을 하는 경우를 보았다. 해당 담당자는 매일 저녁 이와 같은 작업을 1년 동안 수행하고 있었다.

해당 SQL을 확인한 결과 해당 SQL은 해당 테이블의 대부분의 데이터를 액세스하여 통계 데이터를 추출하고 있었다. 해당 데이터를 액세스 하는 과정에는 인덱스를 이용하고 있었다. 해당 SQL을 최적화한 후에는 1시간 동안 수행되던 SQL이 단지 50초 정도에 종료할 수 있었다. 최적화 하는 과정은 해당 SQL이 인덱스를 이용하지 못하게 하고 테이블을 전체 스캔하도록 변경해 주었다.

단지, 인덱스를 이용하는가 아니면 인덱스를 이용하지 않는가에 의해 이와 같이 큰 영향을 미치게 된 것이다. 다른 어느 사이트에서도 이러한 문제를 인식하지 못하고 당연히 오래 수행되는 작업이라고 생각하고 매일 작업을 수행하고 있는 사이트가 있을 것이다.

바로 이것이 SQL이 인덱스를 이용해야 하는지 이용해서는 안 되는지에 기준을 제시해 줄 것이다. 그렇다면 인덱스를 이용해야 할지 인덱스를 이용하면 안되는지에 대한 기준을 제시하는 요소는 무엇인가? 바로 액세스 하는 데이터의 양이다.

해당 테이블에서 많은 양의 데이터를 액세스 한다면 인덱스를 이용하여 테이블을 액세스 하는 경우에는 인덱스를 액세스 한 후 테이블을 액세스 하는 랜덤 액세스 가 발생하기 때문에 성능은 매우 저하된다. 이와 같은 경우라면 인덱스를 이용하여 테이블을 액세스 하는 방법보다는 인덱스를 이용하지 않고 테이블을 전체 액세스 하는 경우가 더 빠른 성능을 보장하게 될 것이다.

예를 들어, 1만개의 단어를 저장하고 있는 사전에서 5000개의 단어를 찾는다고 가정하자. 해당 사전은 한 페이지에 20개 씩의 단어가 기록되어 있으며 그렇기 때문에 전체 페이지는 500 페이지가 된다고 가정하자. 이와 같다면 여러분들은 사전의 인덱스를 이용하여 원하는 5000개의 단어를 찾을 것인가 아니면 사전의 인덱스를 이용하지 않고 테이블의 데이터를 액세스 할 것인가?

대부분의 사람들은 사전의 인덱스를 이용하지 않고 테이블의 데이터를 액세스 해야 더 빠른 성능을 보장할 수 있을 거라고 이야기한다. 이는 분명히 맞는 답이다. 20개의 단어가 저장되어 있는 하나의 페이지에서 평균 10개의 단어는 우리가 찾고자 하는 단어일 것이다. 이와 같은 경우 사전의 인덱스를 이용한다면 하나의 페이지를 10번씩 액세스 하게 된다. 하지만, 사전의 인덱스를 이용하지 않고 사전을 처음부터 끝까지 읽게 된다면 하나의 페이지에서 10개의 단어를 찾을 수 있기 때문에 우리는 하나의 페이지를 한번만 액세스 하면 될 것이다. 하나의 페이지를 10번 액세스 하는 것이 빠르겠는가 아니면 하나의 페이지를 한번만 액세스 하는 것이 빠르겠는가? 두 말할 것도 없이 하나의 페이지를 한번만 액세스 하는 것이 빠를 것이다. 이와 같은 차이에 의해 성능에 있어서는 엄청난 차이가 발생할 수 밖에 없게 된다.

결국, 인덱스를 이용해야 할지 아닐지는 액세스 하는 데이터의 양에 의해 좌우된다. SQL의 성능을 최적화하기 위해 무조건 인덱스를 생성해서는 안될 것이다. 해당 SQL이 테이블의 많은 데이터를 액세스 해야 한다면 인덱스를 이용하는 것보다는 테이블을 전체 스캔하는 방법이 성능을 보장한다는 것을 명심하길 바란다.

두 번째로 인덱스를 이용해야 하는 SQL을 확인해 보자. SQL은 위와 같이 테이블을 전체 스캔해야 하는 SQL을 제외하면 인덱스를 이용하여 데이터를 액세스 해야 할 것이다.

그렇다면 테이블의 데이터 중 어느 정도의 데이터를 액세스 하는 것이 많은 양의 데이터를 액세스 하는 것일까? 또는 어느 정도의 데이터를 액세스 해야 적은 양의 데이터를 액세스 하는 것일까? 일반적으로 해당 테이블의 3%~5% 정도의 데이터가 기준이 된다. 해당 테이블의 데이터가 10만건이라고 가정하자. 그렇다면 3000건에서 5000건의 데이터가 기준이 될 것이다. 따라서, 1000건의 데이터를 액세스 해야 한다면 인덱스를 이용하는 것이 성능을 보장할 수 있게 된다.

하지만 10만건의 데이터를 액세스 하는 경우에는 3%~5%의 기준을 넘게 되므로 인덱스를 이용하는 것보다는 인덱스를 이용하지 않는 것이 더 유리할 것이다. 테이블의 데이터가 대용량이라면 3%~5%의 기준 값은 낮아질 것이다. 그렇기 때문에 초 대용량 테이블은 1%가 기준이 되기도 한다. 이와 같은 정확한 기준 값이 중요한 것은 아니다.

중요한 것은 많은 데이터를 액세스 하는 SQL이 인덱스를 이용한다면 우리가 원하는 성능을 보장 받을 수 없으며 반대로 인덱스를 이용해야 하는 SQL이 인덱스를 이용하지 않는다면 이 또한 성능을 보장 받을 수 없다는 것이다.

이와 같기 때문에 SQL을 작성하는 경우 해당 SQL이 인덱스를 이용해야 할지 아니면 테이블을 전체 스캔해야 할지를 가장 먼저 고려해야 할 것이다. 이제는 맹목적으로 해당 SQL에 인덱스를 생성해야 성능을 보장 받을 수 있다는 잘못된 사실에서 벗어나야 할 것이다.

인덱스 컬럼들의 순서와 분포도는 많은 상관 관계가 없다

SQL에 필요한 인덱스를 생성한다면 우리는 많은 경우에 결합 인덱스를 생성하게 된다. 결합 인덱스를 생성하면서 많은 경우에는 해당 컬럼의 분포도를 고려하여 분포도가 좋은 컬럼을 인덱스의 첫 번째 컬럼으로 구성하는 경우를 많이 보았을 것이다. 과연, 이와 같이 분포도가 좋은 컬럼을 결합 인덱스의 첫번째 컬럼으로 선정하는 방식이 우리가 선택할 수 있는 최상의 인덱스 선정일까?

결론부터 언급하자면 결합 인덱스에서는 컬럼의 분포도는 의미가 없게 된다. 이 뜻은 결합 인덱스를 생성하는 경우 각 컬럼의 분포도는 의미가 없다는 것이다. 분포도를 고려하지 않고 결합 인덱스를 생성한다는 것은 말이 되지 않는다고 할 수도 있을 것이다. 하지만, 분명한 것은 결합 인덱스에서의 분포도는 큰 의미를 가지지 않는다. 왜 이와 같은 현상이 발생하는 것일까?

카드 회사에서 카드 가입자의 카드번호만을 관리하는 테이블에서 카드번호 컬럼은 분포도가 매우 좋을 것이다. 하지만, 여기서 우리는 하나의 함정에 빠지게 된다. 그것은 무엇인가? 바로 분포도가 좋다는 뜻에 대한 함정이다. 우리가 카드번호 값에 대해 분포도가 좋다는 뜻은 무엇을 의미하는가? 이는 하나의 카드번호만을 액세스 하는 경우에 해당할 것이다.

모든 카드번호는 ‘1’로 시작한다고 가정하자. 만약, 카드번호 값에 대해 ‘1’로 시작하는 카드번호 값을 액세스 한다면 분포도는 어떠한가? 이와 같이 데이터를 액세스 한다면 아무리 분포도가 좋은 카드번호 컬럼도 많은 데이터가 추출되며 분포도는 안 좋게 된다. 결국, 우리가 항상 이야기 하는 분포도가 좋은 컬럼과 분포도가 나쁜 컬럼 컬럼의 기준에는 우리도 모르게 동일한 데이터를 액세스 하는 경우를 의미하게 된다.

‘111111’번 카드번호 값을 액세스 한다면 우리가 원하는 데이터는 한건의 데이터가 되므로 분포도는 좋게 된다. 하지만, SQL에서 ‘1’로 시작하는 모든 카드번호 데이터를 액세스 한다면 분포도는 나쁘게 된다. 이와 같이 우리가 말하는 분포도는 서로 약속은 안 했지만 해당 컬럼의 값과 동일한 데이터를 추출하는 경우에 해당하게 된다.

결국, 우리가 말하는 분포도는 동일한 값을 의미하게 된다. 하지만, 우리가 추출하고자 하는 데이터는 항상 동일한 데이터만을 의미하지는 않게 된다. 때로는 LIKE 연산자 또는 BETWEEN 연산자 등을 많이 이용하기 때문에 이런 경우라면 해당 컬럼의 분포도는 의미 없게 된다. 이와 같은 이유에서 해당 컬럼의 분포도는 더 이상 결합 인덱스를 생성하는 컬럼의 순서에 중요한 역할을 수행하지 못하게 된다.

인덱스 컬럼들의 순서를 효과적으로 선정하자

인덱스를 구성하는 각각의 컬럼의 분포도가 중요하지 않다면 결합 인덱스를 구성하는 컬럼의 순서를 고려할 경우 가장 먼저 고려해야 하는 요소는 무엇인가? 결합 인덱스를 구성할 경우 우리가 반드시 고려해야 하는 요소는 아래와 같다.

점 조건과 선분 조건

결합 인덱스의 순서를 정하는 가장 중요한 요소는 해당 컬럼에 사용되는 연산자이다. 아직도 많은 교육과 문서에서 컬럼의 분포도가 인덱스 선정에 중요하다고 언급하는 경우가 있다. 하지만, 이러한 것이 우리에게 많은 오류를 발생시킨다는 것을 이해하길 바란다. 가장 중요한 요소는 해당 컬럼을 액세스 하는 연산자라는 것을 명심하길 바란다. 위에서 점 조건에는 =과 IN 연산자만이 포함되며 나머지 연산자는 선분 조건에 해당된다.

SQL> SELECT ……
FROM TAB1
WHERE COL1 = ‘A’
AND COL2 BETWEEN ‘A’ AND ‘B’;

위와 같은 SQL이 수행되며 각 컬럼의 분포도는 COL1 컬럼의 경우에는 분포도가 좋으며 COL2 컬럼의 경우에는 분포도가 좋지 않다고 가정하자. 그렇다면 많은 사람들은 분포도만을 고려하여 COL2+COL1 인덱스를 생성하려고 하는 경우가 많다. 하지만, COL2 컬럼은 BETWEEN 연산자를 사용했으므로 해당 컬럼의 분포도는 의미가 없게 된다. 따라서, 위의 SQL에서 최적의 인덱스는 COL1+COL2 인덱스가 된다.

결국, 분포도를 배제하고 연산자를 통해 결합 인덱스를 생성해야 한다. 이와 같이 인덱스를 구성해야만 COL1 컬럼과 COL2컬럼에 의해 처리 범위가 감소하게 된다. 앞의 값의 하나의 값이 아닌 선분 조건이라면 처리 범위는 증가하기 때문이다. 결합 인덱스는 반드시 아래와 같은 특성을 가지게 된다.

● 점 조건+점 조건: 두 컬럼에 의해 처리 범위 감소
● 점 조건+선분 조건: 두 컬럼에 의해 처리 범위 감소
● 선분 조건+선분 조건: 앞의 선분 조건에 의해서만 처리 범위 감소
● 선분 조건+점 조건: 앞의 선분 조건에 의해서만 처리 범위 감소

위와 같이 컬럼의 분포도가 아닌 컬럼의 연산자에 의해 인덱스는 처리 범위를 감소시키게 되며 처리 범위를 가장 많이 감소시킬 수 있는 형태의 결합 인덱스만이 성능을 보장할 수 있게 된다. 분포도에 의한 결합 인덱스 선정이 아닌 연산자에 의한 결합 인덱스 선정의 중요성을 인식하길 바란다. 이것이야 말로 해당 SQL의 성능을 보장할 수 있는 유일한 방법이다.

SQL을 작성한 후 무조건 인덱스를 만들려고 하는 생각과 결합 인덱스에서 연산자를 고려하지 않고 분포도가 좋은 컬럼을 앞에 위치시키는 인덱스야 말로 성능을 저하시키는 주범이 된다. 이제부터 최적의 인덱스를 선정하기 위해 우리 함께 노력해야 할 것이다. 인덱스에 대한 우리가 쉽게 빠질 수 있는 함정에 빠지지 않게 항상 주의해야 할 것이다.

제공 : DB포탈사이트 DBguide.net
http://www.dbguide.net/know/know101003.jsp?IDX=1330&catenum=14

-- 락이 걸려있는 테이블을 조회한다 --

SELECT vo.session_id,do.object_name, do.owner, do.object_type,do.owner,
vo.xidusn, vo.locked_mode
FROM v$locked_object vo , dba_objects do
WHERE vo.object_id = do.object_id
;

-- 락이 걸려있는 테이블이 몇개인지 확인한다 --

SELECT count(*)
FROM v$locked_object vo , dba_objects do
WHERE vo.object_id = do.object_id

select a.sid, a.serial#
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'
;

-- 해당되는 테이블 락을 해제시킨다 --

alter system kill session '10,23779';
alter system kill session '14,22484';
commit;

테이블의 인덱스의 조회와 인덱스 생성하는 쿼리 이다.
아래는 타겟 테이블과 똑같이 템프성 테이블을 만들어 사용하면서 동일한 인덱스를 만들어 주기 위해서 쿼리를 작성했다.

-- 인덱스 조회 쿼리(테이블명으로 조회함)
SELECT C.INDEX_NAME
, C.COLUMN_NAME
, C.COLUMN_POSITION
, I.UNIQUENESS, 'A' DIV
FROM USER_INDEXES I
, USER_IND_COLUMNS C
WHERE I.INDEX_NAME = C.INDEX_NAME
AND I.TABLE_NAME = '원본테이블명'
--ORDER BY UNIQUENESS, COLUMN_POSITION
UNION ALL
SELECT C.INDEX_NAME
, C.COLUMN_NAME
, C.COLUMN_POSITION
, I.UNIQUENESS, 'B' DIV
FROM USER_INDEXES I
, USER_IND_COLUMNS C
WHERE I.INDEX_NAME = C.INDEX_NAME
AND I.TABLE_NAME = '타겟테이블명'
ORDER BY DIV, UNIQUENESS, COLUMN_POSITION
;

-- 유니크 인덱스 생성 쿼리
CREATE UNIQUE INDEX 유니크인덱스_타겟테이블
ON 타겟테이블명(
유니크인덱스컬럼명1
,유니크인덱스컬럼명2
,유니크인덱스컬럼명3
);

-- 일반 인덱스 생성 쿼리
CREATE INDEX 인덱스_테이블명
ON 인덱스넣을테이블명(
인덱스컬럼명1
,인덱스컬럼명2
,인덱스컬럼명3
,인덱스컬럼명4
);

-- 인덱스 삭제 쿼리
DROP INDEX XPK_TEMP_N_A통_기타;

1. 디비링크 생성

create [public] database link 링크이름

connect to 원격디비 아이디 identified by 원격디비 비밀번호

using ‘TNS명’

…

public : public 을 사용하면 공용 디비링크가 생성된다.

사용하지 않으면 생성한 사용자만 사용이 가능하다.

* NLS_CHARACTER_SET이 동일하지 않으면 한글이 깨져서 보인다.

…

예) create public database link linkWebHR

connect to ksprg identified by pw1234

using ‘WEB_HR’

…

2. 디비링크 사용

select * from table@링크이름

…

예) select * from table@linkWebHR

…

3. 디비링크 삭제

drop public database link 생성된 링크명칭

…

* 디비링크 현황

select * from all_db_links;

오라클 DB 링크
- 관리자로 로그인
- fwms TNS 지정
FWMS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 150.1.2.3)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA10)
)
)

- 링크 지정
create public database link [link_name] connect to [user] identified by [암호] using '[tns]';

create public database link fwms connect to xxxx identified by xxxx using 'FWMS';

이렇게 지정하면

select * from tab@fwms; 검색해보면 리스트를 나온다.

※ fwms.a & a@fwms 의 차이점
- fwms.t : 같은 계정의 ORA10의 fwms유저에 접근한다는 거다.
- a@fwms : fwms tns로 지정된 DB서버 정보에 접근한다는거다.