조건 :
1.고객은 100만 명이고, 거주지역으로는 15개의 값이 있으며 분포는 균일하다고 가정
2.쿼리1과 쿼리4는 수행빈도가 매우 높고, 쿼리2와 3은 그다지 높지 않음
3.액세스 효율을 높이는 것도 중요하지만 인덱스 개수를 최소화하는 것도 중요함.
-쿼리1
select * from 고객
where 고객번호 = :no;
-쿼리2
select * from 고객
where 연령 = :age
and 성별 = :gender
and 이름 like :name || '%';
-쿼리3
select * from 고객
where 연령 between :age1 and :age2
and 거주지역 = :region
and 등록일 like :rdate || '%';
-쿼리4
select /*+ ordered use_nl(b) */ * from 주문 a, 고객 b
where a.거래일자 = :tdate
and a.상품번호 = :good
and b.고객번호 = a.고객번호
and b.거주지역 = :region;
-선택도가 높은 인덱스는 생성해 봐야 효용가치가 별로 없다.
'='조건으로 항상 사용되는 컬럼들을 앞쪽에 위치시켰다면, 그 중 선택도가 낮은 것을 앞쪽에 두려는 노력은 의미 없는 or 손해
선택도가 높은 컬럼을 선두에 두면 나중에 이 컬럼이 조건절에서 누락되거나 범위검색 조건으로 조회되는 일이 생기더라도 오라클 9i부터 제공되는 Index Skip Scan을
효과적으로 활용할 수 있어 유리함.(Index Skip Scan 또는 IN-List는 Distinct Value개수가 적고, 후행 컬럼의 Distinct Value개수가 많아야 효율적이다.)
참고, 톰 카이트(Tom Kyte)의 설명에 의하면 변별력이 좋은 컬럼(선택도가 낮은 컬럼)을 선두에 두는 것이 좋다는 상식은 오라클 5 버전에서 사용하던 인덱스 압축 방식
때문에 생긴 것이고 그런 특징은 오라클 6에서 로우 단위 Lock이 구현되면서 사라졌다고 한다.
-선택도가 높은 컬럼을 인덱스 선두에 두면 나중에 범위검색 조건이 사용되거나 아예 누락되더라도 Index Skip Scan또는 IN-List를 활용할 수 있어 유리한다.
(단, Distinct Value 개수가 충분히 적은지가 관건)
-선택도가 낮은 컬럼을 선두에 두면 이를 범위검색 조건으로 조회하는 일이 생겼을 때 불리하지만, 입력 값의 범위가 좁다면 비효율이 크지 않아
Index Skip Scan이나 IN-List를 활용하지 못하더라도 오리려 유리할 수 있다.
-범위검색 조건을 사이에 둔 컬럼끼리는 선택도가 낮은 컬럼을 앞쪽에 두는 것이 유리함.
예 : where 고객번호 = :a and 상품번호 =:b and 거래일자 between :c and :d
조건 : 고객 : 100만명 , 삼품개수 : 10,000
X01 인덱스 : 고객번호 + 거래일자 + 상품번호 => 변별력(선택도가 낮은)이 좋은 고객번호에 의해 스캔 범위가 최소화되기 때문에 비효율이 크지 않음
X02 인덱스 : 상품번호 + 거래일자 + 고객번호 => 변별력(선택도가 낮은)이 좋은 고객번호가 필터 역할을 하므로 비효율적이다.
질문 : 아래와 같은 조건절 하에서 만약 거래일자를 선두에 둔 '단 하나'의 인덱스를 생성하기로 하였고, 후행 컬럼으로서 상품번호와 고객번호 둘 중 하나만 '선택'하라면 어느 쪽을 택하겠는가?
(과정: 두 검색조건은 사용빈도가 같다. 고객 : 100만명 , 삼품개수 : 10,000)
검색조건 1 : where 거래일자 = :a and 상품번호 =:b
검색조건 2 : where 거래일자 = :a and 고객번호 =:b
결론 : 선택도가 낮은 고객번호를 선택하는 것이 현명
- 더 적은 양의 테이블 Random 액세스가 발생
참고: 결합 인덱스 컬럼 간 순서를 정할 때는, 개별 컬럼의 선택도보다는 조건절에서 어떤형태로 자주 사용되는지, 사용빈도는 어느 쪽이 높은지, 데이터를 빠르게 검색하는 데에 어느 쪽 효용성이 높은지 등이 더 중요한 판단 기준임.
-인덱스는 항상 정렬 상태를 유지(order by, group by를 위한 소트 연산을 생략가능)
-조건절에 사용되지 않은 컬럼이더라도 소트 연산을 대체할 목적으로 인덱스 구성에 포함시킴
-order by, group by 절에서 소트 오퍼레이션 생략 조건
1. 인덱스 컬럼 구성과 같은 순서로 누락 없이(뒤쪽 컬럼이 누락되는 것은 상관없음) order by절에 기술해 주어야함.
단. 인덱스 구성 컬럼이 조건절에서 '='연산자로 비교된다면, 그 컬럼은 order by절에서 누락되거나 인덱스와 다른 순서로 기술하더라도 상관없음.
SQL> create table t
2 as
3 select rownum a, rownum b, rownum c, rownum d, rownum e
4 from dual
5 connect by level < 100
6 /
테이블이 생성되었습니다.
SQL> create index t_idx on t( a, b, c, d );
인덱스가 생성되었습니다.
SQL> select * from t;
A B C D E
---------- ---------- ---------- ---------- ----------
1 1 1 1 1
2 2 2 2 2
|
|
|
99 99 99 99 99
99 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=99 Bytes=6435)
1 0 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=3 Card=99 Bytes=6435)
예)인덱스 : create index t_idx on t( a, b, c, d);
SQL> select * from t where a = 1 order by a, b, c;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=65)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card=1 Bytes=65)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)
SQL> select * from t where a = 1 and b = 1 order by c, d;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=65)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card= 1 Bytes=65)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)
SQL> select * from t where a = 1 and c = 1 order by b, d;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=65)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card= 1 Bytes=65)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)
SQL> select * from t where a = 1 and b = 1 order by a, c, b, d;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=65)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card= 1 Bytes=65)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)
2. order by절에 기술한 순서가 인덱스 순서와 일치하기만 한다면 조건절에서 어떤 연산자로 비교되더라도 정렬 작업이 생략가능
SQL> select * from t
2 where a between 1 and 2
3 and b not in(1, 2)
4 and c between 2 and 3
5 order by a, b, c, d;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=65)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card= 1 Bytes=65)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)
SQL> select * from t
2 where a between 1 and 2
3 and c between 2 and 3
4 order by a, b, c;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=65)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card= 1 Bytes=65)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)
SQL> select * from t
2 where a between 1 and 2
3 and b <> 3
4 order by a, b, c;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=2 Bytes=130 )
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card=2 Bytes=130)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)
3. 정렬 작업이 생략되지만 인덱스 선두 컬럼이 조건절에 누락됐으므로 Index Full Scan방식으로 처리
SQL> select /*+ index(t) */ * from t
2 where b between 2 and 3
3 order by a, b, c, d;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=2 Bytes=130)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card= 2 Bytes=130)
2 1 INDEX (FULL SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)
4. 소트 오퍼레이션 대체가 불가능한 경우
SQL> select * from t where a = 1 order by c;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=65)
1 0 SORT (ORDER BY) (Cost=3 Card=1 Bytes=65)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card=1 Bytes=65)
3 2 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)
SQL> select * from t
2 where a =1
3 and b between 1 and 2
4 order by c, d;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=65)
1 0 SORT (ORDER BY) (Cost=3 Card=1 Bytes=65)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card=1 Bytes=65)
3 2 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)
SQL> select * from t
2 where a = 1
3 and b between 1 and 2
4 order by a, c, b;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=65)
1 0 SORT (ORDER BY) (Cost=3 Card=1 Bytes=65)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card=1 Bytes=65)
3 2 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)
그림1-57 참조