Alice's Story

권순용의 DB 이야기

인덱스 관련 실행 계획은 SQL 최적화의 기본이다

 인덱스의 실행 계획은 다른 어떤 실행 계획보다 종류가 많다. 그래서인지 많은 사람들이 인덱스의 실행 계획에 대해 잘 이해하지 못하는 경우가 많다. 인덱스의 실행 계획은 인덱스를 이용했는지 안 했는지만을 의미한다고 생각하는 경우가 많은 것 같다. 이와 같은 생각이 우리로 하여금 SQL 최적화를 더욱 어렵게 만드는 원인일 것이다. 이제부터라도 인덱스에 대한 모든 실행 계획을 이해해야 할 것이다.

 SQL의 최적화에 있어서 인덱스의 실행 계획에 대한 이해는 매우 중요하다. 이는 대부분의 실행 계획에는 인덱스 관련 실행 계획이 생성되기 때문이다. 이와 같기 때문에 인덱스 관련 실행 계획을 이해하지 못한다면 SQL 최적화는 절대 수행할 수 없게 된다. 또한 인덱스 관련 실행 계획의 종류가 많기 때문에 인덱스 실행 계획에 대한 정확한 이해는 반드시 필요하다. 이번 호에서는 인덱스를 이용하는 실행 계획 중 Index Full Scan에 대한 실행 계획을 분석하는 방법에 대해 같이 한번 확인해 보도록 하자.

 인덱스도 Full Scan이 가능하다

 많은 사람들은 Full Scan하면 테이블에 대한 Full Scan만 생각하게 된다. 테이블에 대한 Full Scan이 보편적이기는 하지만 인덱스 또한 Full Scan이 발생할 수 있다는 것을 잊지 않길 바란다. 그렇다면 인덱스에 대한 Full Scan은 한 가지만 존재하는가? 인덱스에 대한 Full Scan에는 여러 가지 종류가 존재한다. 우선 인덱스에서 발생할 수 있는 모든 실행 계획을 확인해 보자. 

- Index Range Scan - Index Unique Scan - Index Full Scan - Index Fast Full Scan - Index Skip Scan - Index Min/Max Scan - Index Join

 인덱스를 엑세스하는 방법에는 위와 같은 형태가 존재한다. 이와 같이 우리가 생각지 못한 여러 방법으로 인덱스를 엑세스할 수 있다. 위의 인덱스 관련 실행 계획 중 인덱스 Full Scan에 대한 실행 계획은 어떤 실행 계획일까? 위의 인덱스 실행 계획 중 인덱스 Full Scan에 대한 실행 계획은 아래와 같이 두 가지이다.

 위의 두 가지 실행 계획 중 하나의 실행 계획이 생성된다면 이는 인덱스를 Full Scan한 실행 계획이 된다. 인덱스 Full Scan은 일반적으로 성능 저하를 발생시키므로 주의해야 될 실행 계획임을 명심하길 바란다.

 인덱스도 Full Scan의 수행을 이해하자

 인덱스 Full Scan은 단어 그대로 인덱스를 처음부터 끝까지 엑세스하는 실행 계획이다. 그렇기 때문에 일반적으로 성능 저하가 발생할 수 있다. 다음 예제를 확인해 보자.  

SQL> SELECT COL1, COL2 FROM TAB1 WHERE COL3 = 'AAA' ORDER BY COL4

 위의 SQL에서 TAB1 테이블에는 COL4+COL3 인덱스가 있다고 가정하자. 그렇다면 해당 SQL은 과연 COL4+COL3 인덱스를 이용할 수 있는가? 기본적으로는 대부분의 사람들은 해당 인덱스를 이용할 수 없다고 이야기할 것이다. 하지만 해당 SQL은 COL4+COL3 인덱스를 이용할 수 있다. 해당 인덱스를 이용한다면 어떻게 실행이 되겠는가? COL4 컬럼은 WHERE 절의 조건이 아니므로 처리 범위를 감소시킬 수 없게 된다. 하지만 해당 인덱스는 COL4 컬럼이 인덱스의 앞에 존재하므로 인덱스를 이용하여 처리 범위를 감소시킬 수는 없게 된다. 그렇기 때문에 해당 SQL은 COL4+COL3 인덱스를 처음부터 끝까지 엑세스하는 인덱스 Full Scan의 실행 계획이 생성되게 된다. 

SELECT STATEMENT SORT(ORDER BY) TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' INDEX (FULL SCAN) OF 'COL4_COL3_IDX'

위와 같이 실행 계획이 생성되면 해당 SQL은 COL4+COL3 인덱스를 Full Scan하게 되므로 인덱스의 처음부터 끝까지 엑세스하게 된다. 이와 같이 수행되는 경우 일반적으로 성능 저하가 발생하게 될 것이다. 이는 처리 범위를 감소시키지 못하고 인덱스를 처음부터 끝까지 엑세스하기 때문이다. 그렇다면 인덱스 Full Scan은 단점만을 가지는 것일까? 세상사가 다 그렇듯이 단점이 있으면 장점이 있고 장점이 있으면 단점이 존재할 것이다. 인덱스 Full Scan도 마찬가지이다.

 인덱스도 Full Scan의 장점과 단점을 분석하자

 인덱스 Full Scan은 앞서 언급한 것과 같이 인덱스를 처음부터 끝까지 엑세스해야 한다는 단점이 존재한다. 앞의 예제에서 COL3 조건을 만족하는 데이터는 100건이며 TAB1 테이블의 전체 데이터는 100,000건이라면 100,000건의 데이터를 인덱스에서 모두 엑세스하여 99,900건은 버리고 100건만을 취하여 결과로 추출하게 되므로 비효율이 발생하게 될 것이다. 이와 같다면 인덱스 Full Scan이 성능 저하를 발생시키는 것은 자명한 일이다.  

- 처리 범위 증가로 인한 성능 저하 발생 가능

이처럼 인덱스 Full Scan은 성능 저하를 발생시킬 수 있는 구조임에는 틀림없다. 하지만 TAB1 테이블이 100,000건이며 조건을 만족하는 데이터가 90,000건이라고 가정하자. 또한, 인덱스는 COL4+COL3+COL2+COL1으로 구성되어 있다면 해당 인덱스를 이용하는 순간 인덱스 Full Scan이 발생하는 것은 동일하다. 하지만 인덱스를 Full Scan하므로 인덱스에서 추출되는 데이터의 순서로 데이터가 추출될 것이다. 인덱스는 현재 COL4 컬럼으로 정렬되어 있으므로 해당 인덱스를 이용하는 순간 자동으로 COL4 컬럼으로 정렬된 데이터가 결과로 추출된다. 이와 같다면 해당 SQL은 정렬을 발생시키지 않게 된다. 또한 인덱스에 필요한 모든 컬럼이 존재하므로 테이블을 별도로 엑세스할 필요가 없으며 엑세스한 데이터의 대부분이 결과로 추출되므로 인덱스를 처음부터 끝까지 엑세스한 데이터에 대한 낭비가 거의 없게 된다. 이와 같다면 인덱스 Full Scan이 정렬도 없애며 테이블보다 크기가 작은 인덱스를 엑세스하게 되어 성능을 향상시킬 수 있다. 이와 같이 실행된다면 실행 계획은 다음과 같이 생성될 것이다. 

SELECT STATEMENT TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' INDEX (FULL SCAN) OF 'COL4_COL3_COL2_COL1_IDX'

 인덱스 Full Scan은 해당 테이블의 데이터를 대부분 추출하는 경우 다음과 같은 장점을 가지게 된다.  

- 테이블보다 작은 인덱스를 전부 엑세스하므로 I/O 양을 감소시킴 - 인덱스의 첫 번째 컬럼으로 정렬된 데이터를 자동으로 추출

위와 같이 인덱스 Full Scan은 인덱스 자동 정렬과 테이블을 엑세스하는 현상만 없다면 더 작은 크기의 인덱스를 엑세스하므로 디스크 I/O에서 장점을 가지게 된다. 물론 해당 테이블의 데이터 중 대부분의 데이터를 정렬하여 추출하는 경우에 해당할 것이다.  

이처럼 인덱스 Full Scan은 단점이 크지만 잘 사용한다면 자동 정렬 등의 장점을 효과적으로 이용할 수 있을 것이다. 이러한 점에 유의하여 인덱스 Full Scan 실행 계획이 생성된다면 많은 분석을 통해 최적화를 해야 하며 인덱스 Full Scan의 장점을 이용하여 SQL을 최적화할 수도 있다. 다음 호에서는 인덱스 Full Scan 중 하나인 Index Fast Full Scan에 대해 함께 확인해 보자.

 필자소개
권순용 kwontra@hanmail.net｜Data Consulting 업무를 수행하는 ㈜엑시엄의 대표이사이며 DBA로 시작하여 SQL 튜닝, 데이터베이스 아키텍처 및 모델링 업무를 주로 수행했다. 데이터베이스 교육에도 많은 관심을 가지고 있으며 저서로는 『Perfect! 오라클 실전 튜닝, 『초보자를 위한 오라클 10g』 및 『INSIDE SQL』이 있다. 또한, 데이터 액세스 최적화에 대한 특허를 출원했다.

 출처 : 한국 마이크로 소프트웨어 [2009년 5월호]
제공 : DB포탈사이트 DBguide.net

일반적으로 object의 size를 검색할 때,

예를 들어 scott 유저의 emp 테이블이라면,

select sum(bytes) from dba_segments

where owner = 'SCOTT' and segment_name = 'EMP';

를 사용하나... 이것은 실제로 정확한 계산은 아닙니다.

조금 더 정확한 계산은...

## TABLE SIZE 계산 공식(ORACLE BLOCK SIZE : 2K 로 가정) ##
------------------------------------------------------
$ sqlplus scott/tiger
SQL> SELECT GREATEST(4, ceil(ROW_COUNT /
((round(((1958 - (initrans * 23)) *
((100 - PCTFREE) /100)) / ADJ_ROW_SIZE)))) * BLOCK_SIZE)
TableSize_Kbytes
FROM dual;

*. 한 개의 BLOCK에 Available 한 Bytes - 1958
*. 각 initrans 는 23 Bytes
*. PCT_FREE : Table 의 pctfree 값(default 10)
*. ADJ_ROW_SIZE : 각 row 의 평균 SIZE 추정치
*. ROW_COUNT : table 의 row 의 갯수
*. BLOCK_SIZE : 1 block의 크기 (단위: K)

예) table 이름이 EMP 일 경우

ROW_COUNT : select count(*) from emp;

ADJ_ROW_SIZE :
analyze table emp compute statistics;
(또는 건수가 매우 많을 때에는 compute 대신 estimate 사용)
select avg_row_len
from user_tables
where table_name='EMP';

를 참고하시고...

더 정확한 계산을 원하신다믄...
##

TABLE(NON-CLUSTERD TABLE)의 초기 사용량을 추정하는 방법을 살펴보자.
여기서는 ORACLE7 SERVER를 기준으로 하여 설명 하기 하며, TABLE의 초기 사용량을
계산하는 단계를 다음과 같이 나눌 수 있다.

STEP 1 - 총 블럭 헤드 크기(BLOCK HEADER SIZE)를 계산
STEP 2 - 데이타 블럭 당 사용 가능한 데이타 영역을 계산
STEP 3 - 평균 ROW의 전체 컬럼의 길이(COMBINED COLUMN LENGTH)를 계산
STEP 4 - 총 평균 ROW 크기를 계산
STEP 5 - 데이타 블럭 내의 평균 ROW 수를 계산
STEP 6 - 테이블에서 요구되는 블럭과 바이트 수를 계산

1. [ STEP 1 ] : 총 블럭 헤드 크기(TOTAL BLOCK HEADER SIZE)를 계산

1) total block header

total block header = block header, part A +
block header, part B

block header, part A = (fixed header + variable transaction header)
block header, part B = (table directory + row directory)

** fixed header : 57 bytes (고정된 블럭 헤드)
variable transaction : 23 * I
header ( I는 해당 TABLE의 INITRANS의 값)

table directory : 4
row directory : 2 * R
( R은 블럭의 ROW 수, STEP 5에서 계산)

2) 예를 들어, INITRANS = 1일때 경우의 total block header
total block header = (57 + (23*1)) + (4 + (2*R))
= 80 + (4 + (2*R)) bytes


2. [ STEP 2 ] : 데이타 블럭 당 사용 가능한 데이타 영역
(DATA SPACE PER DATA BLOCK)을 계산

1) available data space

available data space = (block size - total block header) -
((block size - block header, part A) *
(PCTFREE/100))

** block size : 데이타베이스의 블럭 크기
(SQL*DBA의 SHOW PARAMETER에서
db_block_size를 확인)

2) 예를 들어, db_block_size=2K, PCTFREE=10 일 경우

available data space = (2048 - (80 + (4 + 2R)) -
((2048 - 80) * (10 / 100))
= (1964 - 2R) - (1968 * 0.1)
= (1964 - 2R - 196)
= (1768 - 2R) bytes


3. [ STEP 3 ] : 평균 ROW의 전체 컬럼의 길이
(COMBINED COLUMN LENGTH)를 계산

ROW 길이를 계산하기 위하여 TABLE 정의에서 컬럼 수, 각 컬럼의 데이타
타입, 가변 길이 컬럼의 평균 크기등을 참조한다.

1) D (data space/average row) : table T 가 다음의 스키마로 정의되어
있을 경우의 ROW의 평균 길이

- create table T ( A char(10), B date, C number(10,2))


D (data space/average row) = ( A + B + C )


** A(컬럼 A의 길이) : 10 bytes -----> CHAR 타입인 경우 fixed length
B( B의 길이) : 7 bytes -----> DATE 타입인 경우
C( C의 길이) : 5 bytes = (P / 2) + 1
-----> NUMBER 타입인 경우에는
PRECISION을 고려해서 계산됨
(단, PRECISION에서 NUMBER의 길
이를 나타내므로 SCALE은 무관함)

4. [ STEP 4 ] : 총 평균 ROW 크기를 계산(TOTAL AVERAGE ROW SIZE)

여기서는 테이블의 ROW에 의해 요구되는 최소 사용 영역을 계산하는 공식을
제공한다.

1) bytes/row

bytes/row = row header + F + V + D


** row header : ROW 당 3 bytes ( NON-CLUSTERED TABLE)
F : 250 bytes 이하를 사용하는 컬럼의 TOTAL LENGTH BYTES
( 각 컬럼 당 1 bytes )
V : 250 bytes 이상을 사용하는 컬럼의 TOTAL LENGTH BYTES
( 각 컬럼 당 3 bytes )
D : [STEP 3]의 D (data space/average row)

2) 예를 들어, [STEP 3]의 테이블 T 의 TOATL AVERAGE ROW SIZE

- 250 byte 이하의 컬럼 수 = 3
250 byte 이상의 컬럼 수 = 0
D (data space/average row) = 22 인 경우
- avg. row size, table T = ( 3 + (1 * 3) + (3 * 0) + 22)
= 28 bytes

5. [ STEP 5 ] : 데이타 블럭내의 평균 ROW 수(AVERAGE ROWS PER BLOCK)를 계산


1) R (avg. # of rows/block)

R (avg. # of rows/block) = available space / average row size


** available space : [STEP 2]의 available data space
average row size : [STEP 4]의 bytes/row

2) 예를 들어, [STEP 2]의 available space가 (1768 - 2R) bytes이고,
[STEP 4]의 테이블 T의 bytes/row가 28bytes일 경우

- R = (1768 - 2R)bytes / 28bytes
28R = 1768 - 2R
30R = 1768
R = 58 rows/block

6. [ STEP 6 ] : 테이블에서 요구되는 블럭과 바이트 수를 계산
(NUMBER OF BLOCKS AND BYTES)

1) # blocks for table과 # bytes for table


# blocks for table = # rows / R


** # rows : TABLE의 ROW 수
R : [STEP 5]의 R rows/block


# bytes for table = # blocks for table * 2048 bytes/block


** 2048 bytes/block : 데이타베이스의 블럭 크기 (db_block_size)


2) 예를 들어, 테이블 T가 10000개의 rows을 가질 경우의 테이블 당 블럭 수

- # blocks for table T = 10000 rows / 58 rows/block
= 173 blocks

- # bytes for table T = 173 blocks * 2048 bytes/block
= 354304 bytes
= 346 Kbytes

#####

를 참고하시기 바랍니다....

[출처] : www.coolx.net

1. 서브쿼리를 이용한 테이블 생성
CREATE TABLE table [column[, column,….]
AS subquery;

ex)CREATE TABLE  addr_second(id, name, addr, phone, e-mail) AS SELECT * FROM address;
CREATE TABLE addr_third AS SELECT id, num FROM address;

2. 테이블 구조만 복사(내용은 복사 안 함)
CREATE TABLE table
AS SELECT * FROM  source_table WHERE condition;

ex) CREATE TABLE addr_forth AS SELECT id, num FROM address WHERE 1=2;

3. 테이블 구조 변경[추가]
ALTER TABLE table
ADD (column datatype [DEFAULT expression], [column datatype]….);

ex)ALTER TABLE address ADD (birth date);
ALTER TABLE address ADD (comments varchar2(200) DEFAULT ‘No Comment’);

4. 테이블 구조 변경[칼럼 삭제]
ALTER TABLE table DROP COLUMN column;

ex) ALTER TABLE address DROP COLUMN comments;

5. 테이블 이름 변경
RENAME old_table TO new_table;

ex)RENAME addr_second TO  client_address;

6. 테이블 삭제
DROP TABLE [schema.]table [cascade constraints];

ex)DROP TABLE addr_third;

7. TRUNCATE 명령문
TRUNCATE TABLE [schema.] table
테이블 구조는 그대로 유지하고 데이터와 할당된 공간을 삭제하는 명령문이다.