Oracle 테이블 연결 방법의 최적화 방법(예제 포함)

이 기사의 내용은 Oracle 테이블 연결 방법의 최적화 방법에 대한 것입니다(예제 포함). 이는 특정 참고 가치가 있으므로 도움이 될 수 있습니다.

오라클 데이터베이스에는 두 테이블 사이에 4가지 테이블 연결 방법이 있습니다: 정렬 병합 조인, 중첩 루프 조인, 해시 조인 및 카티션 조인

1. 정렬 병합 조인(정렬 병합 조인)

정렬 병합 연결은 테이블 연결입니다. 두 테이블이 연결되었을 때 정렬(SORT) 및 병합(MERGE) 작업을 사용하여 연결 결과 집합을 얻는 방법

테이블 연결을 수행할 때 t1 테이블과 t2 테이블이 정렬을 사용하는 경우 연결 병합을 수행하면 Oracle이 수행합니다.

a. 대상 SQL에 지정된 조건에 따라 t1 테이블에 액세스한 후 t1 테이블의 연결 열에 따라 액세스 결과를 정렬하면 s1

에 기록됩니다.

b. 대상 SQL에 지정된 조건에 따라 t2 테이블에 액세스한 후 t2 테이블의 연결 열에 따라 액세스 결과를 s2

c로 기록합니다. , 일치하는 레코드를 최종 결과 세트로 가져옵니다

정렬 병합 조인의 장점, 단점 및 적용 가능한 시나리오:

a 일반적으로 해시 조인의 효과는 정렬 병합 조인보다 낫습니다. 정렬 병합 조인을 수행할 때 다시 정렬할 필요가 없습니다. 이 경우 정렬 병합 조인은 해시 조인보다 성능이 좋습니다

b 일반적으로 정렬 병합 조인은 다음과 같은 경우에만 사용됩니다. 발생:

1) RBO 모드

2) 불일치 값 조인(>,=,

3) 해시 조인이 비활성화된 경우(_HASH_JOIN_ENABLED=false)

예

SQL> select * from scott.emp t1,scott.emp t2 where t1.empno > t2.mgr;

89 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3950110903

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |    62 |  4712 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                   |        |    62 |  4712 |     6  (17)| 00:00:01 |
|   2 |   SORT JOIN                   |        |    14 |   532 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   532 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |
|*  5 |   SORT JOIN                   |        |    14 |   532 |     4  (25)| 00:00:01 |
|   6 |    TABLE ACCESS FULL          | EMP    |    14 |   532 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access(INTERNAL_FUNCTION("T1"."EMPNO")>INTERNAL_FUNCTION("T2"."MGR"))
       filter(INTERNAL_FUNCTION("T1"."EMPNO")>INTERNAL_FUNCTION("T2"."MGR"))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       6612  bytes sent via SQL*Net to client
        575  bytes received via SQL*Net from client
          7  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         89  rows processed

SQL>

2. 중첩 루프 조인(nested loops Join)

중첩 루프 조인은 두 테이블을 연결할 때 설정된 연결 결과를 얻기 위해 두 수준의 중첩 루프(외부 루프/내부 루프)를 사용하는 테이블 연결 방법입니다

t1 table t2 테이블과 함께 중첩 루프 연결을 사용할 때 Oracle은 다음 단계를 순서대로 수행합니다:

a. 먼저 옵티마이저는 특정 규칙에 따라 t1 및 t2에서 누가 구동 테이블이고 누가 구동 테이블인지 결정합니다. 테이블, 드라이버 테이블은 외부 루프에 사용되고 피동 테이블은 메모리 루프에 사용됩니다. t1이 구동 테이블

b라고 가정하면 대상 SQL에 지정된 조건으로 구동 테이블 t1에 액세스하여 s1을 탐색하고 동시에 구동 테이블 t2를 탐색합니다. 즉, 연결 조건에 따라 s1의 레코드를 꺼내고 구동 테이블 t2가 매칭을 수행합니다. 마지막으로 결과 집합이 반환됩니다

중첩 루프 연결의 장점, 단점 및 적용 가능한 시나리오:

a 빠른 응답을 얻을 수 있습니다. 즉, 연결되어 연결 조건을 충족하는 레코드가 처음에 반환될 수 있습니다. 시간을 기다리지 않고 모든 연결 작업이 완료된 후에만 연결 결과가 반환됩니다.

b. 운전 테이블에 해당하는 운전 결과 세트가 적은 수의 기록을 갖는 데 적합합니다. 이때, 구동 테이블의 연결 컬럼에 고유 인덱스가 있는 경우(또는 구동 테이블에 있는 테이블의 연결 컬럼에 선택성이 좋은 고유하지 않은 인덱스가 있는 상황이 있습니다

예

SQL> select /*+ gather_plan_statistics use_nl(t1,t2)*/* from scott.emp t1,scott.dept t2 where t1.deptno = t2.deptno;
SQL> select * from table(dbms_xplan.display_cursor(null,0,'allstats,last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dcsf9m1rzzga5, child number 0
-------------------------------------
select /*+ gather_plan_statistics use_nl(t1,t2)*/* from scott.emp
t1,scott.dept t2 where t1.deptno = t2.deptno

Plan hash value: 4192419542

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     14 |00:00:00.01 |      32 |
|   1 |  NESTED LOOPS      |      |      1 |     14 |     14 |00:00:00.01 |      32 |
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.01 |       7 |
|*  3 |   TABLE ACCESS FULL| EMP  |      4 |      4 |     14 |00:00:00.01 |      25 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
- filter("T1"."DEPTNO"="T2"."DEPTNO")
rows selected.

SQL>

3. 해시 조인(hash Join)

해시 조인은 두 테이블 간의 일종의 테이블 연결입니다. 연결 결과 집합을 얻기 위해 해시 연산을 사용하는 테이블 조인 방법으로, Oracle 7.3 이후에 도입된 해시 조인의 작동 방법은 다음과 같습니다. 하나의 테이블(일반적으로 작은 테이블)을 해시하고 해시 목록에 저장합니다. 테이블에서 레코드를 추출하고 해시 작업을 수행하고 해시 목록에서 해당 값을 찾아 일치를 수행합니다.

해시 조인은 다음에만 적용됩니다. CBO이며 등가 조인 조건에만 사용할 수 있습니다.

해시 조인은 작은 테이블에 매우 적합합니다. 특히 작은 테이블의 조인 열의 선택성이 매우 좋은 경우에는 테이블이 큰 테이블에 연결될 때 실행이 매우 좋습니다. 해시 조인 시간은 대략적으로 대형 테이블의 전체 테이블 스캔에 소요된 시간으로 간주할 수 있습니다. 해시 연결의 경우 드라이버 결과 세트에 해당하는 해시 테이블을 메모리(PGA 작업 공간)에 완전히 수용할 수 있습니다. . 이때 해시 연결의 실행 효율성은 매우 높습니다. 해시 연결의 성능 문제는 10104 이벤트를 통해 진단할 수 있으며, 관련 지침은 다음과 같습니다.

인메모리 파티션 수(변경되었을 수 있음) ): Hash Partition

최종 해시 버킷 수: 해시 버킷 수

총 버킷 수: 빈 버킷: 비어 있지 않은 버킷: 해시 버킷이 비어 있음 레코드 및 비어 있지 않은 레코드

총 행 수: 구동 중인 레코드 수 결과 집합

버킷의 최대 행 수: 해시 버킷에 포함된 레코드 수 중 가장 많은 레코드 수

비활성화된 비트맵 필터링: 비트맵 필터링 활성화 여부

예

SQL> select /*+ gather_plan_statistics use_hash(t1,t2)*/* from scott.emp t1,scott.dept t2 where t1.deptno = t2.deptno;
SQL> select * from table(dbms_xplan.display_cursor(null,0,'allstats,last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0j83q86ara5u2, child number 0
-------------------------------------
select /*+ gather_plan_statistics use_hash(t1,t2)*/* from scott.emp
t1,scott.dept t2 where t1.deptno = t2.deptno

Plan hash value: 615168685

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     14 |00:00:00.01 |      13 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |     14 |     14 |00:00:00.01 |      13 |  1321K|  1321K| 1070K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.01 |       6 |       |       |          |
|   3 |   TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."DEPTNO"="T2"."DEPTNO")


21 rows selected.

SQL>

[관련 권장 사항:
SQL 튜토리얼
]

위 내용은 Oracle 테이블 연결 방법의 최적화 방법(예제 포함)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!