두 인덱스에서 병합 조인이 여전히 정렬을 유발합니까?

두 인덱스에서 병합 조인이 여전히 정렬을 유발합니까?

이것은 두 인덱스를 결합하기 위해 단순화된 성능 질문입니다.다음 설정을 수행합니다.

CREATE TABLE ZZ_BASE AS SELECT dbms_random.random AS ID, DBMS_RANDOM.STRING('U',10) AS STR FROM DUAL CONNECT BY LEVEL <=1000000;
CREATE INDEX ZZ_B_I ON ZZ_BASE(ID ASC); 
CREATE TABLE ZZ_CHILD AS SELECT dbms_random.random AS ID, DBMS_RANDOM.STRING('U',10) AS STR FROM DUAL CONNECT BY LEVEL <=1000000;
CREATE INDEX ZZ_C_I ON ZZ_CHILD(ID ASC);

-- As @Flado pointed out, the following is required so index scanning can be done
ALTER TABLE ZZ_BASE MODIFY (ID CONSTRAINT NN_B NOT NULL); 
ALTER TABLE ZZ_CHILD MODIFY (ID CONSTRAINT NN_C NOT NULL); -- given the join below not mandatory.

이제 이 두 테이블을 LEFT OUTER JOIN하고 이미 인덱스된 ID 필드만 출력하려고 합니다.

SELECT  ZZ_BASE.ID 
FROM ZZ_BASE 
LEFT OUTER JOIN ZZ_CHILD ON (ZZ_BASE.ID = ZZ_CHILD.ID);
----------------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |  1000K|  9765K|       |  4894   (2)| 00:00:30 |
|*  1 |  HASH JOIN OUTER      |        |  1000K|  9765K|    16M|  4894   (2)| 00:00:30 |
|   2 |   INDEX FAST FULL SCAN| ZZ_B_I |  1000K|  4882K|       |   948   (3)| 00:00:06 |
|   3 |   INDEX FAST FULL SCAN| ZZ_C_I |  1000K|  4882K|       |   948   (3)| 00:00:06 |
----------------------------------------------------------------------------------------

테이블 액세스는 필요하지 않으며 인덱스 액세스만 필요합니다.하지만 상식적으로 해시 조인은 이 두 인덱스를 결합하는 가장 최적의 방법이 아닙니다.이 두 테이블이 훨씬 더 크면 매우 큰 해시 테이블을 만들어야 합니다.

훨씬 더 효율적인 방법은 두 인덱스를 SORT-Merge하는 것입니다.

SELECT /*+ USE_MERGE(ZZ_BASE ZZ_CHILD) */ ZZ_BASE.ID 
FROM ZZ_BASE 
LEFT OUTER JOIN ZZ_CHILD ON (ZZ_BASE.ID = ZZ_CHILD.ID);
-----------------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |  1000K|  9765K|       |  6931   (3)| 00:00:42 |
|   1 |  MERGE JOIN OUTER      |        |  1000K|  9765K|       |  6931   (3)| 00:00:42 |
|   2 |   INDEX FULL SCAN      | ZZ_B_I |  1000K|  4882K|       |  2258   (2)| 00:00:14 |
|*  3 |   SORT JOIN            |        |  1000K|  4882K|    22M|  4673   (4)| 00:00:29 |
|   4 |    INDEX FAST FULL SCAN| ZZ_C_I |  1000K|  4882K|       |   948   (3)| 00:00:06 |
-----------------------------------------------------------------------------------------

그러나 이미 "인덱스가 있는 경우 데이터베이스는 첫 번째 데이터 집합을 정렬하지 않을 수 있습니다.그러나 데이터베이스는 인덱스에 관계없이 항상 두 번째 데이터 집합을 정렬합니다.")¹

기본적으로 제가 원하는 것은 SORT-MERGE 조인을 사용하여 즉시 레코드 출력을 시작하는 쿼리입니다. 예:

• 해시 테이블(디스크에 저장된 경우 IO 오버헤드)을 먼저 만들어야 하므로 해시 조인이 없습니다.
• 즉시 출력되지만 인덱스가 큰 경우 인덱스 포인터에 log(N) 복잡성이 있고 비순차 인덱스 읽기에 큰 IO 오버헤드가 있는 NESTED LOOP 조인이 없습니다.

INDEX_ASC(또는 INDEX만)는 성능을 실제 데이터와 비교하기 위해 시도해 볼 수 있는 힌트입니다.

B*Tree 인덱스는 NULL 키와 ZZ_를 찾을 수 없기 때문에 외부 행 소스에 대한 인덱스 스캔을 수행한다는 사실에 조금 놀랐습니다.BASE에 다음이 없습니다.NOT NULL속박이를 추가하고 조금 더 암시하면 ZZ_C_I 인덱스의 인덱스 순서로 전체 스캔을 수행할 수 있습니다.그것은 당신을 구해주지 않습니다.SORT JOIN불행히도 데이터가 이미 정렬되었기 때문에 최소한 O(n)는 훨씬 빨라야 합니다.

alter table zz_base modify (id not null);
SELECT 
  /*+ leading(zz_base) USE_MERGE(ZZ_CHILD) 
      index_asc(zz_base (id)) index(zz_child (id)) */ ZZ_BASE.ID 
FROM ZZ_BASE left outer join ZZ_CHILD on zz_base.id=zz_child.id;

이 쿼리는 다음 실행 계획을 사용합니다.

------------------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |  1000K|  9765K|       |  8241   (3)| 00:00:50 |
|   1 |  MERGE JOIN OUTER |        |  1000K|  9765K|       |  8241   (3)| 00:00:50 |
|   2 |   INDEX FULL SCAN | ZZ_B_I |  1000K|  4882K|       |  2258   (2)| 00:00:14 |
|*  3 |   SORT JOIN       |        |  1000K|  4882K|    22M|  5983   (3)| 00:00:36 |
|   4 |    INDEX FULL SCAN| ZZ_C_I |  1000K|  4882K|       |  2258   (2)| 00:00:14 |
------------------------------------------------------------------------------------

언급URL : https://stackoverflow.com/questions/34352852/merge-join-on-two-indexes-still-causing-a-sort