|
9.1 About Joins
조인은 테이블 또는 뷰와 같은 정확히 두 개의 행 소스의 출력을 결합하고 하나의 행 소스를 리턴합니다.
리턴 된 행 소스는 데이터 세트입니다.
조인은 SQL 문의 WHERE (ANSI가 아닌) 또는 FROM ... JOIN (ANSI) 절에있는 다중 테이블을 특징으로합니다.
FROM 절에 여러 테이블이 존재할 때마다 Oracle Database는 조인을 수행합니다.
조인 조건은 표현식을 사용하여 두 개의 행 소스를 비교합니다.
조인 조건은 테이블 간의 관계를 정의합니다.
명령문이 조인 조건을 지정하지 않으면 데이터베이스는 데 티지 조인을 수행하여 한 테이블의 모든 행을 다른 테이블의 모든 행과 일치시킵니다.
See Also:
• "Cartesian Joins (page 9-23)"
• Oracle Database SQL Language Reference for a concise discussion of joins in Oracle SQL
9.1.1 Join Trees
일반적으로 조인 트리는 거꾸로 된 트리 구조로 표시됩니다.
다음 그래픽에서와 같이 table1은 왼쪽 테이블이고 table2는 오른쪽 테이블입니다.
옵티마이 저는 왼쪽에서 오른쪽으로 조인을 처리합니다.
예를 들어이 그래픽에 중첩 루프 조인이 표시된 경우 table1은 외부 루프이고 table2는 내부 루프입니다.
Figure 9-1 Join Tree
조인의 입력은 이전 조인의 결과 세트가 될 수 있습니다.
조인 트리의 모든 내부 노드에서 오른쪽 자식이 테이블 인 경우 트리는 다음 예제와 같이 왼쪽 딥 조인 트리입니다.
대부분의 조인 트리는 깊은 조인으로 남아 있습니다.
Figure 9-2 Left Deep Join Tree
조인 트리의 모든 내부 노드의 왼쪽 자식이 테이블이면 다음 다이어그램과 같이 트리를 오른쪽 딥 조인 트리라고합니다.
Figure 9-3 Right Deep Join Tree
조인 트리의 내부 노드의 왼쪽 또는 오른쪽 하위 노드가 조인 노드 일 수있는 경우이 트리를 덤불 같은 조인 트리라고합니다.
다음 예제에서 table4는 조인 노드의 오른쪽 자식이고 table1은 조인 노드의 왼쪽 자식이며 table2는 조인 노드의 왼쪽 자식입니다.
Figure 9-4 Bushy Join Tree
또 다른 변형에서, 조인의 두 입력 모두 이전 조인의 결과입니다.
9.1.2 How the Optimizer Executes Join Statements
데이터베이스는 행 소스 쌍을 조인합니다.
FROM 절에 여러 테이블이있는 경우, 옵티마이 저는 각 쌍에 대해 가장 적합한 조인 조작을 판별해야합니다.
옵티마이 저는 다음과 같은 상호 관련 결정을 내려야합니다.
• 액세스 경로
단순 명령문의 경우 옵티마이 저는 Join 문에서 각 테이블의 데이터를 검색하기 위해 액세스 경로를 선택해야합니다.
예를 들어, 옵티마이 저는 전체 테이블 스캔 또는 인덱스 스캔 중에서 선택할 수 있습니다.
• Join 메소드
각 쌍의 행 원본을 조인하려면 Oracle Database에서 행 원본을 결정해야합니다.
"방법"은 조인 방법입니다.
가능한 조인 메소드는 중첩 루프, 정렬 병합 및 해시 조인입니다.
데카르트 결합은 앞의 결합 방법 중 하나를 필요로합니다.
각 조인 메소드에는 다른 조인보다 더 적합한 특정 상황이 있습니다.
• 조인 유형
조인 조건에 따라 조인 유형이 결정됩니다.
예를 들어, 내부 조인은 조인 조건과 일치하는 행만 검색합니다.
외부 조인은 조인 조건과 일치하지 않는 행을 검색합니다.
• 가입 주문
3 개 이상의 테이블을 조인하는 명령문을 실행하기 위해 Oracle Database는 두 개의 테이블을 조인 한 다음 결과 행 원본을 다음 테이블에 조인합니다.
이 프로세스는 모든 테이블이 결과에 결합 될 때까지 계속됩니다.
예를 들어 데이터베이스는 두 테이블을 조인 한 다음 결과를 세 번째 테이블에 조인 한 다음이 결과를 네 번째 테이블과 조인합니다.
9.1.3 How the Optimizer Chooses Execution Plans for Joins
조인 순서 및 방법을 결정할 때 옵티마이 저가 목표는 조기에 행 수를 줄여서 SQL 문의 실행을 통해 작업량을 줄이는 것입니다.
옵티마이 저는 가능한 결합 순서, 결합 메소드 및 사용 가능한 액세스 경로에 따라 실행 계획 세트를 생성합니다.
그런 다음 옵티마이 저가 각 플랜의 비용을 추정하고 비용이 가장 낮은 플랜을 선택합니다.
실행 계획을 선택할 때 최적화 프로그램은 다음 요소를 고려합니다.
• 최적화 프로그램은 두 개 이상의 테이블을 조인하여 최대 하나의 행을 포함하는 행 소스가 생성되는지 여부를 먼저 결정합니다.
옵티마이 저는 테이블의 UNIQUE 및 PRIMARY KEY 제약 조건을 기반으로 이러한 상황을 인식합니다.
이러한 상황이 존재하면 옵티마이 저는 이러한 테이블을 먼저 조인 순서에 놓습니다.
그런 다음 옵티마이 저는 나머지 테이블 세트의 조인을 최적화합니다.
• 외부 조인 조건이있는 조인 문의 경우 외부 조인 연산자가있는 테이블은 일반적으로 조인 순서의 조건에있는 다른 테이블 뒤에옵니다.
일반적으로 최적화 프로그램은 특정 상황에서이 주문 조건을 무시하지만이 가이드 라인을 위반하는 조인 주문은 고려하지 않습니다.
마찬가지로 하위 쿼리가 antijoin 또는 semijoin으로 변환 된 경우 하위 쿼리의 테이블은 연결되었거나 상관 된 외부 쿼리 블록의 테이블을 따라 와야합니다.
그러나 해시 안티 조인 및 세미 조인은 특정 상황에서이 주문 조건을 무시할 수 있습니다.
옵티마이 저는 예상 된 I / O 및 CPU를 계산하여 쿼리 계획의 비용을 계산합니다.
이러한 I / O에는 단일 블록 I / O에 대해 하나의 비용이, 멀티 블록 I / O에 또 다른 비용이 드는 것과 관련된 특정 비용이 있습니다.
또한 서로 다른 함수와 표현식에는 CPU 비용이 있습니다.
옵티마이 저는 이러한 메트릭을 사용하여 쿼리 계획의 총 비용을 결정합니다.
이러한 메트릭은 DB_FILE_MULTI_BLOCK_READ_COUNT 설정, 시스템 통계 등과 같이 컴파일시 많은 초기화 매개 변수 및 세션 설정의 영향을받을 수 있습니다.
예를 들어, 옵티마이 저는 다음과 같은 방법으로 비용을 계산합니다.
• 중첩 루프 조인의 비용은 외부 테이블의 각 선택된 행과 내부 테이블의 일치하는 각 행을 메모리로 읽는 비용에 따라 다릅니다.
옵티마이 저는 데이터 사전의 통계를 사용하여 이러한 비용을 추정합니다 ( "옵티 마이저 통계 소개 (10-1 페이지)"참조).
정렬 병합 비용은 주로 모든 소스를 메모리로 읽어서 정렬하는 비용에 달려 있습니다.
• 해시 조인 비용은 조인의 입력 쪽 중 하나에 해시 테이블을 만들고 조인의 다른 쪽에서 행을 사용하여이를 검사하는 데 드는 비용에 크게 좌우됩니다.
Example 9-1 Estimating Costs for Join Order and Method
개념적으로 옵티마이 저는 조인 순서와 메소드의 매트릭스와 각각의 비용을 구성합니다.
예를 들어, 옵티마이 저는 쿼리에서 date_dim 및 lineorder 테이블을 조인하는 최선의 방법을 결정해야합니다.
다음 표는 방법 및 주문의 가능한 변형과 각각의 비용을 보여줍니다.
이 예제에서 중첩 루프는 date_dim 순서로 조인합니다. lineorder의 비용이 가장 낮습니다.
Table 9-1 Sample Costs for Join of date_dim and lineorder Tables
See Also:
• "Influencing the Optimizer (page 19-1)" for more information about optimizer hints
• Oracle Database Reference to learn about DB_FILE_MULTIBLOCK_READ_COUNT
9.2 Join Methods
조인 메소드는 두 개의 행 소스를 결합하는 메커니즘입니다.
통계에 따라 옵티마이 저는 예상 비용이 가장 낮은 f}을 선택합니다.
그림 9-5(9-5 페이지)에서 볼 수 있듯이 각 조인 메소드에는 구동 (외부라고도 함) 행 소스와 구동 대상 (내부라고도 함) 행 소스라는 두 개의 자식이 있습니다.
Figure 9-5 Join Method
9.2.1 Nested Loops Joins
중첩 루프는 외부 데이터 세트를 내부 데이터 세트에 조인합니다.
단일 테이블 술어와 일치하는 외부 데이터 세트의 각 행에 대해 데이터베이스는 Join 술어를 충족시키는 내부 데이터 세트의 모든 행을 검색합니다.
색인이 사용 가능한 경우 데이터베이스는이를 사용하여 rowid에 의해 설정된 내부 데이터에 액세스합니다.
9.2.1.1 최적화자가 중첩 루프 조인을 고려하는 경우
중첩 된 루프 조인은 데이터베이스가 작은 데이터 하위 집합과 조인 할 때 유용하며 데이터베이스는 FIRST_ROWS로 설정된 최적화 모드로 많은 양의 데이터를 조인하거나 조인 조건이 내부 테이블에 효율적으로 액세스하는 방법입니다.
Note:
조인에서 예상되는 행 수는 기본 테이블의 크기가 아니라 옵티 마이저 결정을 결정합니다.
예를 들어 쿼리는 각각 10 억 개의 행으로 구성된 두 테이블을 조인 할 수 있지만 필터로 인해 옵티마이 저는 각각 5 행의 데이터 집합을 필요로합니다.
일반적으로 중첩 루프 조인은 조인 조건에 대한 인덱스가있는 작은 테이블에서 가장 잘 작동합니다.
행 원본에 기본 키 값에 대한 동일성 조회 (예 : WHERE employee_id = 101)와 같이 하나의 행만있는 경우 조인은 간단한 조회입니다.
최적화 프로그램은 항상 가장 작은 행 소스를 먼저 배치하여 드라이브 테이블로 만듭니다.
여러 요소가 중첩 루프를 사용하도록 옵티마이 저가 결정합니다. 예를 들어, 데이터베이스는 일괄 처리에서 외부 행 원본의 여러 행을 읽을 수 있습니다.
검색된 행 수에 따라 옵티마이 저는 내부 행 소스에 대한 중첩 루프 또는 해시 조인을 선택할 수 있습니다.
예를 들어, 쿼리가 부서를 테이블 직원을 고용하는 데 조인하고 술어가 employees.last_name에 값을 지정하면 데이터베이스는 last_name의 인덱스에 충분한 항목을 읽어 내부 임계 값이 전달되는지 여부를 결정할 수 있습니다.
임계 값이 전달되지 않으면 옵티마이 저는 부서에 중첩 루프 조인을 선택하고 임계 값이 전달되면 데이터베이스는 해시 조인을 수행하여 나머지 직원을 읽고이를 메모리에 해싱 한 다음 부서.
내부 루프에 대한 액세스 경로가 외부 루프에 종속되지 않으면 결과는 카디 전 곱일 수 있습니다. 외부 루프의 모든 반복에 대해 내부 루프는 동일한 행 집합을 생성합니다.
이 문제점을 방지하려면 다른 조인 메소드를 사용하여 두 개의 독립 행 소스를 조인하십시오.
9.2.1.2 How Nested Loop Joins Work
개념적으로 중첩 된 루프는 두 개의 중첩 된 for 루프와 같습니다.
예를 들어 쿼리가 직원 및 부서를 조인 할 경우 의사 코드의 중첩 루프는 다음과 같을 수 있습니다.
FOR erow IN (select * from employees where X=Y) LOOP
FOR drow IN (select * from departments where erow is matched) LOOP
output values from erow and drow
END LOOP
END LOOP
내부 루프는 외부 루프의 모든 행에 대해 실행됩니다.
employees 테이블은 외부 for 루프에 있기 때문에 "외부"데이터 세트입니다.
바깥 쪽 테이블을 때로는 운전대라고 부릅니다.
부서 테이블은 내부 용 루프에 있기 때문에 "내부"데이터 세트입니다.
중첩 루프 조인에는 다음 기본 단계가 포함됩니다.
1. 최적화 프로그램은 구동중인 행 소스를 판별하고이를 외부 루프로 지정합니다.
외부 루프는 조인 조건을 구동하는 행 세트를 생성합니다.
행 원본은 인덱스 검색, 전체 테이블 검색 또는 행을 생성하는 다른 작업을 사용하여 액세스 된 테이블이 될 수 있습니다.
내부 루프의 반복 횟수는 외부 루프에서 검색 한 행 수에 따라 달라집니다.
예를 들어, 외부 테이블에서 10 개의 행이 검색되면 데이터베이스는 내부 테이블에서 10 개의 검색을 수행해야합니다.
외부 테이블에서 10,000,000 개의 행을 검색하면 데이터베이스는 내부 테이블에서 10,000,000 개의 검색을 수행해야합니다.
2. 옵티마이 저는 다른 행 소스를 내부 루프로 지정합니다.
외부 루프는 다음과 같이 실행 계획의 내부 루프 앞에 나타납니다.
NESTED LOOPS
outer_loop
inner_loop
3. 클라이언트의 모든 페치 요청에 대해 기본 프로세스는 다음과 같습니다.
에이. 외부 행 소스에서 행 가져 오기
비. 조건 행렬과 일치하는 행을 찾기 위해 내부 행 소스를 조사하십시오.
기음. 페치 요청으로 모든 행을 가져올 때까지 앞의 단계를 반복하십시오.
때때로 데이터베이스는보다 효율적인 버퍼 액세스 패턴을 얻기 위해 ROWID를 정렬합니다.
9.2.1.3 Nested Nested Loops
중첩 루프의 외부 루프는 그 자체가 다른 중첩 루프에 의해 생성 된 행 소스가 될 수 있습니다.
데이터베이스는 두 개 이상의 외부 루프를 중첩하여 필요한만큼 테이블을 조인 할 수 있습니다.
각 루프는 데이터 액세스 방법입니다.
다음 템플릿은 데이터베이스가 세 개의 중첩 루프를 반복하는 방법을 보여줍니다.
SELECT STATEMENT
NESTED LOOPS 3
NESTED LOOPS 2 - Row source becomes OUTER LOOP 3.1
NESTED LOOPS 1 - Row source becomes OUTER LOOP 2.1
OUTER LOOP 1.1
INNER LOOP 1.2
INNER LOOP 2.2
INNER LOOP 3.2
데이터베이스는 다음과 같이 루프를 정렬합니다.
1. 데이터베이스는 중첩 루프1를 통해 반복합니다.
NESTED LOOPS 1
OUTER LOOP 1.1
INNER LOOP 1.2
NESTED LOOP 1의 출력은 행 소스입니다.
2. 데이터베이스는 NESTED LOOPS 1을 외부 루프로 생성 한 행 소스를 사용하여 NESTED LOOPS 2를 반복합니다.
NESTED LOOPS 2
OUTER LOOP 2.1 - Row source generated by NESTED LOOPS 1
INNER LOOP 2.2
NESTED LOOPS 2의 출력은 다른 행 소스입니다.
3. 데이터베이스는 NESTED LOOPS 2를 통해 외부 루프로 생성 된 행 소스를 사용하여 NESTED LOOPS 3을 반복합니다.
NESTED LOOPS 3
OUTER LOOP 3.1 - Row source generated by NESTED LOOPS 2
INNER LOOP 3.2
Example 9-2 Nested Nested Loops Join
다음과 같이 직원 및 부서 테이블을 조인한다고 가정합니다.
SELECT /*+ ORDERED USE_NL(d) */ e.last_name, e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id=d.department_id
AND e.last_name like 'A%';
이 계획은 옵티마이 저가 데이터에 액세스하기 위해 두 개의 중첩 루프 (1 단계 및 2 단계)를 선택했다는 것을 나타냅니다.
SQL_ID ahuavfcv4tnz4, child number 0
-------------------------------------
SELECT /*+ ORDERED USE_NL(d) */ e.last_name, d.department_name FROM
employees e, departments d WHERE e.department_id=d.department_id AND
e.last_name like 'A%'
Plan hash value: 1667998133
----------------------------------------------------------------------------------
|Id| Operation |Name |Rows|Bytes|Cost(%CPU)|Time|
----------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | |5 (100)| |
| 1| NESTED LOOPS | | | | | |
| 2| NESTED LOOPS | | 3|102|5 (0)|00:00:01|
| 3| TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 3| 54|2 (0)|00:00:01|
|*4| INDEX RANGE SCAN | EMP_NAME_IX | 3| |1 (0)|00:00:01|
|*5| INDEX UNIQUE SCAN | DEPT_ID_PK | 1| |0 (0)| |
| 6| TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 1| 16|1 (0)|00:00:01|
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."LAST_NAME" LIKE 'A%')
filter("E"."LAST_NAME" LIKE 'A%')
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
이 예제에서 기본 프로세스는 다음과 같습니다.
1. 데이터베이스는 다음과 같이 내부 중첩 루프 (2 단계)를 통해 반복을 시작합니다.
a. 데이터베이스는 emp_name_ix에서 A로 시작하는 모든 성의 ROWID를 검색합니다 (4 단계).
예 :
Abel,employees_rowid
Ande,employees_rowid
Atkinson,employees_rowid
Austin,employees_rowid
b. 이전 단계의 ROWID를 사용하여 데이터베이스는 employees 테이블에서 행 배치를 검색합니다 (3 단계).
예 :
Abel,Ellen,80
Abel,John,50
이 행은 가장 안쪽의 중첩 루프의 외부 행 소스가됩니다.
일괄 처리 단계는 일반적으로 적응 형 실행 계획의 일부입니다.
중첩 된 루프가 해시 조인보다 나은지 판별하려면 옵티마이 저는 행 소스에서 많은 행을 찾아야합니다.
너무 많은 행이 리턴되면 옵티마이 저는 다른 조인 f}으로 전환합니다.
c. 외부 행 소스의 각 행에 대해 데이터베이스는 dept_id_pk 색인을 스캔하여 일치하는 부서 ID (단계 5)의 부서에서 rowid를 얻고이를 직원 행에 조인합니다.
예 :
Abel,Ellen,80,departments_rowid
Ande,Sundar,80,departments_rowid
Atkinson,Mozhe,50,departments_rowid
Austin,David,60,departments_rowid
이 행은 외부 중첩 루프의 외부 행 소스가됩니다 (1 단계).
2. 데이터베이스는 다음과 같이 외부 중첩 루프를 반복합니다.
에이. 데이터베이스는 외부 행 소스의 첫 번째 행을 읽습니다.
예 :
Abel,Ellen,80,departments_rowid
비. 데이터베이스는 departments ROWID를 사용하여 부서에서 해당 행을 검색 한 다음 (6 단계) 결과를 조인하여 요청 된 값을 얻습니다 (1 단계).
예 :
Abel,Ellen,80,Sales
c. 데이터베이스는 외부 행 소스에서 다음 행을 읽고 부서 rowid를 사용하여 부서에서 해당 행을 검색하고 (6 단계) 모든 행을 검색 할 때까지 루프를 반복합니다.
결과 집합의 형식은 다음과 같습니다.
Abel,Ellen,80,Sales
Ande,Sundar,80,Sales
Atkinson,Mozhe,50,Shipping
Austin,David,60,IT
9.2.1.4 Current Implementation for Nested Loops Joins
중첩 된 루프 조인에 대한 9.2.1.4 현재 구현
Oracle Database 11g는 물리적 I / O에 대한 전반적인 대기 시간을 줄여주는 중첩 루프를위한 새로운 구현을 도입했습니다.
인덱스 또는 테이블 블록이 버퍼 캐시에없고 조인을 처리하는 데 필요한 경우 실제 I / O가 필요합니다.
데이터베이스는 여러 물리적 I / O 요청을 일괄 처리하고 한 번에 하나씩이 아닌 벡터 I / O (배열)를 사용하여 처리 할 수 있습니다.
데이터베이스는 읽기를 수행하는 운영 체제에 일련의 rowid를 보냅니다.
새로운 구현의 일부로, NESTED LOOPS 조인 행 소스가 이전 릴리스에서 하나만있는 실행 계획에 나타날 수 있습니다.
이러한 경우 Oracle Database는 하나의 NESTED LOOPS 조인 행 소스를 할당하여 조인의 외부 쪽 테이블 값을 내부 인덱스에 조인합니다.
두 x 째 행 소스는 색인에 저장된 rowid를 포함하는 첫 번째 조인의 결과를 조인의 내부 측에있는 테이블과 조인하도록 할당됩니다.
"중첩 루프 조인의 원래 구현 (9-12 페이지)"에서 쿼리를 고려하십시오.
현재 구현에서이 쿼리의 실행 계획은 다음과 같습니다.
-------------------------------------------------------------------------------------
| Id | Operation | Name |Rows|Bytes|Cost%CPU| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 722 | 3 (0)|00:00:01|
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 19 | 722 | 3 (0)|00:00:01|
|* 3 | TABLE ACCESS FULL | DEPARTMENTS | 2 | 32 | 2 (0)|00:00:01|
|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)|00:00:01|
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 220 | 1 (0)|00:00:01|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("D"."DEPARTMENT_NAME"='Marketing' OR "D"."DEPARTMENT_NAME"='Sales')
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
이 경우 hr.departments 테이블의 행은 내부 중첩 루프 (2 단계)의 외부 행 소스 (3 단계)를 형성합니다.
인덱스 emp_department_ix는 내부 중첩 루프의 내부 행 소스 (4 단계)입니다.
내부 중첩 루프의 결과는 외부 중첩 루프 (행 1)의 외부 행 소스 (Row 2)를 형성합니다.
hr.employees 테이블은 외부 중첩 루프의 외부 행 소스 (행 5)입니다.
각 페치 요청에 대한 기본 프로세스는 다음과 같습니다.
1. 내부 중첩 루프 (2 단계)를 통해 데이터베이스를 반복 실행하여 가져 오기에서 요청 된 행을 얻습니다.
에이. 데이터베이스는 부서의 첫 x 째 행을 읽어 Marketing 또는 Sales라는 부서의 부서 ID를 얻습니다 (3 단계).
예 :
Marketing,20
이 행 집합은 외부 루프입니다. 데이터베이스는 PGA에 데이터를 캐시합니다.
비. 데이터베이스는 employees 테이블의 인덱스 인 emp_department_ix를 검색하여이 부서 ID (단계 4)에 해당하는 직원 ROWID를 찾은 다음 결과 (단계 2)를 조인합니다.
결과 집합의 형식은 다음과 같습니다.
Marketing,20,employees_rowid
Marketing,20,employees_rowid
Marketing,20,employees_rowid
기음. 데이터베이스는 부서의 다음 행을 읽고 emp_department_ix를 스캔하여이 부서 ID에 해당하는 직원 ROWID를 찾은 다음 클라이언트 요청이 충족 될 때까지 루프를 반복합니다.
이 예에서 데이터베이스는 부서의 두 행만이 술어 필터를 충족시키기 때.에 외부 루프를 두 번 반복합니다.
개념적으로 결과 집합은 다음과 같은 형식을 갖습니다.
이 행은 외부 중첩 루프의 외부 행 소스가됩니다 (1 단계).
이 행 세트는 PGA에 캐시됩니다.
2. 데이터베이스는 이전 단계에서 확보 한 ROWID를 구성하여 캐시에서보다 효율적으로 액세스 할 수있게합니다.
3. 데이터베이스는 다음과 같이 외부 중첩 루프를 통해 반복을 시작합니다.
에이. 데이터베이스는 다음 예제와 같이 이전 단계에서 얻은 행 집합에서 첫 번째 행을 검색합니다.
마케팅, 20, employees_rowid
비. rowid를 사용하여 데이터베이스는 다음 예제와 같이 요청 된 값을 확보하기 위해 직원의 행을 검색합니다 (1 단계).
Michael, Hartstein, 13000, 마케팅
기음. 데이터베이스는 행 세트에서 다음 행을 검색하고 rowid를 사용하여 직원에게 일치하는 행을 검사 한 다음 모든 행을 검색 할 때까지 루프를 반복합니다.
결과 집합의 형식은 다음과 같습니다.
Michael,Hartstein,13000,Marketing
Pat,Fay,6000,Marketing
John,Russell,14000,Sales
Karen,Partners,13500,Sales
Alberto,Errazuriz,12000,Sales
.
.
.
경우에 따라 두 번째 조인 행 소스가 할당되지 않으며 실행 계획은 Oracle Database 11g 이전과 동일하게 보입니다.
다음 목록에서는 이러한 경우에 대해 설명합니다.
• 조인의 안쪽에서 필요한 모든 열이 인덱스에 있으며 테이블 액세스가 필요하지 않습니다.
이 경우 Oracle Database는 하나의 조인 로우 소스만을 할당합니다.
• 리턴 된 행의 순서는 Oracle Database 12c 이전 릴리스에서 반환 된 순서와 다를 수 있습니다.
따라서 Oracle Database가 ORDER BY 정렬의 필요성을 없애기 위해 행의 특정 순서를 유지하려고하면 Oracle Database는 중첩 루프 조인에 원래 구현을 사용할 수 있습니다.
• OPTIMIZER_FEATURES_ENABLE 초기화 매개 변수는 Oracle Database 11g보다 먼저 릴리스로 설정됩니다.
이 경우 Oracle Database는 중첩 루프 조인에 원래 구현을 사용합니다.
9.2.1.5 Original Implementation for Nested Loops Joins
현재 릴리스에서는 중첩 루프의 새로운 구현과 원래 구현이 모두 가능합니다.
원래 구현의 예를 보려면 hr.employees 및 hr.departments 테이블의 다음 조인을 고려하십시오.
SELECT e.first_name, e.last_name, e.salary, d.department_name
FROM hr.employees e, hr.departments d
WHERE d.department_name IN ('Marketing', 'Sales')
AND e.department_id = d.department_id;
Oracle Database 11g 이전 릴리스에서는이 쿼리의 실행 계획이 다음과 같이 나타날 수 있습니다.
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 722 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 220 | 1 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 19 | 722 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | DEPARTMENTS | 2 | 32 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("D"."DEPARTMENT_NAME"='Marketing' OR "D"."DEPARTMENT_NAME"='Sales')
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
각 페치 요청에 대한 기본 프로세스는 다음과 같습니다.
1. 데이터베이스는 루프를 통해 반복하여 반입에서 요청 된 행을 얻습니다.
에이. 데이터베이스는 부서의 첫 x 째 행을 읽어 Marketing 또는 Sales라는 부서의 부서 ID를 얻습니다 (3 단계).
예 :
Marketing,20
이 행 집합은 외부 루프입니다. 데이터베이스는 행을 PGA에 캐시합니다.
비. 데이터베이스는 employees.department_id 열의 색인 인 emp_department_ix를 검색하여이 부서 ID (단계 4)에 해당하는 직원 ROWID를 찾은 다음 결과 (단계 2)를 조인합니다.
개념적으로 결과 집합은 다음과 같은 형식을 갖습니다.
Marketing,20,employees_rowid
Marketing,20,employees_rowid
Marketing,20,employees_rowid
기음. 데이터베이스는 부서의 다음 행을 읽고 emp_department_ix를 스캔하여이 부서 ID에 해당하는 직원 ROWID를 찾은 다음 클라이언트 요청이 충족 될 때까지 루프를 반복합니다.
이 예에서 데이터베이스는 부서의 두 행만이 술어 필터를 충족시키기 때.에 외부 루프를 두 번 반복합니다.
개념적으로 결과 집합은 다음과 같은 형식을 갖습니다.
Marketing,20,employees_rowid
Marketing,20,employees_rowid
Marketing,20,employees_rowid
.
.
.
Sales,80,employees_rowid
Sales,80,employees_rowid
Sales,80,employees_rowid
.
.
.
2. 상황에 따라 데이터베이스는 이전 단계에서 얻은 캐시 된 ROWID를 구성하여보다 효율적으로 액세스 할 수 있습니다.
3. 중첩 루프에 의해 생성 된 결과 집합의 각 employee rowid에 대해 데이터베이스는 직원으로부터 행을 검색하여 요청 된 값을 얻습니다 (1 단계).
따라서 기본 프로세스는 ROWID를 읽고 일치하는 직원 행을 검색하고 다음 ROWID를 읽고 일치하는 직원 행을 검색하는 등입니다.
개념적으로 결과 집합은 다음과 같은 형식을 갖습니다.
Michael,Hartstein,13000,Marketing
Pat,Fay,6000,Marketing
John,Russell,14000,Sales
Karen,Partners,13500,Sales
Alberto,Errazuriz,12000,Sales
.
.
.
9.2.1.6 Nested Loops Controls
일부 SQL 예에서, 데이터는 옵티마이 저가 전체 테이블 스캔 및 해시 조인을 선호하기에 충분히 작습니다.
그러나 USE_NL을 추가하여 지정된 테이블을 내부 테이블로 사용하여 중첩 된 루프 조인이있는 다른 행 소스에 지정된 각 테이블을 조인하도록 옵티 마이저에 지시 할 수 있습니다.
관련 힌트 USE_NL_WITH_INDEX (테이블 인덱스) 힌트는 지정된 테이블을 내부 테이블로 사용하여 중첩 루프 조인을 사용하여 지정된 테이블을 다른 행 소스에 조인하도록 옵티 마이저에 지시합니다.
색인은 선택 사항입니다. 색인이 지정되지 않으면, 중첩 된 루프 조인은 적어도 하나의 조인 술어가있는 색인을 색인 키로 사용합니다.
Example 9-3 Nested Loops Hint
옵티마이 저가 다음 쿼리에 대해 해시 조인을 선택했다고 가정합니다.
SELECT e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id=d.department_id;
The plan looks as follows:
------------------------------------------------------------------------------
|Id | Operation | Name | Rows | Bytes |Cost(%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
|*1 | HASH JOIN | | 106 | 2862 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1177 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
부서를 내부 테이블로 사용하여 중첩 루프 조인을 강제 실행하려면 다음 쿼리와 같이 USE_NL 힌트를 추가하십시오.
SELECT /*+ ORDERED USE_NL(d) */ e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id=d.department_id;
The plan looks as follows:
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 34 (100)| |
| 1 | NESTED LOOPS | | 106 | 2862 | 34 (3)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1177 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 16 | 0 (0)| |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
데이터베이스는 다음과 같이 결과 집합을 얻습니다.
1. 중첩 루프에서 데이터베이스는 직원을 읽고 직원의 성 및 부서 ID를 얻습니다 (2 단계).
예 :
De Haan,90
2. 이전 단계에서 얻은 행에 대해 데이터베이스는 부서를 스캔하여 직원 부서 ID (3 단계)와 일치하는 부서 이름을 찾은 다음 결과를 조인합니다 (1 단계).
예 :
De Haan,Executive
3. 데이터베이스는 직원의 다음 행을 검색하고 부서에서 일치하는 행을 검색 한 다음 모든 행을 검색 할 때까지이 프로세스를 반복합니다.
결과 집합의 형식은 다음과 같습니다.
De Haan,Executive
Kochnar,Executive
Baer,Public Relations
Join Methods
9-14 SQL Tuning Guide
King,Executive
.
.
.
See Also:
• "Guidelines for Join Order Hints (page 19-14)" to learn more about the USE_NL hint
• Oracle Database SQL Language Reference to learn about the USE_NL hin
9.2.2 Hash Joins
데이터베이스는 더 큰 데이터 세트를 결합하기 위해 해시 조인을 사용합니다.
옵티마이 저는 두 개의 데이터 세트 중 작은 것을 사용하여 메모리의 조인 키에 해시 테이블을 작성하고 결정적 해시 함수를 사용하여 해시 테이블에서 각 행을 저장할 위치를 지정합니다.
그런 다음 데이터베이스는 더 큰 데이터 세트를 스캔하여 해시 테이블을 조사하여 조인 조건을 충족시키는 행을 찾습니다.
9.2.2.1 When the Optimizer Considers Hash Joins
일반적으로 옵티마이 저는 상대적으로 많은 양의 데이터를 조인해야하거나 작은 테이블의 많은 부분을 조인해야하는 경우 해시 조인을 고려하며 조인은 동등한 조인입니다.
해시 조인은 작은 데이터 세트가 메모리에 들어갈 때 가장 비용 효율적입니다.
이 경우 비용은 두 데이터 세트에 대한 단일 읽기 패스로 제한됩니다.
해시 테이블은 PGA에 있기 때문에 Oracle Database는 래치하지 않고 행에 액세스 할 수 있습니다.
이 기술은 데이터베이스 버퍼 캐시에서 블록을 반복적으로 래치 및 읽기 할 필요성을 피함으로써 논리 I / O를 줄입니다.
데이터 세트가 메모리에 맞지 않으면 데이터베이스는 행 소스를 파티션하고 결합은 파티션별로 파티션을 진행합니다.
이렇게하면 많은 정렬 영역 메모리와 I / O를 임시 테이블 공간에 사용할 수 있습니다.
이 방법은 데이터베이스가 병렬 쿼리 서버를 사용하는 경우 특히 비용 효과적 일 수 있습니다.
9.2.2.2 How Hash Joins Work
해싱 알고리즘은 입력 집합을 사용하여 결정적 해시 함수를 적용하여 1에서 n 사이의 해시 값을 생성합니다. 여기서 n은 해시 테이블의 크기입니다.
해시 조인에서 입력 값은 조인 키입니다. 출력 값은 해시 테이블 인 배열의 인덱스 (슬롯)입니다.
9.2.2.2.1 Hash Tables
해시 테이블을 설명하기 위해 데이터베이스가 부서 및 직원의 조인에 hr.departments를 해시한다고 가정합니다.
조인 키 열은 department_id입니다.
부서의 처음 5 개 행은 다음과 같습니다.
SQL> select * from departments where rownum < 6;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
데이터베이스는 테이블의 각 department_id에 해시 함수를 적용하여 각 해시 값을 생성합니다.
이 그림에서 해시 테이블에는 5 개의 슬롯이 있습니다 (더 많거나 적을 수 있음).
n은 5이므로 가능한 해시 값의 범위는 1에서 5 사이입니다.
해시 함수는 부서 ID에 대해 다음 값을 생성 할 수 있습니다.
f(10) = 4
f(20) = 1
f(30) = 4
f(40) = 2
f(50) = 5
해시 함수는 부서 10 및 30에 대해 동일한 해시 값 4를 생성합니다.
이를 해시 충돌이라고합니다. 이 경우 데이터베이스는 링크 된 목록을 사용하여 동일한 슬롯에 부서 10 및 30의 레코드를 저장합니다.
개념적으로 해시 테이블은 다음과 같습니다.
1 20,Marketing,201,1800
2 40,Human Resources,203,2400
3
4 10,Administration,200,1700 -> 30,Purchasing,114,1700
5 50,Shipping,121,1500
9.2.2.2.2 Hash Join: Basic Steps
옵티마이 저는 작은 데이터 소스를 사용하여 메모리의 조인 키에 해시 테이블을 작성한 다음 큰 테이블을 스캔하여 조인 된 행을 찾습니다.
기본 단계는 다음과 같습니다.
1. 데이터베이스는 빌드 테이블이라고하는 작은 데이터 세트의 전체 스캔을 수행 한 다음 PGA에 해시 테이블을 작성하기 위해 각 행의 조인 키에 해시 함수를 적용합니다.
의사 코드에서 알고리즘은 다음과 같이 보일 수 있습니다.
FOR small_table_row IN (SELECT * FROM small_table)
LOOP
slot_number := HASH(small_table_row.join_key);
INSERT_HASH_TABLE(slot_number,small_table_row);
END LOOP;
2. 데이터베이스는 비용이 가장 낮은 액세스 메커니즘을 사용하여 프로브 테이블이라고하는 두 번째 데이터 세트를 프로브합니다.
일반적으로 데이터베이스는 더 작은 데이터 세트와 큰 데이터 세트의 전체 스캔을 수행합니다.
의사 코드의 알고리즘은 다음과 같습니다.
FOR large_table_row IN (SELECT * FROM large_table)
LOOP
slot_number := HASH(large_table_row.join_key);
small_table_row = LOOKUP_HASH_TABLE(slot_number,large_table_row.join_key);
IF small_table_row FOUND
THEN
output small_table_row + large_table_row;
END IF;
END LOOP;
더 큰 데이터 세트에서 검색된 각 행에 대해 데이터베이스는 다음을 수행합니다.
에이. 조인 열에 같은 해시 함수를 적용하여 해시 테이블의 관련 슬롯 번호를 계산합니다.
예를 들어 부서 ID 30의 해시 테이블을 조사하기 위해 데이터베이스는 해시 함수를 30에 적용하여 해시 값 4를 생성합니다.
비. 행이 슬롯에 있는지 여부를 확인하기 위해 해시 테이블을 검사합니다.
행이 없으면 데이터베이스는 더 큰 데이터 세트의 다음 행을 처리합니다.
행이 있으면 데이터베이스는 다음 단계로 진행합니다.
기음. 일치하는 조인 열을 검사합니다. 일치가 발생하면 데이터베이스는 행을보고하거나 계획의 다음 단계로 전달한 다음 큰 데이터 세트의 다음 행을 처리합니다.
해시 테이블 슬롯에 여러 행이있는 경우 데이터베이스는 연결된 행 목록을 탐색하고 각 행을 검사합니다.
예를 들어 부서 30이 슬롯 4에 해시를 수행하면 데이터베이스는 30을 찾을 때까지 각 행을 검사합니다.
Example 9-4 Hash Joins
응용 프로그램은 order_id 열에 가입하여 oe.orders 및 oe.order_items 테이블을 쿼리합니다.
SELECT o.customer_id, l.unit_price * l.quantity
FROM orders o, order_items l
WHERE l.order_id = o.order_id;
The execution plan is as follows:
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 665 | 13300 | 8 (25)|
|* 1 | HASH JOIN | | 665 | 13300 | 8 (25)|
| 2 | TABLE ACCESS FULL | ORDERS | 105 | 840 | 4 (25)|
| 3 | TABLE ACCESS FULL | ORDER_ITEMS | 665 | 7980 | 4 (25)|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("L"."ORDER_ID"="O"."ORDER_ID")
주문 테이블은 order_items 테이블에 비해 상대적으로 작기 때문에 6 배 더 크므로 데이터베이스는 주문을 해시합니다.
해시 조인에서 빌드 테이블의 데이터 세트는 항상 작업 목록의 첫 번째 항목에 나타납니다 (2 단계).
3 단계에서 데이터베이스는 나중에 더 큰 order_items의 전체 스캔을 수행하여 각 행에 대한 해시 테이블을 탐색합니다.
9.2.2.3 How Hash Joins Work When the Hash Table Does Not Fit in the PGA
해시 테이블이 PGA에 완전히 들어 맞지 않을 때 데이터베이스는 다른 기술을 사용해야합니다.
이 경우 데이터베이스는 해시 테이블의 부분 (파티션이라고 함)을 보유하기 위해 임시 공간을 사용하고 해시 테이블을 탐색하는 더 큰 테이블의 일부를 유지하기도합니다.
기본 프로세스는 다음과 같습니다.
1. 데이터베이스는 더 작은 데이터 세트의 전체 스캔을 수행 한 다음 PGA와 디스크 모두에서 해시 버킷의 배열을 작성합니다.
PGA 해시 영역이 가득 차면 데이터베이스는 해시 테이블에서 가장 큰 파티션을 찾아 디스크의 임시 공간에 씁니다. 데이터베이스는
디스크의이 디스크상의 파티션에 속하는 새로운 행 및 PGA의 다른 모든 행.
따라서 해시 테이블의 일부는 메모리에 있고 일부는 디스크에 있습니다.
2. 데이터베이스는 다른 데이터 세트를 읽을 때 첫 단계를 거칩니다.
각 행에 대해 데이터베이스는 다음을 수행합니다.
에이. 관련 해시 버킷의 수를 계산하기 위해 조인 열에 동일한 해시 함수를 적용합니다.
비. 행이 버킷에 메모리에 있는지 여부를 확인하기 위해 해시 테이블을 검사합니다.
해시 된 값이 메모리의 행을 가리키는 경우 데이터베이스는 조인을 완료하고 행을 반환합니다.
그러나 값이 디스크의 해시 파티션을 가리키는 경우 데이터베이스는 원래 데이터 세트에 사용 된 것과 동일한 분할 스키마를 사용하여이 행을 임시 테이블 공간에 저장합니다.
3. 데이터베이스는 각 디스크상의 임시 파티션을 하나씩 읽습니다
4. 데이터베이스는 각 파티션 행을 해당 디스크상의 임시 파티션에있는 행과 조인합니다.
9.2.2.4 Hash Join Controls
USE_HASH 힌트는 두 테이블을 결합 할 때 최적화 프로그램이 해시 조인을 사용하도록 지시합니다.
9.2.3 Sort Merge Joins
정렬 병합 조인은 중첩 루프 조인의 변형입니다.
조인의 두 데이터 세트가 아직 정렬되지 않은 경우 데이터베이스가이를 정렬합니다.
SORT JOIN 작업입니다. 첫 번째 데이터 세트의 각 행에 대해 데이터베이스는 일치하는 행에 대한 두 번째 데이터 세트를 프로브하고 이전 반복에서 수행 된 일치에서 시작 위치를 기준으로 조인합니다.
MERGE JOIN 작업입니다.
Figure 9-6 Sort Merge Join
9.2.3.1 When the Optimizer Considers Sort Merge Joins
해시 조인에는 해시 테이블과이 테이블에 대한 하나의 해시 테이블이 필요하지만 정렬 병합 조인에는 두 가지 정렬이 필요합니다.
다음 조건 중 하나가 충족되면 대량의 데이터를 조인하기 위해 옵티마이 저가 해시 조인을 통한 정렬 병합 조인을 선택할 수 있습니다.
• 두 테이블 간의 조인 조건이 등호가 아니므로 <, <=,> 또는> = 같은 비항 등 조건을 사용합니다.
정렬 병합과 달리 해시 조인에는 항등 조건이 필요합니다.
• 다른 연산에 필요한 정렬 때문에 옵티마이 저가 정렬 병합을 사용하는 것이 더 저렴하다는 것을 알 수 있습니다.
인덱스가 있으면 데이터베이스는 첫 번째 데이터 세트 정렬을 피할 수 있습니다.
그러나 데이터베이스는 인덱스에 관계없이 항상 두 번째 데이터 세트를 정렬합니다.
정렬 병합은 해시 조인과 같이 중첩 된 루프 조인에 비해 동일한 장점이 있습니다. 데이터베이스는 SGA가 아닌 PGA의 행에 액세스하여 데이터베이스 버퍼 캐시에서 블록을 반복적으로 래치 및 읽기 할 필요가 없으므로 논리 I / O를 줄입니다.
일반적으로 정렬이 비싸기 때문에 해시 조인은 정렬 병합 조인보다 성능이 우수합니다. 그러나 정렬 병합 조인은 해시 조인에 비해 다음과 같은 이점을 제공합니다.
초기 정렬 후 병합 단계가 최적화되므로 출력 행이보다 빠르게 생성됩니다.
• 해시 테이블이 메모리에 완전히 들어 가지 않으면 정렬 병합이 해시 조인보다 비용 효율적일 수 있습니다.
메모리가 부족한 해시 조인에는 해시 테이블과 다른 데이터 세트가 모두 디스크에 복사되어야합니다.
이 경우 데이터베이스는 디스크에서 여러 번 읽을 수 있습니다.
정렬 병합에서 메모리가 두 데이터 세트를 보유 할 수 없으면 데이터베이스는 디스크를 디스크에 모두 씁니다. 그러나 각 데이터 세트를 한 번만 읽습니다.
9.2.3.2 How Sort Merge Joins Work
중첩 루프 조인 에서처럼 정렬 병합 조인은 두 개의 데이터 세트를 읽지 만 정렬되지 않은 경우 정렬합니다.
첫 번째 데이터 세트의 각 행에 대해 데이터베이스는 두 번째 데이터 세트에서 시작 행을 찾은 다음 일치하지 않는 행을 찾을 때까지 두 번째 데이터 세트를 읽습니다.
의사 코드에서 정렬 병합을위한 상위 레벨 알고리즘은 다음과 같습니다.
READ data_set_1 SORT BY JOIN KEY TO temp_ds1
READ data_set_2 SORT BY JOIN KEY TO temp_ds2
READ ds1_row FROM temp_ds1
READ ds2_row FROM temp_ds2
WHILE NOT eof ON temp_ds1,temp_ds2
LOOP
IF ( temp_ds1.key = temp_ds2.key ) OUTPUT JOIN ds1_row,ds2_row
ELSIF ( temp_ds1.key <= temp_ds2.key ) READ ds1_row FROM temp_ds1
ELSIF ( temp_ds1.key => temp_ds2.key ) READ ds2_row FROM temp_ds2
END LOOP
예를 들어, 다음 표는 temp_ds1과 temp_ds2의 두 데이터 세트의 정렬 된 값을 보여줍니다.
Table 9-2 Sorted Data Sets
다음 표에서 보듯이 데이터베이스는 temp_ds1에서 10을 읽은 다음 temp_ds2에서 첫 번째 값을 읽습니다.
temp_ds2에서 20이 temp_ds1에서 10보다 높기 때문에 데이터베이스는 temp_ds2 읽기를 중지합니다.
Table 9-3 Start at 10 in temp_ds1
데이터베이스는 temp_ds1의 다음 값 (20)으로 진행합니다.
데이터베이스는 다음 표와 같이 temp_ds2를 통해 진행됩니다.
Table 9-4 Start at 20 in temp_ds1
데이터베이스는 temp_ds1의 다음 행 (30)으로 진행합니다.
데이터베이스는 마지막 일치 번호 인 20에서 시작하여 다음 표에 나와있는 것처럼 일치 항목을 찾기 위해 temp_ds2를 계속 진행합니다.
Table 9-5 Start at 30 in temp_ds1
데이터베이스는 temp_ds1의 다음 행 (40)으로 진행합니다.
다음 표에서 보듯이 데이터베이스는 temp_ds2에서 마지막으로 일치하는 숫자 (20)에서 시작한 다음 temp_ds2를 통해 일치하는 항목을 찾습니다.
Table 9-6 Start at 40 in temp_ds1
temp_ds2의 마지막 70과 일치 할 때까지 데이터베이스는 이런 방식으로 계속됩니다.
이 시나리오는 temp_ds1을 통해 읽는 데이터베이스가 temp_ds2의 모든 행을 읽을 필요가 없음을 보여줍니다.
중첩 루프 조인보다 이점입니다.
Example 9-5 Sort Merge Join Using Index
다음 쿼리는 department_id 열의 employees 및 departments 테이블을 조인하고 다음과 같이 department_id의 행을 정렬합니다.
SELECT e.employee_id, e.last_name, e.first_name, e.department_id,
d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
ORDER BY department_id;
DBMS_XPLAN.DISPLAY_CURSOR의 쿼리는 계획이 정렬 병합 조인을 사용함을 보여줍니다.
--------------------------------------------------------------------------------
|Id| Operation | Name |Rows|Bytes |Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | | 5(100)| |
| 1| MERGE JOIN | |106 | 4028 | 5 (20)| 00:00:01 |
| 2| TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 3| INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|*4| SORT JOIN | |107 | 2354 | 3 (34)| 00:00:01 |
| 5| TABLE ACCESS FULL | EMPLOYEES |107 | 2354 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
두 데이터 세트는 departments 테이블과 employees 테이블입니다.
인덱스가 departments 테이블을 department_id로 정렬하기 때문에 데이터베이스는이 인덱스를 읽고 정렬을 피할 수 있습니다 (3 단계).
데이터베이스는 CPU를 가장 많이 사용하는 작업 인 employees 테이블을 정렬하기 만하면됩니다 (4 단계).
Example 9-6 Sort Merge Join Without an Index
department_id 열의 employees W departments 테이블을 조인하여 다음과 같이 department_id의 행을 정렬합니다.
이 예에서는 NO_INDEX 및 USE_MERGE를 지정하여 옵티마이 저가 정렬 병합을 선택하도록합니다.
SELECT /*+ USE_MERGE(d e) NO_INDEX(d) */ e.employee_id, e.last_name, e.first_name,
e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
ORDER BY department_id;
DBMS_XPLAN.DISPLAY_CURSOR의 쿼리는 계획이 정렬 병합 조인을 사용함을 보여줍니다.
--------------------------------------------------------------------------------
| Id| Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | MERGE JOIN | | 106 | 9540 | 6 (34)| 00:00:01|
| 2 | SORT JOIN | | 27 | 567 | 3 (34)| 00:00:01|
| 3 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 567 | 2 (0)| 00:00:01|
|*4 | SORT JOIN | | 107 | 7383 | 3 (34)| 00:00:01|
| 5 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7383 | 2 (0)| 00:00:01|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
departments.department_id 인덱스가 무시되므로 옵티마이 저는 정렬을 수행하여 2 단계와 3 단계의 조합 비용을 67 % (3에서 5까지) 증가시킵니다.
9.2.3.3 Sort Merge Join Controls
USE_MERGE 힌트는 옵티마이 저가 정렬 병합 조인을 사용하도록 지시합니다.
경우에 따라 최적화 프로그램을 USE_MERGE 힌트로 대체하는 것이 좋습니다.
예를 들어, 옵티마이 저는 테이블에서 전체 스캔을 선택하고 조회에서 정렬 조작을 피할 수 있습니다.
그러나 전체 테이블 스캔을 통한 빠른 액세스와 달리 인덱스 및 단일 블록 읽기를 통해 큰 테이블에 액세스하기 때문에 비용이 증가합니다.
See Also: Oracle Database SQL Language Reference to learn about the USE_MERGE hint
9.2.4 Cartesian Joins
하나 이상의 테이블에 명령문의 다른 테이블에 대한 조인 조건이 없을 때 데 이터베이스는 데카르트 조인을 사용합니다.
옵티마이 저는 하나의 데이터 소스의 모든 행을 다른 데이터 소스의 모든 행과 조인하여 두 세트의 카디 전 곱을 작성합니다.
따라서 조인으로 인한 총 행 수는 다음 수식을 사용하여 계산됩니다. 여기서 rs1은 첫 번째 행 집합의 행 수이고 rs2는 두 번째 행 집합의 행 수입니다.
rs1 X rs2 = total rows in result set
9.2.4.1 When the Optimizer Considers Cartesian Joins
옵티마이 저는 다음 상황에서 두 개의 행 소스에 대해 데카르트 조인을 사용합니다.
• 조인 조건이 없습니다.
경우에 따라 옵티마이 저는 두 테이블 간의 공통 필터 조건을 가능한 조인 조건으로 선택할 수 있습니다.
노트 :
데카르트 조인이 쿼리 계획에 나타나면 우연히 생략 된 조인 조건으로 인해 발생할 수 있습니다.
일반적으로 쿼리가 n 개의 테이블을 조인하면 데카르트 조인을 피하기 위해 n-1 조인 조건이 필요합니다.
• 데카르트 결합은 효율적인 방법입니다.
예를 들어, 옵티마이 저는 동일한 대형 테이블에 결합 된 두 개의 매우 작은 테이블의 카디 전 곱을 생성하기로 결정할 수 있습니다.
• ORDERED 힌트는 조인 테이블이 지정되기 전에 테이블을 지정합니다.
9.2.4.2 How Cartesian Joins Work
상위 레벨에서 데카르트 조인 알고리즘은 다음과 같습니다. 여기서 ds1은 일반적으로 더 작은 데이터 세트이고 ds2는 더 큰 데이터 세트입니다.
FOR ds1_row IN ds1 LOOP
FOR ds2_row IN ds2 LOOP
output ds1_row and ds2_row
END LOOP
END LOOP
Example 9-7 Cartesian Join
이 예제에서 사용자는 employees 테이블과 departments 테이블의 내부 조인을 수행하려고하지만 실수로 조인 조건을 벗어납니다.
SELECT e.last_name, d.department_name
FROM employees e, departments d;
The execution plan is as follows:
--------------------------------------------------------------------------------
| Id| Operation | Name | Rows | Bytes |Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |11 (100)| |
| 1 | MERGE JOIN CARTESIAN | | 2889 | 57780 |11 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 324 | 2 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 107 | 856 | 9 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| EMP_NAME_IX | 107 | 856 | 0 (0)| |
--------------------------------------------------------------------------------
위의 계획의 1 단계에서 CARTESIAN 키워드는 데카르트 조인이 있음을 나타냅니다.
행 수 (2889)는 27과 107의 곱입니다.
3 단계에서, BUFFER SORT 조작은 데이터베이스가 emp_name_ix 스캔으로 얻은 데이터 블록을 SGA에서 PGA로 복사 중임을 나타냅니다.
이 전략은 데이터베이스 버퍼 캐시에서 동일한 블록을 여러 번 스캔하지 않아 많은 논리적 읽기를 생성하고 리소스 경합을 허용합니다.
9.2.4.3 Cartesian Join Controls
ORDERED 힌트는 옵티마이 저가 FROM 절에 나타나는 순서대로 테이블을 조인하도록 지시합니다.
직접 연결이없는 두 행 소스간에 조인을 강제 실행하면 옵티마이 저는 데카르트 조인을 수행해야합니다.
ORDERED 힌트에 대해 배우려면 Oracle Database SQL Language Reference를 참조하십시오.
Example 9-8 ORDERED Hint
다음 예에서 ORDERED 힌트는 옵티마이 저가 직원과 위치를 조인하도록 지시하지만 조인 조건은이 두 행 소스를 연결하지 않습니다.
SELECT /*+ORDERED*/ e.last_name, d.department_name, l.country_id, l.state_province
FROM employees e, locations l, departments d
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;
다음 실행 계획은 위치 (6 단계)와 직원 (4 단계) 사이의 데카르트 제품 (3 단계)을 보여 주며 부서별 테이블에 조인됩니다 (2 단계).
--------------------------------------------------------------------------------
| Id| Operation | Name | Rows | Bytes |Cost (%CPU)|Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 37 (100)| |
|*1 | HASH JOIN | | 106 | 4664 | 37 (6)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 513 | 2 (0)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN| | 2461 | 61525 | 34 (3)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1177 | 2 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 23 | 322 | 32 (4)| 00:00:01 |
| 6 | TABLE ACCESS FULL | LOCATIONS | 23 | 322 | 0 (0)| |
--------------------------------------------------------------------------------
9.3 Join Types
조인 유형은 조인 조건 유형에 따라 결정됩니다.
9.3.1 Inner Joins
내부 조인 (단순 조인이라고도 함)은 조인 조건을 만족하는 행만 반환하는 조인입니다.
내부 조인은 동등한 조인 또는 단독 조인입니다.
9.3.1.1 Equijoins
동등 연산자는 내부 조인이며이 조인 조건에는 항등 연산자가 포함됩니다.
다음 예제는 동등 조건이므로 동등 조건 연산자 만 포함되어 있습니다.
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id=d.department_id;
앞의 쿼리에서 조인 조건은 e.department_id = d.department_id입니다.
employees 테이블의 행에 departments 테이블의 행에있는 값과 일치하는 부서 ID가 있으면 데이터베이스는 조인 된 결과를 리턴합니다. 그렇지 않으면 데이터베이스가 결과를 리턴하지 않습니다.
9.3.1.2 Nonequijoins
nonequijoin은 조인 조건에 동등 연산자가 아닌 연산자가 포함 된 내부 조인입니다.
다음 쿼리는 직원 176 (2007 년에 일자리를 변경했기 때문에 job_history에 올라온 사람)이 회사에서 근무할 때 고용 날짜가 발생한 모든 직원을 나열합니다.
SELECT e.employee_id, e.first_name, e.last_name, e.hire_date
FROM employees e, job_history h
WHERE h.employee_id = 176
AND e.hire_date BETWEEN h.start_date AND h.end_date;
앞의 예제에서 employees와 job_history를 결합하는 조건에는 항등 연산자가 포함되어 있지 않으므로 하나의 정규 표현식이 아닙니다.
Nonequijoins는 비교적 드뭅니다.
해시 조인에는 적어도 부분적으로 동등한 조인이 필요합니다.
다음 SQL 스크립트에는 동등 조인 조건 (e1.empno = e2.empno)과 비 한정 조건이 포함되어 있습니다.
SET AUTOTRACE TRACEONLY EXPLAIN
SELECT *
FROM scott.emp e1 JOIN scott.emp e2
ON ( e1.empno = e2.empno
AND e1.hiredate BETWEEN e2.hiredate-1 AND e2.hiredate+1 );
옵티마이 저는 다음과 같이 이전 쿼리에 대한 해시 조인을 선택합니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 3638257876
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 174 | 5 (20)| 00:00:01 |
|*1 | HASH JOIN | | 1 | 174 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 1218 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 1218 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E1"."EMPNO"="E2"."EMPNO")
filter("E1"."HIREDATE">=INTERNAL_FUNCTION("E2"."HIREDATE")-1 AND
"E1"."HIREDATE"<=INTERNAL_FUNCTION("E2"."HIREDATE")+1)
9.3.1.3 Band Joins
밴드 조인은 하나의 데이터 세트의 키 값이 두 번째 데이터 세트의 지정된 범위 ( "밴드") 내에 있어야하는 특별한 유형의 키 집합입니다.
동일한 테이블은 첫 번째 및 두 번째 데이터 세트로 사용될 수 있습니다.
Oracle Database 12c Release 2 (12.2)부터 데이터베이스는 대역 조인을보다 효율적으로 평가합니다.
최적화는 정의 된 밴드 바깥에있는 행의 불필요한 스캐닝을 피합니다.
옵티마이 저는 비용 예측을 사용하여 조인 메소드 (해시, 중첩 루프 또는 정렬 병합) W 병렬 데이터 분 h 메소드를 선택합니다.
대부분의 경우 최적화 된 성능은 동등한 수준입니다.
다음 예제는 각 직원의 급여보다 100 달러 이하의 급여를받는 직원을 쿼리합니다.
따라서 밴드의 너비는 $ 200입니다.
이 예에서는 모든 직원의 급여를 자체와 비교하는 것이 허용된다고 가정합니다.
다음 쿼리는 부분 샘플 출력을 포함합니다.
SELECT e1.last_name ||
' has salary between 100 less and 100 more than ' ||
e2.last_name AS "SALARY COMPARISON"
FROM employees e1,
employees e2
WHERE e1.salary
BETWEEN e2.salary - 100
AND e2.salary + 100;
SALARY COMPARISON
-------------------------------------------------------------
King has salary between 100 less and 100 more than King
Kochhar has salary between 100 less and 100 more than Kochhar
Kochhar has salary between 100 less and 100 more than De Haan
De Haan has salary between 100 less and 100 more than Kochhar
De Haan has salary between 100 less and 100 more than De Haan
Russell has salary between 100 less and 100 more than Russell
Partners has salary between 100 less and 100 more than Partners
...
Example 9-9 Query Without Band Join Optimization
밴드 조인 최적화가 없으면 데이터베이스는 다음 쿼리 계획을 사용합니다.
------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN | |
| 2 | SORT JOIN | |
| 3 | TABLE ACCESS FULL | EMPLOYEES |
|*4 | FILTER | |
|*5 | SORT JOIN | |
| 6 | TABLE ACCESS FULL| EMPLOYEES |
------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("E1"."SAL"<="E2"."SAL"+100)
5 - access(INTERNAL_FUNCTION("E1"."SAL")>="E2"."SAL"-100)
filter(INTERNAL_FUNCTION("E1"."SAL")>="E2"."SAL"-100)
이 계획에서 2 단계는 e1 행 소스를 정렬하고 5 단계는 e2 행 소스를 정렬합니다.
정렬 된 행 원본은 다음 표에 설명되어 있습니다.
Table 9-7 Sorted row Sources
조인은 조인의 왼쪽 분기 인 정렬 된 입력 (e1)을 반복하여 시작합니다.이 분기는 계획의 2 단계에 해당합니다.
원래 쿼리에는 두 개의 술어가 있습니다.
• e1.sal> = e2.sal-100, 이는 5 단계 필터입니다
• e1.sal> = e2.sal + 100, 이는 4 단계 필터입니다
정렬 된 행 소스 e1의 각 반복에 대해 데이터베이스는 행 소스 e2를 반복하여 모든 행을 5 단계 필터 e1.sal> = e2.sal-100에 대해 확인합니다.
행이 5 단계 필터를 통과하면 데이터베이스는이를 4 단계 필터로 보내고 5 단계 필터에 대해 e2의 다음 행을 테스트합니다.
그러나 행이 5 단계 필터에 실패하면 e2의 스캔이 중지되고 데이터베이스는 e1의 다음 반복을 진행합니다.
다음 표는 데이터 세트 e1에서 24000 (King)으로 시작하는 e1의 첫 번째 반복을 보여줍니다.
데이터베이스는 e2의 첫 번째 행 (24000 (King))이 5 단계 필터를 통과 한 것으로 판단합니다.
그런 다음 데이터베이스는 4 단계 필터 e1.sal <= w2.sal + 100으로 행을 보냅니다.
데이터베이스는이 행을 MERGE 행 소스로 보냅니다.
그런 다음 데이터베이스는 17000 (Kochhar)이 5 단계 필터와 대조되는지 확인합니다.
그러나 행은 4 단계 필터에 실패하여 버려집니다.
데이터베이스는 5 단계 필터에 대해 17000 (De Haan) 테스트로 진행합니다.
Table 9-8 First Iteration of e1: Separate SORT JOIN and FILTER
앞의 표에 표시된 것처럼 e2 급여가 내림차순으로 정렬되기 때문에 모든 e2 행은 반드시 5 단계 필터를 통과해야합니다.
따라서 5 단계 필터는 항상 행을 4 단계 필터로 보냅니다.
e2 급여는 내림차순으로 정렬되기 때문에 Step 4 필터는 반드시 17000 (Kochhar)으로 시작하는 모든 행에서 실패합니다.
비효율은 데이터베이스가 e2의 모든 후속 행을 필연적으로 통과하는 5 단계 필터와 4 단계 필터에 대해 테스트하기 때문에 발생합니다. 이는 반드시 실패합니다.
Example 9-10 Query With Band Join Optimization
Oracle Database 12c Release 2 (12.2)부터 데이터베이스는 별도의 FILTER 작업이없는 다음 계획을 사용하여 대역 결합을 최적화합니다.
------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN | |
| 2 | SORT JOIN | |
| 3 | TABLE ACCESS FULL | EMPLOYEES |
|* 4 | SORT JOIN | |
| 5 | TABLE ACCESS FULL | EMPLOYEES |
------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(INTERNAL_FUNCTION("E1"."SALARY")>="E2"."SALARY"-100)
filter(("E1"."SALARY"<="E2"."SALARY"+100 AND
INTERNAL_FUNCTION("E1"."SALARY")>="E2"."SALARY"-100))
차이점은 4 단계는 부울 AND 논리를 사용하여 두 개의 술어가 단일 필터를 작성한다는 것입니다.
하나의 필터에 대해 행을 검사 한 다음 두 번째 필터에 대해 검사하기 위해 다른 행 소스로 전송하는 대신 데이터베이스는 하나의 필터에 대해 하나의 검사를 수행합니다.
검사가 실패하면 처리가 중지됩니다.
이 예제에서 쿼리는 24000 (King)으로 시작하는 e1의 첫 번째 반복을 시작합니다.
다음 그림은 범위를 나타냅니다. 2300 미만 및 2500 초과의 e2 값은 범위를 벗어납니다.
Figure 9-7 Band Join
다음 표는 데이터베이스가 24000 (King) 인 e2의 첫 번째 행을 4 단계 필터에 대해 테스트 함을 보여줍니다.
행이 테스트를 통과하므로 데이터베이스가 병합 할 행을 보냅니다.
e2의 다음 행은 17000입니다 (Kochhar).
이 행은 범위 (대역)를 벗어나서 필터 조건자를 만족하지 않으므로 데이터베이스는이 반복에서 e2 행을 테스트하지 않습니다.
e2의 내림차순 정렬은 e2의 모든 후속 행이 필터 테스트에 실패했음을 보장하므로 데이터베이스는 테스트를 중지합니다.
따라서 데이터베이스는 e1의 두 번째 반복으로 진행할 수 있습니다.
Table 9-9 First Iteration of e1: Single SORT JOIN
이러한 방식으로, 대역 결합 최적화는 불필요한 처리를 제거합니다.
최적화되지 않은 경우처럼 e2의 모든 행을 검색하는 대신 데이터베이스는 최소 두 행만 검색합니다.
9.3.2 Outer Joins
외부 조인은 조인 조건을 만족하는 모든 행을 리턴하고 다른 조의 행이 조인 조건을 충족시키지 않는 한 행에서 일부 또는 모든 행을 리턴합니다.
따라서 외부 조인은 단순 조인의 결과를 확장합니다.
ANSI 구문에서 OUTER JOIN 절은 외부 조인을 지정합니다. FROM 절에서 OUTER JOIN 키워드의 왼쪽에 왼쪽 테이블이 나타나고 오른쪽 테이블이이 키워드의 오른쪽에 나타납니다.
왼쪽 테이블은 외부 테이블이라고도하며, 오른쪽 테이블은 내부 테이블이라고도합니다.
예를 들어, 다음 명령문에서 employees 테이블은 왼쪽 또는 외 부 테이블입니다.
SELECT employee_id, last_name, first_name
FROM employees LEFT OUTER JOIN departments
ON (employees.department_id=departments.departments_id);
외부 조인을 위해서는 외부 조인 된 테이블이 구동 테이블이어야합니다.
앞의 예에서 employees는 구동 테이블이고 departments는 driven-to 테이블입니다.
9.3.2.1 Nested Loop Outer Joins
데이터베이스는이 작업을 사용하여 두 테이블 간의 외부 조인을 반복합니다.
외부 조인은 내부 (선택적) 테이블에 해당 행이없는 경우에도 외부 (보존 된) 테이블 행을 리턴합니다.
표준 중첩 루프에서 옵티마이 저는 비용을 기준으로 테이블의 순서 (드라이브 테이블 및 구동 테이블)를 선택합니다.
그러나 중첩 루프 외부 조인에서 조인 조건은 테이블의 순서를 결정합니다.
데이터베이스는 외부의 행 보존 테이블을 사용하여 내부 테이블로 이동합니다.
옵티마이 저는 다음과 같은 상황에서 중첩 루프 조인을 사용하여 외부 조인을 처리합니다.
• 외부 테이블에서 내부 테이블로 운전할 수 있습니다.
• 데이터 볼륨이 충분히 낮아서 중첩 루프 메서드를 효율적으로 만들 수 있습니다.
중첩 루프 외부 조인의 예는 USE_NL 힌트를 예제 9-11 (9-31 페이지)에 추가하여 옵티마이 저가 중첩 루프를 사용하도록 지시 할 수 있습니다.
예 :
SELECT /*+ USE_NL(c o) */ cust_last_name,
SUM(NVL2(o.customer_id,0,1)) "Count"
FROM customers c, orders o
WHERE c.credit_limit > 1000
AND c.customer_id = o.customer_id(+)
GROUP BY cust_last_name;
9.3.2.2 Hash Join Outer Joins
옵티마이 저는 다음 조건 중 하나가 충족 될 때 외부 조인을 처리하기 위해 해시 조인을 사용합니다.
• 데이터 볼륨은 해시 조인 방법을 효율적으로 수행 할 수있을 정도로 충분히 큽니다.
• 외부 테이블에서 내부 테이블로 이동할 수 없습니다.
비용은 테이블의 순서를 결정합니다.
보존 된 행을 포함한 외부 테이블은 해시 테이블을 작성하는 데 사용되거나 해시 테이블을 조사하는 데 사용될 수 있습니다.
Example 9-11 Hash Join Outer Joins
이 예제는 일반적인 해시 조인 외부 조인 쿼리와 그 실행 계획을 보여줍니다.
이 예에서는 1000보다 큰 여신 한도가있는 모든 고객을 조회합니다.
주문이 없는 고객을 쿼리가 캡처 할 수 있도록 외부 조인이 필요합니다.
• 바깥 쪽 테이블은 고객입니다.
• 내부 테이블은 주문입니다.
• 조인은 주문에 해당 행이없는 행을 포함하여 고객 행을 보존합니다.
NOT EXISTS 부속 조회를 사용하여 행을 리턴 할 수 있습니다.
그러나 테이블의 모든 행을 쿼리하기 때문에 NOT EXISTS 하위 쿼리가 중첩되어 있지 않으면 해시 조인이 더 잘 수행됩니다.
SELECT cust_last_name, SUM(NVL2(o.customer_id,0,1)) "Count"
FROM customers c, orders o
WHERE c.credit_limit > 1000
AND c.customer_id = o.customer_id(+)
GROUP BY cust_last_name;
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
| 1 | HASH GROUP BY | | 168 | 3192 | 7 (29)| 00:00:01 |
|*2 | HASH JOIN OUTER | | 318 | 6042 | 6 (17)| 00:00:01 |
|*3 | TABLE ACCESS FULL| CUSTOMERS | 260 | 3900 | 3 (0)| 00:00:01 |
|*4 | TABLE ACCESS FULL| ORDERS | 105 | 420 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C"."CUSTOMER_ID"="O"."CUSTOMER_ID")
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
3 - filter("C"."CREDIT_LIMIT">1000)
4 - filter("O"."CUSTOMER_ID">0)
쿼리는 다양한 조건을 만족하는 고객을 찾습니다.
외부 조인은 내부 테이블에서 대응하는 행을 찾을 때 외부 (보존 된) 테이블 행과 함께 내부 테이블 C 럼에 대해 NULL을 리턴합니다.
이 조작은 주. 행이없는 모든 고객 행을 찾습니다.
이 경우 외부 조인 조건은 다음과 같습니다.
customers.customer_id = orders.customer_id(+)
이 조건의 구성 요소는 다음을 나타냅니다.
Example 9-12 Outer Join to a Multitable View
이 예제에서 외부 조인은 다중 테이블 뷰에 대한 것입니다.
옵티마이 저가 정상 조인과 같이 뷰로 이동할 수 없으며 술어를 밀어서 뷰의 전체 행 세트를 빌드합니다.
SELECT c.cust_last_name, sum(revenue)
FROM customers c, v_orders o
WHERE c.credit_limit > 2000
AND o.customer_id(+) = c.customer_id
GROUP BY c.cust_last_name;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 144 | 4608 | 16 (32)|
| 1 | HASH GROUP BY | | 144 | 4608 | 16 (32)|
|* 2 | HASH JOIN OUTER | | 663 | 21216 | 15 (27)|
|* 3 | TABLE ACCESS FULL | CUSTOMERS | 195 | 2925 | 6 (17)|
| 4 | VIEW | V_ORDERS | 665 | 11305 | |
| 5 | HASH GROUP BY | | 665 | 15960 | 9 (34)|
|* 6 | HASH JOIN | | 665 | 15960 | 8 (25)|
|* 7 | TABLE ACCESS FULL| ORDERS | 105 | 840 | 4 (25)|
| 8 | TABLE ACCESS FULL| ORDER_ITEMS | 665 | 10640 | 4 (25)|
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O"."CUSTOMER_ID"(+)="C"."CUSTOMER_ID")
3 - filter("C"."CREDIT_LIMIT">2000)
6 - access("O"."ORDER_ID"="L"."ORDER_ID")
7 - filter("O"."CUSTOMER_ID">0)
뷰 정의는 다음과 같습니다.
CREATE OR REPLACE view v_orders AS
SELECT l.product_id, SUM(l.quantity*unit_price) revenue,
o.order_id, o.customer_id
FROM orders o, order_items l
WHERE o.order_id = l.order_id
GROUP BY l.product_id, o.order_id, o.customer_id;
9.3.2.3 Sort Merge Outer Joins
외부 조인이 외부 (보존 된) 테이블에서 내부 (선택적) 테이블로 이동할 수 없으면 해시 조인 또는 중첩 된 루프 조인을 사용할 수 없습니다.
이 경우 정렬 병합 외부 조인을 사용합니다.
옵티마이 저는 다음과 같은 경우 외부 조인에 정렬 병합을 사용합니다.
중첩 루프 조인은 비효율적입니다. 중첩 루프 조인은 데이터 볼륨 때문에 비효율적 일 수 있습니다.
• 옵티마이 저는 다른 연산에 필요한 정렬 때문에 해시 조인에 대한 정렬 병합을 사용하는 것이 비용이 적게 듭니다.
9.3.2.4 Full Outer Joins
전체 외부 조인은 왼쪽 및 오른쪽 외부 조인의 조합입니다.
내부 조인 외에도 내부 조인의 결과로 리턴되지 않은 두 테이블의 행은 보존되고 널 (NULL)로 확장됩니다.
즉, 완전 외부 조인은 테이블을 함께 조인하지만 조인 된 테이블에는 해당 행이없는 행을 표시합니다.
Example 9-13 Full Outer Join
다음 쿼리는 모든 부서 및 각 부서의 모든 직원을 검색하지만 다음도 포함합니다.
• 부서가없는 직원
• 직원이없는 부서
SELECT d.department_id, e.employee_id
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id
ORDER BY d.department_id;
명령문은 다음과 같은 출력을 생성합니다.
DEPARTMENT_ID EMPLOYEE_ID
------------- -----------
10 200
20 201
20 202
30 114
30 115
30 116
...
270
280
178
207
125 rows selected.
Example 9-14 Execution Plan for a Full Outer Join
Oracle Database 11g부터 Oracle Database는 가능할 때마다 전체 외부 조인을 실행하기 위해 해시 조인 기반의 고유 실행 메소드를 자동으로 사용합니다.
데이터베이스가 새 외부 메소드를 사용하여 전체 외부 조인을 실행하면 쿼리의 실행 계획에 해시 조인 (HASH JOIN FULL OUTER)이 포함됩니다.
예제 9-13 (9-33 페이지)의 쿼리는 다음 실행 계획을 사용합니다.
--------------------------------------------------------------------------------
| Id| Operation | Name |Rows |Bytes |Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 122 | 4758 | 6 (34)| 00:0 0:01 |
| 1 | SORT ORDER BY | | 122 | 4758 | 6 (34)| 00:0 0:01 |
| 2 | VIEW | VW_FOJ_0 | 122 | 4758 | 5 (20)| 00:0 0:01 |
|*3 | HASH JOIN FULL OUTER | | 122 | 1342 | 5 (20)| 00:0 0:01 |
| 4 | INDEX FAST FULL SCAN| DEPT_ID_PK | 27 | 108 | 2 (0)| 00:0 0:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 749 | 2 (0)| 00:0 0:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
HASH JOIN FULL OUTER는 위의 계획 (3 단계)에 포함되어 있으며 쿼리가 해시 전체 외부 조인 실행 방법을 사용함을 나타냅니다.
일반적으로 두 테이블 간의 완전 외부 조인 조건이 동등한 경우 해시 전체 외부 조인 실행 방법이 가능하며 Oracle Database는이를 자동으로 사용합니다.
최적화 프로그램이 해시 전체 외부 조인 실행 메서드를 사용하도록 고려하려면 NATIVE_FULL_OUTER_JOIN 힌트를 적용하십시오.
옵티마이 저가 해시 전체 외부 조인 실행 방법을 사용하지 않도록 지시하려면 NO_NATIVE_FULL_OUTER_JOIN 힌트를 적용하십시오.
NO_NATIVE_FULL_OUTER_JOIN 힌트는 옵티마이 저가 지정된 각 테이블을 결합 할 때 원시 실행 메소드를 제외하도록 지시합니다.
대신 전체 외부 조인은 왼쪽 외부 조인과 안티 조인의 결합으로 실행됩니다.
9.3.2.5 Multiple Tables on the Left of an Outer Join
Oracle Database 12c에서는 외부 조인 된 테이블의 왼쪽에 여러 테이블이 존재할 수 있습니다.
이 향상된 기능을 통해 Oracle Database는 여러 테이블을 포함하고 외부 조인의 왼쪽에 나타나는 뷰를 병합 할 수 있습니다.
Oracle Database 12c 이전 릴리스에서는 다음과 같은 쿼리가 유효하지 않아 ORA-01417 오류 메시지가 나타납니다.
SELECT t1.d, t3.c
FROM t1, t2, t3
WHERE t1.z = t2.z
AND t1.x = t3.x (+)
AND t2.y = t3.y (+);
Oracle Database 12c부터는 앞의 쿼리가 유효합니다.
9.3.3 Semijoins
세미 조인은 일치하는 행이 하위 쿼리 데이터 집합에있을 때 첫 번째 집합에서 행을 반환하는 두 데이터 집합 간의 조인입니다.
데이터베이스는 첫 x 째 일치에서 두 x 째 데이터 세트 처리를 중지합니다.
따라서 두 번째 데이터 세트의 여러 행이 하위 쿼리 기준을 충족시킬 때 최적화는 첫 번째 데이터 세트의 행을 복제하지 않습니다.
노트 :
Semijoins 및 antijoins는 조인 유형으로 간주되지만 SQL 조인은 서브 쿼리입니다.
이들은 최적화 알고리즘이 하위 쿼리 구문을 병합하여 조인 방식으로 해결할 수 있도록하는 내부 알고리즘입니다.
9.3.3.1 When the Optimizer Considers Semijoins
세미 조인은 쿼리에서 일치하는 항목이 있는지 여부 만 확인해야하는 경우 엄청난 수의 행을 반환하지 않습니다.
대용량 데이터 세트의 경우이 최적화는 외부 쿼리의 모든 행에 대해 내부 쿼리에서 반환 된 모든 레코드를 반복해야하는 중첩 루프 조인보다 많은 시간을 절약 할 수 있습니다.
최적화 프로그램은 중첩 루프 조인, 해시 조인 및 정렬 병합 조인에 세미 조인 최적화를 적용 할 수 있습니다.
최적화 프로그램은 다음과 같은 상황에서 세미 조인을 선택할 수 있습니다.
• 문은 IN 또는 EXISTS 절을 사용합니다.
• 명령문에 IN 또는 EXISTS 절에 하위 쿼리가 포함되어 있습니다.
• IN 또는 EXISTS 절이 OR 분기 내에 포함되어 있지 않습니다.
9.3.3.2 How Semijoins Work
세미 조인 최적화는 사용되는 조인 유형에 따라 다르게 구현됩니다.
다음 의사 코드는 중첩 루프 조인에 대한 세미 조인을 보여줍니다.
FOR ds1_row IN ds1 LOOP
match := false;
FOR ds2_row IN ds2_subquery LOOP
IF (ds1_row matches ds2_row) THEN
match := true;
EXIT -- stop processing second data set when a match is found
END IF
END LOOP
IF (match = true) THEN
RETURN ds1_row
END IF
END LOOP
앞의 의사 코드에서 ds1은 첫 번째 데이터 집합이고 ds2_subquery는 부질의 데이터 집합입니다.
코드는 첫 번째 데이터 세트에서 첫 번째 행을 가져온 다음 일치 항목을 찾기 위해 하위 쿼리 데이터 세트를 반복합니다.
코드는 일치하는 것을 찾자 마자 내부 루프를 종료 한 다음 첫 번째 데이터 세트에서 다음 행을 처리하기 시작합니다.
Example 9-15 Semijoin Using WHERE EXISTS
다음 쿼리는 WHERE EXISTS 절을 사용하여 직원이 포함 된 부서 만 나열합니다.
SELECT department_id, department_name
FROM departments
WHERE EXISTS (SELECT 1
FROM employees
WHERE employees.department_id = departments.department_id);
실행 계획은 1 단계에서 NESTED LOOPS SEMI 작업을 나타냅니다.
--------------------------------------------------------------------------------
| Id| Operation | Name |Rows|Bytes|Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | NESTED LOOPS SEMI | | 11 | 209 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
|*3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 44 | 132 | 0 (0)| |
--------------------------------------------------------------------------------
외부 루프를 형성하는 부서의 각 행에 대해 데이터베이스는 부서 ID를 얻은 다음 employees.department_id 색인에서 일치하는 항목을 탐색합니다.
개념적으로 인덱스는 다음과 같습니다.
10,rowid
10,rowid
10,rowid
10,rowid
30,rowid
30,rowid
30,rowid
...
departments 테이블의 첫 번째 항목이 department 30 인 경우 데이터베이스는 처음 30 개의 항목을 찾을 때까지 인덱스의 범위 스캔을 수행하며,이 시점에서 인덱스 읽기를 중지하고 부서에서 일치하는 행을 반환합니다.
외부 루프의 다음 행이 부서 20 인 경우 데이터베이스는 인덱스를 검색하여 일치 항목을 찾지 않고 외부 항목의 다음 반복을 수행합니다.
일치하는 모든 행이 리턴 될 때까지 데이터베이스는 이런 식으로 진행됩니다.
Example 9-16 Semijoin Using IN
다음 쿼리는 IN 절을 사용하여 직원이 포함 된 부서 만 나열합니다.
SELECT department_id, department_name
FROM departments
WHERE department_id IN
(SELECT department_id
FROM employees);
실행 계획은 1 단계에서 NESTED LOOPS SEMI 작업을 나타냅니다.
--------------------------------------------------------------------------------
| Id| Operation | Name |Rows|Bytes|Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | NESTED LOOPS SEMI | | 11 | 209 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
|*3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 44 | 132 | 0 (0)| |
--------------------------------------------------------------------------------
이 계획은 예제 9-15 (9-36 페이지)의 계획과 동일합니다.
9.3.4 Antijoins
안티 조인은 일치하는 행이 하위 쿼리 데이터 집합에 없을 때 첫 번째 집합에서 행을 반환하는 두 데이터 집합 간의 조인입니다.
세미 조인과 마찬가지로 안티 조인은 첫 번째 일치 항목이 발견되면 하위 쿼리 데이터 집합 처리를 중단합니다.
세미 조인과 달리 안티 조인은 일치하는 항목이 없을 때만 행을 반환합니다.
9.3.4.1 When the Optimizer Considers Antijoins
안티 조인은 일치하는 항목이 없을 때 쿼리가 행을 반환하기 만하면 불필요한 처리를 피합니다.
대용량 데이터 세트의 경우이 최적화는 외부 쿼리의 모든 행에 대해 내부 쿼리에서 반환 된 모든 레코드를 반복해야하는 중첩 루프 조인보다 많은 시간을 절약 할 수 있습니다.
옵티마이 저는 중첩 루프 조인, 해시 조인 및 정렬 병합 조인에 안티 조인 최적화를 적용 할 수 있습니다.
옵티마이 저가 다음과 같은 상황에서 안티 조인을 선택할 수 있습니다.
• 명령문은 NOT IN 또는 NOT EXISTS 절을 사용합니다.
• 명령문에 NOT IN 또는 NOT EXISTS 절에 부속 조회가 있습니다.
• NOT IN 또는 NOT EXISTS 절이 OR 분기 내에 포함되어 있지 않습니다.
• 다음 예제와 같이 명령문은 외부 조인을 수행하고 IS NULL 조건을 조인 열에 적용합니다.
SET AUTOTRACE TRACEONLY EXPLAIN
SELECT emp.*
FROM emp, dept
WHERE emp.deptno = dept.deptno(+)
AND dept.deptno IS NULL;
Execution Plan
----------------------------------------------------------
Plan hash value: 1543991079
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 14 | 1400 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 1218 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
9.3.4.2 How Antijoins Work
안티 조인 최적화는 사용되는 조인 유형에 따라 다르게 구현됩니다.
다음 의사 코드는 중첩 루프 조인에 대한 antijoin을 보여줍니다.
FOR ds1_row IN ds1 LOOP
match := true;
FOR ds2_row IN ds2 LOOP
IF (ds1_row matches ds2_row) THEN
match := false;
EXIT -- stop processing second data set when a match is found
END IF
END LOOP
IF (match = true) THEN
RETURN ds1_row
END IF
END LOOP
선행 의사 코드에서 ds1은 첫 번째 데이터 집합이고 ds2는 두 번째 데이터 집합입니다.
코드는 첫 번째 데이터 집합에서 첫 번째 행을 가져온 다음 두 번째 데이터 집합을 반복하여 일치하는 부분을 찾습니다.
코드는 일치하는 것을 찾자 마자 내부 루프를 종료하고 첫 번째 데이터 세트의 다음 행을 처리하기 시작합니다.
Example 9-17 Semijoin Using WHERE EXISTS
다음 쿼리는 WHERE EXISTS 절을 사용하여 직원이 포함 된 부서 만 나열합니다.
SELECT department_id, department_name
FROM departments
WHERE EXISTS (SELECT 1
FROM employees
WHERE employees.department_id = departments.department_id);
실행 계획은 1 단계에서 NESTED LOOPS SEMI 작업을 나타냅니다.
--------------------------------------------------------------------------------
| Id| Operation | Name |Rows|Bytes |Cost(%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | NESTED LOOPS SEMI | | 11 | 209 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
|*3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 44 | 132 | 0 (0)| |
--------------------------------------------------------------------------------
외부 루프를 형성하는 부서의 각 행에 대해 데이터베이스는 부서 ID를 얻은 다음 employees.department_id 색인에서 일치하는 항목을 탐색합니다.
개념적으로 인덱스는 다음과 같습니다.
10,rowid
10,rowid
10,rowid
10,rowid
30,rowid
30,rowid
30,rowid
...
부서 테이블의 첫 번째 레코드가 부서 30 인 경우 데이터베이스는 처음 30 개의 항목을 찾을 때까지 인덱스의 범위 스캔을 수행합니다.이 시점에서 인덱스 읽기는 중지되고 부서에서 일치하는 행을 반환합니다.
외부 루프의 다음 행이 부서 20 인 경우 데이터베이스는 인덱스를 검색하여 일치 항목을 찾지 않고 외부 항목의 다음 반복을 수행합니다.
일치하는 모든 행이 리턴 될 때까지 데이터베이스는 이런 식으로 진행됩니다.
9.3.4.3 How Antijoins Handle Nulls
세미 조인의 경우 IN과 EXISTS는 기능적으로 동일합니다.
그러나 NOT IN과 NOT EXISTS는 기능적으로 동일하지 않습니다.
차이는 널 (null) 때문입니다.
Null 값이 NOT IN 연산자로 반환되면 명령문은 레코드를 반환하지 않습니다.
이유를 보려면 다음 WHERE 절을 고려하십시오.
WHERE department_id NOT IN (null, 10, 20)
데이터베이스는 위의 표현식을 다음과 같이 테스트합니다.
WHERE (department_id != null)
AND (department_id != 10)
AND (department_id != 20)
전체 표현식이 참이 되려면 각 개별 조건이 참이어야합니다.
그러나 널 값은 다른 값과 비교 될 수 없으므로 department_id! = null 조건은 참일 수 없으므로 전체 표현식이 참일 수 없습니다.
다음 기술을 사용하면 문이 NOT IN 연산자로 반환 될 때도 레코드를 반환 할 수 있습니다.
• 하위 쿼리에서 반환 한 열에 NVL 함수를 적용합니다.
• IS NOT NULL 술어를 하위 쿼리에 추가합니다.
• NOT NULL 제약 조건을 구현합니다.
NOT IN과는 달리 NOT EXISTS 절은 일치가 있는지를 리턴하는 술어 만 고려하고, 널 (null)로 인해 일치하지 않거나 판별 할 수없는 행을 무시합니다.
하위 쿼리의 하나 이상의 행이 외부 쿼리의 행과 일치하면 NOT EXISTS가 false를 반환합니다.
튜플이 일치하지 않으면 NOT EXISTS가 true를 반환합니다. 하위 쿼리에 null이 있으면 일치하는 레코드 검색에 영향을주지 않습니다.
Oracle Database 11g 이전 버전에서는 옵티마이 저가 하위 쿼리에서 null을 반환 할 수있는 경우 안티 조인 최적화를 사용할 수 없었습니다.
그러나 Oracle Database 11g부터는 다음 섹션에서 설명하는 ANTI NA (및 ANTI SNA) 최적화를 통해 null이 가능할 때도 옵티마이 저가 안티 조인을 사용할 수 있습니다.
Example 9-18 Antijoin Using NOT IN
사용자가 NOT IN 절과 함께 다음 쿼리를 실행하여 직원이없는 부서를 나열한다고 가정합니다.
SELECT department_id, department_name
FROM departments
WHERE department_id NOT IN
(SELECT department_id
FROM employees);
위의 쿼리는 여러 부서에 직원이 없더라도 행을 반환하지 않습니다.
사용자가 의도하지 않은이 결과는 employees.department_id 열이 Null 가능하기 때문에 발생합니다.
실행 계획은 2 단계에서 NESTED LOOPS ANTI SNA 작업을 나타냅니다.
--------------------------------------------------------------------------------
| Id| Operation | Name |Rows|Bytes|Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4(100)| |
|*1 | FILTER | | | | | |
| 2 | NESTED LOOPS ANTI SNA| | 17 | 323 | 4 (50)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
|*4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 41 | 123 | 0 (0)| |
|*5 | TABLE ACCESS FULL | EMPLOYEES | 1 | 3 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NULL)
4 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
5 - filter("DEPARTMENT_ID" IS NULL)
ANTI SNA는 "단일 null 인식 antijoin"의 약자입니다. ANTI NA는 "nullaware antijoin"의 약자입니다.
널 (Null) 인식 조작은 옵티마이 저가 널 (NULL) 입력 가능 C 럼에서도 Semijoin 최적화를 사용할 수있게합니다.
Oracle Database 11g 이전 릴리스에서는 null이 가능할 때 데이터베이스가 NOT IN 쿼리에 대해 안티 조인을 수행 할 수 없었습니다.
사용자가 하위 쿼리에 IS NOT NULL 조건을 적용하여 쿼리를 다시 작성한다고 가정합니다.
SELECT department_id, department_name
FROM departments
WHERE department_id NOT IN
(SELECT department_id
FROM employees
WHERE department_id IS NOT NULL);
앞의 쿼리는 예상 된 결과 인 16 개의 행을 반환합니다.
계획의 1 단계는 하위 쿼리가 null을 반환 할 수 없기 때문에 ANTI NA 또는 ANTI SNA 조인 대신 표준 NESTED LOOPS ANTI 조인을 보여줍니다.
--------------------------------------------------------------------------------
|Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | | 2 (100)| |
| 1| NESTED LOOPS ANTI | | 17 | 323 | 2 (0)| 00:00:01 |
| 2| TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
|*3| INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 41 | 123 | 0 (0)| |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
filter("DEPARTMENT_ID" IS NOT NULL)
Example 9-19 Antijoin Using NOT EXISTS
사용자가 NOT EXISTS 절과 함께 다음 쿼리를 실행하여 직원이없는 부서를 나열한다고 가정합니다.
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS
(SELECT null
FROM employees e
WHERE e.department_id = d.department_id);
앞의 쿼리는 NOT IN 절에 대한 null 문제를 방지합니다.
따라서 employees.department_id 열이 Null 인 경우에도 명령문은 원하는 결과를 반환합니다.
실행 계획의 1 단계는 ANTI NA 변형이 아닌 NESTED LOOPS ANTI 연산을 나타냅니다.이 연산은 Null이 가능할 때 NOT IN에 필요합니다.
--------------------------------------------------------------------------------
| Id| Operation | Name | Rows | Bytes | Cost (%CPU)|Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | NESTED LOOPS ANTI | | 17 | 323 | 2 (0)|00:00:01|
| 2 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 2 (0)|00:00:01|
|*3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 41 | 123 | 0 (0)| |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
9.4 Join Optimizations
9.4.1 Bloom Filters
작성자 인 Burton Bloom의 이름을 따서 명명 된 블룸 필터는 집합의 구성원을 테스트하는 메모리 부족 데이터 구조입니다.
Bloom 필터는 요소가 집합에없는 경우를 올바르게 나타내지 만 요소가 집합에 포함되어있는 경우이를 잘못 나타낼 수 있습니다.
따라서 위양성은 불가능하지만 위양성이 가능합니다.
9.4.1.1 Purpose of Bloom Filters
블룸 필터는 한 세트의 값이 다른 세트인지 여부를 판별하기 위해 값 세트를 테스트합니다 (예 : set (10,20,30,40) 및 set (10,30,60,70)).
필터는 60과 70이 첫 번째 집합의 멤버가 아니며 10과 30이 멤버 일 수 있음을 보장한다고 판단합니다.
블룸 필터는 필터를 저장하는 데 필요한 메모리 양이 데이터 세트의 데이터 양에 비하여 작고 대부분의 데이터가 멤버십 테스트에 실패 할 것으로 예상되는 경우에 특히 유용합니다.
Oracle Database는 Bloom 필터를 사용하여 다음과 같은 다양한 목표를 달성합니다.
• 병렬 쿼리에서 종속 프로세스로 전송되는 데이터의 양을 줄입니다. 특히 조인 조건을 충족시키지 않아 데이터베이스가 대부분의 행을 삭제하는 경우
• 파티션 정리 목록이라고도하는 조인에서 파티션 액세스 목록을 작성할 때 불필요한 파티션 제거
• 서버 결과 캐시에 데이터가 있는지 테스트하여 디스크 읽기 방지
• Exadata 셀의 멤버 필터링, 특히 스타 스키마의 큰 팩트 테이블과 작은 차원 테이블 조인시 블룸 필터는 병렬 처리와 직렬 처리 모두에서 발생할 수 있습니다.
9.4.1.2 How Bloom Filters Work
Bloom 필터는 비트 배열을 사용하여 집합에 포함을 나타냅니다.
예를 들어, 배열에서 8 개의 요소 (이 예제에 사용 된 임의의 숫자)는 처음에 0으로 설정됩니다.
e1 e2 e3 e4 e5 e6 e7 e8
0 0 0 0 0 0 0 0
이 배열은 집합을 나타냅니다.
이 배열에서 입력 값 i를 나타 내기 위해 세 개의 개별 해시 함수 (이 예제에 사용 된 임의의 숫자)가 i에 적용되며 각 해시 값은 1과 8 사이의 해시 값을 생성합니다.
f1(i) = h1
f2(i) = h2
f3(i) = h3
예를 들어 값 17을이 배열에 저장하려면 해시 함수가 i를 17로 설정 한 후 다음 해시 값을 반환합니다.
f1(17) = 5
f2(17) = 3
f3(17) = 5
앞의 예에서 해시 함수 중 두 개가 해시 충돌이라고하는 동일한 값인 5를 반환했습니다.
별개의 해시 값은 5와 3이므로 배열의 5 번째와 3 번째 요소는 1로 설정됩니다.
e1 e2 e3 e4 e5 e6 e7 e8
0 0 1 0 1 0 0 0
세트 17에서 멤버십을 테스트하면 프로세스가 취소됩니다.
집합이 값 17을 제외하는지 여부를 테스트하려면 요소 3 또는 요소 5에 0이 있어야합니다. 두 요소 중 하나에 0이 있으면 17 개를 포함 할 수 없습니다.
위양성은 없습니다.
집합에 17이 포함되는지 여부를 테스트하려면 요소 3과 요소 5가 모두 하나의 값을 포함해야합니다.
그러나 테스트에서 두 요소 모두에 대해 1을 나타내면 집합에 17이 포함되지 않을 수도 있습니다.
거짓 긍정이 가능합니다.
예를 들어, 다음 배열은 값 22를 나타낼 수 있으며 요소 3과 요소 5에 대해 1을 갖습니다.
e1 e2 e3 e4 e5 e6 e7 e8
1 0 1 0 1 0 0 0
9.4.1.3 Bloom Filter Controls
최적화 프로그램은 블룸 필터 사용 여부를 자동으로 결정합니다.
옵티 마이저 결정을 무시하려면 힌트 PX_JOIN_FILTER 및 NO_PX_JOIN_FILTER를 사용하십시오.
참조 : 블룸 필터 힌트에 대한 추가 정보는 Oracle Database SQL Language Reference를 참조하십시오.
9.4.1.4 Bloom Filter Metadata
다음 동적 성능 뷰에는 Bloom 필터에 대한 메타 데이터가 포함되어 있습니다.
• V$SQL_JOIN_FILTER
이보기는 활성 블룸 필터에 의해 필터링 (FILTERED 열) 및 테스트 (PROBED 열) 행 수를 표시합니다.
• V$PQ_TQSTAT
이 뷰는 실행 트리의 각 단계에서 각 병렬 실행 서버를 통해 처리 된 행 수를 표시합니다.
이를 사용하여 병렬 프로세스에서 얼마나 많은 블룸 필터가 데이터 전송을 줄 였는지 모니터링 할 수 있습니다.
실행 계획에서 Bloom 필터는 Operation 열의 JOIN FILTER 키워드와 다음 Plan Snippet의 9 단계에서와 같이 Name 열의 접두사 : BF로 표시됩니다.
----------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------
...
| 9 | JOIN FILTER CREATE | :BF0000 | Q1,03 | PCWP | |
계획의 술어 정보 섹션에서 문자열 SYS_OP_BLOOM_FILTER로 시작하는 함수를 포함하는 필터는 블룸 필터 사용을 나타냅니다.
9.4.1.5 Bloom Filters: Scenario
이 예에서 병렬 쿼리는 판매 팩트 테이블을 제품 및 시간 차원 테이블에 조인하고 회계 주 18에 필터를 추가합니다.
SELECT /*+ parallel(s) */ p.prod_name, s.quantity_sold
FROM sh.sales s, sh.products p, sh.times t
WHERE s.prod_id = p.prod_id
AND s.time_id = t.time_id
AND t.fiscal_week_number = 18;
DBMS_XPLAN.DISPLAY_CURSOR를 쿼리하면 다음과 같은 결과가 출력됩니다.
SELECT * FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'BASIC,+PARALLEL,+PREDICATE'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT /*+ parallel(s) */ p.prod_name, s.quantity_sold FROM sh.sales s,
sh.products p, sh.times t WHERE s.prod_id = p.prod_id AND s.time_id =
t.time_id AND t.fiscal_week_number = 18
Plan hash value: 3054993226
Plan hash value: 1183628457
------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT |PQ Distrib |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | Q1,03 | P->S | QC (RAND) |
|*3 | HASH JOIN BUFFERED | | Q1,03 | PCWP | |
| 4 | PX RECEIVE | | Q1,03 | PCWP | |
| 5 | PX SEND BROADCAST | :TQ10001 | Q1,01 | S->P | BROADCAST |
| 6 | PX SELECTOR | | Q1,01 | SCWC | |
| 7 | TABLE ACCESS FULL | PRODUCTS | Q1,01 | SCWP | |
|*8 | HASH JOIN | | Q1,03 | PCWP | |
| 9 | JOIN FILTER CREATE | :BF0000 | Q1,03 | PCWP | |
| 10 | BUFFER SORT | | Q1,03 | PCWC | |
| 11 | PX RECEIVE | | Q1,03 | PCWP | |
| 12 | PX SEND HYBRID HASH | :TQ10000 | | S->P |HYBRID HASH |
|*13 | TABLE ACCESS FULL| TIMES | | | |
| 14 | PX RECEIVE | | Q1,03 | PCWP | |
| 15 | PX SEND HYBRID HASH | :TQ10002 | Q1,02 | P->P |HYBRID HASH |
| 16 | JOIN FILTER USE | :BF0000 | Q1,02 | PCWP | |
| 17 | PX BLOCK ITERATOR | | Q1,02 | PCWC | |
|*18 | TABLE ACCESS FULL| SALES | Q1,02 | PCWP | |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("S"."PROD_ID"="P"."PROD_ID")
8 - access("S"."TIME_ID"="T"."TIME_ID")
13 - filter("T"."FISCAL_WEEK_NUMBER"=18)
18 - access(:Z>=:Z AND :Z<=:Z)
filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."TIME_ID"))
실제 실행 결과
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT /*+ parallel(s) */ p.prod_name, s.quantity_sold FROM sh.sales s,
sh.products p, sh.times t WHERE s.prod_id = p.prod_id AND s.time_id =
t.time_id AND t.fiscal_week_number = 18
Plan hash value: 3054993226
--------------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
| 2 | TABLE ACCESS FULL | PRODUCTS |
| 3 | NESTED LOOPS | |
| 4 | NESTED LOOPS | |
|* 5 | TABLE ACCESS FULL | TIMES |
| 6 | PARTITION RANGE ITERATOR | |
| 7 | BITMAP CONVERSION TO ROWIDS | |
|* 8 | BITMAP INDEX SINGLE VALUE | SALES_TIME_BIX |
| 9 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES |
---------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("S"."PROD_ID"="P"."PROD_ID")
5 - filter("T"."FISCAL_WEEK_NUMBER"=18)
8 - access("S"."TIME_ID"="T"."TIME_ID")
단일 서버 프로세스가 시간 테이블을 스캔하고 (13 단계) 하이브리드 해시 분산 메소드를 사용하여 병렬 실행 서버에 행을 보냅니다 (12 단계).
세트 Q1,03의 프로세스는 블룸 필터를 작성합니다 (9 단계).
집합 Q1,02의 프로세스는 병렬로 판매를 스캔 한 다음 (단계 18) 블룸 필터를 사용하여 하이브리드 해시 분산 (단계 15)을 사용하여 Q1,03을 설정하기 전에 Bloom 필터를 사용하여 판매에서 행을 삭제합니다 (단계 16).
집합 Q1,03의 프로세스는 times 행을 필터링 된 sales 행에 결합합니다 (8 단계).
세트 Q1,01의 프로세스는 제품을 스캔하고 (7 단계) Q1,03으로 행을 보냅니다 (5 단계).
마지막으로 Q1,03의 프로세스는 제품 행을 이전 해시 조인 (3 단계)에 의해 생성 된 행에 조인합니다.
다음 그림은 기본 프로세스를 보여줍니다.
Figure 9-8 Bloom Filter
Bloom filter
:BF0000
Q1, 03
Q1, 01 Q1, 02
Create
9.4.2 Partition-Wise Joins
파티션 방식 조인은 조인 키로 분할되어야하는 두 테이블의 큰 조인을 몇 개의 작은 조인으로 나누는 최적화입니다.
파티션 방식의 조인은 다음 중 하나입니다.
전체 파티션 와이즈 조인
두 테이블 모두 조인 키에서 파티션을 나누거나 참조 파티션을 사용해야합니다 (즉, 참조 제한 조건과 관련이 있어야 함).
데이터베이스는 큰 조인을 두 개의 조인 된 테이블에서 두 파티션 사이의 작은 조인으로 나눕니다.
• 부분 파티션 방식의 조인
조인 키에서는 하나의 테이블 만 분할됩니다. 다른 테이블은 분할 될 수도 있고 분할되지 않을 수도 있습니다.
참조 : Oracle Database VLDB 및 Partitioning Guide에서는 파티션 방식의 조인에 대해 자세히 설명합니다.
9.4.2.1 Purpose of Partition-Wise Joins
파티션 방식의 조인은 조인이 병렬로 실행될 때 병렬 실행 서버간에 교환되는 데이터의 양을 최소화하여 쿼리 응답 시간을 줄입니다.
이 기술은 응답 시간을 크게 줄이고 CPU 및 메모리 사용을 향상시킵니다.
Oracle RAC (Oracle Real Application Clusters) 환경에서 파티션 단위 조인은 대규모 상호 조업 작업을위한 우수한 확장 성을 달성하는 데 핵심 인 상호 연결을 통한 데이터 트래픽을 방지하거나 적어도 제한합니다.
9.4.2.2 How Partition-Wise Joins Work
데이터베이스가 파티션 방식의 조인을 사용하지 않고 두 개의 파티션 된 테이블을 순차적으로 조인 할 경우 단일 서버 프로세스가 조인을 수행합니다.
다음 그림에서 서버 프로세스는 테이블 t1의 모든 파티션을 테이블 t2의 모든 파티션에 연결하기 때문에 조인 방식이 파티션 방식이 아닙니다.
Figure 9-9 Join That Is Not Partition-Wise
Server Process
t1 t2
9.4.2.2.1 How a Full Partition-Wise Join Works
데이터베이스는 직렬 또는 병렬로 전체 파티션 - 와이즈 조인을 수행합니다.
다음 그래픽은 병렬로 수행되는 전체 파티션 와이즈 조인을 보여줍니다.
이 경우 병렬 처리의 과립은 파티션입니다.
각 병렬 실행 서버는 쌍으로 파티션을 조인합니다.
예를 들어, 첫 번째 병렬 실행 서버는 t1의 첫 번째 파티션을 t2의 첫 번째 파티션에 조인합니다.
그런 다음 병렬 실행 코디네이터가 결과를 어셈블합니다.
Figure 9-10 Full Partition-Wise Join in Parallel
전체 파티션 와이즈 조인은 파티션을 다른 파티션 방법을 사용할 때 유용하게 사용할 수있는 하위 파티션에 파티션을 조인 할 수 있습니다.
예를 들어 고객은 해시로 분할되지만 판매는 범위별로 분할됩니다.
해시별로 판매를 서브 파티션 화하면 데이터베이스는 고객의 해시 파티션과 판매의 해시 하위 파티션간에 전체 파티션 방식으로 조인을 수행 할 수 있습니다.
실행 계획에서 조인 이전에 파티션 작업이 있으면 다음 조각과 같이 전체 파티션 방식 조인이 있음을 알립니다.
| 8 | PX PARTITION HASH ALL|
|* 9 | HASH JOIN |
참조 : Oracle Database VLDB 및 Partitioning Guide에서는 전체 파티션 방식의 조인에 대해 자세히 설명하고 몇 가지 예가 포함되어 있습니다.
9.4.2.2.2 How a Partial Partition-Wise Join Works
부분 파티션 방식의 조인은 전체 파티션 방식과 달리 병렬로 실행해야합니다.
다음 그래픽은 분할 된 t1과 분할되지 않은 t2 간의 부분적인 분할 방식을 보여줍니다.
Figure 9-11 Partial Partition-Wise Join
t2가 분할되지 않았으므로 병렬 실행 서버 세트는 필요에 따라 t2에서 파티션을 생성해야합니다.
그런 다음 서로 다른 병렬 실행 서버 집합이 t1 파티션을 동적으로 생성 된 파티션에 조인합니다.
병렬 실행 코디네이터가 결과를 어셈블합니다.
실행 계획에서 PX SEND PARTITION (KEY) 작업은 다음 스 니펫 에서처럼 부분 파티션 방식의 조인을 신호로 보냅니다.
| 11 | PX SEND PARTITION (KEY) |
참조 : Oracle Database VLDB 및 Partitioning Guide에서는 전체 파티션 방식의 조인에 대해 자세히 설명하고 몇 가지 예가 포함되어 있습니다.
9.4.3 In-Memory Join Groups
조인 그룹은 의미있게 조인 될 수 있는 두 개 이상의 열을 나열하는 사용자 작성 개체입니다.
특정 쿼리에서 조인 그룹은 컬럼 값의 압축 해제 및 해시의 성능 오버 헤드를 제거합니다.
조인 그룹에는 메모리 내 열 저장소(IM 열 저장소)가 필요합니다.
참조 : 조인 그룹으로 인 메모리 쿼리를 최적화하는 방법은 Oracle Database In-Memory Guide를 참조하십시오.
|
cs |
'Oracle Database SQL Tuning Guide 12c Release 2 (12.2)' 카테고리의 다른 글
| Chapter17.Transporting Optimizer Statistics (0) | 2019.02.05 |
|---|---|
| Chapter08.Optimizer Access Paths (0) | 2019.02.05 |
| Chapter07.Reading Execution Plans (0) | 2019.02.05 |
| Chapter06.Generating and Displaying Execution Plans (0) | 2019.02.05 |
| Chapter05.Query Transformations (0) | 2019.01.01 |
