700M 행의 Oracle 테이블에서 실행되는 업데이트 SQL을 최적화하는 방법

700M 행의 Oracle 테이블에서 실행되는 업데이트 SQL을 최적화하는 방법

UPDATE [TABLE] SET [FIELD]=0 WHERE [FIELD] IS NULL

[TABLE]은 7억개 이상의 행을 가진 Oracle 데이터베이스 테이블입니다.SQL 실행을 6시간만에 취소하였습니다.

성능을 향상시킬 수 있는 SQL 힌트가 있습니까?아니면 그것을 가속화할 다른 해결책이 있습니까?

편집: 이 쿼리는 한 번만 실행되고 다시는 실행되지 않습니다.

우선 일회성 쿼리입니까 아니면 반복 쿼리입니까?한 번만 이 작업을 수행해야 하는 경우 병렬 모드에서 쿼리를 실행하는 방법을 조사할 수 있습니다.어쨌든 모든 행을 검색해야 합니다. ROWID(Do-it-self parallelism) 범위로 워크로드를 직접 분할하거나 Oracle 내장 기능을 사용할 수 있습니다.

이 쿼리를 자주 실행하고 이 쿼리를 최적화하려면fieldNULL로서 열은 전체 행 수에 비해 결국 작습니다.그런 경우 지수가 일을 더 빨리 진행시킬 수 있습니다.Oracle은 색인화된 모든 열이 있는 행을 NULL로 색인화하지 않으므로 인덱스를 on으로 합니다.field쿼리에 사용되지 않습니다(모든 행을 찾으려면fieldNULL)입니다.

둘 중 하나:

• 색인을 만들다(FIELD, 0),그0NULL 의사열이 아닌 역할을 하며 모든 행이 테이블에서 색인화됩니다.

• 함수 기반 인덱스를 만듭니다.(CASE WHEN field IS NULL THEN 1 END), NULL인 행만 인덱싱합니다(따라서 인덱스가 매우 작음).이 경우 쿼리를 다시 작성해야 합니다.

  UPDATE [TABLE] SET [FIELD]=0 WHERE (CASE WHEN field IS NULL THEN 1 END)=1

편집:

이 시나리오는 일회성 시나리오이므로 다음을(를) 사용하는 것이 좋습니다.PARALLEL힌트:

SQL> EXPLAIN PLAN FOR
  2  UPDATE /*+ PARALLEL(test_table 4)*/ test_table
  3     SET field=0
  4   WHERE field IS NULL;

Explained

SQL> select * from table( dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4026746538
--------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |            | 22793 |   289K|    12   (9)| 00:00:
|   1 |  UPDATE               | TEST_TABLE |       |       |            |
|   2 |   PX COORDINATOR      |            |       |       |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000   | 22793 |   289K|    12   (9)| 00:00:
|   4 |     PX BLOCK ITERATOR |            | 22793 |   289K|    12   (9)| 00:00:
|*  5 |      TABLE ACCESS FULL| TEST_TABLE | 22793 |   289K|    12   (9)| 00:00:
--------------------------------------------------------------------------------

다른 사용자가 테이블의 동일한 행을 동시에 업데이트하고 있습니까?

그렇다면 동시성 문제(잠금 대기)가 많이 발생할 수 있으며 이를 소규모 트랜잭션으로 분할할 가치가 있습니다.

DECLARE
  v_cnt number := 1;
BEGIN
 WHILE v_cnt > 0 LOOP
   UPDATE [TABLE] SET [FIELD]=0 WHERE [FIELD] IS NULL AND ROWNUM < 50000;
   v_cnt := SQL%ROWCOUNT;
   COMMIT;
 END LOOP;
END;
/

ROWNUM 제한이 작을수록 동시성/잠금 문제가 덜 발생하지만 테이블 스캔에 더 많은 시간을 할애할 수 있습니다.

빈센트는 이미 당신의 질문에 완벽하게 대답했지만, 저는 이 행동의 배후에 있는 "이유"가 궁금합니다.모든 NULL을 0으로 업데이트하는 이유는 무엇입니까?

안녕, 롭.

몇 가지 제안 사항:

1. UPDATE 문을 실행하기 전에 필드가 포함된 인덱스를 모두 삭제한 다음 나중에 다시 추가합니다.

2. 1000개 또는 10000개의 행 뒤에 커밋하는 PL/SQL 프로시저를 작성합니다.

도움이 되길 바랍니다.

ALTER 테이블을 사용하여 열 "DEFAULT" 값을 0으로 설정하면 업데이트하지 않고도 동일한 결과를 얻을 수 있습니다.

언급URL : https://stackoverflow.com/questions/2964422/how-to-optimize-an-update-sql-that-runs-on-a-oracle-table-with-700m-rows