h3.컬럼에 불필요한 ORACLE 제공함수를 사용하지 말자
부적절하게 FUNCTION을 컬럼에 사용하는 경우
CUSTNO 데이터 타입이 CHAR일 때
변경 전 : WHERE SUBSTR(custno, 1 , 7) = '1234567'
변경 후 : WHERE custno LIKE '1234567' || '%'
INSERT_DATE 데이터 타입이 DATE일 때
변경 전 : WHERE TO CHAR(insert date, 'yyyymmdd' ) = '20110125'
변경 후 : WHERE insert date >= TO DATE( '20110125' , 'yyyymmdd' )
AND insert date < TO DATE ( '20110125' , 'yyyymmdd' ) + 1
CUSTNO, INSERT_DATE 컬럼은 각각 인덱스가 이미 존재한다.
따라서 변경 전 조건을 변경 후 처럼 바꿔주면, 인덱스 사용이 가능해진다.
하지만 Where 절 조건 (변경 전)을 변경하지 못하는 환경 (프로그램 변경이 불가)
이라면 차선책으로 Function Based Index 를 생성하여 성능을 개선할 수 있다.
CREATE INDEX idx_t1_01 ON t1 ( SUBSTR(custno, 1, 7) ) ;
CREATE INDEX idx_t1_01 ON t1 ( TO_CHAR(insert_date, 'yyyymmdd') );
Function Based Index 의 구성정보는 DBA_IND_EXPRESSIONS 에서 확인할 수 있다.
h3.컬럼 변형으로 인해 인돼스의 MIN/MAX를 활용하지 못하는 경우
인텍스 스캔이 INDEX RANGE SCAN (MIN/MAX)로 수행되어, 적은 리소스를 사용하여 MIN/MAX 데이터를 추출
INDEX RANGE SCAN (MIN/MAX)는 인텍스의 정렬된 데이터 첫 번째와 마지막 값을 이용하여 MIN 과 MAX 값을 추출
MIN, MAX 함수를 수행하는 추줄 컬럼에 변형이 가해지면 INDEX RANGE SCAN(MIN/MAX)로 수행되지 못하므로
성능 문제가 발생할 수 있다. 그러므로 MIN, MAX 값을 추출하는 컬럼에 컬럼 변형이 가해지지 않도록 반드시 주의
DROP TABLE T1 PURGE;
■ 생성 요건
- T1테이블의 전체 건수는 총 100,000 ROWS
- CUST_ID 값은 UNIQUE 함.
- CUSTNAME 값의 종류는 5가지임.
■ 테이블 생성
CREATE TABLE T1 ( CUSTID NUMBER,
CUSTNAME VARCHAR2(50) ) ;
■ 데이터 생성
BEGIN
FOR I IN 1 .. 100000
LOOP
IF (MOD(I,5)=0)
THEN INSERT INTO T1 VALUES (I, 'A') ;
END IF;
IF (MOD(I,5)=1)
THEN INSERT INTO T1 VALUES (I, 'B') ;
END IF;
IF (MOD(I,5)=2)
THEN INSERT INTO T1 VALUES (I, 'C') ;
END IF;
IF (MOD(I,5)=3)
THEN INSERT INTO T1 VALUES (I, 'D') ;
END IF;
IF (MOD(I,5)=4)
THEN INSERT INTO T1 VALUES (I, 'E') ;
END IF;
END LOOP;
COMMIT ;
END;
/
■ 각 칼럼에 인덱스 생성 및 통계정보 수집
CREATE INDEX IDX01_T1_CUSTID ON T1(CUSTNAME , CUSTID);
BEGIN
dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',
TABNAME=>'T1',
ESTIMATE_PERCENT=>99,
METHOD_OPT=>'FOR ALL INDEXED COLUMNS',
GRANULARITY=>'ALL',
CASCADE=>TRUE,
NO_INVALIDATE=>FALSE) ;
END;
/
h5.컬럼 변형에 의한 비효율적인 MAX() 추출 (TO CHAR() 로 변경 후 MAX 값을 주출)
SQL> set autot on
SQL> SELECT MAX(TO_CHAR(custid)) AS custid
2 FROM T1
3 WHERE custname= 'a';
CUSTID
----------------------------------------
Execution Plan
----------------------------------------------------------
Plan hash value: 3773181093
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX RANGE SCAN| IDX01_T1_CUSTID | 1 | 6 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUSTNAME"='a')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
420 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
h5.컬럼 변형 없이 인덱스툴 이용한 효율적인 MAX() 추출 (MAX 값을 추출한 후 TO_CHAR() 로 변경)
SQL> SELECT TO_CHAR(MAX(custid)) AS custid
2 FROM T1
3 WHERE custname= 'a';
CUSTID
----------------------------------------
Execution Plan
----------------------------------------------------------
Plan hash value: 3855659005
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
| 2 | FIRST ROW | | 1 | 6 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| IDX01_T1_CUSTID | 1 | 6 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CUSTNAME"='a')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
420 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
h3.파티션키컬럼변형은모든파티션을 읽는다
파티션 테이블을 조회할 때 겪는 성능 문제 중에서， 파티선 키 컬럼의 데이터 타입과 조회
변수의 데이터 타입이 달라서 발생하는 경우가 있다.
날짜 (월) 컬럼을 기준으로 월별 파티션을 생성한 테이블이 있다고 가정하자.
데이터 특성상 보관주기가 길다면， 시간이 지날수록 파티션의 개수는 점점 많아 질 것이다.
많은 파티션들 중에서 특정 월 데이터에 대해 조회를 했지만 의도와는 다르게 전체 파티션
을 조회한다면 Where 절 조건 중 파티선 키 컬럼의 형 변환이 발생하였는지 의심해야 한다.
DROP TABLE PART_T1 PURGE;
■ 생성 요건
- REGDATE 테이블에 대해 LIST PARTTITION 테이블 생성
■ 파티션 테이블 생성
CREATE TABLE PART_T1 ( ID NUMBER NOT NULL,
MEM VARCHAR2(2) NOT NULL,
REGDATE VARCHAR2(8) NOT NULL )
PARTITION BY RANGE (REGDATE)
(
PARTITION PART_T1_201001 VALUES LESS THAN ('20100201'),
PARTITION PART_T1_201002 VALUES LESS THAN ('20100301'),
PARTITION PART_T1_201003 VALUES LESS THAN ('20100401'),
PARTITION PART_T1_201004 VALUES LESS THAN ('20100501'),
PARTITION PART_T1_201005 VALUES LESS THAN ('20100601'),
PARTITION PART_T1_201006 VALUES LESS THAN ('20100701'),
PARTITION PART_T1_201007 VALUES LESS THAN ('20100801'),
PARTITION PART_T1_201008 VALUES LESS THAN ('20100901'),
PARTITION PART_T1_201009 VALUES LESS THAN ('20101001'),
PARTITION PART_T1_201010 VALUES LESS THAN ('20101101'),
PARTITION PART_T1_201011 VALUES LESS THAN ('20101201'),
PARTITION PART_T1_201012 VALUES LESS THAN ('20110101')
) ;
■ 데이터 입력
INSERT INTO PART_T1 VALUES ( 1,'A1','20100101') ;
INSERT INTO PART_T1 VALUES ( 2,'B1','20100201') ;
INSERT INTO PART_T1 VALUES ( 3,'C1','20100301') ;
INSERT INTO PART_T1 VALUES ( 4,'D1','20100401') ;
INSERT INTO PART_T1 VALUES ( 5,'E1','20100501') ;
INSERT INTO PART_T1 VALUES ( 6,'A1','20100601') ;
INSERT INTO PART_T1 VALUES ( 7,'B1','20100701') ;
INSERT INTO PART_T1 VALUES ( 8,'C1','20100801') ;
INSERT INTO PART_T1 VALUES ( 9,'D1','20100901') ;
INSERT INTO PART_T1 VALUES ( 10,'E1','20101001') ;
INSERT INTO PART_T1 VALUES ( 11,'D1','20101101') ;
INSERT INTO PART_T1 VALUES ( 12,'E1','20101201') ;
INSERT INTO PART_T1 VALUES (101,'A1','20100102') ;
INSERT INTO PART_T1 VALUES (102,'B1','20100202') ;
INSERT INTO PART_T1 VALUES (103,'C1','20100302') ;
INSERT INTO PART_T1 VALUES (104,'D1','20100402') ;
INSERT INTO PART_T1 VALUES (105,'E1','20100502') ;
INSERT INTO PART_T1 VALUES (106,'A1','20100602') ;
INSERT INTO PART_T1 VALUES (107,'B1','20100702') ;
INSERT INTO PART_T1 VALUES (108,'C1','20100802') ;
INSERT INTO PART_T1 VALUES (109,'D1','20100902') ;
INSERT INTO PART_T1 VALUES (110,'E1','20101002') ;
INSERT INTO PART_T1 VALUES (111,'D1','20101102') ;
INSERT INTO PART_T1 VALUES (112,'E1','20101202') ;
COMMIT ;
■ 각 칼럼에 인덱스 생성 및 통계정보 수집
CREATE INDEX IDX_PART_T1_01 ON PART_T1(REGDATE) LOCAL ;
SQL> SELECT /*+ FULL(PART Tl) */ *
2 FROM PART_T1
3 where regdate BETWEEN to_date( '20101101' , 'yyyymmdd' )
4 and to_date('20101131','yyyymmdd');
and to_date('20101131','yyyymmdd')
*
4행에 오류:
ORA-01839: 지정된 월에 대한 날짜가 부적합합니다
---> 컬럼 데이터 타입과 조건 데이터 타입 불일치
SQL> SELECT /*+ FULL(PART Tl) */ *
2 FROM PART_T1
3 where regdate BETWEEN '20101101' and '20101131';
---> 컬럼과 같은 데이터 타입으로 조회
ID ME REGDATE
---------- -- --------
11 D1 20101101
111 D1 20101102
Execution Plan
----------------------------------------------------------
Plan hash value: 2410793023
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 44 | 3 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 2 | 44 | 3 (0)| 00:00:01 | 11 | 11 |
|* 2 | TABLE ACCESS FULL | PART_T1 | 2 | 44 | 3 (0)| 00:00:01 | 11 | 11 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("REGDATE"<='20101131')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
72 recursive calls
0 db block gets
35 consistent gets
0 physical reads
0 redo size
589 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
*테이블의 컬럼들을 가공한 값으로 인덱스로 생성한 것.
*인덱스가 생성될 열에 산술공식 또는 함수를 적용하여 인덱스를 생성하는 방식.
*사용자가 실행하는 SQL 문의 WHERE 절에 산술 표현 또는 함수를 자주 사용할 때 빠르게 검색.
*수식이나 함수를 포함하는 컬럼의 경우 인덱스를 생성할 때 수식과 함수를 포함하여 인덱스를 생성하여 이러한 문제를 해결.
==> 조회시 빠른 검색이 가능하나 DML시에는 성능저하 우려
SQL> create index idx_emp_salyear on scott.emp(sal*12);
인덱스가 생성되었습니다.
set pagesize 200
set linesize 200
col COLUMN_NAME format a20
col INDEX_NAME format a15
col table_name format a15
col TABLE_OWNER format a15
SELECT index_name,index_type,table_name,table_owner,uniqueness
FROM DBA_INDEXES
WHERE table_name='EMP';
INDEX_NAME INDEX_TYPE TABLE_NAME TABLE_OWNER UNIQUENES
--------------- --------------------------- --------------- --------------- ---------
PK_EMP NORMAL EMP SCOTT UNIQUE
IDX_EMP_SALYEAR FUNCTION-BASED NORMAL EMP SCOTT NONUNIQUE
select index_name,column_name
from dba_ind_columns
where table_name='EMP';
INDEX_NAME COLUMN_NAME
------------------------------ --------------------
IDX_EMP_SALYEAR SYS_NC00009$
* 함수기반 인덱스의 활용
테이블 설계상의 문제를 해결
오류데이터의 검색문제를 해결
가공처리 결과의 검색
오브젝트 타입의 인덱스검색
배타적 관계의 인덱스 검색
사용가능한 형식은 테이블의 열, 상수, sql함수 및 사용자가 정의한 함수이다.
(집합함수는 안됨.==>인덱스의 속성상 특정 로우를 가리키는 포인터의 역활에 주목)
SQL> CREATE INDEX sum_idx ON scott.emp(sum(sal));
CREATE INDEX sum_idx ON scott.emp(sum(sal))
*
1행에 오류:
ORA-00934: 그룹 함수는 허가되지 않습니다
이화식 - 새로쓴 대용량 데이터베이스 솔루션 참고
http://wiki.gurubee.net/pages/viewpage.action?pageId=1507454
select * from hr.employees;
select index_name,column_name
from dba_ind_columns
where table_name='EMPLOYEES';
SELECT index_name,index_type,table_name,table_owner,uniqueness
FROM DBA_INDEXES
WHERE table_name='EMPLOYEES';
drop index 이름인덱스;
CREATE INDEX quot_idx ON hr.employees('LAST_NAME');
select index_name,column_name
from dba_ind_columns
where table_name='EMPLOYEES';
SELECT index_name,index_type,table_name,table_owner,uniqueness
FROM DBA_INDEXES
WHERE table_name='EMPLOYEES';
set autot on
SELECT LAST_NAME FROM hr.employees WHERE last_name='Marlow';
SELECT LAST_NAME FROM hr.employees WHERE LAST_NAME='Marlow';
SELECT LAST_NAME FROM hr.employees WHERE 'LAST_NAME'='Marlow';
set autot off
응????????
CREATE INDEX noquot_idx ON hr.employees(last_name);
select index_name,column_name
from dba_ind_columns
where table_name='EMPLOYEES';
SELECT index_name,index_type,table_name,table_owner,uniqueness
FROM DBA_INDEXES
WHERE table_name='EMPLOYEES';
set autot on
SELECT LAST_NAME FROM hr.employees WHERE LAST_NAME='Marlow';
SELECT LAST_NAME FROM hr.employees WHERE last_name='Marlow';
SELECT LAST_NAME FROM hr.employees WHERE 'LAST_NAME'='Marlow';
set autot off
| 인덱스 컬럼 가공 사례 | 튜닝 방안 |
| substr(업체명, 1, 2) = '대한' | 업체명 like '대한%' |
| 월급여 * 12 = 36000000 | 월급여 = 36000000 / 12 |
| to_char(일시, 'yyyymmdd') = :dt | 일시 >= to_date(:dt, 'yyyymmdd') and 일시 < to_date(:dt, 'yyyymmdd') + 1 |
| 연령 || 직업 = '30공무원' | 연령 = 30 and 직업 = '공무원' |
| 회원번호 || 지점번호 = :str | 회원번호 = substr(:str, 1, 2) and 지점번호 = substr(:str, 3, 4) |
| nvl(주문수량, 0) >= 100 | 주문수량 >= 100 |
| nvl(주문수량, 0) < 100 | not null 컬럼이면 nvl제거, 아니면 함수기반 인덱스(FBI) 생성 고려 => create index 주문_x01 on 주문(nvl(주문수량, 0) ); |