What's your opinion about updating only if necessary

From: Juan Cachito Reyes Pacheco <jreyes_at_dazasoftware.com>
Date: Fri, 20 Feb 2004 11:22:07 -0400
Message-ID: <005201c3f7c5$4eb1a9b0$2501a8c0@dazasoftware.com>

Like in this example, I had seen several times is better update only the necessary
WHEN you have several columns with the values you want to update. Specially check the redo size.

SQL> UPDATE CUENTASF SET

  2       CUF_AUTORIZACIONHOY=0,
  3       CUF_DEPOSITOHOY=0,
  4       CUF_NRORETHOY=0,
  5       CUF_RETIROHOY = 0
  6     WHERE NOT CUF_STATUS = 'CE';

147 filas actualizadas.

 real: 90

Statistics

---

          0  recursive calls
        301  db block gets
        171  consistent gets
          0  physical reads
      68804  redo size
        405  bytes sent via SQL*Net to client
        664  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        147  rows processed

SQL> rollback;

Rollback terminado.

 real: 50
SQL> UPDATE CUENTASF SET

  2       CUF_AUTORIZACIONHOY=0,
  3       CUF_DEPOSITOHOY=0,
  4       CUF_NRORETHOY=0,
  5       CUF_RETIROHOY = 0
  6     WHERE NOT CUF_STATUS = 'CE' AND
  7           NOT (CUF_AUTORIZACIONHOY=0 AND  CUF_DEPOSITOHOY=0 AND

CUF_NRORETHO
Y=0 AND CUF_RETIROHOY = 0 ); 2 filas actualizadas.

 real: 50

Statistics

---

          0  recursive calls
          5  db block gets
        171  consistent gets
          0  physical reads
       1056  redo size
        405  bytes sent via SQL*Net to client
        771  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> rollback;

Rollback terminado.

 real: 10
SQL>

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

Received on Fri Feb 20 2004 - 09:22:07 CST