SQL*Loader는 외부 화일의 데이타를 ORACLE 데이타베이스의 table에 넣기
위한 유틸리티입니다. SQL*Loader를 사용하려면 외부 데이타 화일과
콘트롤 화일이 필요합니다. 콘트롤 화일이라고 하는 것은 로드하는 데이타의
정보를 저장한 화일입니다. 간단한 샘플 콘트롤 화일을 설명하겠습니다.
load data 제어 화일의 선두에는 반드시 이 말이 필요합니다.
infile sample.dat 외부 화일을 지정합니다.
replace 테이블에 데이타 넣는 방법 지정
into table TABLE_NAME 데이타를 로드하는 테이블을 지정
fields terminated by ',' 데이타 필드의 종결 문자 지정
(a integer external, 테이블의 열, 외부 데이타 화일의 데이타 형을 지정
b char)
참고로 replace 외에 다음의 옵션이 가능합니다.
replace 테이블의 기존 행을 모두 삭제(delete)하고 insert
append 새로운 행을 기존의 데이타에 추가
insert 비어 있는 테이블에 넣을 때
truncate 테이블의 기존 데이타를 모두 truncate 하고 insert
SQL*Loader를 실행하면 아래의 화일이 작성됩니다.
* 로드 작업 중 동작에 관한 통계 등을 포함한 로그 화일(확장자는 log)
* 데이타 에러 때문에 로드가 안된 레코드를 저장한 화일(확장자는 bad)
* 사용자의 선택 기준에 적합하지 않은 레코드를 저장한 화일(discard 화일)
이것은 discardfile 옵션으로 별도로 지정해야 생성됩니다.
ESC: 열린 윈도우 닫기
F1: 모든 단축키를 보여준다.
F2: FULL SCREEN
F4: Table, View, Proc, Funct, Package를 DESC(테이블명에 커서를 둔 상태에서 해야함)
F6: SQL Editor와 결과창간의 전환
F7: 화면을 모두 CLEAR
F8: 실행했던 SQL들의 HISTORY
F9: 모든 쿼리 실행(Ctrl+Enter는 현재 쿼리만 실행)
Ctrl+F9: 실제 쿼리를 실행하지 않고 validate함
Ctrl+. : table Completion (매칭되는 테이블목록 출력)
Ctrl+T : Columns Dropdown (해당테이블의 컬럼목록 표시)
Alt+Up : History Up (리눅스 bash의 UP키와 같다.)
Alt+Down: History Down (리눅스 bash의 DOWN키와 같다.)
Ctrl+Tab: MDI Child윈도간 전환
그냥 'desc 테이블명' 을 치면, 팝업윈도로 해당 테이블의 컬럼목록윈도가 표시된다.
이때, 해당 컬럼을 double-click하면 SQL Editor로 반영된다.
"테이블명."까지 입력하고 좀 있으면, 해당 테이블의 컬럼목록이 표시된다.
마치 프로그래밍툴에서 클래스내 멤버들의 목록을 표시하듯이..
Ctrl+T는 출력된 목록을 취소했을 경우, 다시 불러낼때 쓰인다.
test, member 2개의 테이블이 있다고 하자.
select * from t까지 입력하고 Ctrl+.를 치면 select * from test로 테이블명을 완성시켜준다.
system계정에서는 안되더군.. 일반계정만 가능했다.
SQL문을 실행시
Ctrl+Enter
현재 커서의 SQL문 실행
여러개의 SQL문이 있을때, SQL문들을 개행문자로 분리해주면 된다.
해당 커서에서 개행문자가 있는 곳까지만 하나의 SQL문으로 간주한다.
F9
SQL Editor의 SQL문 실행하여 Grid에 출력.
여러 SQL문들을 개행문자로 구분하지 않는다는 것을 제외하면, 위의 Ctrl+Enter와 동일하다.
에디터내의 모든 SQL문은 하나로 취급하므로, 여러 개의 SQL문이 있으면 실행할 SQL문만 Selection하여 실행(F9)해준다.
F5
SQL Editor내의 모든 SQL문 실행. 여러개의 SQL문들을 모두 실행하여 SQL*PLUS와 같이 출력해준다.
이것은 출력이 Grid로 표시되지 않고 Editor윈도에 표시되므로, SQL*PLUS의 출력과 같이,
다른 편집기로의 복사/붙여넣기 할때 사용하면 유용하다.
Extraction
(추출)
=============================================
• 다수의 소스시스템으로 부터 데이터를
추출하는 기능
---------------------------------------------
• 계정계시스템으로부터 ODS DB로의 데이터 추출은 실시간/배치로 수행
• 현행 계정계 중계로그 디퍼드시스템이 아닌 CDC 도구에 의한
레코드 이미지 방식으로 ODS에 1차 적재후 확장기반DB로 적재
• 대량 데이터 처리는 일마감 후 배치로 적재
• 초기적재는 최초1회 실시하며, 이후 변경적재 수행
Transformation
(변환)
=============================================
• DW내 표준화 규칙에 의거 데이터 변환
• 소스시스템 데이터를 DW 시스템 모델 형태
로 변경
---------------------------------------------
• 데이터 필드의 형식 변화
• 규칙에 따른 형태 변경
• 업무 코드의 변형
Cleansing
(정제)
=============================================
• 오류데이터를 Check하여 정제Rule 에 따른
데이터 정제 수행
---------------------------------------------
• 필수적인 데이터가 존재하지 않는 경우
• 데이터의 Type(문자형, 숫자형)에 위배되는 값이 들어있는 경우
• 날짜의 경우 날짜로서 유효한 형태를 갖고 있지 않은 경우
• 금액의 경우 유효한 값의 범위를 벗어나는 경우
• 업무 코드의 값이 정해진 범위를 벗어나는 경우
• 데이터가 복수로 존재하며 서로 상충되는 경우
Loading
(적재)
=============================================
• 소스시스템으로 부터 추출/변환/정제된
데이터를 타켓 시스템에 적재
• 적재 시간의 최소화
• 데이터 검증 수행
---------------------------------------------
• Load 유틸리티, 병렬 처리 기능 활용
• 일과 중에 On-line을 통한 적재 및 갱신을 수행할 경우에는 병렬
처리 기능을 활용
• 적재 과정에서 오류 발생시 사후 복구처리 체계 확립
• 현행 계정계 및 정보계간 데이터 검증 체계 파악 후 최적의 방법 적용
/*
인덱스
INDEX - 인덱스는 "값 + rowid" 로 구성되어 있다.
- 동일한 값을 같은 경우 ROWID순으로 소트되어 있기 때문에 블럭에서 테이블 엑세스가 이루어 진다.
인덱스 컬럼
+ 검색조건에 빈빈히 이용되는 컬럼 혹은 컬럼의 집합(SQL문의 WHERE절)
+ ORDER BY, GROUP BY, UNION, DISTINCT (TEMP TABLESPACE) 등 정렬이 필요한 구문에 빈번히 이용되는 컬럼 혹은 컬럼의 집합
+ 집계함수의 이용에 사용되는 컬럼 혹은 컬럼의 집합
+ 수정이 잦은 컬럼은 인텍스 지정 억제(오히려 부하를 줄수 있다)
+ 외부키(FOREIG KEY)는 클러스터인텍스 지정
클러스터 테이블
= 클러스터는 하나 이상의 테이블이 물리적으로 같이 저장되어 있는 그룹을 말한다.
관련된 행들이 같이 저장되어 있다면 디스크 엑세스는 시간을 개선시킨다.
= 클러스터 안에 있는 관련된 테이블의 컬럼을 CLUSTER KEY라고 말한다.
인덱스 적용 대상
+ 중,대규모 테이블
+ 대상 데이터의 10% 이내의 데이터 요구인 경우(분포도)
+ 스캔의 범위를 줄이고자 할때
+ 정렬을 필요로 하는 경우
+ 테이블에 접근하지 않고 인덱스만으로 데이터 요구를 해결하고자 하는 경우
인덱스 적용시 고려사항
+ 인덱스를 위한 추가적인 기억공간을 필요로 함(INDEX TABLESPACE)
+ 인덱스와 테이블 I/O수의 합계가 전체 테이블을 스캔하는 I/O수보다 적은 경우에만 성능향상에 기여한다고 볼 수 있음(손익분기점: 10~15%)
+ 여러 컬럼을 결합하여 인덱스를 구성하는 경우 컬럼의 순서 중요
+ 입력, 수정 혹은 인덱스 컬럼의 값을 수정하는 경우 인덱스 데이터도 변경되어야 하므로 그에 따른 오버헤드도 고려할 것
- 테이블당 인덱스의 수는 4개 정도가 적당
- 무분별한 인덱스는 등록, 수정, 삭제작업시 부하
+ 새로 추가된 인덱스는 기존 엑세스 경로에 영향을 미칠 수 있음(RANK 등)
인텍스 대상테이블 선정
+ 테이블의 크기가 5~6블럭 이상
+ 무작위접근(RANDOM ACCESS)이 빈번한 경우
+ 특정범위/순서의 데이터조회가 필요한 경우
+ JOIN의 연결고리가 되는 칼럼
+ 참조 무결성을 지정한 경우 연결고리 되는 컬럼
+ NESTED JOIN이 일어나는 테이블
+ 단순보관용이거나 전체조회용일 경우에는 인덱스를 생성하지 않는다.
+ 여러 개의 인덱스가 있는 경우 동시에 많은 양의 입력/수정/삭제가 이루어지면 부하가 발생하므로 인덱스의 수나 생성시점 또한 중요하다.
분포도와 손익분기점
+ 인덱스를 생성하고자 하는 컬럼의 분포도는 10~15%를 넘지 않아야 한다.
분포도 = 1/컬럼값의 종류 * 100
= 데이터별 평균 로우 수 / 테이블의 총 로우 수 * 100
+ 인덱스의 선택성(SELECTIVITY)
테이블의 전체 레코드중에서 구분값(DISTINCT VALUE)이 차지하는 비율.
선택성 = (구분값의 갯수 / 전체 ROW 갯수) * 100
선택성이 높을 수록 인덱스의 분포도는 양호 - UNIQUE INDEX의 선택성은 1.00
예) 테이블의 레코드수: 1000, 구분값:950 => 선택성: 950/1000 (0.95)
인덱스의 활용[적용기준]
+ 6블럭 이상의 테이블에 적용(6블럭이하는 연결고리만)
+ 컬럼의 분포도가 10~15% 이내인 경우 적용
+ 분포도가 범위이내더라도 절대량이 많은 경우에는 단일 테이블 클러스터링을 검토할 것
+ 분포도가 범위 이상이더라도 부분범위처리를 목적으로 하는 경우에는 적용
+ 인덱스만을 사용하여 요구를 해결하고자 하는 경우는 분포도가 나쁘더라도 적용할 수 있음(손익분기점)
분포도 1/컬럼값의 종류 * 100 = 컬럼값의 평균 로우수/테이블의 총 로우수 * 100
인덱스의 효율적 이용
+ 일반적으로 가장 좋은 하나의 인덱스만 사용하는 것이 여러 개의 인덱스를 사용하는 것보다 유리하다.
-> 인덱스 머지(INDEX MERGE)의 회피
+ 인덱스 테이블과 데이터 테이블은 각기 다른 디스크에 배치
+ 인덱스 테이블에 대한 적절한 기억공간 할당
+ 테이블 접근없이 인덱스만으로 데이터 요구 해결
+ 인덱스 생성 및 삭제 시점의 효율적 운용 검토
+ 데이터 로드나 일시에 많은 데이터를 입력할 경우 인덱스에 대한 부하를 줄이기 위해
인덱스를 삭제,입력 및 로드가 끝난 이후 재생성
+ 인덱스의 영영은 생성목적과 의미를 내포
데이터 검색 방식
1. FULL-TABLE SCAN
데이터 BLOCK만을 읽는다.
다중 BLOCK I/O를 한다(DB_FILE_MULTIBLOCK_READ_COUNT)
PARALLEL QUERY OPTION 기능을 사용할 수 있다.
SELECT 되는 ROW의 수가 많은 경우에 유리하다.
2. INDEX SCAN
INDEX BLOCK 과 데이터 BLOCK을 읽는다.
다중 BLOCK I/O를 할 수 없다.
SELECT 되는 ROW의 수가 적은 경우에 유리하다.(10~15% 이내)
3. FAST-FULL INDEXED SCAN
SELECT 에 사용된 모든 컬럼이 INDEX 내에 포함이 되는 경우
즉 INDEX에만 접근하면 원하는 데이터가 전부 있는 경우에 사용한다.
FULL-TABLE SCAN 을 해야 하는 경우에 FULL-TABLE SCAN에 대한 대체방법.
FULL-TABLE SCAN 보다 가볍다.
FULL-TABLE SCAN의 장점인 다중 BLOCK I/O와 PARELLEL QUERY를 사용한다.
옵티마이저의 실행계획 작성 우선순위[RULE BASED]
1. ROWID에 의한 1 로우 엑세스
2. 클러스터 조인에 의한 1 로우 엑세스
3. UNIQUE KEY 또는 PRIMARY KEY 를 사용하는 HASH CLUSTER KEY에 의한 1 로우 엑세스
4. UNIQUE KEY 또는 PRIMARY KEY 에 의한 1 로우 엑세스
5. 클러스터 조인
6. 해시 클러스터 키
7. 인덱스 클러스터 키
8. 복합 키
9. 단일 컬럼 인덱스
10. 인덱스 컬럼에서의 바운드 범위 조회
11. 인덱스 컬럼에서의 언바운드 범위 조회
12. 소트 병합 조인
13. 인덱스 컬럼의 최대 또는 최소값
14. 인덱스 컬럼에서의 ORDER BY 사용
15. 테이블의 전체 검색
INDEX를 사용하지 않는 경우
+ 좌변을 가공하지 마라.(인덱스컬럼의 연산, 결합 등의 변형)
-> SAL * 12 = 2000 => SAL = 2000/12로
+ NULL은 가급적 사용하지 마라.(NULL 비교)
+ 부정형 비교( <>, NOT )
+ 함수를 사용하는 경우(SUBSTR, NVL 등)
+ LIKE 문에서 '%AB%',''%AB는 사용목함. ''AB% 가능.
+ WHERE 절에 인덱스가 2개 있다면 인덱스의 우선순위에 의해 LIKE 보다는 = 조건이 작동한다.(취사선택)
-- INDEX COLUMN 의 변형
-- SELECT * FROM DEPT WHERE SUBSTR(DNAME, 1, 2) = 'AC'
-- NOT OPERATOR
-- SELECT * FROM EMP WHERE JOB <> 'SALES'
-- NULL, NOT NULL
-- SELECT * FROM EMP WHERE ENAME IS NOT NULL
-- OPTIMIZER 의 취사선택
-- SELECT * FROM EMP WHERE JOB LIKE 'AB%' AND EMPNO = '7891'
INDEX COLUMN의 변형[EXTERNAL]
+ 모든 내용을 읽어서 서브스트링을 돌리지 말고, INDEX가 잡혀있다면 'AC'로 시작하는 것을 찾아서 그것만 테이블 엑세스
SELECT * FROM DEPT WHERE SUBSTR(DNAME, 1, 2) = 'AC'
-> SELECT * FROM DEPT WHERE DNAME LIKE 'AC%'
+ 모두를 연산한 후 비교하지 말고, 한번만 연산후 그 값인 것을 찾는다.
SELECT * FROM EMPTEST WHERE SAL * 12 = 12000000
-> SELECT * FROM EMPTEST WHERE SAL = 12000000 / 12
+ 1차 인덱스 컬럼은 CHAR타입으로 변경한다.
SELECT * FROM EMPTEST WHERE TO_CHAR(HIRE_DATE,'YYYYMMDD') = '19811203'
-> SELECT * FROM EMPTEST WHERE HIRE_DATE = ''19811203
+ 테이블 설계시에 NULL 을 예방하라.
SELECT * FROM EMP WHERE NVL(COMM, 0) < 100
SELECT * FROM EMP WHERE NVL(JOB,'') = 'CLERK'
-> SELECT * FROM EMP WHERE JOB LIKE 'CLERK%'
+ INDEX가 설정되있는 것을 각각 검색
SELECT * FROM EMP WHERE DEPTNO||JOB = '30MANAGER'
-> SELECT * FROM EMP WHERE DEPTNO = 30 AND JOB = 'MANAGER'
의도적인 SUPPRESSING(억제)
: 의도적인 SUPPRESSING은 타입에 관계없이 RTRIM()을 사용하자.
- 인덱스는 분포도 10~15% 이하의 경우 효율적.
- 분포도가 90% 이면 엄청 속도가 느려진다. 따라서 옵티마이저가 인덱스를 타지 않도록 의도적으로 SUPPRESSING한다.
SELECT * FROM EMP WHERE JOB = 'MANAGER'
-> SELECT * FROM RTRIM(JOB) = 'MANAGER'
-> SELECT * FROM EMP WHERE JOB||'' = 'MANAGER'
SELECT * FROM EMP WHERE EMPNO = 8978
-> SELECT * FROM EMP WHERE EMPNO + 0 = 8978 (NUMBER 형일 경우에만)
- 값이 틀려지면 더 작은 ROWID가 있을 구 있으므로 인덱스 머지 방식은 절대로 사용할 수 없다.
둘중 하나의 인덱스를 선정합니다.랭크를 비교.
둘 다 LIKE 이므로 만들어진 시점을 비교하여 최근에 만들어진 인덱스를 사용합니다. 전부다 비기면 나중에
쓰여진 인덱스를 사용. STATUS인덱스를 사용했다면 엄청 느려짐.
SELECT * FROM AAA WHERE CUST LIKE 'DN%' ABD STATUS LIKE '9%'
-> SELECT * FROM AAA WHERE CUST LIKE 'DN%' AND RTRIM(STATUS) LIKE '9%' (일부러 안쪽의 인덱스를 안쓰게 만들었다.)
CREATE TABLE SAMPLET(
CHA CHAR(10),
NUM NUMBER(12,3),
VAR VARCHAR2(20),
DAT DATE
)
INDEX COLUMN 의 변형(INTERNAL)
SELECT * FROM SAMPLET WHERE CHA = 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')
+ 서로 다른 DATA TYPE이 비교시의 INTERNAL CONVERSION(옵티마이저의 인공지능은 사람을 따라올 수 없다)
-- EMAIL 은 현재 VARCHAR2형
SELECT * FROM EMPLOYEES WHERE EMAIL = 100 -- (문자열을 숫자형으로 비교, FULL SCAN)
-> SELECT * FROM EMPLOYEES WHERE EMAIL = '100' -- (TO_CHAR(100) 로 변화하여 검색)
-- DATE형: FULL SCAN -> 옵티마이저가 TO_DATE()로 변환한다.
SELECT * FROM EMPLOYEES WHERE HIRE_DATE = '99/06/21'
-> SELECT * FROM EMPLOYEES WHERE HIRE_DATE = TO_DATE('99/06/21')
NOT OPERATOR
+ 부정을 긍정으로 만들어서 사용한다.
SELECT * FROM EMP WHERE JOB <> 'SALESMAN'
-> SELECT * FROM EMP E WHERE E.EMPNO > 0 AND NOT EXISTS(SELECT * FROM EMP B WHERE B.EMPNO = E.EMPNO AND B.JOB = 'SALESMAN')
SELECT * FROM EMP WHERE EMPNO > 0 MINUS SELECT * FROM EMP B WHERE B.JOB = 'SALESMAN'
NULL, NOT NULL
+ NULL, NOT NULL 비교인 경우 인덱스 사용 안함.
+ NULL 이 정말 필요한지를 고민해봐야 한다. 테이블 설계시 디폴트값 셋팅이 필요한지 검토
+ NULL 이 필요한 경우는 정말 극히 드물다.
SELECT * FROM EMPLOYEES WHERE JOB_ID IS NOT NULL;
-> SELECT * FROM EMPLOYEES WHERE JOB_ID > ' ';
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID IS NOT NULL;
-> SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID >= 0;
SELECT * FROM EMPLOYEES WHERE JOB_ID > ' ' AND EMPLOYEE_ID IS NULL;
OPTIMIZER 의 취사선택
+ 랭킹의 차이
SELECT * FROM EMPLOYEES WHERE LAST_NAME LIKE 'S%' AND JOB_ID = 'SH_CLERK'
(ENAME INDEX만 사용, LIKE 보다 = 이 우선임.)
+ 인덱스머지의 회피
SELECT * FROM EMPLOYEES WHERE LAST_NAME LIKE 'S%' AND JOB_ID LIKE 'S%';
(ENAME or JOB INDEX중 하나만 사용.)
+ 힌트에 의한 선택
SELECT /*+INDEX(EMP_JOB_IDX)* / * FROM EMPLOYEES WHERE LAST_NAME LIKE 'S%' AND JOB_ID LIKE 'S%'
(같은 조건일때 힌트를 사용)
RULE_based vs COST_based - 차이점 예제
+ SqlPlus 에서 SHOW PARAMETER OPT 하면... optimizer_mode string CHOOSE
SQL> conn system
암호 입력: ********
SQL> show parameter opt;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string
object_cache_optimal_size integer 102400
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
plsql_optimize_level integer 2
현재 설정은 CHOOSE.
즉 오라클이 알아서 CBO 나 RBO를 선택하겠다는 ㅤㅆㅡㅅ이다.(Analyze작업이 되면 cost방식으로 해석 - dept테이블로 확인)
+ CBO를 적용하기 위해선 ANALYZE작업이 선행되어야 한다.
+ ANALYZE작업에 의한 통계값이 없다면 오라클은 정확하게 CBO를 수행할 수 없다.
+ 일단 analyze를 하게 되면 옵티마이저는 choose일 경우 cost를 선택하게 된다.
이때 통계치에 의해 기존 rbo로 수행되면 sql문의 실행계획이 달라지게 된다.
-> Cost를 사용하기 위해서느 ㄴ전체 app의 sql문을 수정할 필요가 있다.
+ RULE based
-- INDEX merge(and_equel)
SELECT * FROM EMPLOYEES WHERE JOB_ID = 'MK_REP' AND LAST_NAME = 'Fay';
-- 항상 EMPLOYEES_ID index만 사용
SELECT * FROM EMPLOYEES WHERE LAST_NAME LIKE 'Fa%' AND EMPLOYEE_ID = '202';
-- 항상 나중에 생성된 Index만 사용(job_id)
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID BETWEEN '202' AND '9000' AND JOB_ID LIKE 'MK%';
+ Cost based
-- INDEX merge(and_equel), 특정 index
SELECT * FROM EMPLOYEES WHERE JOB_ID = 'MK_REP' AND LAST_NAME = 'Fay';
-- 분포도에 따라 LAST_NAME index도 사용
SELECT * FROM EMPLOYEES WHERE LAST_NAME LIKE 'Fa%' AND EMPLOYEE_ID = '202';
-- 분포도에 따라 index 사용, 혹은 Full-Scan
SELECT * FROM EMPLOYEES WHERE LAST_NAME LIKE 'Fa%' AND JOB_ID LIKE 'MK%';
ANALYZE
-- 명령은 다음과 같다.
ANALYZE TABLE HR.EMPLOYEES COMPUTE STATISTICS;
+ 옵티마이저가 CBO로 수행되기 위해서는 ANALYZE에 의한 통계 정보가 있어야만 하며...
다음과 같이 확인해 볼 수 있다.
CONN SYSTEM
SELECT NUM_ROWS, BLOCKS, EMPTY_BLOCKS AS EMPTY, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN
FROM DBA_TABLES
WHERE OWNER = 'HR'
AND TABLE_NAME = 'EMPLOYEES';
+ 인덱스 Rebuild(Index Reorganization)를 위한 정보도 얻을 수 있다.
ANALYZE 명령어
+ Cost-Based 방식에 사용할 통계데이터를 모아 준다.
+ Analyze 명령어는 수행될 때마다 다음의 정보를 수정
- table
테이블의 행수, 사용된 블록 수, 한번도 사용되지 않은 블록 수,
사용가능한 Space의 평균, Chained Row수, 컬럼당 Distinct Value의 수,
컬럼당 두 번째로 가장 작은 값, 컬럼당 두번째로 가장 큰 값.
- INDEX
-> Index level, Level 블록 수, Distinct Key 수, 키당 leaf 블록 수의평균,
키당 Data 블록 수의 평균, Clustering Factor, 최소키 값, 최대키 값.
*/
Index Merge
select employee_id, hire_date from employees where salary between 8600 and 10000 and hire_date like '94%';
-- 결합 인덱스 생성
-> create index hire_date_salary_idx on employees(hire_date, salary);
select salary, hire_date from employees where salary between 8600 and 10000 and hire_date like '94%';
select * from employees where hire_date like '95/%';
-> select * from employees where hire_date in ( '95/10/17','95/07/14','95/05/18','95/05/01');
-> select * from employees where hire_date in ( select hire_date from employees where hire_date like '95/%' );
결합인덱스의 조건 : equal이 결합인덱스에 미치는 영향
- 결합인덱스(COL1, COL2)
+ 결합 인덱스가 WHERE 조건에 나올 경우 순서에 의해 효율의 차이가 많이 날수 있다.
결합인덱스 선택에서 EQUAL로 나오게 할 수 있느냐가 중요하다.
: 결합인덱스 컬럼 결정 순서
a. 항상 조건이 될 수 있는 컬럼이 제일 앞에 와야 한다.
b. 항상 = 로 나올수 있는가?
-> = 로 만들수 있는가 까지 볼수 있어야 겠다.
c. 분포도가 좋은 컬럼을 우선으로 한다.
d. sort 순서는?
결합인덱스 구성 컬럼 중 sort을 해야 하는 컬럼에 대한 결정.
e. 어떤 컬럼을 추가? (후보선수)
인덱스 선정 절차
+ 해당 테이블의 엑세스 유형 조사
+ 대상 컬럼의 선정 및 분포도 분석
+ 반복 수행되는 엑세스 경로의 해결
+ 클러스터링 검토(인덱스만으로도 안되는 경우가 있다.)
+ 인덱스 컬럼의 조합 및 순서의 결정
+ 시험생성 및 테스트
+ 수정이 필요한 애플리케이션 조사 및 수정
엑세스 유형이 조사(설계단계)
+ 반복 수행되는 엑세스 형태를 찾는다.
+ 분포도가 아주 양호한 컬럼을 찾아 인덱스 유형을 찾는다.
+ 자주 넓은 범위의 조건이 부여되는 경우를 찾는다.
+ 자주 조건절에 사용되는 컬럼들의 엑세스 유형을 찾는다.
+ 자주 결합되는 사용되는 경우를 찾는다.
+ 소트의 유형을 조사한다.
+ 일련번호를 부여하는 경우를 찾는다.(PK)
+ 통계자료 추출을 위한 엑세스 유형을 조사항다.(거의 대용량일 경우 대량의
데이터를 읽어서 GROUP BY 등을 하는 경우 클러스터링 고려)
인덱스의 선정기준
+ 분포도가 좋은 컬럼은 단독적으로 생성하여 활용도를 향상
+ 자주 조합되어 사용되는 경우는 결합인덱스 생성
+ 각종 액세스 경우의 수를 만족할 수 있도록 인덱스간의 역할 분담
+ 가능한 수정이 빈번하지 않는 컬럼(발란스트리 깨짐)
+ 기본키 및 외부키(조인 연결고리가 되는 컬럼)
+ 결합 인덱스의 컬럼 순서 선정에 주의
+ 반복수행 되는 조건은 가장 빠른 수행 속도를 내게 할 것
+ 실제 조사된 액세스 종류를 토대로 선정 및 검증
인덱스의 선정기준
+ 분포도가 좋은 컬럼은 단독적으로 생성하여 활용도를 향상
+ 자주 조합되어 사용되는 경우는 결합인덱스 생성
+ 각종 엑세스 경우의 수를 만족할 수 있도록 인덱스 간의 역활 분담
+ 가능한 수정이 빈번하지 않는 컬럼(발란스트리 깨짐)
+ 기본키 및 외부키(조인 연결고리가 되는 컬럼)
+ 결합 인덱스의 컬럼 순서 선정에 주의
+ 반복수행되는 조건은 가장 빠른 수행 속도를 내게 할 것
+ 실제 조사된 엑세스 종류를 토대로 선정 및 검증
인덱스 고려사항
+ 새로 추가된 인덱스는 기존 엑세스 경로에 영향을 미칠 수 있음
(실행 계획이 바뀔 수 있다.)
+ 지나치게 많은 인덱스는 오버헤드 발생
+ 넓은 범위를 인덱스로 처리시 많은 오버헤드 발생
랜덤엑세스(rowid에 의한 direct엑세스가 다량 발생)
+ 옵티마이저를 위한 통계데이터를 주기적으로 갱신(CBO)
+ 인덱스의 개수는 테이블의 사용형태에 따라 적절히 생성
+ 인덱스의 개수는 테이블의 사용형태에 따라 적절히 생성
트랜잭션이 많은 경우(OLTP) 최소로 인덱스를 만들어야 한다.
+ 분포도가 양호한 컬럼도 처리범위에 따라 분포도가 나빠질 수 있음
+ 인덱스 사용원칙을 준수해야 인덱스가 사용되어짐
+ 조인시에 인덱스가 사용여부에 주의
/*
부분범위 처리
= 처리할 내용이 아무리 많더라도 일단 운반단위에 차게 되면 데이터를 RETURN시키고,
사용자가 다음 ACTION을 취할 때까지 기다리므로 처리범위의 크기에 영향을 받지 않도록 하는 기법
- 조건을 만족하는 전체 집합이 아닌 일부분만 엑세스
- 데이터량이 많아도 퍼포먼스에 지정이 없고, 오히려 향상
- 인덱스나 클러스터를 적절히 활용한 소트의 대체(ORDER BY 를 안하도록 한다.)
그 외..
- 테이블을 엑세스하지 않고 인덱스만 사용하도록 유도
- EXIST의 활용(IN 보다 효율적, EXISTS 는 옵티마이저가 부분범위처리한다.)
- ORDER BY 생략(불필요한 정렬작업 생략)
- MIN / MAX
- ROWNUM의 활용 -> STOP KEY 를 이용
- QUERY를 이원화 하여 일부분씩 스캔 하도록 유도(소트의 범위를 줄임)
등의 활용예를 통해 부분범위 처리를 확인해 볼 수 있다.
옵티마이저 힌트는 적대적인 것은 아니다. 힌트를 줘 놓고 힌트를 쓸수 있는 요소가 없으면 힌트가 사용되지 않는다.
때문에 멍텅구리 조건을 주는 경우가 있다.
- 인덱스가 있는 컬럼을 조건절에 넣는다.(단, 추출하려는 데이터에 영향을 미치지 않는 한도내에서.)
* 전체범위처리 : FULL RANGE SCAN 후 가공하여 ARRAY SIZE 만큼 추출
* 조건을 만족하는 ROW 수가 ARRAY SIZE에 도달되면 멈춤(STOP KEY)
*/
/*
SORT 를 대신하는 인덱스
*/
-- 싱글 인덱스
-- SELECT * FROM PRODUCT WHERE YMD = '951023' AND ITEM LIKE 'AB%' ORDER BY YMD, ITEM;
-- 결합 인덱스
-- SELECT * FROM PRODUCT WHERE YMD = '951023' AND ITEM LIKE 'AB%';
/*
INDEX 생성
*/
-- create index HIREDATE_IDX ON EMPLOYEES(HIRE_DATE)
/*
MAX 처리
*/
-- SELECT MAX(HIRE_DATE) FROM EMPLOYEES;
-- SELECT /*+INDEX_DESC(EMPLOYEES HIREDATE_IDX) */ HIRE_DATE FROM EMPLOYEES WHERE HIRE_DATE < '20/12/30' AND ROWNUM = 1;
-- SELECT MAX(EMPLOYEE_ID)+ 1 FROM EMPLOYEES WHERE DEPARTMENT_ID = 100;
-- SELECT /*+INDEX_DESC(EMPLOYEE_ID) */ EMPLOYEE_ID + 1 FROM EMPLOYEES WHERE DEPARTMENT_ID = 100 AND ROWNUM = 1;
/*
EXISTS
*/
-- SELECT COUNT(*) FROM hr.EMPLOYEES WHERE DEPARTMENT_ID = 100 AND EMPLOYEE_ID > 10 ;
-- SELECT 1 FROM DUAL WHERE EXISTS(SELECT 'X' FROM EMPLOYEES WHERE DEPARTMENT_ID = 100 AND EMPLOYEE_ID < 10);
/*
범위가 좁은것 부터 큰 것으로 조건을 지정
*/
-- select * from employees where employee_id between 10 and 10000 and department_id = '100';
-- select /*+index_asc(department_id, employee_id)*/ * from employees
-- where department_id = '100' and employee_id between 10 and 10000 ;
/*
인덱스를 최대한 활용하라.
*/
-- select min(salary) from employees;
-- select min(salary) from employees where salary > 0 and rownum = 1;
/*
부분범위 처리가능시 최초운반단위를 빨리 채우도록 한다.
*/
-- select last_name, salary * 100 from employees where hire_date like '9%' order by last_name;
-- select last_name, salary * 100 from employees where hire_date like '9%' and last_name > ' ' ;
/*
UNION 대신 UNION ALL을 사용하라.
*/
-- select last_name, salary * 100 from employees where hire_date like '96%'
-- union
-- select last_name, salary * 100 from employees where hire_date not like '96%' ;
-- select last_name, salary * 100 from employees where hire_date like '96%'
-- union all
-- select last_name, salary * 100 from employees where hire_date not like '96%' ;
한 분야에서 1등부터 5등까지 최고의 전문가들을 만나 심층 인터뷰를 해보라.
인터뷰가 끝나면 당신이 1등이다.
당신이 최고의 전문가가 된다.
1등은 절대 2등한테 안 물어본다.
2등은 3등한테 안 물어본다.
그래서 각자 자기 것만 알고 있는 것이다.
1등부터 5등 한테까지, 모든 것을 듣고 나면 답이 딱 나오게 되어있다.
- 박원순 변호사, ‘희망을 심다’에서
답을 구하는 여러 방법 가운데 하나가
관계된 전문가들을 깊이 있게 인터뷰 하는 것입니다.
회사에서도 풀기 힘든 어려운 문제에 부딪힐 경우
현장 실무자들과의 심층 인터뷰에서 실마리가 발견되는 경우가 많습니다.
해답은 어딘가에 이미 존재하고 있습니다.
겸손한 마음으로 이미 존재하는 지식과 정보, 해답을 제대로 파악하는 것이
새로운 창조를 위한 첫 걸음입니다.
회사에서의 서열은
의상을 보아도 알 수 있다.
여사장은 고급 브랜드의 우아한 정장을 입고,
여비서는 블라우스와 치마를 입는다. 당신과
직급이 같은 동료들과 똑같은 수준으로 옷을 입어라.
여기에서 너무 크게 벗어나면 호감 점수를 잃는다.
동료들보다 시원찮게 입으면 무성의하고 칠칠치
못해 보인다. 가장 안전한 것은 평균보다
살짝 수준 높게 입는 것이다.
베스트 드레서가 되려 하지 마라.
- 프랑크 나우만의《호감의 법칙》중에서 -
* 옷이 날개라고 하지요?
옷은 맵시도 드러내지만 센스도 드러냅니다.
'베스트 드레서가 되지 말라'는 말은 '옷 하나를 입어도
잘 살펴서 입으라'는 뜻이겠지요. 그 사람의 격(格)과
내면까지 드러내 보여주는 것이 옷이니까요.
그런 점에서 겉옷보다 속옷을, 속옷보다
마음 속 내면의 옷을 멋지게 입어야
진짜 베스트 드레서입니다.
