긍적적인 사고, 음식의 절제, 규칙적인 운동

출처 : http://blog.naver.com/engram77/40005073745

RowCache의 Miss율을 보여준다



/* rowCache 의 MissRatio를 조사하는 스크립트

** <<박제용 99.11>>

** Row chache 의 Miss ratio는 15% 이하로 유지하는 것이 좋다.

** 그렇지 않을경우 shared_pool_size를 늘리는것을 고려해야 한다.

*/

select sum(gets) "Gets",

sum(getmisses) "Misses",

(1-(sum(getmisses)/(sum(gets)+sum(getmisses))))*100 "HitRate"

from v$rowcache;

---

Library Cache의 Hitratio를 보여준다.(shared_pool의 size결정을위해)



/* library Cache Hitratio 출력 스크립트

** <<박제용 99.11>>

** library Cache 의 hitratio 가 0.9 이하이면

** Shared Pool Size를 늘려주거나, SQL 문의 이상을

** 조사해야 한다.

*/

select sum(pins) Executions,

sum(pinhits) "Execution Hits",

sum(reloads) Misses,

((sum(pins) / (sum(pins) + sum(reloads))) * 100) hitratio

from v$librarycache;

---

SQL Cursor를 보여주는 스크립트



/* SQL Cursor를 조사하는 스크립트.

** <<박제용 99.11>>

** SQL Cursor 를 조사하여 부하가 많이 걸리는 SQL문과

** 메모리를 조사한다.

** loads : 캐쉬에서 나갔다 들어온 횟수(best=1).

** invalidations : LRU에서 무효화된 횟수. 이 값이 4이상이면

** shared_pool_area를 확장해야한다.

** parse_calls : 이 커서의 호출 수.

** sorts : 수행된 소트횟수

** command_type: 2 - insert, 3-select, 4-update, 7-delete

*/

select sql_text, loads, invalidations, parse_calls, sorts

FROM v$sqlarea

WHERE sql_text NOT LIKE '%$%'

AND command_type IN(2,3,6,7);

---

explain을 보기 쉽게 출력해주는 SQL



*

** expain plan 결과를 보기 쉽게 출력해주는 스크립트.

**

** 1) expain을 처음 사용할 경우엔 [ORACLE_HOME]/rdbms/admin/utlxplan.sql을 실행,

** plan_table을 생성한다.

** 2) 처음 사용이 아니면 delete from plan_table; 을 실행하여 이전 결과를 삭제.

**

** 실행결과 파싱번호(id)가 길면 SQL이 비효율적이거나, shared_pool_size가 작은것이다.

** 기타 SQL문이 인덱스를 사용하는지 등등을 알수 있다.

*/

col operation format a30

col options format a20

col id format 99

select id, lpad(' ',2*level) || operation ||

decode(id, 0, ' Cost= ' || position )"operation",

options, object_name "object"

from plan_table

connect by prior id=parent_id

start with id =0;

---

세션별로 과도한 memory read를 하는 SQL문을 찾아주는 스크립트



/*

** SQL query 튜닝 스크립트.. <박제용>

**

** 유저별로 과도한 logical read를 수행하는 sql 문 찾기

**

**

*/

Break on User_Name On Disk_Reads on Buffer_Gets on Rows_Processed

Select A.User_Name, B.Disk_Reads, B.Buffer_Gets, B.Rows_Processed, C.SQL_Text

From V$Open_Cursor A, V$SQLArea B, V$SQLText C

Where A.User_Name = Upper('&&User') And A.Address = C.Address

And A.Address = B.Address

Order By A.User_Name, A.Address, C.Piece;

---

과도한 memory read를 하는 SQL문을 찾아주는 스크립트



/*

** SQL query 튜닝 스크립트.. <박제용>

**

** 과도한 logical read를 수행하는 SQL문을 V$SQLAREA 에서 검색해줌.

**

** 원인 => 1) 인덱스 컬럼에 distinct한 값이 적은, 부적절한 인덱스의 사용. (대체로 인덱스를 지워야 할 경우)

** 2) 최적화 되지 않은 SQL 문장

*/

select buffer_gets, sql_text from v$sqlarea

where buffer_gets > 200000

order by buffer_gets desc;

---

과도한 disk read를 하는 SQL문을 찾아주는 스크립트



/*

** SQL query 튜닝 스크립트.. <박제용>

**

** 과도한 disk read를 수행하는 SQL문을 V$SQLAREA 에서 검색해줌.

**

** 원인 => 1) SQL문이 최적화 되지 않아 disk read를 많이 할 수 밖에 없는 쿼리일경우.

** (index가 없거나 사용되지 않을때)

** 2) db_block_buffers 또는 shared_pool_size 가 작은 경우. (메모리가 적음)

*/

select disk_reads, sql_text from v$sqlarea

where disk_reads > 10000

order by disk_reads desc;

---

SGA의 shared pool내에 캐쉬된 object중 큰 영역을 차지 하고 있는 것을 찾아주는 스크립트



/*

** Shared_pool에 저장된 내용보기 <박제용>

**

** 프로시져나 패키지등은 shared_pool에 저장되며 저장된 객체중

** 그 크기가 100K 가 넘는것을 보여준다.

*/

col name format a30



select name, sharable_mem

from v$db_object_cache

where sharable_mem > 100000

and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')

and kept = 'NO';

---

SGA의 shared pool 의 현재 사용도를 출력해주는 SQL



/*

** shared_pool_size의 현재 사용 현황을 보여줌. <박제용>

**

** shared_pool_size의 현재의 사용현황을 보여준다.

** 이 데이터를 주기적으로 보관하여 분석한다.

*/

col value for 999,999,999,999 heading "Shared Pool Size"

col bytes for 999,999,999,999 heading "Free Bytes"

select to_number(v$parameter.value) value, v$sgastat.bytes,

(v$sgastat.bytes/v$parameter.value)*100 "Percent Free"

from v$sgastat, v$parameter

where v$sgastat.name = 'free memory'

and v$ parameter .name = ‘shared_pool_size;

---

/*
** Shared_pool의 hit ratio보는 스크립트.. <박제용>
**
** 이 영역은 SQL 쿼리문이 저장되고, 유저별 사용 영역과, 데이터 딕셔너리등이 저장된다.
** 만일 적게 할당되면 유저의 접속이 많아질수록 throughput에 큰 영향을 준다.
** hit ratio는 95% 이상을 유지시켜야 한다.
**
*/
select sum(gets) "Gets", sum(getmisses) "Misses",
(1-(sum(getmisses) / (sum(gets)+sum(getmisses))))*100
"HitRate"
from v$rowcache;

---

SGA에 큰 영향을 주는 파라미터들의 설정을 표시해주는 SQL

/*
** DB의 주요 메모리 사용 조회 <박제용>
**
** DB의 주요 메모리 사용을 보여준다. DB가 사용하는 메모리는
** v7.3의 경우 OS메모리의 2/5 를, v8.x 버젼의 경우 1/2 정도를
** 할당해 주는 것이 좋다.
**
*/
select name, value
from v$parameter
where name in('db_block_buffers','db_block_size','shared_pool_size','sort_area_size');

---

SGA중에 Block buffer의 현재 사용량과 빈공간을 보고싶을 때

/*
** DB_BLOCK_BUFFERS의 현재 사용 현황을 보여줌. <박제용>
**
** block_buffer를 튜닝하기 전에 현재의 사용현황을 보여준다.
** 이 데이터를 주기적으로 보관하여 분석한다.
*/
select decode(state, 0, 'FREE',
1, decode(lrba_seq,0,'AVAILABLE','BEING USED'),
3, 'BEING USED', state)
"BLOCK STATUS", count(*)
from x$bh
group by decode(state,0, 'FREE',
1, decode(lrba_seq,0,'AVAILABLE','BEING USED'),
3, 'BEING USED', state);

---

SGA의 Block buffer의 hitratio를 연산해주는 SQL

/*
** DB_BLOCK_BUFFERS의 hit ratio보는 스크립트.. <박제용>
**
** 이 영역은 유저의 쿼리 내용이 버퍼링 되는 공간으로 크기가 적으면
** 유저별로 과도한 disk read를 발생시킨다.
** hit ratio는 90~95% 이상을 유지시켜야 한다.
**
*/
select 1-(sum(decode(name, 'physical reads', value,0))/
(sum(decode(name, 'db block gets', value,0)) +
(sum(decode(name, 'consistent gets', value,0))))) * 100
"Read Hit Ratio"
from v$sysstat;

---

한 유저 스키마의 모든 객체를 Analyze 해주는 SQL

/*
** 테이블 analyze 스크립트 2.. <박제용>
** 한 유저에 속한 모든 객체를 analyze한다.
**
** 사용방법 SQL>@analyze0 [유저ID]
** 유저ID는 반드시 대문자로.
*/
exec dbms_utility.analyze_schema('&1','DELETE');
exec dbms_utility.analyze_schema('&1','COMPUTE');

---

지정한 테이블을 다시 Analyze 해주는 SQL

/*
** 테이블 analyze 스크립트.. <박제용>
** Query를 파싱하는 Optimizer로 하여금 더욱 정확하고, 빠른 파싱을 유도하기위해 Analyze를 한다.
**
** 사용방법 SQL>@analyze1 [테이블명]
*/
analyze table &1 delete statistics;
analyze table &1 compute statistics;

TKPROF 사용방법

** trace 결과를 파악을 위한 Tkprof 유틸리티 사용

** TKPROF trace_file output_file [옵션설정] [explain=username/password]

** trace_file SQL_TRACE로 생성한 트레이스 *.trc 트레이스 파일.
** output_file 결과가 저장될 파일명
** SORT=parameters 소팅 파리미터 execpu, ....
** EXPLAIN=username/password
** SYS=no/yes 시스템이 사용한 쿼리를 볼때는 yes로 설정한다.

예제))))

tkprof ora_12626.trc result.txt explain=scott/tiger sys=no

c:\orawin95\bin\tkprof73.exe 2.TRC ORA%1.TXT explain=TUNING/TUNING sys=no sort=execpu

EDIT ORA%1.PRF

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장

Handling Exceptions

1. Exception Types

- Predefined Oracle Server
; 오라클에서 미리 정한 ERROR...(NO_DATA_FOUND등...)
- Non-predefined Oracle Server
; 자주일어나는 error가 아니어서 미리 define되어 있지 않은 error
- User-defined
; 오라클 서버 error가 아니라 user가 업무상 rule에 위배되는 사항을 exception처리 하는 것.


2. Predefined Exception Names

ACCESS_INTO_NULL
ORA-06530 초기화되지 않은 object의 속성에 값을 assign하라.

ACCESS_INTO_NULL
ORA-06530 초기화되지 않은 object의 속성에 값을 assign하라.

COLLECTION_IS_NULL
ORA-06531 초기화되지 않은 nested table에 EXISTS와 다른 method를 적용시켜라.

CURSOR_ALREADY_OPEN
ORA-06511 OPEN CURSOR가 이미 open되어 있다.

DUP_VAL_ON_INDEX
ORA-00001 중복된 값을 insert하려고 한다.

INVALID_CURSOR
ORA-01001 잘못된 cursor연산자를 발생시켰다.

INVALID_NUMBER
ORA-01722 문자열을 number type으로 convertion 실패

LOGIN_DENIED
ORA-01017 오라클 login시 username or password를 잘못 입력했다.

NO_DATA_FOUND
ORA-01403 single row SELECT된 데이터가 없다.

NOT_LOGGED_ON
ORA-01012 PL/SQL이 오라클에 connect되지 않은 상태에서 database를 호출하려고 한다.

PROGRAM_ERROR
ORA-06501 PL/SQL이 내부적인 문제가 있다.

ROWTYPE_MISMATCH
ORA-06504 Host cursor변수와 PL/SQL cursor변수가 return type과 일치하지 않는다.

STORAGE_ERROR
ORA-06500 PL/SQL을 실행중 memory를 손상시켰다.

SUBSCRIPT_BEYOND_COUNT
ORA-06533 nested table을 참조하는 element가 너무 큰 index를 사용했다.

SUBSCRIPT_OUTSIDE_LIMIT
ORA-06532 nested table을 참조하는 element가 정상적인 범위의 벗어난index를 사용했다.

TIMEOUT_ON_RESOURCE
ORA-00051 resource를 기다리는 동안 time-out이 발생했다.

TOO_MANY_ROWS
ORA-01422 single row SELECT된 데이터가 너무많은 row를 갖고있다.

VALUE_ERROR
ORA-06502 Arithmetic, conversion, truncation, size constraint ERROR

ZERO_DIVIDE
ORA-01467 0으로 나누기를 실행했다.


3. Predefined Exception

BEGIN SELeCT ... COMMIT ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
statement ;
WHEN TOO_MANY_ROWS THEN
statement ;
WHEN OTHERS THEN
statement ;
END ;

4. Non-Predefined Error

DECLARE
e_products_invalid EXCEPTION ;
PRAGMA EXCEPTION_INIT (e_products_invalid, -2292 ) ;
v_message VARCHAR2(50) ;
BEGIN
...
EXCEPTION
WHEN e_products_invalid THEN
:g_message := 'Product code specified is not valid.' ;
...
END ;

--> -2292 error code를 e_products_invalid 라는 이름으로 정의 하겠다.

5. User-Defined Exception

DECLARE
e_amount_remaining EXCEPTION ;
...
BEGIN
...
RAISE e_amount_remaining ; -- 여기서부터 e_amount_remaining을 발생 시킨다.
...
EXCEPTION
WHEN e_amount_remaining THEN
:g_message := 'There is still an amount in stock.' ;
...
END ;

6. SQLCODE 와 SQLERRM

- SQLCODE : error code를 return한다.
- SQLERRM : error message를 return한다.

DECLARE
v_error_code NUMBER ;
v_error_message VARCHAR2(255) ;
BEGIN
...
EXCEPTION
...
WHEN OTHERS THEN
ROLLBACK ;
v_error_code := SQLCODE ;
v_error_message := SQLERRM ;
INSERT INTO errors VALUES (v_error_code, v_error_message) ;
END ;




7. RAISE_APPLICATION_ERROR
; error message를 user가 define하고 싶을때 사용한다.
error code범위는 ( -20000 ~ -20999 )

예1) EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20201, 'Manager is not a valid employee.') ;
END ;

예2) DELETE FROM emp
WHERE mgr = v_mgr ;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20202,'This is not a valid manager') ;
END IF ;



.

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장

*오라클 힌트 사용예

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

select /*+ index( idx_col_1 ) */

name, age, hobby

from member

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

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장

Why Join ?
DB를 설계할 때 정규화(Normalization)가 기본 개념이듯이, SQL에서는 서로 다른 테이블들에 흩어져 있는 데이터를 주어진 조건에 의해 연결하는 조인(Join)이 기본 개념이라 할 수 있다.

예를 들어, 서울에 위치한 부서명과 소속된 사원명을 출력하라는 쿼리가 있을 때에 부서명은 DEPT 테이블에 있고, 사원명은 EMP 테이블에 있다. 따라서 조인이 필요할 것이고, 조인에 사용되는 연결조건은 소속관계이므로 DEPT 테이블의 부서번호와 EMP 테이블의 부서번호가 동일하다는 조건이 될 것이다.

SELECT d.dname, e.ename
FROM dept d, emp e
WHERE d.loc = ‘서울’
AND d.deptno = e.deptno ß 조인조건

Join Order
조인은 두개 이상의 테이블을 주어진 조인조건으로 연결한다. 이때 연결은 순차적으로 수행되므로 만약 2개의 테이블을 조인하면 2종류의 순서가 가능하고, 3개의 테이블을 조인하면 6종류의 순서가 가능하다. 이때 항상 성능상 유리한 순서 하나로 수행되도록 할 필요가 있다.

RBO에서는 FROM 절의 가장 오른쪽부터 왼쪽으로 풀어나간다. 그러나 CBO를 사용하는 것이 권장된다.

1. 많이 걸러지는 순서대로

테이블에 주어지는 조건에 따라 많이 걸러지는 테이블을 먼저 처리하도록 해야 한다. 많이 걸러진다는 것은 조인조건으로 연결할 소스데이터가 그만큼 줄어든다는 것을 뜻하는 것이므로 성능상 유리하기 때문이다. 많이 걸러지는 것은 대개 그 테이블에 주어지는 WHERE 조건으로 판단하며, 많이 걸러질 수 있는 것은 데이터 상황으로 파악해야 할 것이다.

예를 들면, 위의 첫번째 SQL예제에서 DEPT 테이블을 먼저 처리하는 것이 좋은지, EMP 테이블을 먼저 처리하는 것이 좋은지 살펴보자. 먼저 DEPT 테이블에 대해서 loc 컬럼에 대한 조건이 있는 반면에, EMP 테이블에 대해서는 조인조건 이외의 조건은 없으므로 DEPT 테이블이 먼저 처리되는 것이 좋을 것이다. 만약 EMP 테이블에 다른 조건이 있고, DEPT 테이블에도 조건이 있는 경우에는 데이터 상황으로 비추어 많이 걸러지는 것을 우선적으로 처리하는 것이 좋다.

SELECT d.dname, e.ename
FROM dept d, emp e
WHERE d.loc = ‘서울’
AND d.deptno = e.deptno
AND e.job = ‘MANAGER’


2. ORDERED 힌트


ORDERED라는 힌트가 주어지면 FROM 절의 왼쪽부터 오른쪽으로 순서대로 조인을 하라는 것이다. 흔히 SQL개발자가 데이터 상황을 보았을 때 가장 유리한 순서를 알고 있다면 FROM 절에 왼쪽부터 오른쪽으로 테이블들을 배치하고 SELECT에 ORDERED 힌트를 준다.

예를 들면 위의 예에서 d.loc = ‘서울’ 이라는 조건이 e.job = ’MANAGER’라는 조건보다 더 많이 걸러진다고 판단되면 아래와 같이 FROM절에 dept 다음에 emp를 기술하고 ORDERED 힌트를 명시한다.

SELECT /*+ ORDERED */ d.dname, e.ename
FROM dept d, emp e
WHERE d.loc = ‘서울’
AND d.deptno = e.deptno
AND e.job = ‘MANAGER’

그러나 항상 조인조건에 사용될 액세스경로를 항상 주의하여야 하고, 만약 중간에 적합치 않은 액세스 경로가 있다면 원하는 순서대로 풀리지 않을 경우가 많으므로 인덱스생성등의 조치를 통해서 액세스경로를 정상으로 만들어야 한다.

예를 들면, 위의 예에서 e.deptno에 인덱스가 없고 d.deptno에는 인덱스가 있는 경우에는 어쩔 수 없이 emp 테이블이 먼저 풀린다. 이때에는 e.deptno에 인덱스를 만드는 것을 고려해야 한다.

Join Operations
조인은 테이블에 존재하는 데이터들을 주어진 조인조건에 만족되는 데이터들끼리 연결하는 것을 의미하는데, 그 조인조건을 만족하는 로우들을 매핑하기 위한 물리적인 방식에는 세가지가 존재한다. 이 방식은 옵티마이저가 인덱스의 존재 유무라든지, 데이터의 분포도를 고려해서 플랜을 작성하지만, SQL 개발자가 힌트를 제공함으로서 원하는 조인방식으로 유도할 수도 있다.

1. NESTED-LOOPS

대부분의 조인에서 많이 사용되는 방식이다. 한 테이블이 먼저 풀리고, 풀린 결과의 각 건에 대해서 다른 테이블에 조인조건이 만족되는 건이 있는지를 검사하는 방식이다. 먼저 풀리는 테이블을 드라이빙(driving) 테이블이라 하고, 나중에 조인조건을 검사하는 테이블을 이너(inner) 테이블이라 한다.

NESTED-LOOPS 조인의 수행성능을 좋게 하기 위해서는 반복적으로 발생하는 이너 테이블에 대한 조인조건 검사 수행이 최적화되어야 한다. 다시말해서, 인덱스가 생성되어 있어야 한다. 인덱스가 없는 상태에서 NESTED-LOOPS 방식으로 플랜이 생성되지 않을 가능성이 크지만, 만약 플랜이 NESTED-LOOPS 조인이면서 이너테이블이 FULL TABLE SCAN이라면 조인성능은 매우 느리다.

만약 SQL 개발자가 조인을 NESTED-LOOPS 방식으로 유도하기 위해서는 USE_NL 힌트를 사용할 수 있으나, 이너 테이블에 조인조건을 검사하기 위한 인덱스가 존재하는지 확인해야 하고, 없다면 생성해야 한다.

SELECT /*+ USE_NL(e) */ ß emp 테이블을 이너 테이블로 하는 NL 조인
d.dname, e.ename
FROM dept d, emp e
WHERE d.loc = ‘서울’
AND d.deptno = e.deptno ß emp 테이블의 deptno 컬럼에 인덱스 필요


2. HASH

한쪽 테이블에 대해서 조인조건에 사용된 컬럼값들을 이용하여 해쉬 테이블을 만들고 다른 쪽 테이블에 대해서 그 해쉬함수를 적용하여 조인조건을 만족하는 건들을 찾아가는 방법이다.

해쉬테이블을 만드는데 오버헤드가 많이 들 수 있으므로, 이 방법은 주로 한쪽 테이블의 풀린 결과가 매우 적은 경우에 사용된다. 그리고 다른 쪽 테이블이 매우 큰 대용량의 경우에 해쉬방식이 NESTED-LOOPS 방식보다 빠르게 수행되는 편이다.

이 방법은 Equi-Join (조인조건이 =로 되어 있는 경우)에서만 사용될 수 있으며, 힌트로는 USE_HASH가 있다.


3. SORT-MERGE


양쪽 테이블을 풀은 결과를 조인컬럼에 의해서 정렬한 이후에 순서대로 내려가면서 조인조건을 비교하는 방식이다. 인덱스가 없어서 NESTED-LOOPS 방식을 사용하기 어려우면서 양쪽 테이블의 풀은 결과가 작지 않아서 HASH 방식도 어려운 경우에 사용될 수 있다.

이 방식도 equijoin에서만 사용할 수 있고, 힌트로는 USE_MERGE가 있다.


4. Cluster


조인하고자 하는 두 테이블이 조인 연결조건에 의해서 클러스터링 되어 있는 경우에 단순히 클러스터만 읽어서 조인을 할 수 있는 방법이다. 이 방식도 equijoin에서만 사용 가능하다.


Join Methods
1. Outer-Join
두 테이블을 조인할 때 비록 조인조건을 만족하지 않더라도 조인결과에 나오고 싶게 하고 싶은 경우가 있다. 이럴때 아우터조인을 사용할 수 있는데, 항상 기준 테이블을 주고, 기준 테이블에 있는 건들은 조인조건에 의해 버려지지 않도록 한다. 아우터조인의 대상이 되는 테이블의 조건에는 (+) 기호를 붙인다. 그러면 아우터조인의 대상이 되는 테이블의 컬럼값들은 조인조건에 만족되면 있는 값들이 출력되고, 만족되지 않는 경우에는 NULL로 출력된다.

예를 들어, 부서명과 사원명을 출력하되 사원이 하나도 없는 부서명도 출력하라.

SELECT d.dname, e.ename
FROM dept d, emp e
WHERE d.deptno = e.deptno(+)

만약, 아우터조인의 대상이 테이블에 조건을 주어야 하는 경우에는, 그 조건에 있는 컬럼에도 (+) 기호를 붙여야 하며, 이 의미는 조인조건을 만족치 못하지만 아우터조인에 의해서 살아난 경우에는 그 조건을 검사하지 말라는 의미이다. 그 조건에 (+) 기호를 붙이지 않는 경우에는 그 조건에 의해서 아우터조인의 효과가 무력화된다.

예를 들어, 모든 부서를 나열하되, 매니저가 있는 경우에 매니저이름을 보여라.

SELECT d.dname, e.ename
FROM dept d, emp e
WHERE d.deptno = e.deptno(+)
AND e.job(+) = ‘MANAGER’


그러나 아우터조인의 대상 테이블에 OR 또는 IN의 조건을 적용해야 하는 경우에는 (+) 기호를 붙이지 못한다.

예를 들어, 모든 부서를 나열하되, 매니저 또는 ANALYST가 있는 경우에는 그 이름을 출력하라.

SELECT d.dname, e.ename
FROM dept d, emp e
WHERE d.deptno = e.deptno(+)
AND e.job(+) IN (‘MANAGER’, ‘ANALYST’)


위의 예를 수행하면 “ORA-01719: outer join operator(+) not allowed in operand of OR or IN” 에러가 발생한다. 이러한 경우에는 인라인뷰를 사용하는 등의 방법으로 우회해야 한다.

아우터조인의 경우, 조인의 순서는 항상 기준 테이블이 먼저 풀린다.


2. Self-Join
자기 자신 테이블과 조인하는 것을 말하는 것으로, 대개 Self Relationship을 가지는 설계에서 많이 사용된다.

예를 들어, 사원번호 100번의 사원의 이름과 그의 관리자명을 구하라는 쿼리에서

SELECT e.ename, m.ename
FROM emp e, emp m
WHERE e.empno = 100
AND e.mgr = m.empno


만약 위의 예제에서 관리자들을 최상위까지 나열하라. 단, 최상위까지는 3명의 관리자가 있을 수 있다. 라고 한다면 아래와 같이 할 수 있다.

SELECT e.ename, m1.ename, m2.ename, m3,ename
FROM emp e, emp m1, emp m2, emp m3
WHERE e.empno = 100
AND e.mgr = m1.empno
AND m1.mgr = m2.empno
AND m2.mgr = m3.empno


그러나 몇 명의 관리자가 있는지 SQL 개발단계에서 알 수 없다면 셀프조인으로 해결할 수는 없고, CONNECT BY로서 해결해야 한다.

SELECT LEVEL, e.ename
FROM emp
START WITH e.empno = 100
CONNECT BY PRIOR mgr = empno


3. Non-equijoin

조인은 항상 FK에 의해 참조되는 컬럼과의 = 로서만 조인되는 것은 아니다. 때로는 크기비교 연산자나 LIKE 등에 의해 데이터가 연결될 수도 있다.

예를 들어, 사원들의 직급과 연봉수준을 출력하라는 쿼리가 있을때에 SALGRADE 테이블과 EMP 테이블은 BETWEEN으로 연결될 것이다.

SELECT e.ename, e.job, g.level
FROM emp e, salgrade g
WHERE e.sal BETWEEN g.losal AND g.hisal

때로 조인을 하다보면 조인조건이 SUBSTR()에 의해 조인되는 컬럼의 변형(Suppress)를 가하게 되어 인덱스를 사용하지 못하는 경우가 생길 수 있다. 이러한 경우에는 더 유리한 플랜으로 유도하기 위해서는 SUBSTR()으로 잘라서 비교하는 것이 아니고 반대편을 ‘%’를 붙여서 LIKE로 비교하면 변형이 발생하지 않고 인덱스를 사용하는 조인으로 유도할 수 있다.


4. Semi-Join
EXISTS를 사용한 서브쿼리에서 SEMI-JOIN 형식으로 풀린다.


5. Anti-Join
NOT IN을 사용한 서브쿼리에서 ANTI-JOIN 형식으로 풀린다.


To Prevent Join
1. 비정규화(Denormalization)


설계 당시에 자주 발생할 소지가 있는 조인에 대해서 미리 조인한 형태의 설계를 하는 것을 말한다. 그러나 데이터 관리 및 정합성을 보장하기 어려울 수 있다.

예, 추천종목 테이블에서 종목코드와 더불어 종목명과 시장구분을 가지고 있다.



2. PL/SQL Function 사용
비정규화는 데이터의 생성시에 부담을 줄 수 있다. 따라서 비정규화가 어려운 경우에는 Function을 사용해서 조인을 피할 수 있다.

예, 주문내역테이블의 경우 주문이 발생할 때마다 종목명과 시장구분을 세팅하기가 어려우므로 쿼리시에 get_stockname(stkcd)이라는 함수를 이용한다.

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장

오늘 배울 것을 내일로 미루지 말고,
올해 배울 것을 내년으로 미루지 말라!
해와 달은 가고 세월은 나를 기다리지 않으니,
오호, 늙어 후회한 들 이 누구의 허물인가?

소년은 늙기 쉽고 학문은 이루기 어려우니,
잠시라도 시간을 가볍게 여기지 말라!
연못가의 봄풀은 아직 꿈을 깨지도 못하는데,
댓돌 앞의 오동나무 잎은 이미 가을 소리를 전하는 구나!

가을에 읽어 보는 주자의 권학문(勸學文)은 일종의 각성제로 다가옵니다.
혹 필요하신 분을 위해 원문도 첨부해 드립니다. 행복한 주말 되세요. 조영탁 Dream


물위금일불학이유래일 (勿謂今日不學而有來日)
물위금년불학이유래년 (勿謂今年不學而有來年)
일월서이세불아연 (日月逝而歲不我延)
오호노이시수지건 (鳴呼老而是誰之愆)
소년이노학난성 (少年易老學難成)
미각지당춘초몽 (未覺池塘春草夢)
계전오엽이추성 (階前梧葉已秋聲)
일촌광음불가경 (一寸光陰不可輕)

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장

자신을 믿고
도전하기로 마음먹었다면
외부적인 요건들을 긍정적으로 해석하고 믿어야 한다.
사사건건 트집을 잡는 상사와 일을 해야 한다면
스스로를 담금질하는 기회로 삼아라. 오히려
실력을 보여줄 수 있는 기회라 여기고
더 열심히 일하라. 우리 몸은 건강이
나빠지면 신호를 보낸다. 그럴 땐
건강관리에 더 신경을 써 더욱
튼튼한 사람이 되는 계기로
삼으면 된다.


- 윤석금의《긍정이 걸작을 만든다》중에서 -


* 상황은 똑같습니다.
그러나 그 해석에 따라 방향이 달라집니다.
긍정과 부정으로 갈리고, 행복과 불행으로 바뀝니다.
무슨 일이든 어떻게 받아들이느냐가 중요합니다.
이치는 간단합니다. 나의 마음 안에
동서남북이 다 들어 있습니다.

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장

할머니국수집 두부국수가 먹고싶어서 명동으로~

두부국수와 김말이, 비빔국수.

비빔국수는 좀 비추.

두부국수가 심심하니 맛있다.

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장