새로쓴 대용량 데이터베이스솔루션 1 (2011년)
필터(Filter)형 세미조인
- 필터 : 말 그대로 '골라내는 작업'방법을 말한다.
- 확인자 역할
- 먼전 수행하여 엑세스한 결과를 서브쿼리를 통해 체크하여 취할 것인지, 아니면 버려야 할 것인지를 결정하는 역할이다.
- 이 형식의 세미조인은 이러한 작업을 보다 효율적으로 수행하기 위해 버퍼( Buffer ) 내에 이전의 값을 저장해 두었다가 대응되는 집합을 엑세스하기 전에 먼저 저장된 값과 비교함으로써 액세스를 최소화하는 방법이다.
- 조인 : 연결된 로우들을 다음 단계의 처리를 위해 보관할 필요가 있지만 필터처리에서는 단지 선별을 위해서만 사용하므로 체크에 필요로 하지만 필터처리에서는 단지 선별을 위해서만 사용하므로 체크에 필요한 최소의 정보만 잠시 저장되어 있을 뿐이다.
준비 스크립트
DROP TABLE "ORDER" PURGE
DROP TABLE DEPT PURGE
CREATE TABLE "ORDER" AS
SELECT LEVEL SEQNO
, TO_CHAR( ( SYSDATE - 100 ) - 1 / 1440 / ( 60 / ( 1000001 - LEVEL )), 'YYYYMMDDHH24MISS' ) ORDDATE
, TO_CHAR( TRUNC( dbms_random.value( 1,12 ) ), 'FM0009' ) SALDEPTNO
FROM DUAL
CONNECT BY LEVEL <= 1000000
CREATE TABLE DEPT AS
SELECT TO_CHAR( LEVEL, 'FM0009' ) DEPTNO
, '부서'|| TO_CHAR( LEVEL, 'FM0009' ) AS DEPTNAME
, TRUNC( dbms_random.value( 1,4 ) ) TYPE1
FROM DUAL
CONNECT BY LEVEL <= 12
CREATE INDEX ORDDATE_INDEX ON "ORDER"( ORDDATE )
CREATE INDEX ORDDATE_INDEX_01 ON "ORDER"( ORDDATE, SALDEPTNO )
CREATE UNIQUE INDEX DEPT_PK ON DEPT ( DEPTNO )
SQL> SELECT *
2 FROM (SELECT *
3 FROM "ORDER"
4 ORDER BY SEQNO ASC
5 )
6 WHERE ROWNUM <= 10;
SEQNO ORDDATE SALDE
---------- -------------- -----
1 20110616073831 0001
2 20110616073832 0008
3 20110616073833 0002
4 20110616073834 0007
5 20110616073835 0005
6 20110616073836 0001
7 20110616073837 0010
8 20110616073838 0011
9 20110616073839 0006
10 20110616073840 0007
10 개의 행이 선택되었습니다.
SQL> SELECT SUBSTR( ORDDATE, 1,8 ), MAX(SEQNO), COUNT(*)
2 FROM "ORDER"
3 GROUP BY SUBSTR( ORDDATE, 1,8 )
4 ORDER BY MAX(SEQNO);
SUBSTR(ORDDATE,1 MAX(SEQNO) COUNT(*)
---------------- ---------- ----------
20110616 58889 58889
20110617 145289 86400
20110618 231689 86400
20110619 318089 86400
20110620 404489 86400
20110621 490889 86400
20110622 577289 86400
20110623 663689 86400
20110624 750089 86400
20110625 836489 86400
20110626 922889 86400
SUBSTR(ORDDATE,1 MAX(SEQNO) COUNT(*)
---------------- ---------- ----------
20110627 1000000 77111
12 개의 행이 선택되었습니다.
SQL> SELECT * FROM DEPT
2 ;
DEPTN DEPTNAME TYPE1
----- --------- ----------
0001 부서0001 3
0002 부서0002 2
0003 부서0003 1
0004 부서0004 1
0005 부서0005 2
0006 부서0006 3
0007 부서0007 3
0008 부서0008 3
0009 부서0009 3
0010 부서0010 3
0011 부서0011 3
DEPTN DEPTNAME TYPE1
----- --------- ----------
0012 부서0012 2
12 개의 행이 선택되었습니다.
p.593 그림 실행 스크립트( NO_HINT )
SQL> SELECT /*+ gather_plan_statistics */ COUNT(*)
2 FROM "ORDER" X
3 WHERE ORDDATE LIKE '20110621%'
4 AND EXISTS ( SELECT 'O'
5 FROM DEPT Y
6 WHERE Y.DEPTNO = X.SALDEPTNO
7 AND Y.TYPE1 = 1 );
COUNT(*)
----------
15832
SQL> @XPLAN
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.11 | 757 | 374 | | | |
| 2 | NESTED LOOPS | | 1 | 17623 | 15832 |00:00:00.19 | 757 | 374 | | | |
| 3 | SORT UNIQUE | | 1 | 2 | 2 |00:00:00.01 | 3 | 0 | 9216 | 9216 | 8192 (0)|
|* 4 | TABLE ACCESS FULL| DEPT | 1 | 2 | 2 |00:00:00.01 | 3 | 0 | | | |
|* 5 | INDEX RANGE SCAN | ORDDATE_INDEX_01 | 2 | 8812 | 15832 |00:00:00.13 | 754 | 374 | | | |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("Y"."TYPE1"=1)
5 - access("ORDDATE" LIKE '20110621%' AND "Y"."DEPTNO"="X"."SALDEPTNO")
filter(("ORDDATE" LIKE '20110621%' AND "Y"."DEPTNO"="X"."SALDEPTNO"))
p. 594 그림 실행 스크립트 ( ORDDATE_INDEX( ORDDATE ) )
SQL> SELECT /*+ gather_plan_statistics INDEX( X ORDDATE_INDEX ) */ COUNT(*)
2 FROM "ORDER" X
3 WHERE ORDDATE LIKE '20110621%'
4 AND EXISTS ( SELECT /*+ NO_UNNEST NO_PUSH_SUBQ */ 'O'
5 FROM DEPT Y
6 WHERE Y.DEPTNO = X.SALDEPTNO
7 AND Y.TYPE1 = 1 );
COUNT(*)
----------
15832
SQL> @XPLAN
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.15 | 697 | 313 |
|* 2 | FILTER | | 1 | | 15832 |00:00:00.21 | 697 | 313 |
| 3 | TABLE ACCESS BY INDEX ROWID| ORDER | 1 | 96928 | 86400 |00:00:00.95 | 675 | 313 |
|* 4 | INDEX RANGE SCAN | ORDDATE_INDEX | 1 | 96928 | 86400 |00:00:00.26 | 316 | 313 |
|* 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 11 | 1 | 2 |00:00:00.01 | 22 | 0 |
|* 6 | INDEX UNIQUE SCAN | DEPT_PK | 11 | 1 | 11 |00:00:00.01 | 11 | 0 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( IS NOT NULL)
4 - access("ORDDATE" LIKE '20110621%')
filter("ORDDATE" LIKE '20110621%')
5 - filter("Y"."TYPE1"=1)
6 - access("Y"."DEPTNO"=:B1)
- 1) 'ORDDATE_INDEX'를 86400 개까지 차례로 범위처리를 하면서 86400 건의 'ORDER' 테이블의 로우를 읽어 내려 간다.
- 2) 대응되는 DEPT 테이블을 연결한다.
- 3) 그렇다면 'ORDER' 테이블에서 엑세스한 86400 개의 각각의 로우에 대해서 메번 서브쿼리가 수행되어 EXISTS를 체크했다
- 4) 그러므로 'DEPT' 테이블을 액세스하는 서브쿼리도 86400 번 수행되어야 할 것이다.
- 5) 그런데 'DEPT' acess 5을 살펴보면 이 메인쿼리가 'DEPT' 테이블을 엑세스한것은 단 11회에 불과 하다.
NL SEMI ( ORDDATE_INDEX )
SQL> SELECT /*+ gather_plan_statistics INDEX( X ORDDATE_INDEX ) */ COUNT(*)
2 FROM "ORDER" X
3 WHERE ORDDATE LIKE '20110621%'
4 AND EXISTS ( SELECT /*+ NL_SJ */ 'O'
5 FROM DEPT Y
6 WHERE Y.DEPTNO = X.SALDEPTNO
7 AND Y.TYPE1 = 1 );
COUNT(*)
----------
15832
SQL> @xplan
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.12 | 688 |
| 2 | NESTED LOOPS SEMI | | 1 | 709 | 15832 |00:00:00.17 | 688 |
| 3 | TABLE ACCESS BY INDEX ROWID| ORDER | 1 | 3899 | 86400 |00:00:00.61 | 675 |
|* 4 | INDEX RANGE SCAN | ORDDATE_INDEX | 1 | 3899 | 86400 |00:00:00.26 | 316 |
|* 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 11 | 1 | 2 |00:00:00.01 | 13 |
|* 6 | INDEX UNIQUE SCAN | DEPT_PK | 11 | 1 | 11 |00:00:00.01 | 2 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("ORDDATE" LIKE '20110621%')
filter("ORDDATE" LIKE '20110621%')
5 - filter("Y"."TYPE1"=1)
6 - access("Y"."DEPTNO"="X"."SALDEPTNO")
그림 2-2-26 설명
- 1) 먼저 ORDDATE_INDEX 에서 '20110621%'를 만족하는 첫 번재 로우를 읽고 그 ROWID로 ORDER 테이블의 해당 로우를 엑세스한다.
- 2) 그 로우가 가지고 있는 SALDEPTNO 와 버퍼에 있는 DEPT와 비교한 결과가 같지 않으므로 DEPT 테이블의 기본키를 이용해 액세스한 후 TYPE='1'을 체크한다.
- 체크를 하여 조건을 만족하면 운반단위에 태우고 아니면 버린다.
- 3) 액세스한 DEPT테이블의 비교 컬럼값들을 버퍼에 저장한다.
- 4) ORDDATE_INDEX의 두번재 로우에 대한 ORDER 테이블 로우를 액세스한 후 버퍼와 체크한다. 이때 ORDER테이블의 SALDEPTNO와 버퍼의 DEPT가 동일하면 버퍼와의 비교만 수행하며, DEPT 테이블은 액세스하지 않는다.
- 버퍼의 DEPT와 일치히지 않을 때는 DEPT 테이블을 액세스하여 체크 조건을 비교하고 그 값을 다시 버퍼에 저장한다.
- 버퍼는 하나의 값만 저장 할수 있으므로 앞서 저장된 값은 갱신된다.
- 그림에는 처음에 버퍼에 있던 '11,1'이 '22,2'로 바뀌어진 것을 표현하였다.
- 5) 이와 같은 방법으로 ORDDATE_INDEX의 처리범위가 완료될 때까지 수행한다.
- 메인 쿼리에서 엑세스되는 로우가 서브 쿼리에서 비교할 컬럼( 연결고리 )으로 정렬되어 있다면 서브 쿼리의 테이블 엑세스 양은 많이 감소시킬 수 있다.
- 즉, 선행처리되는 ORDER 테이블의 연결고리인 SALDEPTNO가 만약에 'ORDERDATE + SALDEPTNO'로 인덱스가 되어 있다면, 그림에서 처럼 '11'이 모든 끝난 다음에 '22'가 나타날 것이므로 DEPT를 액세스하러 가는 경우가 최소화 될 것이다. ( 정말... ?? )
- 그러나 극단저긴 경우를 가정해보자. 만약 SALDEPT가 매번 바뀌었다고 한다면 항상 버퍼에 저장된 것과 일치하지 않을 것이므로 버퍼를 활용한 이득을 전혀 얻을 수 없다.
- 물론 이것은 최악의 경우를 가정한 것이므로 대부분의 경우는 최소한 유리하게 된다고 말 할 수 있다. ( 이처럼 서브쿼리를 확인자 역할로만 사용하고자 한다면 FILTER형 세미조인을 활용하는 것이 나쁠 것이 없다.
- 그러나 무조건 그렇게 생각하는 것도 약간의 문제는 잇다. 그림에서 볼 수 있듯이 이 처리방식은 개념적으로는 NL형시과 유사하므로 램덤액세스가 증가할 가능성이 있다.
- 물론 버퍼만 체크하는 경우가 많다면 걱정할 것이 없겠지만 그렇지 않을 때는 Sort Merge이나 해쉬 조인이 되도록 하는 것이 보다 유리할 것이다.
- 일반적으로 EXISTS 서브 쿼리를 사용할 경우는 대부분 Filter 처리 방식으로 실행계획이 수립되므로 필요하다면 인라인뷰를 활용한 조이문을 사용하느게 좋다. ( 정말..?? )
먼가 이상 하지 않나요?? ( 물론 제가 잘못 이해 할 수도 있습니다. )
- 현재 ORDER Table 데이타 상황은 책에서 말한 필터를 사용할 경우 최악의 상황인데.. 딱 11번만 서브쿼리르 엑세스 했습니다.
- 그래서 찾아봤습니다. ( _query_execution_cache_max_size )
- 아 그리고 세미조인도 캐쉬영역을 사용하는 것 같습니다.
SQL> SELECT /*+ gather_plan_statistics INDEX( X ORDDATE_INDEX ) */ *
2 FROM "ORDER" X
3 WHERE ORDDATE LIKE '20110621%'
4 AND ROWNUM <= 50
5 ORDER BY ORDDATE ASC;
SEQNO ORDDATE SALDE
---------- -------------- -----
404490 20110621000000 0008
404491 20110621000001 0007
404492 20110621000002 0002
404493 20110621000003 0004
404494 20110621000004 0005
404495 20110621000005 0001
404496 20110621000006 0002
404497 20110621000007 0008
404498 20110621000008 0007
404499 20110621000009 0011
404500 20110621000010 0009
SEQNO ORDDATE SALDE
---------- -------------- -----
404501 20110621000011 0010
404502 20110621000012 0008
404503 20110621000013 0007
404504 20110621000014 0003
404505 20110621000015 0003
404506 20110621000016 0006
404507 20110621000017 0011
404508 20110621000018 0009
404509 20110621000019 0009
404510 20110621000020 0004
404511 20110621000021 0004
SEQNO ORDDATE SALDE
---------- -------------- -----
404512 20110621000022 0004
404513 20110621000023 0008
404514 20110621000024 0002
404515 20110621000025 0001
404516 20110621000026 0006
404517 20110621000027 0004
404518 20110621000028 0001
404519 20110621000029 0009
404520 20110621000030 0003
404521 20110621000031 0011
404522 20110621000032 0010
SEQNO ORDDATE SALDE
---------- -------------- -----
404523 20110621000033 0002
404524 20110621000034 0001
404525 20110621000035 0011
404526 20110621000036 0007
404527 20110621000037 0008
404528 20110621000038 0009
404529 20110621000039 0011
404530 20110621000040 0004
404531 20110621000041 0003
404532 20110621000042 0009
404533 20110621000043 0010
SEQNO ORDDATE SALDE
---------- -------------- -----
404534 20110621000044 0001
404535 20110621000045 0003
404536 20110621000046 0008
404537 20110621000047 0008
404538 20110621000048 0003
404539 20110621000049 0007
50 개의 행이 선택되었습니다.
SQL> SELECT /*+ gather_plan_statistics INDEX( X ORDDATE_INDEX ) */ DISTINCT SALDEPTNO
2 FROM "ORDER" X
3 WHERE ORDDATE LIKE '20110621%' ;
SALDE
-----
0008
0010
0009
0001
0011
0006
0005
0007
0002
0004
0003
11 개의 행이 선택되었습니다.
SQL> select ksppinm name,
2 ksppstvl value,
3 decode(bitand(ksppiflg/256,1),1,'true','false') ses_modifiable,
4 decode(bitand(ksppiflg/65536,3),1,'immediate',2,'deferred',3,'immediate','false') sys_modifiable,
5 ksppdesc description
6 from sys.x$ksppi i, sys.x$ksppcv v
7 where i.indx = v.indx
8 and i.ksppinm like '%_query_execution_cache_max_size%';
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SES_M SYS_MODIF
----- ---------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
_query_execution_cache_max_size
65536 <--
true deferred
max size of query execution cache
- 결론 : 버퍼는 1개가 아니다.
p. 594 그림 실행 스크립트 ( ORDDATE_INDEX_01( ORDDATE ) )
SQL> SELECT /*+ gather_plan_statistics INDEX( X ORDDATE_INDEX_01 ) */ COUNT(*)
2 FROM "ORDER" X
3 WHERE ORDDATE LIKE '20110621%'
4 AND EXISTS ( SELECT /*+ NO_UNNEST NO_PUSH_SUBQ */ 'O'
5 FROM DEPT Y
6 WHERE Y.DEPTNO = X.SALDEPTNO
7 AND Y.TYPE1 = 1 );
COUNT(*)
----------
15832
SQL> @XPLAN
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.10 | 399 | 3 |
|* 2 | FILTER | | 1 | | 15832 |00:00:00.16 | 399 | 3 |
|* 3 | INDEX RANGE SCAN | ORDDATE_INDEX_01 | 1 | 3899 | 86400 |00:00:00.26 | 377 | 3 |
|* 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 11 | 1 | 2 |00:00:00.01 | 22 | 0 |
|* 5 | INDEX UNIQUE SCAN | DEPT_PK | 11 | 1 | 11 |00:00:00.01 | 11 | 0 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( IS NOT NULL)
3 - access("ORDDATE" LIKE '20110621%')
filter("ORDDATE" LIKE '20110621%')
4 - filter("Y"."TYPE1"=1)
5 - access("Y"."DEPTNO"=:B1)
NL SEMI ( ORDDATE_INDEX_01 )
SQL> SELECT /*+ gather_plan_statistics INDEX( X ORDDATE_INDEX_01 ) */ COUNT(*)
2 FROM "ORDER" X
3 WHERE ORDDATE LIKE '20110621%'
4 AND EXISTS ( SELECT /*+ NL_SJ */ 'O'
5 FROM DEPT Y
6 WHERE Y.DEPTNO = X.SALDEPTNO
7 AND Y.TYPE1 = 1 );
COUNT(*)
----------
15832
SQL> @XPLAN
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.08 | 390 |
| 2 | NESTED LOOPS SEMI | | 1 | 709 | 15832 |00:00:00.13 | 390 |
|* 3 | INDEX RANGE SCAN | ORDDATE_INDEX_01 | 1 | 3899 | 86400 |00:00:00.26 | 377 |
|* 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 11 | 1 | 2 |00:00:00.01 | 13 |
|* 5 | INDEX UNIQUE SCAN | DEPT_PK | 11 | 1 | 11 |00:00:00.01 | 2 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ORDDATE" LIKE '20110621%')
filter("ORDDATE" LIKE '20110621%')
4 - filter("Y"."TYPE1"=1)
5 - access("Y"."DEPTNO"="X"."SALDEPTNO")
"구루비 데이터베이스 스터디모임" 에서 2011년에 "새로쓴 대용량 데이터베이스 솔루션1" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4454
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.