sql - update - Manera eficiente de actualizar todas las filas en una tabla

modificar un registro en mysql

Question

Tengo una tabla con muchos registros (podría ser más de 500 000 o 1 000 000). Agregué una nueva columna en esta tabla y necesito completar un valor para cada fila en la columna, usando el valor de fila correspondiente de otra columna en esta tabla.

Traté de usar transacciones separadas para seleccionar cada fragmento siguiente de 100 registros y actualizar el valor para ellos, pero todavía lleva horas actualizar todos los registros en Oracle10, por ejemplo.

¿Cuál es la forma más eficiente de hacer esto en SQL, sin utilizar algunas características específicas del dialecto, por lo que funciona en todas partes (Oracle, MSSQL, MySQL, PostGre, etc.)?

INFORMACIÓN ADICIONAL: No hay campos calculados. Hay índices. Se utilizan sentencias SQL generadas que actualizan la tabla fila por fila.

Answer 1

¿Cuál es la versión de la base de datos? Ver columnas virtuales en 11g:

Agregar columnas con un valor predeterminado http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-schemamanagement.html

Answer 2

Como Marcelo sugiere:

UPDATE mytable
SET new_column = <expr containing old_column>;

Si esto lleva demasiado tiempo y falla debido a errores de "instantánea demasiado antigua" (por ejemplo, si la expresión consulta otra tabla altamente activa), y si el nuevo valor de la columna es siempre NO NULO, puede actualizar la tabla en lotes:

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

Simplemente ejecute esta declaración, COMPROMETE, luego ejecútelo nuevamente; enjuague, repita hasta que informe "0 filas actualizadas". Llevará más tiempo, pero es menos probable que falle cada actualización.

EDITAR:

Una mejor alternativa que debería ser más eficiente es usar la API DBMS_PARALLEL_EXECUTE .

Código de muestra (de documentos de 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;
/

Documentos de Oracle: https://docs.oracle.com/database/121/ARPLS/d_parallel_ex.htm#ARPLS67333

Answer 3

La forma habitual es usar ACTUALIZAR:

UPDATE mytable
   SET new_column = <expr containing old_column>

Deberías poder hacer esto es una transacción única.

Answer 4

Puede que no te funcione, pero es una técnica que he usado un par de veces en el pasado para circunstancias similares.

creado updated_ {table_name}, luego seleccione insert en esta tabla en lotes. Una vez finalizado, esto depende de Oracle (que no conozco ni uso) que soporte la capacidad de cambiar el nombre de las tablas de forma atómica. updated_ {table_name} se convierte en {table_name}, mientras que {table_name} se convierte en original_ {table_name}.

La última vez que tuve que hacer esto fue para una tabla muy indexada con varios millones de filas que definitivamente no se pudieron bloquear por el tiempo necesario para realizar algunos cambios serios.

Answer 5

Puede soltar cualquier índice en la tabla, luego hacer su inserción, y luego volver a crear los índices.