데이터베이스 질문과 답변
by 빈이 [SQL Query] INDEX FULL SCAN RANGE SCAN [2017.08.10 17:14:19]
현재 아래와 같이 인덱스힌트를 적용한 SQL을 사용시 INDEX(FULL SCAN) 을 하고 있습니다.
강제로 INDEX(RANGE SCAN) 을 할 수 있는 방법 문의 드립니다.(오라클버전 : 9i)
SELECT /*+ INDEX(T1 IDX_TAB1_COL1) */
*
FROM TAB1 T1
WHERE T1.COL1 IN (
SELECT /*+ INDEX(T2 IDX_TAB2_COL1) */
T2.COL3
FROM TAB2 T2
WHERE T2.COL1 = 'VAL1'
AND T2.COL2 IN ('A','B','C','D')
);
Execution Plan
--------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=854 Card=2K Bytes=3M)
1 0 MERGE JOIN (SEMI) (Cost=854 Card=2K Bytes=3M)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=826 Card=18M Bytes=19G)
3 2 INDEX (FULL SCAN) OF 'IDX_COL1' (NON-UNIQUE) (Cost=26 Card=18M)
4 1 SORT (UNIQUE) (Cost=28 Card=2K Bytes=52K)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'TAB2' (Cost=2 Card=2K Bytes=52K)
6 5 INDEX (RANGE SCAN) OF 'IDX_TAB2_COL1' (NON-UNIQUE) (Cost=1 Card=96K)
전체 실행계획 올려주시면 다른 분들도 같이 고민해보실 수 있을 것 같은데..
혹시
t2를 index full scan 하고 t1과 해시조인하는 것 아닌가요?
플랜 본문내용에 추가했습니다.
쿼리를 그대로 적으신 게 아닌 것 같네요.
쿼리에 조인 조건 이외에는 없는데 INDEX FULL SCAN 후 테이블에 접근하는 하는 걸 보면
WHERE 조건이 더 있을 것 같네요.
서브쿼리에도 RANGE SCAN인 것 보니 WHERE 조건이 더 있을 것 같구요.
혹시 원본 쿼리 올려주실 수 있다면 잠깐 올려주시면 좋을 것 같아요.
T1을 실제 RANGE SCAN할 수 있는데도 FULL SCAN한 것인지 확인이 필요할 것 같습니다.
본문의 SQL 수정했습니다.
TAB2에 대한 내용은 특별한게 없어서 안적었었는데 관심 가져 주셔서 감사합니다.
참고로 TAB1에 DESC 힌트를 주면 DESC RANGE SCAN 을 하긴 하는데 이렇게 해도 PLAN이 변경안될수 있는지 의문이 들어서 일단 보류 한 상태에요.
SELECT /*+ INDEX_DESC(T1 IDX_TAB1_COL1) */
*
FROM TAB1 T1
WHERE T1.COL1 IN (
SELECT /*+ INDEX(T2 IDX_TAB2_COL1) */
T2.COL3
FROM TAB2 T2
WHERE T2.COL1 = 'VAL1'
AND T2.COL2 IN ('A','B','C','D')
);
저의 어설픈 사견으로는 IN 조건의 쿼리를 인라인뷰로 묶어서 hash join 으로 푸는게 낫지 않을까 합니다.
사실 tab1 테이블 update sql 이라서요;;
전체 쿼리 올려주세요.
최상단 SELECT 가 UPDATE 에요.
update /*+ INDEX(T1 IDX_TAB1_COL1) */
SET COL2 = '01'
FROM TAB1 T1
WHERE T1.COL1 IN (
SELECT /*+ INDEX(T2 IDX_TAB2_COL1) */
T2.COL3
FROM TAB2 T2
WHERE T2.COL1 = 'VAL1'
AND T2.COL2 IN ('A','B','C','D')
);
일단은 위의 쿼리가 그대로 쓰신 게 맞고 merge 조인을 해야 하는 상황이 맞다면
t1을 인덱스 scan하는 것은 불필요해 보이고요.
어차피 t1 테이블 full scan 해야 하므로 제 생각에는 table full scan이 나을 것 같습니다.
IDX_TAB1_COL1 index 힌트 없애는 게 나아 보이고요.
그러면 merge 조인을 해야하는 상황이 맞느냐를 체크해보아야 할 것 같은데
t1 테이블이 엄청 크고
서브쿼리의 col3도 엄청 많은 데이터 건수(distinct)가 나온다면 merge 조인도 괜찮은 방법 같구요.
만약 서브쿼리의 결과 건수가 엄청 작고(distinct t2.col3 건수) 결과 건수도 작을 것으로 예상되는데
저런 실행계획이 나왔다면 통계정보 이상으로 저런 실행계획이 나왔을 것 같습니다.
이런 경우는 nl 조인으로 바꿔보시고요.
SELECT
*
FROM TAB1 T1
WHERE T1.COL1 IN (
SELECT /*+ nl_sj */
T2.COL3
FROM TAB2 T2
WHERE T2.COL1 = 'VAL1'
AND T2.COL2 IN ('A','B','C','D')
);
t1 테이블은 크고 서브쿼리의 결과 건수는 크기 않았다면 hash 조인으로 유도해보시는 게 좋을 것 같습니다.
SELECT
*
FROM TAB1 T1
WHERE T1.COL1 IN (
SELECT /*+ hash_sj */
T2.COL3
FROM TAB2 T2
WHERE T2.COL1 = 'VAL1'
AND T2.COL2 IN ('A','B','C','D')
);
index를 쓰는 게 무조건 좋은 게 아니고 table full scan이 무조건 나쁜 게 아닙니다.
예를 들어 국어 사전에서 '가'에서 '자' 까지 모든 데이터를 불러오라고 한다면
사전 인덱스에서 '가'를 읽고 인덱스에 표시된 페이지를 가서 해당 내용을 읽고
그다음 '가가대소'를 읽고 인덱스에 표시된 페이지를 가서 해당 내용을 읽고
그다음 '가가례', '가가린'.. 이렇게 계속 페이지를 왔다갔다하며 읽는 것 보다
그냥 인덱스는 무시하고 사전 처음부터 끝까지 읽는 게 더 효율적이 잖아요.
db에서 인덱스와 테이블과의 관계도 비슷하다고 생각하시면 됩니다.
최종 CARDINALITY가 2K 정도 나오는데 전체 FULL SCAN은 좀 무리이지 않을까?
테이블 풀스캔 뜨면 18M 이 나오는데.
merge join을 해야하는 상황이 맞다는 전체하에;;
2개 테이블모두 1억건 정도의 데이터가 있구요.
INDEX(T1 IDX_TAB1_COL1), INDEX(T2 IDX_TAB2_COL1) 인덱스를 사용 할시에 조회되는 데이터는 약2~4건 정도로 분포 하고 있습니다.
TAB2의 COL2 필드에도 인덱스는 있지만 'A','B','C','D'로 조회할 경우 천만건은 조회되기 때문에 강제로 IDX_TAB2_COL1 인덱스를 태우려고 했습니다.
아래는 주신 nl_sj 힌트를 준 플랜입니다. hash 힌트는 TAB1.COL1 FULL SCAN 하면서 오래걸셔서 nl힌트준것만 플랜 가져왔어요 ㅎ
Execution Plan
--------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1'
2 1 NESTED LOOPS
3 2 VIEW OF 'SYS.VW_NSO_1'
4 3 SORT (UNIQUE)
5 4 CONCATENATION
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'TAB2'
7 6 AND-EQUAL
8 7 INDEX (RANGE SCAN) OF 'IDX_TAB2_COL2' (NON-UNIQUE)
9 7 INDEX (RANGE SCAN) OF 'IDX_TAB2_COL1' (NON-UNIQUE)
10 5 TABLE ACCESS (BY INDEX ROWID) OF 'TAB2'
11 10 AND-EQUAL
12 11 INDEX (RANGE SCAN) OF 'IDX_TAB2_COL2' (NON-UNIQUE)
13 11 INDEX (RANGE SCAN) OF 'IDX_TAB2_COL1' (NON-UNIQUE)
14 5 TABLE ACCESS (BY INDEX ROWID) OF 'TAB2'
15 14 AND-EQUAL
16 15 INDEX (RANGE SCAN) OF 'IDX_TAB2_COL2' (NON-UNIQUE)
17 15 INDEX (RANGE SCAN) OF 'IDX_TAB2_COL1' (NON-UNIQUE)
18 5 TABLE ACCESS (BY INDEX ROWID) OF 'TAB2'
19 18 AND-EQUAL
20 19 INDEX (RANGE SCAN) OF 'IDX_TAB2_COL2' (NON-UNIQUE)
21 19 INDEX (RANGE SCAN) OF 'IDX_TAB2_COL1' (NON-UNIQUE)
22 2 INDEX (RANGE SCAN) OF 'IDX_TAB1_CID' (NON-UNIQUE)
추가로 TAB2.COL2의 경우에는 분포도가 너무 안좋아서 LTRIM() 구문 추가해서 강제로 안타게 변경해봤어요.
SELECT
*
FROM TAB1 T1
WHERE T1.COL1 IN (
SELECT /*+ nl_sj */
T2.COL3
FROM TAB2 T2
WHERE T2.COL1 = 'VAL1'
AND LTRIM(T2.COL2) IN ('A','B','C','D')
);
Execution Plan
--------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1'
2 1 NESTED LOOPS
3 2 VIEW OF 'SYS.VW_NSO_1'
4 3 SORT (UNIQUE)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'TAB2'
6 5 INDEX (RANGE SCAN) OF 'IDX_TAB2_COL1' (NON-UNIQUE)
7 2 INDEX (RANGE SCAN) OF 'IDX_TAB1_COL1' (NON-UNIQUE)
SELECT
*
FROM TAB1 T1
WHERE T1.COL1 IN (
SELECT /*+ nl_sj */
T2.COL3
FROM TAB2 T2
WHERE T2.COL1 = 'VAL1'
AND LTRIM(T2.COL2) IN ('A','B','C','D')
);
위의 SQL을 사용시에는 옵티마이저가 제가 원하는 INDEX를 사용하고 NL 조인까지 하는데
nl_sj 힌트가 PLAN이 바낄 요지가 있나요? nl_sj 힌트에 몰라서요.
한번에 천만건을 UPDATE 하시려고 한건가요?
방법 자체가 잘못된듯 합니다.
저라면 프로시져로 돌려서 UPDATE 하겠습니다.
아찌는 업데이트는 2~4건입니다!
말씀하신 천만건 분포는 TAB2의 COL2 필드이기 때문에
강제로 TAB2의 COL1 인덱스를 태우려고 한거에요.
nl_sj 힌트가 PLAN이 바뀔 요지가 있냐고 물으신 게 어떤 의미인지 잘 모르겠습니다.
merge 조인으로 효율적이지 않은 실행계획을 옵티마이저가 선택했기에
nl_sj 힌트를 써서 nested loop semi 유도 한 거구요.
아 그런데.. 올려주신 최종 플랜보고 생각해보니 굳이 세미 hint는 쓰지 않아도 되었네요.
--9i
SELECT
/*+ordered use_nl(t1)*/ *
FROM TAB1 T1
WHERE T1.COL1 IN (
SELECT /*+unnest*/T2.COL3
FROM TAB2 T2
WHERE T2.COL1 = 'VAL1'
AND T2.COL2||'' IN ('A','B','C','D')
);
힌트로 제어했다면 이게 더 정확했을 것 같습니다.
update라면 요런식으로도 한번 시도해보세요.
merge into t1 x
using
(
select /*+ordered use_nl(b)*/ b.rowid rid
from
(
select distinct col3
from tab2
where col1 = 'VAL1'
and col2||'' in ('A','B','C','D')
) a, t1 b
where a.col3 = b.col1
) y
on
(x.rowid = y.rid)
when matched then
update set col2 = '01'
) 버튼을 클릭하여 작성 하시면 됩니다.