Jpa 잘 (하는 척) 하기

Jpa 잘 (하는 척) 하기 from 경원 이

오라클 wm_concat 정렬

오라클 row 데이터를 하나의 로우로 합칠려다 보니 문제가 많다. ㅜㅜ

1. LISTAGG(ADDITION_INFO_T, ' | ') WITHIN GROUP(ORDER BY SEQ)

LISTAGG 함수는 ROW 단위별 연결 문자 지정이 가능하고 정렬도 가능하다.
하지만 OTL 문자열이 VARCHAR2 4000 자를 초과하면 에러가 난다.

그래서 대신 할수 있는 함수가 무엇일까?

WM_CONCAT 두둥 근데 정렬이 ㅠㅠ 구분자가 ㅠㅠ

정렬은 검색으로 찾았다. 근데 구분자는 ㅠㅠ

참고로 XMLAGG 나 LISTAGG 는 문자열 크기 초과에러 발생한다 ㅠㅠ

with tbl as (     select 1 seq , '20110801' ymd, 't' gubun, 'input1' title from dual     union select 2 seq , '20110802' ymd, 't' gubun, 'input2' title from dual     union select 3 seq , '20110802' ymd, 't' gubun, 'input3' title from dual     union select 4 seq , '20110802' ymd, 't' gubun, 'input4' title from dual     union select 5 seq , '20110802' ymd, 't' gubun, 'input5' title from dual     union select 6 seq , '20110804' ymd, 'c' gubun, 'input6' title from dual ) select     ymd,wm_concat(seq||'||'||title) as seq_title from     tbl group by ymd 제가 할려는게 같은 날짜의 title 를 seq 순서대로 뽑아내는건대요 위에 쿼리를 돌려보면 20110801    1||input1 20110802    2||input2,4||input4,5||input5,3||input3   <== 여기가 문제임 20110804    6||input6 이런식으로 나옵니다. 즉 2,3,4,5 이런순으로 나와야 하는대 엉뚱하게 3이 젤 나중에 나와버립니다. 어떻게 해야 할까요? 도움 주시면 감사하겠습니다...

이 글에 대한 댓글이 총 1건 있습니다.

wm_concat 간단하면서도 강력한 기능 정말 좋은데...
한가지 아쉬운 점이 정렬 기능이 없다는 거죠...
XmlAgg(9i) 나 ListAgg(11g) 를 이용하시면 됩니다.

SELECT ymd
     , SUBSTR(XMLAGG(XMLELEMENT(x, ',', seq, '||', title) ORDER BY seq)
       .EXTRACT('//text()'), 2) v_9i
     , wm_concat(seq||'||'||title) v_10g
     , LISTAGG(seq||'||'||title, ',') WITHIN GROUP(ORDER BY seq) v_11g
  FROM tbl
 GROUP BY ymd
 ORDER BY ymd
;

wm_concat를 이용해 정렬하고자 한다면..
인라인뷰에서 분석함수의 정렬기능을 이용하신후 밖에서 걸러내시면 됩니다.

SELECT ymd
     , seq_title
  FROM (SELECT ymd, seq
             , wm_concat(seq||'||'||title)
               OVER(PARTITION BY ymd ORDER BY seq) seq_title
             , MAX(seq) OVER(PARTITION BY ymd) max_seq
          FROM tbl
        )
 WHERE seq = max_seq
 ORDER BY ymd
;

Connect_By_Path(9i)를 이용하는 방법도 있는데 더 복잡하고 성능도 안좋아요.
SELECT ymd
     , SUBSTR(SYS_CONNECT_BY_PATH(seq||'||'||title, ','), 2) seq_title
  FROM (SELECT ymd, seq, title
             , ROW_NUMBER() OVER(PARTITION BY ymd ORDER BY seq) rn
             , COUNT(*) OVER(PARTITION BY ymd) cnt
          FROM tbl
        )
 WHERE rn = cnt
 START WITH rn = 1
 CONNECT BY PRIOR ymd = ymd
        AND PRIOR rn + 1 = rn
;

개발자를 위한 DB 튜닝 가이드

참고 URL :  MS-SQL
https://www.microsoft.com/korea/technet/sql/tuning_guide_developer01.mspx

참고 URL :

http://iamakira.tistory.com/15
http://luvstudy.tistory.com/6

oracle big data

http://www.oracle.com/kr/corporate/magazines/bigdata-modified-1920003-ko.pdf

클러스터링 형태의 결정기준

인덱스 선정을 위한 접근 절차
1. 테이블의 엑세스 형태를 최대한으로 수집
2. 인덱스 대상 컬럼의 선정 및 분포도 조사
3. 특수한 엑세스 형태에 대한 인덱스 선정
4. 클러스터링 검토
5. 결합 인덱스 구성 및 순서의 결정
6. 시험 생성 및 테스트
7. 수정이 필요한 애플리케이션 조사 및 수정
8. 일괄 적용

조인의 최적화 방안

전체범위처리로 sql 수행시 반복연결보다 조인방식이 효율적

조인처리 : tab1 처리범위가 1000 건이면 1000번 연결작업을 랜덤엑세스 방식으로 수행
1000 번 연결작업에 sql 은 최대 1회 수행

반복연결 : tab1 처리 범위 1000건을 전체범위로 엑세스하여 가공한후 loop 를 돌면서 fetch
fetch 마다 sql 수행되어 전체 sql 1001 회 수행

연결되는 작업의 수행횟수가 처리되어야 할 일 량의 일부분씩 처리되는 경우 반복연결 유리
select a.fld1... , b.col2... from tab2 b , tab1 a where a.key1 = b.key2 and a.fld1 = '10'
order by a.fld2

조인처리:
1. 연결고리 정상
2. tab1 에 fld1 인덱스 존재
3. tab1의 fld='10' 전체범위
4. fld1='10' 범위 1000 로우
5. 1000의연결수행 후 정렬작업

반복처리 :
1. tab1 은 1000건 모두 엑세스 정렬
2. tab2 연결은 운반단위 만큼 수행
3. tab2 연결횟수 감소
4. 온라인조회 경우 반복연결 유리

전체범위 처리 방식에서 - 인라인뷰 처리

select x.부서코드 , y.부서명, 매출액 from
( select 부서코드 , sum(매출액) 매출액 from tab1 where 매출일 like '20000205%' group by 부서코드 ) x ,tab2 y where y.부서코드 = x.부서코드

연결고리 상태가 조인에 미치는 영향
조인의 양을 먼저 많이 줄여 줄수 있는 테이블을 먼저 엑세스하면 처리할 양이 감소
-연결고리 정상이면 어느 방향으로 연결을 수행하든 연결작업의 논리적인 양은 동일
-연결고리 정상이면 연결시도 전에 처리범위를 감소시키는 경로가 유리

부분범위 처리

부분범위 처리의 개념
전체범위처리 : 드라이빙 조건을 만족하는 범위를 모두 스캔하여 체크조건 검증한 후 임시 저장공간에 저장 후 운반단위만큼 추출
부분범위처리 : 드라이빙 조건을 만족하는 범위를 차례로 스캔하면서 체크조건 검증하여 일단 운반단위만큼만 추출

Survey 를 우선 전체범위 엑세스하고, 그 결과와 DA100T 를 부분범위 처리로 NL 조인
전체범위 처리 :
sort : 하위단계는 전체범위 처리
view : 내부적으로 임시 저장공간 쓰기 작업
sort (unique) , sort (join) , sort (aggregate) , sort (order by) , sort (group by) , merge join
hash join
부분범위 처리 :
where 절 없이 대용량 테이블을 처리하더라도 즉각 결과를 추출할수 있음
Trace 의 Execute , Fetch 라인의 Query, Disk, Current 값이 테이블 블록 수보다 훨씬 적을경우
부분범위 처리의 자격 :
논리적으로 전체범위를 읽어서 가공해야만 하는 경우를 제외한 모든 형태에서 부분범위 처리 가능
- Select list , where 절 그룹 함수 : sum , count
- order by : 드라이빙 역활 인덱스와 order by 컬럼이 동일하면 부분범위 처리 가능
- sort 수행 : 실행 계획에 sort 수행시 전체범위 처리
- union  : union all 사용으로 부분범위 처리 가능
- minus : exists , in 서브쿼리로 세미조인 사용하여 부분처리가능
- intersect : 전체범위 엑세스 -> sort (unique) -> merge

부분범위 처리의 수행속도 향상원리
엑세스 주관조건의 범위가 좁을수록 일 량 감소, 체크조건의 대상범위가 넓을수록 일량감소
-엑세스 주관 컬럼의 처리범위는 좁을수록 유리
-엑세스 주관 컬럼의 범위가 넓어도 체크조건을 만족하는 범위가 넓다면 유리
-엑세스 주관 컬럼의 범위가 넓고, 체크조건의 범위가 좁을 경우 역활 변경
-부분범위의 양호한 수행속도 보장을 위하여 부분범위처리로의 유도 방법 필요

부분범위처리로의 유도
-엑세스 경로를 이용한 sort 대체
-인덱스만 처리하는 부분범위 처리
-min, max 의 처리
-filter 형 부분범위 처리
-rownum 을 이용한 부분범위 처리
-인라인뷰를 이용한 부분범위 처리
-저장형 함수를 이용한 부분범위 처리
-쿼리 이원화를 이용한 부분범위 처리

인덱스 선정 기준

테이블 형태별 적용기준

1. 적은 데이터를 가진 소형 테이블

- DB_FILE_MULTIBLOCK_READ_COUNT 값 이하 블록 크기의 테이블

- 한번의 멀티블럭 I/O 에 의해 인덱스 없이 전체 테이블 스캔 가능

2. 주로 참조되는 역활을 하는 중대형 테이블

-트랙잭션 데이터의 행위,주체,목적이 되는 개체들로 구성된 테이블

3. 업무적 구체적인 행위를 관리하는 중대형 테이블

- 매출정보 와 같이 업무의 구체적인 수행 내용을 담고 있는 트랜잭션 테이블

4. 저장용 대형 테이블

- 로그성 데이터 관리 목적의 테이블

- 저장 우선,갱신 거의 없으므로 PCTFREE 여유공간 불필요,PRIMARY  KEY 제약조건불필요

분포도와 손익 분기점

-어떻게 인덱스를 구성해야 가장 최소의 범위를 처리하는가?

-최소의 인덱스로 최대의 엑세스 형태를 만족하는 전략 필요

인덱스 머지와 결합 인덱스 비교

-인덱스를 머지하는 것보다 가장 좋은 분포도의 하나의 인덱스만 사용하는 것이 대부분유리

-머지할 대상이 서로 비슷한 분포도일 경우 효용 가치 있음

-결합인덱스는 인덱스를 머지하여 성공한 결과를 저장한 형태

-컬럼이 '=' 사용하면 항상 유리

분포도와 결합순서의 상관관계

select * from tab where col1 = 'A' and col2=115;

index1 =col1 + col2  

index2= col2 + col1

-분포도가 넓은 컬럼 선행하여도 equal 을 사용한경우 실제 처리량은 서로 차이 없음

equal (=) 이 결합순서에 미치는 영향

select * from tab where col1 = 'A' and col2 between 113 and 115;

-인덱스 첫컬럼이 '=' 로 사용되지 않으면뒤 컬럼의'='사용은 처리범위 감소에는효과가없다.

IN 연산자를 이용한 징검다리 효과

range scan :

BETWEEN , LIKE -> 선분 , IN -> 점 

select * from tab where col1 = 'A' and col2 between 113 and 115; 

inlist iterator : 

INLIST ITERATOR 실행계획으로 IN 리스트 값만큼 인텍스 탐침

select * from tab where col1 = 'A' and col2 in (113 , 115 ); 

optimizer where (col2=113 and col1='A') or (col=115 and col1='A')

결합 인덱스의 컬럼순서 결정 기준

1. 항상 사용하는가?

2. 항상 '=' 로 사용하는가?

3. 어느것이 더 좋은 분포도를 가지는가?

4. 자주 정렬되는 순서는 무엇인가?

5. 부가적으로 추가시킬 컬럼은?

실행계획의 제어

실행계획의 유형
스캔을 위한 실행계획
데이터 연결을 위한 실행 계획
각종 연산을 위한 실행계획
비트맵 실행계획
기타 특수한 목적을 처리하는 실행계획

select * from emp , dept where dmp.deptno = dept.deptno
and not exists (select * from salgrade where emp.sal between losal and hisal)

id  operation options object name
1  6. filter
2  4. nested loops
3  1. table access (full) emp
4  3. table access (by rowid) dept
5  2. index (unique scan) pk_dept
6  5. table access (full) salgrade

cr = number of buffers - retrieved for or reads
pr = number of physical reads
pw = number of physical writes
time = elapsed of time , microsecond(us)

$oracle_home/rdbms/admin/utlxplan.sql
create unique index plan_index on plan_table (statement_id , id)

수동 explain plan set statement_id = 'a1'
for select col3 ,sum(col4) from tab1
where a.col1 in (10,50) group by col3

자동 set auto traceonly exp
select col3 ,sum(col4) from tab1
where a.col1 in (10,50) group by col3

전체테이블 스캔
High water mark 내에 있는 모든 블록을 스캔
--> 사용된 저장공간의 총합계 또는 데이터를  INSERT 하기 위한 포맷된 영역 표시
멀티 블록 I/O
옵티마이저의 전체테이블 스캔 선택사유
적용가는 인덱스의 부재
넓은 범위의 데이터 엑세스
소량의 테이블 엑세스
병렬처리 엑세스

인덱스 스캔
인덱스 유일 스캔
- 대부분 단 하나의 row 추출
- 전제조건을 만족할 경우 옵티마이저는 인덱스 유일 스캔을 선택
- 데이터베이스 링크 사용시 힌트로 적용
- 힌트는 index (table_alias_index_name) 힌트적용
인덱스 범위 스캔
- 추출되는 row 는 index 구성컬럼의 정렬순서와 동일
- order by 절에 있더라도 추가정렬작업에 필요없을 수도 있음



데이터 연결을 위한 실행계획

내포조인 nested loop join
가장 고전적 형태의 조인방식이나 현실적으로 가장 많이 적용
single block i/o
전처리 집합의 처리범위가 전체 일량을 좌우
다량의 랜덤 엑세스 발생
따라서 소량의 엑세스는 유용, 다량의 엑세스는 큰 부하 발생
nested loops join = 내포조인 = 중첩루프조인
정렬변합 sort merged join


해쉬조인 hash join
세미조인 semi join
카티전 조인 cartesian join
아우터 조인 outer join
인덱스 조인 index join

실행계획의 유형

실행계획의 고정화
실행에 따라서 동적으로 최적화를 하는 것이 가장 이상적인가?
조건에 따라 유인하게 변화 / 튜닝된 실행계획을 고정

아우트라인 (outline) 이란?  제조법 (recipe)
실행계획의 요약본을 저장해 두었다가 참조해 실행계획을 수립하는 기능
완전한 실행계획이 아니라 동일하게 재현할수 있는 최소한의 참조정보

적용기준
잘정의된 옵티마이징 팩터와 적절항 SQL 을 기반으로 대부분은 옵티마이저에게 맡기고,
특별히 문제가 있는 경우만 아우트라인으로 통제

적용방법
범용적으로 관리하거나 , 개별적으로 관리할수도 있으며 필요에 따라 적용시키거나 금지 가능 , 필요시 강제로 편집도 가능 , 익스포트 해 두었다가 원할때 임포트 가능
그룹을 지정하여 선별적인 적용 가능

아우트라인의 생성과 조정
package : dbms_outln  , dbms_outln_edit
procedure:
create_outlin : 지정된 건을 공유커서에서 찾아 아우트라인 생성
clear_used : 지정한 아우트라인을 제거
drop_by_cat : 지정한 카테고리에 속한 아우트라인들을 제거
drop_unused : sql 파싱에 사용된 적이 없는 아우트라인을 제거
update_by_cat : 어떤 카테고리를 새로운 카테고리로 변경
generate_signature : 지정한 sql  문에 대한 식별자를 생성

생성: alter session set create_stored_outlines = category_name : 지정한 카테고리 생성
사용 :alter session set use_stored_outlines = category_name : 지정한 카테고리 사용

개별 아우트라인의 적용
한 세션에만 유효하고 다른세션에는 전혀 영향을 미치지 않음
공식적으로 적용하기에 부담이 있을때 사전검토를 위한 사용형태
기존 아우트라인 조정시 복제하여 수정한 후 대체시키기 위한 사용형태

1. 기존의 아우트라인에서 새로운 개별 아우트 라인으로 복제한다
create private outline prv_ol_1 from outln_1;
2. 아우트라인을 수정할 수 있는 룰이나 dbms_outln_edit 패키지에 있는 여러 프로시저를 이용해 조인의 순서를 조정하는 등의 작업을 한다.
3. 생성된 개별 아우트라인을 검증하기 위해 use_private_outline 을 true 로 지정하고 검증을 실시한다
4. 충분한 검증이 끝나면 공식적인 적용을 위해 다음의 작업을 수행한다
create or replace outline outln_1 from private prv_ol_1;
5. use_private_outlines 을 false 로 지정하여 개별 아우트라인의 수행을 종료한다.

아우트라인의 관찰 : 딕셔녀리 뷰를 통해 확인
user_outlines
user_outlines_hints

selet empno from emp e where e.emp_no = 7856

name : sys sys_outline_0522
category : lhss001
used : unused
sel_text :selet empno from emp e where e.emp_no = "SYS_B_0"
signature : sql_text 를 자동 인식하여 raw 타입으로 된 sql 식별자

동일한 sql을 다른 카테고리에 생성하면 signature 는 동일, name 은 달라짐

아우트라인의 저장형태 : 조인방법과 조인순서, 사용하는 인덱스 , 비용, 카디널리티 보유
테이블에 저장되어 있는 값이므로 직접 수정도 가능
기본값으로 지정된 SYSTEM 테이블 스페이스가 아닌 원하는 테이블스페이스에 생성 가능

업그레이드 시의 적용기준

1. 특정세션이나 전체 sql 에 대하여 다음과 같이 특정 카테고리에 아우트라인 생성 시작
alter session set create_stored_outlines = category_name;
2. 많은 sql 의 아우트라인이 생성되도록 오랜 동안 수집 , 월 단위 이상이나 특정 기간만 수행되는 것은 별도 처리
3. 아우트라인 생성을 종료시키려면 create_stored_outlines 파라미터를 false로 지정
4. dbms_stats 패키지를 이용하여 통계정보를 생성
5. 옵티마이저 모드를 rule 에서 choose 로 변경
6. use_stored_outlines 파라미터에 생성한 카테고리를 지정하여 아우트라인을 적용

비용 기준에서 전환 시
1. 이전버전에 대한 아우트라인을 생성할때 가능하다면 다양한 분류별로 카테고리를 지정
2. 많은 sql의 아우트라인이 생성되오록 오랜 동안 수집별도 처리
3. 아우트라인 생성을 종려시키려면 create_stored_outlines 파라미터를 false 로 지정
4. 업그레이드를 수행한 후 dbms_stat   패키지를 사용하여 통계정보를 생성
5. 애플리케이션을 수행하면서 테스트 실시
6. 비전 업그레이드로 인해 수행속도에 심각한 문제가 발생하엿다면 생성해 두었던 아우트라인을 적용

옵티마이저의 한계
현재의 정보만으로 미래를 예측해야 함
정확한 분포도 산정의 어려움
단지 논리적으로 이미 존재하는 길을 선택할 뿐임
현실에서 사용되는 대부분의 조건은 변수 형태로 부여

결국 중요한 것은 다양한 사용 형태를 만족할 수 있도록 종합적이고 전략적인 차원에서 데이터 구조와 인덱스를 설계하고, 수준 높은 SQL 을 구사하는 것이 반드시 필요

옵티마이저의 최적화 절차
SQL -> Parser -> Query Transformer -> Estimator -> Plan Generator -> Row source generator

사용자가 실행한 sql은 데이터 딕셔너리를 참조하여 파싱을 수행
옵티마이저는 파싱 결과를 이용해 논리적으로 적용 가능한 실행 계획 형태를 선택하고
힌트를 감안하여 일치적으로 잠정적인 실행계획들을 생성

데이터 딕셔너리의 통계정보 (데이터의 분포도, 테이블 저장구조,인덱스 구조, 파티션 형태.

비교연산자 등을 감안하여 각 실행계획의 비용을 계산

실행계획들의 산출된 비용을 비교하여 가장 최소의 비용을 가진 실행계획을 선택

질의 변환기 Query transformer
보다 양호한 실행계획을 얻을 수 있도록 적절하게 sql 형태를 변환하는 것
-인라인 뷰나 뷰의 병합 (merging)
-조건절의 진입 (predicate pushing)
-서브쿼리의 비내포화(subquery unnesting)
-실제뷰(Materialized view) 의 질의 재생성(rewrite)
-OR 조건의 전개(expansion)

view merging
뷰정의시에 지정한 쿼리 (뷰쿼리)를 엑세스가 수행되는 쿼리 (엑세스쿼리)에 병합
predicate pushing
뷰병합을 할 수 없는 경우를 대상으로 뷰쿼리 내부에 엑세스 쿼리와 조건절을 진입시키는 질의 변환
subquery unnesting
서브쿼리는 경우에 따라서 내포관계를 해체하여 조인형식으로 대체함으로써 보다 양호한 수행속도를 얻을 수 있음
Query Rewrite
실제뷰는 테이블과 밀접한 논리적 관계를 가진 물리적 집합이므로 최적의 집합을 처리하도록 쿼리를 재생성
OR expansion
OR 조건이 처리주관 조건이 되면 여러개의 단위 쿼리로 분기하고 UNION ALL 로 연결하는 질의로 변환

비용산정기 (Estimator)
Selectivity : 처리할 집합에서 해당조건을 만족하는 로우가 차지하는 비율
Cardinality : 판정대상이 가진 결과 건수 혹은, 다음 단계로 들어가는 중간결과 건수
Cost : 실행계획의 각 연산들을 수행할 때 소요 시간비용을 상대적으로 계산한 예측치

선택도 : 대상 집합에서 해당 조건을 만족하는 로우가 차지하는 비율
판정단위는 ? 선택도 판정단위는 개별 컬럼이 아니라 해당 엑세스를 주관할수 있는 조건들
선택도는 0.0 에서 1.0 사이의 값을 갖도록 생성
선택도의 값이 낮다는 것은 전체에서 차지하는 비율이 낮다는 것 변별력이 좋다는 것
좋은 선택도를 가진 것을 처리주관으로 결정하면 보다 적은 처리범위를 엑세스

카디널리티 : 판정대상이 가진 결과건수 혹은 다음단계로 들어가는 중간결과 건수
산정방법: 선택도와 전체로우수 계산
select statement
table access (by index rowid) of 'emp' (cost=2 , card=1 . bytes =32)
index (full scam) of 'pk_emp' (unique) (cost=1 , card=15)

필요한 이유
선택도는 단지 비율일 뿐임 ,같은 대상 집합에 대해서는 비율만으로 충분하지만 만약 조인의 순서나 방향 등의 결정을 위해 먼저 수행될 집합을 선택하기 위해서

비용:실행계획 상의 각 연산들을 수행할때 소요되는 시간 비용을 상대적으로 계산한 예측치
산정방법: 통계정보에 CPU 와 메모리 상황, 디스크 I/O 비용도 고려하여 계산

동일한 평가결과의 우선순위 결정
규칙기준 : 로우캐시에 나타난 순서 / 비용기준 : 인덱스 명의 ASCII 값

실행계획 생성기
쿼리를 처리할 수 있는 적용 가능한 실행계획을 선별하고, 그들에 대한 비교검토를 거쳐 가장 최소의 비용을 가진것을 선택

특기사항 : 후보로 등장했던 여러개의 실행계획들은 다양한 처리단위 (query block) 조합으로 구성
최적화란 쿼리가 실행되기전에 아주 짧은 시간내에 수행해야만 하는 작업
이슈 : 최적화에 최대한의 시간을 투자한다면 조금 더 나은 실행계획을 얻을수 있을지 모르지만 이로인한 부하가 전체 수행시간에 너무 많은 비중을 차지한다면 켤고 적절 할수없다.

적용적 탐색 (Adaptive search) 과 경험적 (Heuristic) 기법을 적용하여 초기자를 선택( Cutoff) 하는 전략을 사용함

adaptive search:쿼리수행의 총예상수행시간에 대해 최적화를 하는 시간이 일정비율을 넘지 않도록 하는 탐색전략
Heuristic cutoff : 탐색도중이더라도 최적이라고 판단되는 실행계획을 발견하면 더이상 진행하지 않고 멈추는 것
힌트 ? 고수가 옆에서 훈수를 해주는 것과 유사 (보다 쉽게 최적을 찾을 수 있다)

질의의 변환
수식연산
sales_qty > 1200/12 --> sales_qty > 100
sales_qty*12 > 1200 --> 좌우를 이항해서 연산하지는 않음
조건연산
job like 'SALESMAN' --> job = 'SALESMAN' (가변길이 타입만)
job in('clerk' , 'manager') --> job = 'clerk' or job = 'manager'
sales_qty > any (:in_qty1, :in_qty2) --> sales_qty > :in_qty1 or sales_qty >:in_qty2
where 10000 > any (select sal from emp where job= 'clerk')
-->where exists(select sal from emp where job='clerk' and 10000>sal)
sales_qty > ALL(:in_qty1,:in_qty2) --> sales_qty >:in_qty1 and sales_qty >:in_qty2

where 1000> all (select sal from emp where job ='clerk'
--> where not (10000 <= any (select sal from emp where job='clerk'))
--> where not exists (select sal from emp where job='clerk' and 10000 <= sal)
sales_qty between 100 and 200 --> sales_qty >=100 and sales_qty<=200
not (sal<3000 OR comm in NULL) --> not sal < 3000 and comm is not null
--> sal >=3000 and comm is not null
not deptno= (select deptno from emp where empno = 7689)
--> deptno <> (select deptno from emp where empno = 1687)
목적 : 보다 양호한 실행계획을 얻을수 있도록 가능한 최대로 적절하게 SQL 형태를 변환하는것

이행성규칙 (transitivity principle)
where column1 comparison_operator constant and column1 = column2
--> column2 comparison_operator constant
comparison_operator : = .!= , ^= < , >, <=
constant : 연산 ,sql 함수 ,문자열 ,바인드 변수, 상관관계 변수를 포함하는 상수수식

진정한 의미는 ?
select * from emp e , dept d where e.deptno =20 and e.deptno = d.deptno
이행성 규칙에 의해 실행계획이 DEPT 테이블을 먼저 인덱스로 엑세스하는 실행계획이 가능

비교값이 상수수식이 아니면 추론이 안됨

OR 조건들의 UNION ALL 분기 --> UNION ALL 로 각각의 인덱스를 경유하는 실행계획을
 수립하고 이를 결합
select * from emp where job='clerk' or deptno=10;
--> select * from emp where deptno=10
union all
select * from emp where job='clerk' and deptno<>10;

뷰병합
뷰쿼리를 엑세스 쿼리에 병합해 넣는 방식
1.엑세스 쿼리에 있는 뷰를 원래 테이블로 변환
2.남아있는 조건절을 다시 엑세스 쿼리에 병합
3. 컬럼들도 대응되는 원래 테이블의 컬럼들로 병합

view merging 가 불가능한 경우 : 엑세스쿼리에 있는 조건들을 뷰쿼리에 진입
집합연산 (union, union all , intersect , minus)
connect by
rownum 을 사용한 경우
select-list 의 그룹함수 (avg , count, max, min , sum)

pushing predicate : 뷰병합을 할 수 없는 경우를 대상으로 뷰쿼리 내부에 엑세스 쿼리의 조건절을 진입시키는 방식

group by 뷰의 병합 (enable 조건)
complex_view_mergeing
optimizer_secure_view_merging

merge : 조건 처리가 완료된 후 group by
no merge : 뷰쿼리를 먼저 처리한 후 조인

IN  서브쿼리의 뷰병합
처리범위를 줄일 수 있는 조건들이 많아 인라인 뷰로 파고들어 가는 것이 일량을 줄일 수 있다면 뷰병합이 유리
파라메터는 기본값을 TRUE 로 하고 필요하다면 NO_MERGE 힌트를 적용하는 것이 바람직함

바인드 변수의 PEEKING
최초에 실질적인 파싱이 일어날때만 단 한번 변수의 값을 엿본다. 여기서 첫번째 파싱이란 공유 SQL  영역에 처음 등록될때를 의미
적용 파라미터 : optim_peek_user_binds = true

SQL 과 실행 계획

SQL 과 옵티마이져와의 상관관계를 이해하고 데이터베이스를 좀더 고급으로 이용할수 있다.

SQL -> SQL 해석 (DATA Dictionary 참조)  -> 실행 계획 작성 -> 실행 (Table 추출) -> 결과
사용자는 요구만 하고 OPTIMIZER 가 실행계획을 수립
수립된 실행계획에 따라 엄청난 수행속도 차이 발생
실행계획 제어가 어렵다
OPTIMIZER 가 좋은 실행계획을 수립할수 있도록 전략적인 FACTOR 을 부여

SQL 로 요구된 결과를 최소의 비용으로 처리할 수 있는 처리 경로를 결정

SQL 은 처리절차를 기술한 것이 아니라 결과에 대한 요구
처리절차는 OPTIMIZER 가 생성, 즉 진정한 프로그래머는 옵티마이저
없는 길을 생성해주는 것이 아니라 이미 존재하는 길을 단지 찾아줄 뿐임
사용자가 부여한 영향요소에 따라 최적은 달라짐 (책임은 사용자)
최적이란 주어진 상황에 따라 달라지는 것임
동일한 결과를 얻을수 있는 경로는 많으나 효율성의 차이는 큼
옵티마이저는 결코 전지전능하지 않다.

옵티마이져 영향 요소
인덱스,테이블 구조, SQL 형태, 사용컬럼,연산자 형태, 힌트 사용, 시스템 및 네트워크 상태, DBMS, 버전, 옵티마이저 모드, 통계 정보

옵티마이저의 형태
SQL을 수행해 보면 쉽게 알 수가 있다.
그렇다고 해서 SQL 작성 시마다 통계정보를 확인하고 비용계산을 해볼 수는 없다!
SQL 만으로 최적의 처리경로를 예측할 수 있는 안목을 가지고  작성하는 것과 무조건 결과만 얻겠다는 접근 방법에는 커다란 차이가 있다.

규칙기준 옵티마이저 (Rule_based optimizer)
1) ROWID로 1 로우 엑세스
2) 클러스터 조인에 의한 1 로우 엑세스
3) Unique Hash Cluster 에 의한 1 로우 엑세스
4) Unique Index 에 의한 1 로우 엑세스
5) Cluster 조인
6) Non Unique Hash Cluster key
7) Non Unique 결합 인덱스
8) Non Unique 한 컬럼 인덱스
9) 인덱스에 의한 범위처리
10) 인덱스에 의한 전체범위처리
11) Sort Merge 조인
12) 인덱스 컬럼의 Min , Max 처리
13) 인덱스 컬럼의 Order by
14) 전체 테이블 스캔
15) Non Unique Cluster key

전략적인 인덱스를 구성하면 확률이 크게 증가

비용기준 옵티마이저 (Cost_Based Optimizer)

통계정보로 실제비용을 계산하여 최소비용을 선택
데이터의 상태에 따른 현실적인 처리 경로 수립
생각만큼 완벽한 처리경로는 얻을 수는 없음

테이블 로우 수와 블록수
블록당 평균 로우수
로우의 평균 길이
컬럼별 상수값의 종류
분포도
컬럼내의 NULL 값의 수
클러스터링 팩터
인덱스의 깊이 (Depth ,Level)
컬럼의 최대,최소값
리프(Leaf) 블록수
가동 시스템의 I/O , CPU 정보

옵티마이저 모드의 종류
First Rows : Mix of cost and heuristics , fast delivery of the first few rows
First Rows n : cost-based approach
All Rows : 비용기준의 Default 모드 , 전체에 대한 Best Throughout

옵티마이저 모드의 선택기준
OLTP 형  -> First rows , First rows n
OLAP 형  -> All rows
옵티마이저 모드를 운영중에 함부로 바꾸는 것은 매우 위험
모드에 따라 항상 실행계획이 변경되는 것은 아니다.

옵티마이저 관련 파라메터
Cursor sharing  force, similar , exact (default)
sql 조건절에 있는 상수값들을 변수로 전환시켜 파싱
Exact 는 대.소문자, 공백, 비교 상수값이 조금만 달라고 공유 못함

DB File MultiBlock read count
full table scan, index fast full scan 을 할때 한번 I/O 에 읽을 블록 수를 지정
운송단가와 같은것, 옵티마이저가 비용을 계산할때 중요하게 사용

Optimizer index caching
nested loops 조인이나 in list 탐침 등의 인덱스를 반복 엑세스에서 인덱스 블록들이 버퍼에 캐쉬되어 있을 확률을 의미
즉 랜덤의 비용감소를 의미하므로 옵티마이저가 인덱스 랜덤 엑세스를 선택하는 경향이 증가함

Optimizer index cost adj
비용계산을 할때 인덱스를 엑세스의 비중을 조정하는 역활을 담당
100 은 계산된 비용을 그대로 적용한다는 의미, 10을 주었다면 1/10로 계산하겠다는 의미
인덱스 엑세스가 전체테이블 스캔으로 자주 나타나며 이값을 조정

동적 표본화 (Dynamic sampling)
소량표본을 동적으로 추출하여 통계정보로 활용
통계정보를 가지고 있지 않거나 여러등의 문제로 사용할수 없거나, 너무 오래되어 신뢰할수 없을 때 적용

SQL 파싱마다 표본을 추출하므로 적은 양의 데이터 처리나 빈번하게 수행되는 경우는 적용하지 말것
동적 표본만으로도 충분히 좋은 수행속도를 낼수 있다거나, 전체 수행시간에 비해 표본 추출시간이 적다거나 매우 오래 수행되는 배치처리인 경우에 적용
실행계획을 위한 최소한의 표본만 사용
표본량에 따라 적중률이 비례하는 것이 아니므로 너무 높은 레벨을 지정할 필요없음
초기값은 기본값을 그대로 적용하고, 필요시 특정 세션에만 지정
레벨을 증가할수록 표본으로 추출하는 블록 수는 증가

독립접으로 존재하는 집합들의 데이터를 사용자 요구와 주어진 다양한 영향요소를 고려하여 최적화된 처리방법을 스스로 결정
옵티마이저 : 구체적 처리과정을 생성  /  사용자 : 요구사항 지시

규칙기준 옵티마이져 : 사전에 정의된 규칙을 기준으로 처리경로 설정
비용기준 옵티마이져: 결과 산출까지의 비용을 기준으로 처리경로 결정

옵티마이져는 현재 존재하는 다양한 영향요소 만을 고려하여 최적경로 수립
옵티마이져가 하는 판단의 정확도를 높이기 위해서는 매우 다양한 것들이 고려

실행계획 고정화 방안
상황에 따른 최적 경로수립이 필요하나 때론 특정 처리경로로 고정이 필요
개념 및 활용방법과 적용 기준을 제시

옵티마이져 내부처리 단계
옵티마이져는 크게 3단계의 내부처리 단계를 가짐
질의 변환기 Query transformer/ 비용산정기 Estimator / 실행계획 생성기 plan generator

SQL 쿼리 재구성
이행성 규칙 : 연산자 유형에 따라 SQL을 재구성
뷰병합 : 뷰쿼리를 엑세스 쿼리로 병합, 엑세스 쿼리를 뷰쿼리로 병합

바인드 함수 PEEKING
최적화는 상수 조건이나 변수 조건이냐에 따라 크게 영향 : 논리적 한계
초기 상수값 참조를 위한 일종의 커닝

함수기반 인덱스

함수기반 인덱스 (function-based index or functional index)

select * from prod where cnt * price between 300 and 320
create index prod_idx1 on prod (cnt  * price)

특징
테이블의 컬럼들을 가공한 논리적 컬럼을 인덱스로 생성한것
INDEX column의 변형에 유연하게 사용할 수 있는 인덱스
- 함수나 수식의 결과로  B*Tree 또는 Bitmap 인덱스 생성
옵티마이저가 쿼리를 파싱하면서 FBI 의 사용 가능여부를 판단
검색효율 향상을 위해 효과적이나 FBI 구성컬럼에 대한 빈번한 입력 , 수정은 부하가중
SQL 문에 사용된 Expression 을 Parsing 하여 일치하는 Expression 을 찾고 Expression Value 를 비교하여 Expression Value 에 대해 Case-Sensitive 함
Dictionary View 에서 index Column 정보 확인 가능

제약사항
사용자 지정함수는 Deterministic 로 선언
Query_rewrite_enabled = true
Query_rewrite_integrity = trusted
필수 권한 : index create / any index create / query rewrite / global query rewrite
함수나 수식의 결과과 NULL 인 경우 사용하지 않음
사용자 지정 함수 재정의 시 --> Disabled or 변경전 함수 유지
owner 의 execute 권한이 revoke 되면 사용불가
disabled 된 인덱스를 사용하려 하면 SQL 은 실패
 - ALTER INDEX .. ENABLE /ALTER INDEX ... REBUILD
 - ALTER INDEX ... UNUSABLE <-- SKIP_UNUSABLE_INDEXES = TRUE 필수
FBI 와 사용자 지정 함수
다른 테이블을 참조하는 사용자 지정함수를 적용한 함수기반 인덱스에서 참조 테이블에 변화가 생긴다면?

1) 참조테이블의 데이터가 변경되면 함수기반 인덱스를 사용하지 못하도록 disabled 시킨 후 수 작업으로 인덱스를 재생성한다
2) 참조테이블의 데이터가 입력,갱신 또는 삭제가 발생하면 모든 함수기반 인덱스를 수정한다 (적용불가)
3) 참조테이블의 데이터 변경을 불허한다 (적용불가)
3) 참조테이블의 데이터 변경이 일어나더라도 함수기반 인덱스의 데이터는 변경시키지 않는다.

거의 변경이 일어나지 않는 코드성 테이블을 참조하는 함수 기반 인덱스는 필요에 따라 적용 가능하나 일반 테이블을 참조하는 함수 기반 인덱스는 권장하지 않는다.

비트맵 인덱스

BITMAP 인덱스의 탄생배경

- 카디널리티카 낮은 컬럼들 때문에 결합 인덱스를 만들어야..

- 조건과 인덱스 결합이 부합되지 않으면 비효율이 ..

- 이런 단점을 모두 해소할 수 있는 방법은 없을까?

BITMAP 인덱스

create bitmap index prod_color on prod(color);

- ROWID 없음

- 어떤값을 갖든지 0 과 1로 표현

  1) 구조

   루트 블록과 브랜치 블록은 B-Tree 인덱스와 동일하나 리프 블록은 아래와 같이 비트맵으    로 구성
   리프블록에는 각각의 컬럼값에 대한 비트들이 저장됨
  2)특성
    비트맵 인덱스에서 추출한 결과를 이용해 비트맵 연산을 통해 처리 할수 있음

    카디널리티가 높은 컬럼에 대해서는 비트맵 인덱스이 장점이 사라진다
    선분형태로 저장되기 때문에 빈번한 수정이 발생하는 컬럼은 인덱스의 크기가 크게 증가     하고 블록레벨 잠금에는 적합하지 않은 경우가 많다.
    NULL 값과 부정형은 존재하지 않기때문에 인덱스를 생성할수 없지만 bitmap 인덱스는        사용 가능
  3)엑세스
     select count(*) from parts where size='MED' and color='RED'
   
     execution plan
     0 select statement
     1 0 sort (aggregate)
     2 1 table access (by index rowid) of 'parts'
     3 2 bitmap conversion (to rowids)
     4 3 bitmap and
     5 4 bitmap index (single value) of 'color_bix'
     6 4 bitmap index (single value) of 'size_bix'

 4) 엑세스 종류
    각각의 비트맵을 추출하고 이들을 연산하여 머지를 한후 최종 결과를 rowid 로 변환하여       테이블 엑세스
    부정형 조인의 경우 1차로 해당 조건의 비트맵에 대하여 MINUS 연산을 하고 다시 NULL       인 비트냅을 MINUS

    bitmap conversion : bitmap <--> Rowid
    bitmap index : bitmap 인덱스를 스캔
    bitmap merge : Range Scan 으로 얻은 몇 개의 Bitmap 을 하나로 Merge
    bitmap minus : 부정형 연산이다 차집합을 구할때
    bitmap or  :두개의 bitmap을 대상으로 bit 열에 대한 or 연산을 수행
    bitmap and : 두개의 bitmap을 대상으로 bit 열에 대한 and 연산을 수행
    bitmap key iteration : 한테이블에서 얻은 각각의 Row들을 특정 bitmap index 에 대해 연       속해서 탐침하여 해당하는 bitmap들을 찾는것

    select * from table1 where col1=123 and col2 <> 'ABC' OR col3 < 100
   1) col1 인덱스에서 123 인 비트맵을 엑세스 하고 col2 인덱스에서 abc 인 비트맵을 엑세스     하여 감산연산을 수행한다
   2) 이 결과에 col2 가 null 인 비트맵을 엑세스 하여 다시 이 연산을 수행해야 한다
   3) col3 < 100 인 조건은 범위 스캔이므로 이 범위의 비트맵을 익어 머지
   4) 이제 2 와 3 에서 수행한 결과에 대해 bitmap or 연산을 수행하여 최종결과 비트맵을 만        들고, 이를 ROWID 로 변경하여 태이블에 엑세스 한다.

   

인덱스 유형과 특징

- 특정 부분을 찾을 수 있는 목차나 색인의 개념
- 활용전략 수립을 위해 내부적 구조, 특징 , 장단점 이해

인덱스 유형

*B - tree 인덱스
가장 기본적인 인덱스의 형태 balanced tree 구조로 생성

 1) 구조
   balanced tree 인덱스는 Root block , branch block , leaf block 으로구성
   leaf block 에 실 저장 위치를 가리키는 rowid 가 존재
   branch block 은 여러 레벨을 가질 수 있음
 2) 범위 스캔
   index = 컬럼 + rowid 로 정렬 -> pga buffer 로 이동
 3) rowid 의 구조 -> 6+3+6+3 display =18 저장 =10
   data  object : database segment 식별정보
   relative file : tablespace 에 상대적 datafile 번호
   block number : row 를 포함하는 data block 번호
   row number : block 에서의 row slot
  인덱스 블록의 전략
  - 최대한 인덱스 컬럼의 수를 줄인다
  - 큰 블록사이를 지정한다
  - PCTREE 를 가능한 최소로 정의한다
  - 키압축 을 활용 (특히, 유일 인덱스, 결합 인덱스)
  - SQL Server의 RID
   8바이트 = page address(4) + file ID(2) + slot number(2)
4) 생성
   데이터 정렬한 후 인덱스 세크먼트의 리프 블록에 기록
   리프블록이 PCTREE 에 도달하면 새로운 리프블록과 브랜치 블록생성
   리프 블록이 추가될 때마다 브랜치 블록에 로우를 추가하며 작업 계속
   브랜치 블록이 PCTREE 에 도달하면 새로운 브랜치 블록과 루트 블록 생성
5) 분활
 결합 인덱스의 구성 : 발생일자 + 항목 <-> 항목 + 발생일자
 인덱스의 재생성 필요
 중간 값의 삽입으로 인해 PCTREE 가 초과되면 기존 블록과 새로 생성되는 블록 모두가 제  한
 맨 앞이나 맨 뒤에 삽입되는 경우에는 기존의 구조에 영향을 미치지 않음
(연결고리만 수정)
6) 삭제 및 갱신
  삭제
    삭제 플래그만 표시하므로 저장공간 낭비, 범위 스캔 대상증가
    리프 블록의 모든 로우가 삭제되더라도 범위 스캔 시에는 엑세스 될수 있음
  갱신
    로우의 삭제와 삽입이 동시에 진행됨
    저장공간의 낭비와 트리 구조의 깊이가 증가
    해결방안은 인덱스의 재생성
    데이터 처리 (DML) 가 많이 수행되는 테이블은 정기적인 재생성 필요
7) 인덱스를 경유한 검색
   lmc = left most child block
   dba = data block address
   terminated or rowid
*B-tree 클러스터 인덱스

*비트맵 인덱스
 최소단위인 BIT 를 이용한 인덱스 형태
 저장공간이 크게 감소
 비트를 연산하여 머지
 B-TREE 인덱스의 많은 단점 해결
 대용량 데이터 처리에 강점

*리버스키 인덱스
 데이터 엑세스 분산 효과
 리버스키 인덱스의 제약사항
 리버스키 인덱스의 활용 방안

*함수기반 인덱스
가공한 논리적인 컬럼에 대해서 인덱스를 생성

클러스터링 테이블

다중 클러스터링의 개념
- 하나의 단위 클러스터 네에 하나이상의 테이블이 존재
- 동일한 클러스터 인덱스를 같는 서로다른 테이블이 같은 클러스터로 같은 위치에 저장되는 것
-클러스터링 테이블은 분리형이나 일체형에서 부탐이던 다량의 조인에 대한 효율을 높여주거나 넓은 범위의 처리에  획기적인 효율 높일수 있는 대안이지만 cost 부담

- 테이블 인덱스 보다 상위 개념이듯 클러스터는 테이블의 상위개념
- 클러스터링 테이블 : 다량의 조인에 대한 효율을 높여주거나 넓은 범위의 처리에 획기적인 효율을 높여줄 수 있습니다.
- 정해진 위치에 데이터를 저장함으로 클러스터링 팩터를 향상 시켜 엑세스 효율을 높이는 저장 방법
장점
-클러스터링에서는 단위 클러스터마다 하나씩 인덱스 로우를 가지고 있음
-단위 클러스터에 여러 로우가 존재하면 한 번 랜덤에 많은 로우를 엑세스
-분포도가 적당하게 넓어야 유리한 것은 기존 인덱스의 단점을 해소하는 경향
cost
엑세스 효율을 향상되지만 삽입,갱신,삭제 부담은 증가 전략적인 인덱스를 구성하여 기존 인덱스를 많이 제거한다면 비용증가 최소화

다중 테이블 클러스터링 실행계획

select ...
from emp e, dept d
where e.deptno = e.deptno
and d.deptno like '11%';

select statement
 nested loops
2) table access (cluster) of dept
1) index (range scan) of 'dept_cluster_idx' (cluster)
3) table access (cluster) of 'emp'

클러스터링 테이블의 부하

sort 된 대상은 입력부하 감소

drop cluster cluster_name
including tables  - 클러스터 내에 테이블이 존재하고 있다면
cascade constraints. - 제약조건이 지정되어 있다면
truncate cluster cluster_name reuse storage; - 스키마 정의를 그대로 사용하겟다면

-drop  table 은 DDL 이지만 Rollback segment 사용
-심각한 부하 발생 우려
-클러스터링 팩터 악화
-Drop 대신 재생성할것
-단위 클러스터를 레코드라 생각하면 클러스터내의 로우들은 필드와 유사 따라서 테이블 Drop은 컬럼 일부를 제거하는 것과 유사하므로 delete 개념이 됨

해쉬 클러스터링의 개념
- 클러스터링 할 컬럼 값을 해쉬 함수로 지정하여 결과 값으로 나온 것을 바탕으로 저장되는것
- 테이블 클러스터링 한다는 것보다 인덱스를 해쉬로 대신하는 개념
-인덱스를 가지지 않고서도 빠른 엑세스가 가능하다는 장점
-size, hashkeys, hash is 파라미터는 변경불가
-'=' 로만 엑세스 가능,
-클러스터가 생성되면서 저장공간이 미리 할당
-저장된 단위 클러스터 보다 많은 로우가 들어오면 오버플로우영역에 저장
-컬럼 값이 고르게 분포되지 않으면 해쉬키 값의 충돌이 발생
-인덱스를 경유하지 않고 해쉬 함수로 계산된 값으로 직접 테이블을 엑세스
-나머지 특징은 인덱스 클러스터와 거리 동일
-인덱스를 생성하지 않고서도 논리적인 계산값을 가지고 데이터 조회

해쉬 클러스터링 활용
-대부분 '=' 로 엑세스 하고,식별자가 차지하는 비율이 높으며. 매우빠른 랜덤 엑세스를 원하는 테이블
-다양한 엑세스 형태를 가지지 않는 테이블일 것
-대량의 데이터를 일정 량의 해쉬 클러스터에 저장시키는 개념을 좀 더 발전한 형태가 바로 해쉬 파티션이므로 이러한 경우라면 차라리 해쉬 파티션을 적용

해쉬 클러스터링의 정의
create cluster [schema].cluster(column datatype, [column datatype]..) - 클러스터와 클러스터 키이 컬럼을 지정
hashkeys integer - 해쉬 클러스터로 생성될 해쉬키 값의 개수 size 값과 더불어 클러스터가 생성될 때 초기 저장공간을 활당하는데 적용
hashkeys expression - 해쉬함수를 지정,사용자가 지정하거나, 기본적으로 제공되는 것을 사용
pctree integer
pctused integer
initrans integer
maxtrans integer
size integer [k|m] - 같은 해쉬키 값을 가지는 로우들을 위해 확보한 단위 클러스터의 크기
storage-clause
tablespace tablespace
해쉬 클러스터의 size 결정
한블록에 지정할 해쉬키의 개수 1
설정할 size 값 : 계산한 size 값

db_block_size/ size 의 결과값은 256을 초과 할수 없다. 즉 한데이터 블록에 최되 256 개 이하로 로우 저장가능

인덱스 일체형 테이블

일체형 테이블의 구조 및 특징
인덱스 구조
 - Branch Node , Leaf Node
 - split 발생 및 overflow area  에 저장

장점

 - 인덱스와 테이블 간의 랜덤 엑세스가 제거되어 효율적으로 넓은 범위를 처리

단점

 - 인덱스에 모든 컬럼이 같이 저장되므로 인덱스 입장에서는 낮은 밀도로저장

 - 인덱스만 스캔할 경우에는 불리

 - split 이 발생하면 최초의 저장 위치를 유지할수 없음

 - 엑세스 형태가 다양하게 나타나는 테이블에는 적용하기 어려움

Logical row ID 와 Physical guess

- Rowid 의 변경으로 인해 발생하는 문제점을 해결하기 위한 방안

- Secondary 인덱스는 논리적 Rowid  와 physical guess 를 보유

- 분리형에서 부담이던 rowid 로 랜덤 엑세스 하는 것보다 더 비효율적인 방식

일체형 테이블의 활용기준 

- 전자 카달로그, 키워드 검색용 테이블, 코드성 테이블, 색인 테이블 , 공간 정보 관리용테이블 , OLAP 의 디멘젼 테이블 등에 적용

- 대부분 기본키로 엑세스 되는 테이블이면서 로우의 길이가 비교적 짧고, 트랜젝션이 빈번하지 않은 테이블에 적용

overflow 의 영역

- 인덱스와 함께 저장될 컬럼의 길이를 줄이고, split 발생 감소효과

- 상대적으로 빈번하게 엑세스 되지 않는 것들을 오버플로우 영역에 저장

- 오버플로우 영역에 대한 전략적인 접근이 필요

(자주 사용되는 컬럼을 오버플로우 영역에 저장을 하게 되면 우리가 피하고자 했던 랜덤 엑세스를 피하지 못하게 되는 결과를 낳지만 자주 사용되지 않는 것들만 오버플로우 영역에 저장하면 일체형이 가진 상당부분의 단점을 해소할수 있다는 것을 의미 )

- 현재 및 향후의 엑세스 패턴을 분석

(모든 정보는 컬럼들 간에 친밀도를 가지고 있으며 또한 중요도 차이가 있으므로 어느정도 미래에 대한 예측이 가능)

secondary key column |  primary  key |  logical row id | physical guess

1. 물리적 위치정보를 사용하지 않은 경우

 - 인덱스를 엑세스 하여 기본키 정보를 얻는다

 - 기본키로 데이터 블록을 엑세스 한다.

2. 물리적 위치정보를 사용하는 경우 

 - 인덱스를 엑세스 하여 물리적 위치정보를 참조한다

 - 물리적 위치정보로 데이터 목록을 엑세스 한 후 비교

 - 기본키 값이 같으면 정상 종료

 - 다르면 기본키로 엑세스 하여 데이터 블록을 가져온다.

 - leaf 블록에는 4byte  데이터 베이스 블록 어드레스 정보 (DBA) 를 가지고 있다.

 - secondary 인덱스가 만들어 지는 순간에는 정확한 값이 생성되지만 오버플로우 되어 split 되는 순간은 변경된다.

 - row 이동이 빈번하게 일어난 정도에 따라 선택적으로 optimizer 에 의하여 선택할수 있다. 

일체형 테이블의 적용 기준

- 중간에 값이 계속 적으로 삽입되지 않는 경우 사용

- 로우의 길이가 너무 길지 않을것

- 다양한 엑세스 형태를 가지지 않을것 

- 주로 키 컬럼으로 랜덤 엑세를 하거나 범위 처리를 하는 경우

- 코드성 테이블 처럼 변화가 적고, 길이가 짧으며, 랜덤 엑세스 위주인 테이블

일체형 테이블 생성을 정의하는 키워드 

- organization index 

인덱스 영역이 저장될 테이블 스페이스를 지정 storage 파라미터도 사용가능 

-tablespace data01

인덱스 블록 내에 예약된 공간의 백분율을 저장 

- pct threshold 

테이블 행을 인덱스와 오버플로우 영역으로 나눌 기준 컬럼을 지정 

- including contents

- 적용시점은 로우가 최초로 입력될때이며 그이후는 pct threshold 에 의하여 인덱스 영역과 오버플로우 영역이 나누어짐

지정됨 임계값을 초과한 로우가 위치할 테이블스페이스를 지정

-overflow tablespace idx01;

일체형에서는 사용자가 체인을 결정할 수 있으며, 그결과에 따라 엑세스 성능에 차이

이 체인은 리프노드의 저장밀도를 높이기 위한 전략

인덱스 영역과 오버플로우 영역을 전약적으로 구분하여 엑세스 효율성 향상 (엑세스 형태 분석필요)

데이터 저장구조와 특징

1.목적이나 용도에 따라 전략적인 결정이 필요
2.대량 데이터나 넓은 범위 처리에서는 큰 영향
3.상황에 따라 변경이 어려워 개념을 이해하고 상황을 파악하여 이상적인 저장형태 결정

1. 테이블과 인덱스가 별도 오브젝트로 구분되어 있는 저장 형태
 - 데이터가 들어오는 순서대로 임의의 위치에 저장되므로 저장 부하는 최소화
 - 내부적 저장방식, 갱신, 삭제 처리
 - 인덱스 경유한 엑세스 절차
 - 장점 : 저장 시의 부하자 적음 , 다량의 데이터를 저리할때는 가치있는 장점
   단점 : 엑세스 시 많은 랜덤 엑세스 발생

* ROWID
 - ROWID 는 물리적인 값이 아니라 주소와 같은 논리적인 값임
 - ROWID 는 단지 ROW 가 있는 위치가 들어있는 방의 이름
 - ROW 의 길이가 변하여 ROW 가 이동하더라도 ROWID 는 변화하지 않음

* 클러스터링 팩터
- 인덱스 컬럼의 정렬순서와 테이블의 저장 순서가 유사한 정도를 표현
  물류 단가와 유사하며 엑세스 효율에 매우 중요
  클러스터링 팩터 향상을 위한 테이블 재생성의 바람직한 방법 이해

2. 인덱스 일체형 테이블 - 테이블과 인덱스가 일체형으로 저장 되어 있는 형태
 - 기본키 인덱스의 리프블록에 테이블 컬럼들이 같이 저장
 -장점 : 테이블 엑세스를 위한 랜덤 제거
  단점 : 인덱스 블록이 대형화 , ROWID 유지 불가 , Secondary 인덱스 사용방안

3. 클러스터링 테이블
 - 정해진 클러스터에 하나이상의 테이블을 같이 저장하는 방식

학습목표 : 관계형 데이터베이스에서 가장 일반적으로 사용되고 있는 테이블 과 인덱스가 분리되어 있는 형식에 대한 저장구조와 클러스터링 팩터를 이해 하므로 적용하는 기준을 익히고 엑세스 효율에 대한 기초지식을 준비
 1. 데이터 저장영역과 인덱스 저장영역에 대한 저장 구조 및 데이터의 삽입, 갱신, 삭제의 내부적인 처리과정과 변화를 이해
 2. 분리형 자장 구조의 장, 단점에 대한 이해
 3. 저장된 데이터의 특성에 따라 달라질수 있는 클러스터링 팩터의 개념 및 클러스터링 팩터를 향상 시킬수 있는 방안을 이해
 4. 분리형 엑세스 영향요소를 이해하고, 넓은 범위의 데이터에 대한 대처방안과 클러스터링 팩터를 향상 시킬수 있는 전략을 숙지

프로젝트 성공의 핵심요소
1. 관계형 데이터 베이스에 맞는 단순 명료한 시스템 설계
무엇을 어떻게 이용할 것인가?
정보와 단절을 어떻게 막을 것인가?
융통성과 통합성을 어떻게 유지할 것인가?
2. 요소 기술 리더의 역활
3. 개발자의 인식 전환
4. 엑세스 효율의 영향 요소  ( 실행 계획 )
 - 데이터 모델 , 데이터 저장형태 , 인덱스 형태 및 구조, SQL 형태 , 통계정보
    옵티마이저 , 메모리의 활용 , 클러스터링 팩터

1. 데이터 저장구조의 종류
분리형 , 일체형 , 인덱스 클러스터링 , Hash 클러스터링

                                                                                                         

테이블 크기별 적용기준

 -  소형 테이블의 경우 일체형 또는 클러스터링 테이블을 적용할수 있으나 중대형 테이블의 경우 대부분의 경우 분리형 테이블이 적당한 저장 구조임