Oracle은 두 테이블이 모두 큰 경우에도 항상 HASH JOIN을 사용합니까?

Oracle은 두 테이블이 모두 큰 경우에도 항상 HASH JOIN을 사용합니까?

제가 알기로는 두 테이블 중 하나가 해시 테이블로 메모리에 들어갈 정도로 작을 때만 해시 조인이 가능합니다.

하지만 두 테이블 모두 수억 개의 행이 있는 오라클에 쿼리를 내렸을 때 오라클은 여전히 해시 조인 설명 계획을 내놓았습니다.OPT_ESTIMATE(행 = ...) 힌트로 속여도 병합 정렬 조인 대신 항상 해시 조인을 사용하기로 결정합니다.

그래서 저는 두 테이블 모두 매우 큰 경우에 어떻게 해시 JOIN이 가능한지 궁금합니다.

감사합니다 Yang

해시 조인은 모든 것이 메모리에 맞을 때 가장 잘 작동합니다.그러나 그렇다고 해서 테이블이 메모리에 맞지 않을 때는 여전히 최상의 조인 방법이 아니라는 뜻은 아닙니다.저는 유일하게 현실적인 조인 방법은 병합 정렬 조인이라고 생각합니다.

해시 테이블이 메모리에 맞지 않으면 병합 정렬 조인을 위한 테이블 정렬도 메모리에 맞지 않습니다.병합 조인은 두 테이블을 모두 정렬해야 합니다.제 경험상 해싱은 정렬, 참여, 그룹화보다 항상 빠릅니다.

하지만 몇 가지 예외가 있습니다.Oracle® Database Performance Tuning Guide의 Query Optimizer:

일반적으로 해시 조인은 병합 조인 정렬보다 성능이 좋습니다.그러나 다음 조건이 모두 존재하는 경우 정렬 병합 조인이 해시 조인보다 더 잘 수행될 수 있습니다.

  The row sources are sorted already.
  A sort operation does not have to be done.

---

시험

수억 개의 행을 생성하는 대신 Oracle이 매우 적은 양의 메모리만 사용하도록 강제하는 것이 더 쉽습니다.

이 차트는 테이블이 너무 커서(인공적으로 제한된) 메모리에 들어갈 수 없는 경우에도 해시 조인이 병합 조인을 능가한다는 것을 보여줍니다.

---

메모들

성능 조정을 위해서는 일반적으로 행 수보다 바이트를 사용하는 것이 좋습니다.그러나 표의 "실제" 크기는 측정하기 어렵기 때문에 차트에 행이 표시됩니다.크기는 약 0.375MB에서 최대 14MB까지입니다. 이러한 쿼리가 실제로 디스크에 쓰이는지 두 번 확인하려면 /*+ gather_plan_statistics */를 사용하여 쿼리를 실행한 다음 v$sql_plan_statistics_all을 쿼리합니다.

해시 조인 대 병합 정렬 조인만 테스트했습니다.저는 많은 양의 데이터에서 조인 방법이 항상 매우 느리기 때문에 중첩 루프를 완전히 테스트하지 않았습니다.건전성 검사를 위해, 저는 그것을 마지막 데이터 크기와 한 번 비교했고, 제가 그것을 죽이기까지 적어도 몇 분이 걸렸습니다.

또한 다양한 _area_size, 정렬 및 정렬되지 않은 데이터, 조인 열의 고유성(일치하는 항목이 많을수록 CPU 바인딩, 일치하는 항목이 적을수록 IO 바인딩)을 테스트하여 비교적 유사한 결과를 얻었습니다.

하지만 메모리 양이 터무니없이 적을 때 결과는 달랐습니다.32K sort|hash_area_size만 있으면 병합 정렬 조인 속도가 훨씬 빨라졌습니다.하지만 기억력이 너무 적다면 걱정해야 할 더 중요한 문제가 있을 것입니다.

병렬화, 하드웨어, 블룸 필터 등 고려해야 할 다른 변수가 여전히 많습니다.사람들은 아마 이 주제에 대한 책을 썼을 것입니다. 저는 그 가능성의 아주 작은 부분도 시험해 본 적이 없습니다.하지만 이것으로 해시 조인이 대용량 데이터에 가장 적합하다는 일반적인 합의를 확인할 수 있기를 바랍니다.

---

코드

다음은 제가 사용한 스크립트입니다.

--Drop objects if they already exist
drop table test_10k_rows purge;
drop table test1 purge;
drop table test2 purge;

--Create a small table to hold rows to be added.
--("connect by" would run out of memory later when _area_sizes are small.)
--VARIABLE: More or less distinct values can change results.  Changing
--"level" to something like "mod(level,100)" will result in more joins, which
--seems to favor hash joins even more.
create table test_10k_rows(a number, b number, c number, d number, e number);
insert /*+ append */ into test_10k_rows
    select level a, 12345 b, 12345 c, 12345 d, 12345 e
    from dual connect by level <= 10000;
commit;

--Restrict memory size to simulate running out of memory.
alter session set workarea_size_policy=manual;

--1 MB for hashing and sorting
--VARIABLE: Changing this may change the results.  Setting it very low,
--such as 32K, will make merge sort joins faster.
alter session set hash_area_size = 1048576;
alter session set sort_area_size = 1048576;

--Tables to be joined
create table test1(a number, b number, c number, d number, e number);
create table test2(a number, b number, c number, d number, e number);

--Type to hold results
create or replace type number_table is table of number;

set serveroutput on;

--
--Compare hash and merge joins for different data sizes.
--
declare
    v_hash_seconds number_table := number_table();
    v_average_hash_seconds number;
    v_merge_seconds number_table := number_table();
    v_average_merge_seconds number;

    v_size_in_mb number;
    v_rows number;
    v_begin_time number;
    v_throwaway number;

    --Increase the size of the table this many times
    c_number_of_steps number := 40;
    --Join the tables this many times
    c_number_of_tests number := 5;

begin
    --Clear existing data
    execute immediate 'truncate table test1';
    execute immediate 'truncate table test2';

    --Print headings.  Use tabs for easy import into spreadsheet.
    dbms_output.put_line('Rows'||chr(9)||'Size in MB'
        ||chr(9)||'Hash'||chr(9)||'Merge');

    --Run the test for many different steps
    for i in 1 .. c_number_of_steps loop
        v_hash_seconds.delete;
        v_merge_seconds.delete;
        --Add about 0.375 MB of data (roughly - depends on lots of factors)
        --The order by will store the data randomly.
        insert /*+ append */ into test1
        select * from test_10k_rows order by dbms_random.value;

        insert /*+ append */ into test2
        select * from test_10k_rows order by dbms_random.value;

        commit;

        --Get the new size
        --(Sizes may not increment uniformly)
        select bytes/1024/1024 into v_size_in_mb
        from user_segments where segment_name = 'TEST1';

        --Get the rows.  (select from both tables so they are equally cached)
        select count(*) into v_rows from test1;
        select count(*) into v_rows from test2; 

        --Perform the joins several times
        for i in 1 .. c_number_of_tests loop
            --Hash join
            v_begin_time := dbms_utility.get_time;
            select /*+ use_hash(test1 test2) */ count(*) into v_throwaway
            from test1 join test2 on test1.a = test2.a;
            v_hash_seconds.extend;
            v_hash_seconds(i) := (dbms_utility.get_time - v_begin_time) / 100;

            --Merge join
            v_begin_time := dbms_utility.get_time;
            select /*+ use_merge(test1 test2) */ count(*) into v_throwaway
            from test1 join test2 on test1.a = test2.a;
            v_merge_seconds.extend;
            v_merge_seconds(i) := (dbms_utility.get_time - v_begin_time) / 100;
        end loop;

        --Get average times.  Throw out first and last result.
        select ( sum(column_value) - max(column_value) - min(column_value) ) 
            / (count(*) - 2)
        into v_average_hash_seconds
        from table(v_hash_seconds);

        select ( sum(column_value) - max(column_value) - min(column_value) ) 
            / (count(*) - 2)
        into v_average_merge_seconds
        from table(v_merge_seconds);

        --Display size and times
        dbms_output.put_line(v_rows||chr(9)||v_size_in_mb||chr(9)
            ||v_average_hash_seconds||chr(9)||v_average_merge_seconds);

    end loop;
end;
/

그래서 저는 두 테이블 모두 매우 큰 경우에 어떻게 해시 JOIN이 가능한지 궁금합니다.

이 작업은 여러 경로를 통해 수행됩니다. 구동되는 테이블을 읽고 청크로 해시하며, 선행 테이블을 여러 번 스캔합니다.

이것은 제한된 메모리 해시 조인 스케일이 다음과 같다는 것을 의미합니다.O(N^2)이 합조스케에서 조정되는 :O(N)(물론 정렬이 필요하지 않음), 그리고 정말 큰 테이블에서는 merge가 해시 조인을 능가합니다.그러나 단일 읽기의 이점이 비순차적 액세스의 단점을 능가할 수 있도록 테이블이 매우 커야 하며, 이러한 단점의 모든 데이터가 필요합니다(일반적으로 집계됨).

때려가 ,RAM현대 서버의 크기에 따라, 우리는 당신이 일상 생활에서 실제로 볼 수 있는 것이 아니라, 구축하는 데 몇 시간이 걸리는 정말 큰 데이터베이스에 대한 정말 큰 보고서에 대해 이야기하고 있습니다.

MERGE JOIN이 출레 코세다가같음이제한경될수있유우로 할 수 있습니다.rownum < N하며, 둘 다 되어 있다는 을 의미합니다.NESTED LOOPS조인 조건이 선택적일 때 더 효율적이기 때문에 최적화 도구에 의해 일반적으로 선택됩니다.

으로, 그의현재구통해을현들,MERGE JOIN 및 항상검및NESTED LOOPS(통계로 백업되는) 두 가지 방법의 보다 현명한 조합이 선호되는 반면, 항상 검색합니다.

제 블로그에서 이 기사를 읽고 싶으실 수도 있습니다.

• SQL에 필요한 것: MERGE JOIN을 찾는 것

해시 조인은 전체 테이블을 메모리에 넣을 필요가 없고 해당 테이블의 where 조건과 일치하는 행(또는 해시 + 행 ID만 해당)만 해당됩니다.

따라서 Oracle은 테이블 중 하나에 영향을 미치는 조건이 충분히 적합하다고 판단할 경우(즉, 몇 개의 행을 해시해야 함) 매우 큰 테이블에서도 해시 조인을 선호할 수 있습니다.

언급URL : https://stackoverflow.com/questions/8188093/oracle-always-uses-hash-join-even-when-both-tables-are-huge