๋ทฐ ๋ณํฉ์ด ๋์ํ์ง ์์ ์ฟผ๋ฆฌ ์ฑ๋ฅ์ด ์ ํ๋ ์ฌ๋ก๋ฅผ ์ดํด๋ณด์.
ํ ์คํธ๋ฅผ ์ํด ์๋์ ๊ฐ์ด ํ ์ด๋ธ๊ณผ ๋ทฐ๋ฅผ ์์ฑํ์.
-- 1 DROP TABLE t1 PURGE; DROP TABLE t2 PURGE; DROP TABLE t3 PURGE; CREATE TABLE t1 AS SELECT ROWNUM AS c1, ROWNUM AS c2 FROM XMLTABLE ('1 to 10000'); CREATE TABLE t2 AS SELECT * FROM t1; CREATE TABLE t3 AS SELECT * FROM t1; CREATE UNIQUE INDEX t1_u1 ON t1 (c2); CREATE UNIQUE INDEX t2_u1 ON t2 (c1); CREATE UNIQUE INDEX t3_u1 ON t3 (c1); CREATE OR REPLACE VIEW v1 AS SELECT * FROM t2;
์๋ ์ฟผ๋ฆฌ๋ ํ ์ด๋ธ๊ณผ ๋ทฐ๋ฅผ ์กฐ์ธํ๋ค. v1 ๋ทฐ๊ฐ ๋ณํฉ๋ ๊ฒ์ ํ์ธํ ์ ์๋ค.
-- 2 SELECT * FROM t1 a, v1 b WHERE a.c2 = 1 AND b.c1 = a.c1; ----------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 | 6 | | 1 | NESTED LOOPS | | 1 | 1 | 1 | 6 | | 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 1 | 3 | |* 3 | INDEX UNIQUE SCAN | T1_U1 | 1 | 1 | 1 | 2 | | 4 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 1 | 1 | 3 | |* 5 | INDEX UNIQUE SCAN | T2_U1 | 1 | 1 | 1 | 2 | -----------------------------------------------------------------------------------
์๋์ ๊ฐ์ด ๋ทฐ์ ์ค์นผ๋ผ ์๋ธ ์ฟผ๋ฆฌ์ ์ค์นผ๋ผ ์๋ธ ์ฟผ๋ฆฌ์ ๊ฒฐ๊ณผ๋ฅผ ์ฌ์ฉํ ํํฐ ์กฐ๊ฑด์ ์ถ๊ฐํด๋ณด์.
-- 3 CREATE OR REPLACE VIEW v1 AS SELECT * FROM (SELECT a.c1 , (SELECT x.c2 FROM t3 x WHERE x.c1 = a.c2) AS c2 --> ADD FROM t2 a) WHERE c2 = 1 --> ADD ;
์ฟผ๋ฆฌ๋ฅผ ๋ค์ ์ํํด๋ณด๋ฉด v1 ๋ทฐ๊ฐ ๋ณํฉ๋์ง ์์ ๊ฒ์ ํ์ธํ ์ ์๋ค. ์ค์นผ๋ผ ์๋ธ ์ฟผ๋ฆฌ๊ฐ 10,000๋ฒ ์ํ๋์ด ๋ธ๋ก I/O๊ฐ 10,175๊ฐ๋ก ์ฆ๊ฐํ๋ค.
-- 4 SELECT /*+ MERGE(B) */ * FROM t1 a, v1 b WHERE a.c2 = 1 AND b.c1 = a.c1; ----------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 | 10175 | | 1 | TABLE ACCESS BY INDEX ROWID | T3 | 10000 | 1 | 10000 | 10142 | |* 2 | INDEX UNIQUE SCAN | T3_U1 | 10000 | 1 | 10000 | 142 | | 3 | NESTED LOOPS | | 1 | 1 | 1 | 10175 | | 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 1 | 3 | |* 5 | INDEX UNIQUE SCAN | T1_U1 | 1 | 1 | 1 | 2 | |* 6 | VIEW | | 1 | 1 | 1 | 10172 | | 7 | TABLE ACCESS FULL | T2 | 1 | 10000 | 10000 | 30 | -----------------------------------------------------------------------------------
ํํธ๋ฅผ ์ถ๊ฐํด๋ JPPD๊ฐ ๋์ํ์ง ์๋ ๊ฒ์ ํ์ธํ ์ ์๋ค.
-- 5 SELECT /*+ LEADING(A) USE_NL(B) PUSH_PRED(B) */ * FROM t1 a, v1 b WHERE a.c2 = 1 AND b.c1 = a.c1; ----------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 | 10175 | | 1 | TABLE ACCESS BY INDEX ROWID | T3 | 10000 | 1 | 10000 | 10142 | |* 2 | INDEX UNIQUE SCAN | T3_U1 | 10000 | 1 | 10000 | 142 | | 3 | NESTED LOOPS | | 1 | 1 | 1 | 10175 | | 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 1 | 3 | |* 5 | INDEX UNIQUE SCAN | T1_U1 | 1 | 1 | 1 | 2 | |* 6 | VIEW | | 1 | 1 | 1 | 10172 | | 7 | TABLE ACCESS FULL | T2 | 1 | 10000 | 10000 | 30 | -----------------------------------------------------------------------------------
์กฐ์ธ์ ์์ฐํฐ ์กฐ์ธ์ผ๋ก ๋ณ๊ฒฝํ๋ฉด JPPD๊ฐ ๋์ํ์ง๋ง, ๋ทฐ๋ฅผ ์ฌ์ฉํ๋ ์ฟผ๋ฆฌ๊ฐ ๋ค์์ธ ๊ฒฝ์ฐ ๊ทผ๋ณธ์ ์ธ ํด๋ฒ์ด ๋ ์ ์๋ค.
-- 6 SELECT /*+ LEADING(A) USE_NL(B) PUSH_PRED(B) NO_OUTER_JOIN_TO_INNER */ * FROM t1 a, v1 b WHERE a.c2 = 1 AND b.c1(+) = a.c1 AND b.c1 IS NOT NULL; ------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 | 9 | | 1 | TABLE ACCESS BY INDEX ROWID | T3 | 1 | 1 | 1 | 3 | |* 2 | INDEX UNIQUE SCAN | T3_U1 | 1 | 1 | 1 | 2 | |* 3 | FILTER | | 1 | | 1 | 9 | | 4 | NESTED LOOPS OUTER | | 1 | 1 | 1 | 9 | | 5 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 1 | 1 | 3 | |* 6 | INDEX UNIQUE SCAN | T1_U1 | 1 | 1 | 1 | 2 | |* 7 | VIEW PUSHED PREDICATE | | 1 | 1 | 1 | 6 | | 8 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 1 | 1 | 3 | |* 9 | INDEX UNIQUE SCAN | T2_U1 | 1 | 1 | 1 | 2 | -------------------------------------------------------------------------------------
ํด๋น ํ์์ ์ค์นผ๋ผ ์๋ธ ์ฟผ๋ฆฌ์ ๊ฒฐ๊ณผ๋ฅผ ํํฐ ์กฐ๊ฑด์ผ๋ก ์ฌ์ฉํ๊ธฐ ๋๋ฌธ์ด๋ค. t3 ํ ์ด๋ธ์ c1 ์ด์ด ๊ณ ์ ํ๋ฏ๋ก ๋ทฐ์ ์ค์นผ๋ผ ์๋ธ ์ฟผ๋ฆฌ๋ฅผ ์กฐ์ธ์ผ๋ก ๋ณ๊ฒฝํ ์ ์๋ค.
-- 7 CREATE OR REPLACE VIEW v1 AS SELECT a.c1, b.c2 FROM t2 a, t3 b WHERE b.c1(+) = a.c2 AND b.c2(+) = 1;
v1 ๋ทฐ๊ฐ ๋ณํฉ๋์ด ๋ธ๋ก I/O๊ฐ 9๊ฐ๋ก ๊ฐ์ํ ๊ฒ์ ํ์ธํ ์ ์๋ค.
-- 8 SELECT * FROM t1 a, v1 b WHERE a.c2 = 1 AND b.c1 = a.c1; ------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1 | 9 | | 1 | NESTED LOOPS OUTER | | 1 | 1 | 1 | 9 | | 2 | NESTED LOOPS | | 1 | 1 | 1 | 6 | | 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 1 | 3 | |* 4 | INDEX UNIQUE SCAN | T1_U1 | 1 | 1 | 1 | 2 | | 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 1 | 1 | 3 | |* 6 | INDEX UNIQUE SCAN | T2_U1 | 1 | 1 | 1 | 2 | |* 7 | TABLE ACCESS BY INDEX ROWID | T3 | 1 | 1 | 1 | 3 | |* 8 | INDEX UNIQUE SCAN | T3_U1 | 1 | 1 | 1 | 2 | ------------------------------------------------------------------------------------
'Oracle > Tuning' ์นดํ ๊ณ ๋ฆฌ์ ๋ค๋ฅธ ๊ธ
์ฌ๋ถ ์์ฑ์ผ๋ก ์ธํ ์ฑ๋ฅ ์ ํ ์ฌ๋ก #2 (0) | 2020.07.09 |
---|---|
์ฌ๋ถ ์์ฑ์ผ๋ก ์ธํ ์ฑ๋ฅ ์ ํ ์ฌ๋ก #1 (0) | 2020.07.08 |
์กฐ์ธ์ ๋ฐ๋ฅธ ์ฌ์ฉ์ ํจ์์ ๋์ (0) | 2020.03.21 |
์ ๊ธฐ์ค ์ด๋ ฅ ์กฐํ (0) | 2020.03.16 |
INDEX ํํธ์ ์ธ ๊ฐ์ง ๋ฐฉ์ (0) | 2020.02.23 |