다수 테이블에 대한 OR 조건은 쿼리의 성능을 저하시킬 수 있다.
테스트를 위해 아래와 같이 테이블을 생성하자.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
DROP TABLE t3 PURGE;
CREATE TABLE t1 AS SELECT ROWNUM AS c1, ROWNUM AS c2, LPAD ('X', 100, 'X') AS c3 FROM XMLTABLE ('1 to 1000');
CREATE TABLE t2 AS SELECT * FROM t1;
CREATE TABLE t3 AS SELECT * FROM t1;
CREATE INDEX t1_x1 ON t1 (c1);
CREATE INDEX t1_x2 ON t1 (c2);
CREATE INDEX t2_x1 ON t2 (c1);
CREATE INDEX t2_x2 ON t2 (c2);
CREATE INDEX t3_x1 ON t3 (c1);
CREATE INDEX t3_x2 ON t3 (c2);
아래 쿼리는 t1, t3 테이블에 OR 조건을 사용했다. t1, t3 테이블이 조인되어야 OR 조건이 처리할 수 있다. 이로 인해 t2 테이블이 비효율적으로 액세스되었다.
-- 2
SELECT /*+ LEADING(A) USE_NL(A B C) */
*
FROM t1 a, t2 b, t3 c
WHERE b.c1 = a.c1
AND c.c1 = b.c1
AND ( a.c2 = 1
OR c.c2 = 1);
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 100 |
| 1 | NESTED LOOPS | | 1 | 1 | 100 |
| 2 | NESTED LOOPS | | 1 | 1000 | 83 |
| 3 | NESTED LOOPS | | 1 | 1000 | 60 |
| 4 | TABLE ACCESS FULL | T1 | 1 | 1000 | 20 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1000 | 1000 | 40 |
|* 6 | INDEX RANGE SCAN | T2_X1 | 1000 | 1000 | 23 |
|* 7 | INDEX RANGE SCAN | T3_X1 | 1000 | 1000 | 23 |
|* 8 | TABLE ACCESS BY INDEX ROWID | T3 | 1000 | 1 | 17 | -- !
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("B"."C1"="A"."C1")
7 - access("C"."C1"="B"."C1")
8 - filter(("A"."C2"=1 OR "C"."C2"=1))
WHERE 절을 변경하여 조인 순서를 t1, t3, t2로 변경하면 t2 테이블에 대한 비효율적인 액세스를 개선할 수 있다.
-- 3
SELECT /*+ LEADING(A) USE_NL(A C B) */
*
FROM t1 a, t3 c, t2 b
WHERE c.c1 = a.c1
AND b.c1 = c.c1
AND ( a.c2 = 1
OR c.c2 = 1);
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 64 |
| 1 | NESTED LOOPS | | 1 | 1 | 64 |
| 2 | NESTED LOOPS | | 1 | 1 | 63 |
| 3 | NESTED LOOPS | | 1 | 1 | 60 |
| 4 | TABLE ACCESS FULL | T1 | 1 | 1000 | 20 |
|* 5 | TABLE ACCESS BY INDEX ROWID| T3 | 1000 | 1 | 40 | -- !
|* 6 | INDEX RANGE SCAN | T3_X1 | 1000 | 1000 | 23 |
|* 7 | INDEX RANGE SCAN | T2_X1 | 1 | 1 | 3 |
| 8 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 1 | 1 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(("A"."C2"=1 OR "C"."C2"=1))
6 - access("C"."C1"="A"."C1")
7 - access("B"."C1"="C"."C1")
OR 조건이 단순하다면 USE_CONCAT 힌트를 통해 OR 조건을 분기할 수 있다.
-- 4
SELECT /*+ USE_CONCAT USE_NL(A B C) */
*
FROM t1 a, t2 b, t3 c
WHERE b.c1 = a.c1
AND c.c1 = b.c1
AND ( a.c2 = 1
OR c.c2 = 1);
-----------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 21 |
| 1 | CONCATENATION | | 1 | 1 | 21 |
| 2 | NESTED LOOPS | | 1 | 1 | 12 |
| 3 | NESTED LOOPS | | 1 | 1 | 11 |
| 4 | NESTED LOOPS | | 1 | 1 | 8 |
| 5 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 1 | 4 |
|* 6 | INDEX RANGE SCAN | T3_X2 | 1 | 1 | 3 | -- !
| 7 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 1 | 4 |
|* 8 | INDEX RANGE SCAN | T2_X1 | 1 | 1 | 3 |
|* 9 | INDEX RANGE SCAN | T1_X1 | 1 | 1 | 3 |
| 10 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 1 | 1 |
| 11 | NESTED LOOPS | | 1 | 0 | 9 |
| 12 | NESTED LOOPS | | 1 | 1 | 8 |
| 13 | NESTED LOOPS | | 1 | 1 | 6 |
| 14 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 3 |
|* 15 | INDEX RANGE SCAN | T1_X2 | 1 | 1 | 2 | -- !
| 16 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 1 | 3 |
|* 17 | INDEX RANGE SCAN | T2_X1 | 1 | 1 | 2 |
|* 18 | INDEX RANGE SCAN | T3_X1 | 1 | 1 | 2 |
|* 19 | TABLE ACCESS BY INDEX ROWID | T3 | 1 | 0 | 1 | -- !
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("C"."C2"=1)
8 - access("C"."C1"="B"."C1")
9 - access("B"."C1"="A"."C1")
15 - access("A"."C2"=1)
17 - access("B"."C1"="A"."C1")
18 - access("C"."C1"="B"."C1")
19 - filter(LNNVL("C"."C2"=1))
12.1 버전부터 OR_EXPAND 힌트를 사용할 수 있다.
-- 5
SELECT /*+ OR_EXPAND USE_NL(A B C) */
*
FROM t1 a, t2 b, t3 c
WHERE b.c1 = a.c1
AND c.c1 = b.c1
AND ( a.c2 = 1
OR c.c2 = 1);
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 21 |
| 1 | VIEW | VW_ORE_99C9837B | 1 | 1 | 21 |
| 2 | UNION-ALL | | 1 | 1 | 21 |
| 3 | NESTED LOOPS | | 1 | 1 | 12 |
| 4 | NESTED LOOPS | | 1 | 1 | 11 |
| 5 | NESTED LOOPS | | 1 | 1 | 8 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 1 | 4 |
|* 7 | INDEX RANGE SCAN | T1_X2 | 1 | 1 | 3 | -- !
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 1 | 4 |
|* 9 | INDEX RANGE SCAN | T2_X1 | 1 | 1 | 3 |
|* 10 | INDEX RANGE SCAN | T3_X1 | 1 | 1 | 3 |
| 11 | TABLE ACCESS BY INDEX ROWID | T3 | 1 | 1 | 1 |
| 12 | NESTED LOOPS | | 1 | 0 | 9 |
| 13 | NESTED LOOPS | | 1 | 1 | 8 |
| 14 | NESTED LOOPS | | 1 | 1 | 6 |
| 15 | TABLE ACCESS BY INDEX ROWID BATCHED| T3 | 1 | 1 | 3 |
|* 16 | INDEX RANGE SCAN | T3_X2 | 1 | 1 | 2 | -- !
| 17 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 1 | 3 |
|* 18 | INDEX RANGE SCAN | T2_X1 | 1 | 1 | 2 |
|* 19 | INDEX RANGE SCAN | T1_X1 | 1 | 1 | 2 |
|* 20 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 0 | 1 | -- !
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("A"."C2"=1)
9 - access("B"."C1"="A"."C1")
10 - access("C"."C1"="B"."C1")
16 - access("C"."C2"=1)
18 - access("C"."C1"="B"."C1")
19 - access("B"."C1"="A"."C1")
20 - filter(LNNVL("A"."C2"=1))
'Oracle > Tuning' 카테고리의 다른 글
| TIMESTAMP 타입과 SYSTIMESTAMP 함수 (0) | 2018.12.01 |
|---|---|
| SQL 메타 데이터 (0) | 2018.12.01 |
| ROWNUM = 1 패턴 (0) | 2018.11.28 |
| 바인드 변수 값 조회 (1) | 2018.11.09 |
| 'PLAN_TABLE' is old version (0) | 2018.11.08 |
