표의 모든 행을 효율적으로 업데이트하는 방법

표의 모든 행을 효율적으로 업데이트하는 방법

기록이 많은 테이블이 있습니다(50,000 또는 1,000,000 이상일 수 있음).이 테이블에 새 열을 추가했는데 이 테이블에 있는 다른 열의 해당 행 값을 사용하여 열의 모든 행에 값을 입력해야 합니다.

개별 트랜잭션을 사용하여 100개의 레코드의 다음 청크를 선택하고 값을 업데이트하려고 했지만 Oracle 10의 모든 레코드를 업데이트하려면 몇 시간이 걸립니다.

이를 위해 SQL에서 방언 고유의 기능을 사용하지 않고 어디에서나 사용할 수 있는 가장 효율적인 방법은 무엇입니까(Oracle, MSQL, MySQL, PostGre 등)?

추가 정보: 계산된 필드가 없습니다.색인이 있습니다.테이블 행을 한 행씩 업데이트하는 생성된 SQL 문을 사용합니다.

일반적인 방법은 UPDATE를 사용하는 것입니다.

UPDATE mytable
   SET new_column = <expr containing old_column>

이 작업은 단일 트랜잭션으로 수행할 수 있습니다.

Marcelo가 제안하는 것:

UPDATE mytable
SET new_column = <expr containing old_column>;

이 작업이 너무 오래 걸리고 "스냅샷이 너무 오래됨" 오류로 인해 실패할 경우(예를 들어 식이 다른 활성 테이블을 쿼리하는 경우), 열의 새 값이 항상 NOT NULL인 경우 테이블을 일괄 업데이트할 수 있습니다.

UPDATE mytable
SET new_column = <expr containing old_column>
WHERE new_column IS NULL
AND ROWNUM <= 100000;

이 문을 실행하고 COMMIT를 실행한 후 다시 실행합니다.린스 후 "0 행 updated"라고 표시될 때까지 반복합니다.시간이 더 걸리겠지만 업데이트마다 실패할 가능성은 낮습니다.

편집:

보다 효율적일 필요가 있는 보다 나은 대안은,DBMS_PARALLEL_EXECUTEAPI.

샘플 코드(Oracle 문서에서):

DECLARE
  l_sql_stmt VARCHAR2(1000);
  l_try NUMBER;
  l_status NUMBER;
BEGIN

  -- Create the TASK
  DBMS_PARALLEL_EXECUTE.CREATE_TASK ('mytask');

  -- Chunk the table by ROWID
  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('mytask', 'HR', 'EMPLOYEES', true, 100);

  -- Execute the DML in parallel
  l_sql_stmt := 'update EMPLOYEES e 
      SET e.salary = e.salary + 10
      WHERE rowid BETWEEN :start_id AND :end_id';
  DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask', l_sql_stmt, DBMS_SQL.NATIVE,
                                 parallel_level => 10);

  -- If there is an error, RESUME it for at most 2 times.
  l_try := 0;
  l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('mytask');
  WHILE(l_try < 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED) 
  LOOP
    l_try := l_try + 1;
    DBMS_PARALLEL_EXECUTE.RESUME_TASK('mytask');
    l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('mytask');
  END LOOP;

  -- Done with processing; drop the task
  DBMS_PARALLEL_EXECUTE.DROP_TASK('mytask');

END;
/

Oracle 문서: https://docs.oracle.com/database/121/ARPLS/d_parallel_ex.htm#ARPLS67333

테이블에 인덱스를 드롭하고 삽입한 후 인덱스를 재생성할 수 있습니다.

너한테는 안 통하겠지만, 예전에 비슷한 상황에서 몇 번 써봤던 기술이야.

created updated_{table_name}을 선택한 후 이 테이블에 일괄 삽입을 선택합니다.작업이 완료되면 Oracle(사용할 수 없음)에 의존하여 테이블 이름을 원자적으로 변경할 수 있습니다.updated_{table_name}은(는) {table_name}이(가) {table_name}이(가) 원래_{table_name}이(가) 됩니다.

마지막으로 이 작업을 수행할 필요가 있었던 것은 수백만 개의 행이 있는 인덱스가 많은 테이블을 위해서였습니다.이 테이블은 몇 가지 중대한 변경을 가하는 데 필요한 기간 동안 절대 잠글 수 없었습니다.

데이터베이스 버전은 무엇입니까?11g의 가상 열 확인:

기본값 http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-schemamanagement.html을 사용한 컬럼 추가

호텔 업데이트는 할인=30을 설정합니다. 여기서 Hotelid > = 1 및 Hotelid <= 5504

Postgresql의 경우 다음과 같은 작업을 수행합니다(업데이트/삽입이 더 이상 수행되지 않는 것이 확실한 경우).

create table new_table as table orig_table with data;
update new_table set column = <expr> 
start transaction;
drop table orig_table;
rename new_table to orig_table;
commit;

언급URL : https://stackoverflow.com/questions/2635689/efficient-way-to-update-all-rows-in-a-table