업데이트 x set y = null에 시간이 오래 걸립니다.

업데이트 x set y = null에 시간이 오래 걸립니다.

직장에는 큰 테이블(40-50개의 열과 같은 300만 개의 행)이 있습니다.때때로 일부 열을 비우고 새 데이터로 채워야 합니다.내가 예상하지 못했던 것은

UPDATE table1 SET y = null

예를 들어 동일한 테이블의 다른 열에서 sql 쿼리로 생성되거나 하위 쿼리의 다른 테이블에서 쿼리되는 데이터로 열을 채우는 것보다 훨씬 많은 시간이 걸립니다.모든 테이블 행을 한 번에 통과하든(위의 업데이트 쿼리에서처럼), 커서를 사용하여 테이블 행을 한 행씩 통과하든(pk 사용) 상관 없습니다.직장에서 큰 테이블을 쓰든, 작은 테스트 테이블을 만들어 수십만 개의 테스트 행을 채우든 상관없습니다.열을 null로 설정하는 것은 항상 일부 동적 데이터(각 행에 대해 다름)로 열을 업데이트하는 것보다 더 오래 걸립니다(테스트를 통해 요인 2 - 10).

그 이유는 무엇입니까?열을 null로 설정하면 Oracle은 어떻게 합니까?또는 - 추론의 오류는 무엇입니까?

도와주셔서 감사합니다!

추신: Oracle 11g2를 사용하고 있는데 plsql developer와 oracle sql developer를 모두 사용하여 이러한 결과를 찾았습니다.

Y 열이 색인화되었습니까?열을 null로 설정하면 Oracle이 인덱스를 업데이트하는 대신 인덱스에서 삭제해야 할 수 있습니다.그런 경우에는 데이터를 업데이트한 후 삭제하고 다시 작성할 수 있습니다.

편집:

문제를 나타내는 것은 Y열뿐입니까, 아니면 업데이트되는 열과 무관합니까?제약 조건을 포함한 테이블 정의를 게시할 수 있습니까?

요약

Oracle이 null을 저장하는 방식을 활용하여 블록의 행을 자주 재구성("heap block compress")하여 많은 추가 실행 취소 및 REDO를 생성하기 때문에 null로 업데이트하는 것이 더 느립니다.

null이 뭐가 그렇게 특별합니까?

Oracle 데이터베이스 개념:

"null은 데이터 값이 있는 열 사이에 있으면 데이터베이스에 저장됩니다.이 경우 열의 길이(0)를 저장하려면 1바이트가 필요합니다.

새 행 헤더는 이전 행의 나머지 열이 null임을 나타내기 때문에 행의 뒤에 있는 null을 저장할 필요가 없습니다.예를 들어 테이블의 마지막 세 열이 null이면 해당 열에 대한 정보가 저장되지 않습니다.열이 많은 테이블에서는 Disk 공간을 절약하기 위해 null을 포함할 가능성이 높은 열을 마지막으로 정의해야 합니다."

시험

업데이트의 실제 비용은 업데이트 문만으로 측정할 수 없기 때문에 업데이트를 벤치마킹하는 것은 매우 어렵습니다.예를 들어 로그 스위치는 업데이트할 때마다 발생하지 않으며 지연된 블록 정리는 나중에 발생합니다.업데이트를 정확하게 테스트하려면 여러 번의 실행이 있어야 하고, 각 실행에 대해 개체를 다시 생성해야 하며, 높음 및 낮음 값은 삭제해야 합니다.

단순화를 위해 아래 스크립트는 높은 결과와 낮은 결과를 내보내지 않고 단일 열이 있는 테이블만 테스트합니다.그러나 이 문제는 열 수, 데이터 및 업데이트되는 열에 관계없이 계속 발생합니다.

저는 http://www.oracle-developer.net/utilities.php 의 RunStats 유틸리티를 사용하여 값으로 업데이트하는 리소스 소비와 값으로 업데이트하는 리소스 소비를 비교했습니다.

create table test1(col1 number);

BEGIN
    dbms_output.enable(1000000);

   runstats_pkg.rs_start;

    for i in 1 .. 10 loop
        execute immediate 'drop table test1 purge';
        execute immediate 'create table test1 (col1 number)';
        execute immediate 'insert /*+ append */ into test1 select 1 col1
            from dual connect by level <= 100000';
        commit;
        execute immediate 'update test1 set col1 = 1';
        commit;
    end loop;

   runstats_pkg.rs_pause;
   runstats_pkg.rs_resume;

    for i in 1 .. 10 loop
        execute immediate 'drop table test1 purge';
        execute immediate 'create table test1 (col1 number)';
        execute immediate 'insert /*+ append */ into test1 select 1 col1
            from dual connect by level <= 100000';
        commit;
        execute immediate 'update test1 set col1 = null';
        commit;
    end loop;

   runstats_pkg.rs_stop();
END;
/

결과

수십 가지 차이점이 있습니다. 이 네 가지가 가장 관련이 있다고 생각합니다.

Type  Name                                 Run1         Run2         Diff
----- ---------------------------- ------------ ------------ ------------
TIMER elapsed time (hsecs)                1,269        4,738        3,469
STAT  heap block compress                     1        2,028        2,027
STAT  undo change vector size        55,855,008  181,387,456  125,532,448
STAT  redo size                     133,260,596  581,641,084  448,380,488

해결책?

제가 생각할 수 있는 유일한 해결책은 테이블 압축을 활성화하는 것입니다.압축된 테이블에는 후행-null 저장 트릭이 발생하지 않습니다.따라서 Run2의 "heap block compress" 수치가 2028년에서 23208년까지 더 높아지지만 실제로는 아무 것도 하지 않습니다.두 실행 사이의 재실행, 실행 취소 및 경과 시간은 테이블 압축이 활성화된 상태에서 거의 동일합니다.

그러나 테이블 압축에는 많은 잠재적인 단점이 있습니다.null로 업데이트하면 실행 속도가 훨씬 빨라지지만 다른 모든 업데이트는 실행 속도가 약간 느려집니다.

이는 해당 데이터를 차단에서 삭제하기 때문입니다.

그리고.delete가장 어려운 작업입니다.피할 수 있으면 그렇게 하세요.

해당 열이 null인 다른 테이블을 생성하는 것이 좋습니다.Create table as select예를 들어, 또는insert select)를 선택하고 절차를 입력합니다.이전 테이블을 삭제한 다음 새 테이블의 이름을 현재 이름으로 변경합니다.

업데이트:

또 다른 중요한 것은 열을 새 값으로 그대로 업데이트해야 한다는 것입니다.그것들을 무효로 설정하고 그 후에 그것들을 다시 채우는 것은 소용이 없습니다.모든 행에 대한 값이 없는 경우 다음과 같이 업데이트할 수 있습니다.

udpate table1 
set y = (select new_value from source where source.key = table1.key)

소스에 존재하지 않는 행을 null로 설정합니다.

저는 톰 키테가 제안한 대규모 업데이트를 시도할 것입니다.거대한 테이블의 경우, 몇 줄을 사용하고, 업데이트하고, 더 많이 사용하고, 업데이트하는 등의 작업을 수행하는 것이 가장 좋습니다.모든 테이블에 대한 업데이트를 시도하지 마십시오.처음부터 살인적인 움직임입니다.

기본적으로 binary_integer 인덱스 테이블을 만들고 한 번에 10개의 행을 가져와 업데이트합니다.

여기 큰 테이블을 성공적으로 사용한 코드가 있습니다.왜냐하면 나는 귀찮고 이제 새벽 2시처럼 여기에 복사해서 붙여넣고 해결할 수 있지만, 도움이 필요하면 알려주세요: 도움이 필요하면 알려주세요.

DECLARE

   TYPE BookingRecord IS RECORD ( 
      bprice  number,
      bevent_id number,
      book_id number
      );

   TYPE array is TABLE of BookingRecord index by binary_integer;
  l_data array;

 CURSOR c1 is
    SELECT LVC_USD_PRICE_V2(ev.activity_version_id,ev.course_start_date,t.local_update_date,ev.currency,nvl(t.delegate_country,ev.sponsor_org_country),ev.price,ev.currency,t.ota_status,ev.location_type) x,
       ev.title,
       t.ota_booking_id
      FROM ota_gsi_delegate_bookings_t@diseulprod t,
           inted_parted_events_t@diseulprod ev
      WHERE t.event_id = ev.event_id
        and t.ota_booking_id = 
BEGIN
   open c1;
        loop
            fetch c1 bulk collect into l_data limit 20;

             for i in 1..l_data.count
               loop
                   update ou_inc_int_t_01 
                      set price = l_data(i).bprice,
                          updated = 'Y'
                    where booking_id = l_data(i).book_id;
               end loop;

           exit when c1%notfound;
       end loop;
       close c1;
END;

업데이트 속도를 높이는 데 도움이 될 수 있는 것은 사용하는 것입니다.alter table table1 nologgingredo redo는 DDL 작업이므로 추가할 수 . 입니다. 이것은 에 다시 .또 다른 방법은 열을 삭제했다가 다시 추가하는 것입니다. DDL 작업이기 때문에 다시 실행하거나 실행 취소하지 않습니다.

언급URL : https://stackoverflow.com/questions/8079026/update-x-set-y-null-takes-a-long-time