Orden de los registros y el Order By

Cuando lanzas una query y quieres que te devuelva los datos en una orden específica se utiliza la clausula Order By. Creía que esto era lógico hasta ayer, cuando un cliente empezó a quejarse de que su aplicación dejó de funcionar después de aplicar el Patchset 9.2.0.8 en su base de datos 9.2.0.1.

Los datos son de este tipo y deben ser devuelto en esta orden

[LOT]PALET.PORT[1].PROD
[LOT]PALET.PORT[1].LG_SP
[LOT]PALET.PORT[1].ETAPA
[LOT]PALET.PORT[1].TIPO_OPER

Dspues del parche esta devolviendo en esta orden

[LOT]PALET.PORT[1].ETAPA
[LOT]PALET.PORT[1].PROD
[LOT]PALET.PORT[1].TIPO_OPER
[LOT]PALET.PORT[1].LG_SP

Se quedó inutilizable la aplicación porque la lógica de la aplicación dependía del orden de inserción de los registros (a quien se le ocurre?). Primero tiene que procesar [LOT]PALET.PORT[1].PROD luego [LOT]PALET.PORT[1].LG_SP y asi sucesivamente.

Por definición Oracle no garantiza la orden de devolver los datos a no ser que se especifíca la clausula de Order By, esta claro que los que diseñaron la aplicación no tenian mucha idea.

De todas maneras el orden que necesitan tampoco se podría resolver con un Order By, no sigue ningún criterio. Problema de diseño.

Con un ejemplo podemos ver que pasaria si desarrollas tu aplicación de dicha manera:

 
-- Creamos Tablespace APEX_D con dos datafiles de 1M cada uno
SQL>create tablespace apex_d
  2  datafile
  3  '/u02/oradata/lnx920/apex_d_01.dbf' size 1M,
  4  '/u02/oradata/lnx920/apex_d_02.dbf' size 1M
  5  extent management local uniform size 128K
  6  segment space management manual;

Tablespace created.

-- Creamos una tabla de tal manera que cada registro ocupe un bloque (de 8K). Usamos el campo A como identificador incremental
SQL>create table t1
  2  (
  3     a number,
  4     b varchar2(64),
  5     c varchar2(64),
  6     d varchar2(64),
  7     e varchar2(64),
  8     f varchar2(64)
  9  )
 10  tablespace apex_d
 11  pctused 1
 12  pctfree 99;

Table created.
-- Insertamos 20 registros para que use 20 bloques
SQL>insert into t1
  2  WITH tdata AS
  3  (SELECT ROWNUM ID
  4     FROM all_objects
  5    WHERE ROWNUM <= 10)
  6  SELECT
  7     ROWNUM,
  8     DBMS_RANDOM.STRING ('U', 64),
  9     DBMS_RANDOM.STRING ('U', 64),
 10     DBMS_RANDOM.STRING ('U', 64),
 11     DBMS_RANDOM.STRING ('U', 64),
 12     DBMS_RANDOM.STRING ('U', 64)
 13    FROM tdata a, tdata b
 14   WHERE ROWNUM <= 20;

20 rows created.

SQL>commit;

Commit complete.
-- Vemos en que datafile y bloque estan ubicados cada registro
SQL>select
  2     dbms_rowid.rowid_to_absolute_fno(rowid, 'LSC', 'T1') ABS_FNO,
  3     dbms_rowid.rowid_relative_fno(rowid) REL_FNO,
  4     dbms_rowid.rowid_block_number(rowid) BLOCKNO,
  5     dbms_rowid.rowid_row_number(rowid) ROWNO,
  6     a
  7  from t1;

   ABS_FNO    REL_FNO    BLOCKNO      ROWNO          A
---------- ---------- ---------- ---------- ----------
         5          5         10          0          1
         5          5         11          0          2
         5          5         12          0          3
         5          5         13          0          4
         5          5         14          0          5
         5          5         15          0          6
         5          5         16          0          7
         5          5         17          0          8
         5          5         18          0          9
         5          5         19          0         10
         5          5         20          0         11
         5          5         21          0         12
         5          5         22          0         13
         5          5         23          0         14
         5          5         24          0         15
         6          6          9          0         16
         6          6         10          0         17
         6          6         11          0         18
         6          6         12          0         19
         6          6         13          0         20

20 rows selected.

-- Liberamos espacio de los bloques intermedios borrando 10 registros
SQL>delete t1 where a between 5 and 14;

10 rows deleted.

SQL>commit;

Commit complete.

-- Insertamos otros 10 para que reutilice el espacio liberado
SQL>insert into t1
  2  WITH tdata AS
  3  (SELECT ROWNUM ID
  4     FROM all_objects
  5    WHERE ROWNUM <= 10)
  6  SELECT
  7     ROWNUM + 20, -- Sumamos 20 para que los nuevos registros empiecen por 21 sobre el campo A
  8     DBMS_RANDOM.STRING ('U', 64),
  9     DBMS_RANDOM.STRING ('U', 64),
 10     DBMS_RANDOM.STRING ('U', 64),
 11     DBMS_RANDOM.STRING ('U', 64),
 12     DBMS_RANDOM.STRING ('U', 64)
 13  FROM tdata a, tdata b
 14  WHERE ROWNUM <= 10;

10 rows created.

SQL>commit;

Commit complete.

-- Consultamos otra vez a ver si el resultado que devuelve la query sigue el orden de insercion
SQL>select
  2     dbms_rowid.rowid_to_absolute_fno(rowid, 'LSC', 'T1') ABS_FNO,
  3     dbms_rowid.rowid_relative_fno(rowid) REL_FNO,
  4     dbms_rowid.rowid_block_number(rowid) BLOCKNO,
  5     dbms_rowid.rowid_row_number(rowid) ROWNO,
  6     a
  7  from t1;

   ABS_FNO    REL_FNO    BLOCKNO      ROWNO          A
---------- ---------- ---------- ---------- ----------
         5          5         10          0          1
         5          5         11          0          2
         5          5         12          0          3
         5          5         13          0          4
         5          5         14          1         30
         5          5         15          0         29
         5          5         16          0         28
         5          5         17          0         27
         5          5         18          0         26
         5          5         19          0         25
         5          5         20          0         24
         5          5         21          0         23
         5          5         22          0         22
         5          5         23          0         21
         5          5         24          0         15
         6          6          9          0         16
         6          6         10          0         17
         6          6         11          0         18
         6          6         12          0         19
         6          6         13          0         20

20 rows selected.

Si fijamos en el campo A vemos que aparece el 30 despues del 4 cuando el que desarrolla piensa que el valor deberia ser 15! Porque se ha reutilizado los espacios de los bloques 14 al bloque 23 del fichero 6 que se liberó con el Delete.

2 thoughts on “Orden de los registros y el Order By

  1. ligarius

    Hola Li

    Excelente tu post.. pero sólo una consulta

    ¿Por qué utilizas la sentencia ?

    insert into t1
    WITH tdata AS
    (SELECT ROWNUM ID
    FROM all_objects
    WHERE ROWNUM

    Reply
  2. lscheng Post author

    Hola

    Se llama subquery factorying, creas un sub-bloque con la query dentro de WITH y puedes usar las veces que sea necesario, en este caso la he usado dos veces para generar datos. Con eso evitas materializar dos veces los datos de

    SELECT ROWNUM ID
    FROM all_objects
    WHERE ROWNUM

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s