NL 조인의 이너에 INLIST ITERATOR 오퍼레이션을 사용하면 과도한 블록 I/O가 발생할 수 있습니다.
테스트를 위해 아래와 같이 t1, t2 테이블과 t2_x1 인덱스를 생성하겠습니다.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
CREATE TABLE t1(c1) AS SELECT ROWNUM FROM XMLTABLE ('1 to 100');
CREATE TABLE t2 (c1, c2, c3, c4) AS
SELECT CEIL (ROWNUM / 100)
, CEIL (NVL (NULLIF (MOD (ROWNUM, 100), 0), 100) / 10)
, NVL (NULLIF (MOD (ROWNUM, 10), 0), 10)
, LPAD ('X', 100, 'X')
FROM XMLTABLE ('1 to 1000000');
CREATE INDEX t2_x1 ON t2 (c1, c2, c3);
아래 쿼리는 INLIST ITERATOR 오퍼레이션으로 인해 t2_x1 인덱스를 5번 액세스합니다.
-- 2
SELECT *
FROM t2
WHERE c1 = 1
AND c2 IN (1, 2, 3, 4, 5)
AND c3 BETWEEN 1 AND 10;
----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 50 | 15 |
| 1 | INLIST ITERATOR | | 1 | 50 | 15 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 5 | 50 | 15 |
|* 3 | INDEX RANGE SCAN | T2_X1 | 5 | 50 | 13 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C1"=1 AND (("C2"=1 OR "C2"=2 OR "C2"=3 OR "C2"=4 OR "C2"=5)) AND "C3">=1 AND "C3"<=10)
아래 쿼리는 NL 조인의 이너에 INLIST ITERATOR 오퍼레이션을 사용합니다. t2_x1 인덱스가 500회 액세스되고, 236개의 블록 I/O가 발생합니다.
-- 3
SELECT /*+ LEADING(A) USE_NL(B) */
*
FROM t1 a, t2 b
WHERE b.c1 = a.c1
AND b.c2 IN (1, 2, 3, 4, 5)
AND b.c3 BETWEEN 1 AND 10;
--------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5000 | 710 |
| 1 | NESTED LOOPS | | 1 | 5000 | 710 |
| 2 | NESTED LOOPS | | 1 | 5000 | 499 |
| 3 | TABLE ACCESS FULL | T1 | 1 | 100 | 52 |
| 4 | INLIST ITERATOR | | 100 | 5000 | 447 | -- !
|* 5 | INDEX RANGE SCAN | T2_X1 | 500 | 5000 | 447 | -- 236 = 447 - 211
| 6 | TABLE ACCESS BY INDEX ROWID| T2 | 5000 | 5000 | 211 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("B"."C1"="A"."C1" AND (("B"."C2"=1 OR "B"."C2"=2 OR "B"."C2"=3 OR "B"."C2"=4 OR "B"."C2"=5)) AND
"B"."C3">=1 AND "B"."C3"<=10)
아래 쿼리는 IN 조건을 가공하여 INLIST ITERATOR 오퍼레이션을 사용하지 않도록 합니다. t2_x1 인덱스의 액세스가 500회에서 100회로 줄어들어 블록 I/O도 235개에서 117개로 감소합니다.
-- 4
SELECT /*+ LEADING(A) USE_NL(B) */
*
FROM t1 a, t2 b
WHERE b.c1 = a.c1
AND b.c2 + 0 IN (1, 2, 3, 4, 5) -- !
AND b.c3 BETWEEN 1 AND 10;
--------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5000 | 591 |
| 1 | NESTED LOOPS | | 1 | 5000 | 591 |
| 2 | NESTED LOOPS | | 1 | 5000 | 380 |
| 3 | TABLE ACCESS FULL | T1 | 1 | 100 | 52 |
|* 4 | INDEX RANGE SCAN | T2_X1 | 100 | 5000 | 328 | -- 117 = 328 - 211
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 5000 | 5000 | 211 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("B"."C1"="A"."C1" AND "B"."C3">=1 AND "B"."C3"<=10)
filter((("B"."C2"+0=1 OR "B"."C2"+0=2 OR "B"."C2"+0=3 OR "B"."C2"+0=4 OR "B"."C2"+0=5) AND "B"."C3">=1 AND
"B"."C3"<=10))
아래 쿼리는 IN 조건이 포함된 범위 조건을 추가하여 t2_x1 인덱스의 블록 I/O를 117개에서 103개로 줄입니다.
-- 5
SELECT /*+ LEADING(A) USE_NL(B) */
*
FROM t1 a, t2 b
WHERE b.c1 = a.c1
AND b.c2 BETWEEN 1 AND 5 -- !
AND b.c2 + 0 IN (1, 2, 3, 4, 5) -- !
AND b.c3 BETWEEN 1 AND 10;
--------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5000 | 577 |
| 1 | NESTED LOOPS | | 1 | 5000 | 577 |
| 2 | NESTED LOOPS | | 1 | 5000 | 366 |
| 3 | TABLE ACCESS FULL | T1 | 1 | 100 | 52 |
|* 4 | INDEX RANGE SCAN | T2_X1 | 100 | 5000 | 314 | -- 103 = 314 - 211
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 5000 | 5000 | 211 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("B"."C1"="A"."C1" AND "B"."C2">=1 AND "B"."C3">=1 AND "B"."C2"<=5 AND "B"."C3"<=10)
filter((("B"."C2"+0=1 OR "B"."C2"+0=2 OR "B"."C2"+0=3 OR "B"."C2"+0=4 OR "B"."C2"+0=5) AND "B"."C3">=1 AND
"B"."C3"<=10))