검색 여닫기
메뉴 여닫기
50
0
9
2.1천
DBSTUDY
개인 메뉴 토글
로그인하지 않음
만약 지금 편집한다면 당신의 IP 주소가 공개될 수 있습니다.

틀:SQL: 두 판 사이의 차이

DBSTUDY
다른 명령
← 이전 편집
시각위키텍스트
Oracle (토론 | 기여)
1,339 편집
대문 디자인에 맞춰 틀 디자인 개편 (기존 변수 호환)
 
(사용자 2명의 중간 판 5개는 보이지 않습니다)
1번째 줄: 1번째 줄:
= {{{제목|문서 제목 없음}}} =
<includeonly><div class="{{{속성|}}}" {{#if:{{{id|}}}|id="{{{id}}}"}} style="border:1px solid {{{테두리색|#dde4dc}}};border-radius:8px;overflow:hidden;margin:16px 0;background:{{{배경색|#ffffff}}};"><div style="display:flex;align-items:center;gap:8px;padding:8px 14px;background:#eef3eb;border-bottom:1px solid #dde4dc;"><span class="material-icons-outlined" style="font-size:16px;line-height:1;vertical-align:-4px;color:#256448;" aria-hidden="true">{{{아이콘이름|post_add}}}</span><span style="font-size:11px;font-weight:600;letter-spacing:0.99px;text-transform:uppercase;color:#256448;">{{{타입|sql}}}</span></div><div style="padding:12px 16px;background:{{{코드배경색|#f7f9f6}}};color:{{{코드글자색|#20382e}}};font-family:Consolas,'D2Coding',Menlo,monospace;font-size:13px;line-height:1.7;overflow-x:auto;">{{{SQL|{{{1|}}}}}}</div></div></includeonly><noinclude>
<div style="font-size:12px;color:#6c7b72;margin:0 0 12px;">대문 디자인 틀: [[틀:개요|개요]] · [[틀:타이틀|타이틀]] · [[틀:타이틀 라운드|타이틀 라운드]] · [[틀:요점|요점]] · [[틀:결론|결론]] · [[틀:중요|중요]] · [[틀:설명|설명]] · [[틀:핵심|핵심]] · [[틀:강조|강조]] · [[틀:서브|서브]] · [[틀:SQL|SQL]] · [[틀:관련글|관련글]] · [[틀:난이도|난이도]] · [[틀:평점|평점]] · [[틀:FAQ|FAQ]] · [[:분류:디자인 틀|전체 보기]]</div>
SQL·코드 조각을 보여주는 패널. 대문 디자인(초록 계열 팔레트)에 맞춰 개편했으며, 기존 변수는 그대로 사용할 수 있습니다.


__TOC__
== 사용법 ==
----
<pre>
{{요점|내용= {{{내용}}}}}
{{SQL|SQL=&lt;pre>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));&lt;/pre>}}
----
</pre>
== 기본 설명 ==
{{{본문|
값의 의미 ===
# 범위
- **최소값**: 테이블의 블록 수 (완벽하게 정렬된 경우)
- **최대값**: 테이블의 행 수 (완전히 무작위로 분산된 경우)


### 해석 방법
== 변수 ==
{| class="wikitable"
! 변수 !! 설명 !! 기본값
|-
| <code>SQL / 1</code> || 코드 내용 (&lt;pre> 또는 syntaxhighlight 가능) || 필수
|-
| <code>타입</code> || 머리줄 라벨 || sql
|-
| <code>아이콘이름</code> || 머리줄 아이콘 || post_add
|-
| <code>코드배경색</code> || 코드 영역 배경 || #f7f9f6
|-
| <code>코드글자색</code> || 코드 글자색 || #20382e
|-
| <code>배경색 / 테두리색</code> || 바깥 배경·테두리 ||
|-
| <code>속성 / id</code> || CSS class / id ||
|}


```sql
== 미리보기 ==
-- Clustering Factor 확인
{{SQL|SQL=<pre>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));</pre>}}
SELECT
    index_name,
    table_name,
    clustering_factor,
    num_rows,
    leaf_blocks,
    -- 테이블 블록 수
    (SELECT blocks FROM dba_tables t
    WHERE t.owner = i.table_owner
      AND t.table_name = i.table_name) AS table_blocks
FROM dba_indexes i
WHERE table_owner = 'HR'
  AND table_name = 'EMPLOYEES';
```


**판단 기준:**
[[분류:틀]]
 
[[분류:디자인 틀]]
| Clustering Factor | 상태 {{!}} 의미 {{!}}
</noinclude>
|------------------|------|------|
| ≈ 테이블 블록 수 | **좋음** ✅ {{!}} 데이터가 인덱스 순서대로 잘 정렬됨 {{!}}
| 테이블 블록 수 ~ 행 수 사이 | 보통 {{!}} 부분적으로 정렬됨 {{!}}
| ≈ 테이블 행 수 | **나쁨** ❌ {{!}} 데이터가 무작위로 흩어져 있음 {{!}}
 
---
 
## Clustering Factor의 계산 원리
 
```
인덱스를 순차적으로 스캔하면서:
- 이전 행과 다른 블록에 있으면: CF++
- 이전 행과 같은 블록에 있으면: CF 증가 없음
```
 
### 예제로 이해하기
 
**시나리오 1: 좋은 Clustering Factor**
```
인덱스 순서: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
블록 배치:  [1,2,3] [4,5,6] [7,8,9] [10]
            Block1  Block2  Block3  Block4
 
Clustering Factor = 4 (블록 수)
```
 
**시나리오 2: 나쁜 Clustering Factor**
```
인덱스 순서: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
블록 배치:  [1] [5] [2] [7] [3] [9] [4] [10] [6] [8]
            B1  B2  B3  B4  B5  B6  B7  B8  B9  B10
 
Clustering Factor = 10 (행 수)
```
 
---
 
## 성능에 미치는 영향
 
### 좋은 Clustering Factor (낮은 값)
 
```sql
-- 예: CF = 1,000 (블록 수와 유사)
SELECT * FROM employees
WHERE department_id BETWEEN 10 AND 50;
 
-- 결과: 약 1,000개 블록만 읽음
-- 각 블록에서 여러 행을 읽을 수 있음
```
 
**장점:**
- 인덱스 Range Scan이 효율적
- 물리적 I/O 최소화
- Buffer Cache 효율 증가
 
### 나쁜 Clustering Factor (높은 값)
 
```sql
-- 예: CF = 100,000 (행 수와 유사)
SELECT * FROM employees
WHERE department_id BETWEEN 10 AND 50;
 
-- 결과: 약 50,000개 블록 읽음
-- 각 행마다 다른 블록을 읽어야 함
```
 
**단점:**
- 많은 물리적 I/O 발생
- 동일 블록을 반복 읽기
- Full Table Scan이 더 효율적일 수 있음
 
---
 
## 실무 조회 쿼리
 
### 1. Clustering Factor 상태 확인
 
```sql
SELECT
    i.owner,
    i.table_name,
    i.index_name,
    i.clustering_factor,
    t.num_rows,
    t.blocks AS table_blocks,
    -- CF 품질 지표
    ROUND(i.clustering_factor / NULLIF(t.blocks, 0), 2) AS cf_ratio,
    CASE
        WHEN i.clustering_factor <= t.blocks * 1.2 THEN '좋음 ✅'
        WHEN i.clustering_factor <= t.blocks * 5 THEN '보통 ⚠️'
        WHEN i.clustering_factor <= t.num_rows * 0.5 THEN '나쁨 ❌'
        ELSE '매우 나쁨 🚫'
    END AS cf_status,
    -- 효율성 점수 (낮을수록 좋음)
    ROUND((i.clustering_factor / NULLIF(t.num_rows, 0)) * 100, 2) AS efficiency_pct
FROM dba_indexes i
JOIN dba_tables t ON i.table_owner = t.owner
                  AND i.table_name = t.table_name
WHERE i.owner = 'HR'
  AND t.num_rows > 0
ORDER BY efficiency_pct DESC;
```
 
### 2. 문제가 있는 인덱스 찾기
 
```sql
SELECT
    i.owner,
    i.table_name,
    i.index_name,
    i.clustering_factor,
    t.blocks AS table_blocks,
    t.num_rows,
    ROUND(i.clustering_factor / t.num_rows * 100, 2) AS cf_pct,
    ROUND((t.num_rows * t.avg_row_len) / 1024 / 1024, 2) AS table_size_mb
FROM dba_indexes i
JOIN dba_tables t ON i.table_owner = t.owner
                  AND i.table_name = t.table_name
WHERE i.owner NOT IN ('SYS', 'SYSTEM')
  AND t.num_rows > 10000  -- 작은 테이블 제외
  AND i.clustering_factor > t.blocks * 10  -- CF가 블록 수의 10배 이상
ORDER BY (i.clustering_factor / t.num_rows) DESC;
```
 
### 3. 인덱스 효율성 분석
 
```sql
WITH index_stats AS (
    SELECT
        i.owner,
        i.table_name,
        i.index_name,
        i.clustering_factor,
        t.blocks,
        t.num_rows,
        -- 인덱스로 100행을 읽을 때 예상 블록 접근 수
        CASE
            WHEN t.num_rows > 0 THEN
                ROUND((i.clustering_factor / t.num_rows) * 100, 0)
            ELSE 0
        END AS blocks_per_100_rows
    FROM dba_indexes i
    JOIN dba_tables t ON i.table_owner = t.owner
                      AND i.table_name = t.table_name
    WHERE i.owner = 'HR'
)
SELECT
    owner,
    table_name,
    index_name,
    clustering_factor,
    blocks AS table_blocks,
    num_rows,
    blocks_per_100_rows,
    CASE
        WHEN blocks_per_100_rows <= 10 THEN 'Range Scan 효율적'
        WHEN blocks_per_100_rows <= 50 THEN 'Range Scan 사용 가능'
        ELSE 'Full Scan 고려'
    END AS recommendation
FROM index_stats
ORDER BY blocks_per_100_rows DESC;
```
 
---
 
## Clustering Factor 개선 방법
 
### 방법 1: 테이블 재구성 (MOVE)
 
```sql
-- 인덱스 컬럼 순서대로 테이블 재정렬
ALTER TABLE hr.employees MOVE;
 
-- 인덱스 재구성
ALTER INDEX hr.emp_dept_idx REBUILD;
 
-- 통계 재수집
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');
 
-- CF 확인
SELECT index_name, clustering_factor
FROM user_indexes
WHERE index_name = 'EMP_DEPT_IDX';
```
 
### 방법 2: 테이블을 인덱스 순서로 재생성
 
```sql
-- 임시 테이블 생성 (인덱스 순서대로)
CREATE TABLE employees_new AS
SELECT * FROM employees
ORDER BY department_id, employee_id;
 
-- 원본 테이블 교체
DROP TABLE employees;
RENAME employees_new TO employees;
 
-- 인덱스 재생성
CREATE INDEX emp_dept_idx ON employees(department_id);
 
-- 통계 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');
```
 
### 방법 3: Partitioning 사용
 
```sql
-- 파티션 테이블로 변환
ALTER TABLE employees MODIFY
PARTITION BY RANGE (department_id)
(
    PARTITION p1 VALUES LESS THAN (20),
    PARTITION p2 VALUES LESS THAN (40),
    PARTITION p3 VALUES LESS THAN (60),
    PARTITION p4 VALUES LESS THAN (MAXVALUE)
);
 
-- 로컬 인덱스 생성
CREATE INDEX emp_dept_idx ON employees(department_id) LOCAL;
```
 
### 방법 4: IOT (Index-Organized Table) 사용
 
```sql
-- 기존 테이블을 IOT로 변환
CREATE TABLE employees_iot
ORGANIZATION INDEX
INCLUDING employee_id
OVERFLOW
AS SELECT * FROM employees;
```
 
---
 
## Optimizer가 Clustering Factor를 사용하는 방법
 
```sql
-- 실행 계획에서 Clustering Factor의 영향 확인
EXPLAIN PLAN FOR
SELECT * FROM employees
WHERE department_id BETWEEN 10 AND 50;
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'BASIC +COST'));
 
-- Cost 계산에 Clustering Factor가 사용됨
-- Cost = (Clustering Factor / Table Blocks) * Index Selectivity
```
 
**Optimizer의 판단:**
- **CF가 낮으면**: Index Range Scan 선호
- **CF가 높으면**: Full Table Scan 고려
 
---
 
## 실전 모니터링 스크립트
 
```sql
-- 일일 CF 모니터링
CREATE OR REPLACE VIEW v_clustering_factor_monitor AS
SELECT
    i.owner,
    i.table_name,
    i.index_name,
    i.clustering_factor,
    t.blocks,
    t.num_rows,
    ROUND(i.clustering_factor / NULLIF(t.blocks, 0), 2) AS cf_block_ratio,
    ROUND(i.clustering_factor / NULLIF(t.num_rows, 0) * 100, 2) AS cf_row_pct,
    i.last_analyzed,
    CASE
        WHEN i.clustering_factor <= t.blocks * 2 THEN 'GOOD'
        WHEN i.clustering_factor <= t.blocks * 10 THEN 'FAIR'
        ELSE 'POOR'
    END AS status
FROM dba_indexes i
JOIN dba_tables t ON i.table_owner = t.owner
                  AND i.table_name = t.table_name
WHERE i.owner NOT IN ('SYS', 'SYSTEM', 'XDB', 'OUTLN')
  AND t.num_rows > 1000;
 
-- 사용
SELECT * FROM v_clustering_factor_monitor
WHERE status = 'POOR'
ORDER BY cf_row_pct DESC;
```
 
---
 
## 주의사항
 
1. **통계 정보 최신화**
  ```sql
  -- CF는 통계 수집 시 계산됨
  EXEC DBMS_STATS.GATHER_INDEX_STATS('HR', 'EMP_DEPT_IDX');
  ```
 
2. **DML 작업 후 CF 변화**
  - INSERT/UPDATE/DELETE 후 CF가 나빠질 수 있음
  - 주기적인 통계 수집 필요
 
3. **파티션 테이블**
  - 파티션별로 CF가 다를 수 있음
  - USER_IND_PARTITIONS에서 확인
 
4. **Multiblock Read**
  - CF가 나빠도 db_file_multiblock_read_count가 크면 영향 감소
 
---
 
## 요약
 
| 상황 | Clustering Factor {{!}} 권장 사항 {{!}}
|-----|------------------|---------|
| OLTP, 자주 Range Scan | 테이블 블록 수와 유사 {{!}} 테이블 재구성 고려 {{!}}
| Batch, Full Scan 위주 | 높아도 무관 {{!}} 현상 유지 {{!}}
| 자주 변경되는 테이블 | 주기적으로 나빠짐 {{!}} 파티셔닝 검토 {{!}}
| 대용량 테이블 | 매우 높음 {{!}} IOT 또는 파티셔닝 {{!}}
 
Clustering Factor는 인덱스 성능을 판단하는 핵심 지표이므로, DBA는 정기적으로 모니터링하고 필요시 개선 작업을 수행해야 합니다!
}}}
 
== 예제 ==
<source lang="{{{언어}}}">
{{{예제}}}
</source>
 
== 이전글/다음글 ==
* [[{{{이전글|}}}]]
* [[{{{다음글|}}}]]
 
== 관련 문서 ==
* [[{{{관련1|}}}]]
* [[{{{관련2|}}}]]
 
== FAQ ==
; {{{질문1|질문}}}
: {{{답변1|답변}}}
 
; {{{질문2|질문}}}
: {{{답변2|답변}}}
 
[[분류:{{{분류|oracle}}}]]

2026년 11월 4일 (수) 14:08 기준 최신판

대문 디자인 틀: 개요 · 타이틀 · 타이틀 라운드 · 요점 · 결론 · 중요 · 설명 · 핵심 · 강조 · 서브 · SQL · 관련글 · 난이도 · 평점 · FAQ · 전체 보기

SQL·코드 조각을 보여주는 패널. 대문 디자인(초록 계열 팔레트)에 맞춰 개편했으며, 기존 변수는 그대로 사용할 수 있습니다.

사용법

{{SQL|SQL=<pre>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));</pre>}}

변수

변수 설명 기본값
SQL / 1 코드 내용 (<pre> 또는 syntaxhighlight 가능) 필수
타입 머리줄 라벨 sql
아이콘이름 머리줄 아이콘 post_add
코드배경색 코드 영역 배경 #f7f9f6
코드글자색 코드 글자색 #20382e
배경색 / 테두리색 바깥 배경·테두리
속성 / id CSS class / id

미리보기

sql
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
원본 주소 "https://dbstudy.co.kr/w/index.php?title=틀:SQL&oldid=2018"