Blog interesante: Andrew Reid.

Hace poco he descubierto el blog de Andrew Reid que no conocía y me ha parecido muy interesante. He leído algunos artículos y tienen muy buena pinta. Trata tanto temas de rendimiento como asuntos de administración, con scripts detallados y tests hechos a conciencia!

Totalmente recomendable!!

Si quieres conocer más sobre el trabajo de Andrew en la red, quizás quieras echarle un ojo a su blog en inglés: http://international-dba.blogspot.com.es/

La autoridad de ORDER BY

Para que el resultado de una consulta SQL esté ordenado, la única cláusula válida es ORDER BY.


Oracle únicamente garantiza la devolución ordenada de las filas cuando se establece ORDER BY como criterio de ordenación.

He repetido estas dos frases como un mantra miles de veces.

Cualquier otra forma de obtener los datos ordenados es:

  • Una casualidad.
  • Una ilusión.
  • Una circunstancia temporal.
  • Una combinación de las anteriores.

Los ejemplos que mostraré a continuación, de resultados ordenados sin cláusula ORDER BY en el comando SELECT, son fruto de su imaginación

FALSO MITO #1 – Si las filas se insertaron de forma ordenada.

SQL> create table objetos as select object_id, object_name, object_type 
   2  from dba_objects order by object_id;

Table created.

SQL> select * from objetos where rownum<10 font="">


 OBJECT_ID OBJECT_NAME          OBJECT_TYPE
———- ——————– ——————-
         2 C_OBJ#               CLUSTER
         3 I_OBJ#               INDEX
         4 TAB$                 TABLE
         5 CLU$                 TABLE
         6 C_TS#                CLUSTER
         7 I_TS#                INDEX
         8 C_FILE#_BLOCK#       CLUSTER
         9 I_FILE#_BLOCK#       INDEX
        10 C_USER#              CLUSTER

9 rows selected.

FALSO MITO #2 – Si se accede a las filas mediante un índice.

SQL> create table objetos_indice as select object_id, object_name, object_type 
   2  from dba_objects;

Table created.

SQL> create index idx_object_id on objetos_indice (object_id);

Index created.

SQL> select * from objetos_indice where object_id between 11256 and 11260;

 OBJECT_ID OBJECT_NAME          OBJECT_TYPE
———- ——————– ——————-
     11256 ALL_APPLY            SYNONYM
     11257 DBA_APPLY_PARAMETERS VIEW
     11258 DBA_APPLY_PARAMETERS SYNONYM
     11259 ALL_APPLY_PARAMETERS VIEW
     11260 ALL_APPLY_PARAMETERS SYNONYM

FALSO MITO #3 – Si la tabla tiene estructura IOT.

SQL> create table objetos_iot
  2  (object_id, object_name, object_type,
  3   constraint pk_objetos_iot primary key (object_id))
  4  organization index
  5  as select object_id, object_name, object_type
  6  from dba_objects;

Table created.


SQL> select * from objetos_iot where object_id between 11256 and 11265;

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
———- —————————— ——————-
     11256 ALL_APPLY                      SYNONYM
     11257 DBA_APPLY_PARAMETERS           VIEW
     11258 DBA_APPLY_PARAMETERS           SYNONYM
     11259 ALL_APPLY_PARAMETERS           VIEW
     11260 ALL_APPLY_PARAMETERS           SYNONYM
     11261 _DBA_APPLY_SOURCE_SCHEMA       VIEW
     11262 _DBA_APPLY_SOURCE_OBJ          VIEW
     11263 DBA_APPLY_INSTANTIATED_OBJECTS VIEW
     11264 DBA_APPLY_INSTANTIATED_OBJECTS SYNONYM
     11265 ALL_APPLY_INSTANTIATED_OBJECTS VIEW

10 rows selected.
FALSO MITO #4 – Si la tabla tiene estructura de cluster.
SQL> create cluster clu_objetos (object_id number) tablespace test;

Cluster created.


SQL> create index idx_cluster_objetos on cluster clu_objetos;

Index created.

SQL> create table padre_objeto (id number constraint pk_objeto primary key) cluster clu_objetos(id);

Table created.

SQL> insert into padre_objeto values (1);

1 row created.

SQL> insert into padre_objeto values (2);

1 row created.

SQL> insert into padre_objeto values (3);

1 row created.

SQL> insert into padre_objeto values (4);

1 row created.

SQL> insert into padre_objeto values (5);

1 row created.

SQL> insert into padre_objeto values (6);

1 row created.

SQL> create table hijo_objeto (id number constraint fk_objeto_padre references padre_objeto(id)) cluster clu_objetos(id);

Table created.

SQL> insert into hijo_objeto values (3);

1 row created.

SQL> insert into hijo_objeto values (6);

1 row created.

SQL> insert into hijo_objeto values (5);

1 row created.

SQL> insert into hijo_objeto values (2);

1 row created.

SQL> select * from hijo_objeto;

        ID
———-
         2
         3
         5
         6
FALSO MITO #5 – Si la sentencia utiliza DISTINCT.

SQL> create table test_orden (texto varchar2(50));

Table created.

SQL> insert into test_orden values (‘PPPPP’);

1 row created.

SQL> insert into test_orden values (‘FFFFF’);

1 row created.

SQL> insert into test_orden values (‘ZZZZZ’);

1 row created.

SQL> insert into test_orden values (‘AAAAA’);


1 row created.

SQL> select texto from test_orden;

TEXTO
————————————————–
PPPPP
FFFFF
ZZZZZ
AAAAA


SQL> select distinct texto from test_orden;

TEXTO
————————————————–
AAAAA
FFFFF
PPPPP
ZZZZZ

FALSO MITO #6 – Si la sentencia utiliza GROUP BY.

SQL> select texto from test_orden;

TEXTO
————————————————–
PPPPP
FFFFF
ZZZZZ
AAAAA

SQL> select texto from test_orden group by texto;

TEXTO
————————————————–
AAAAA
FFFFF
PPPPP

ZZZZZ


FALSO MITO #7 – Si la sentencia utiliza realiza una join de tipo MERGE JOIN.

SQL> create table test_orden2 as select * from test_orden;


Table created.

SQL> select /*+USE_MERGE(t,t2) */ t.texto
  2  from test_orden t, test_orden2 t2
  3  where t.texto=t2.texto;

TEXTO
————————————————–
AAAAA
FFFFF
PPPPP
ZZZZZ
OLVIDEN ESTOS EJEMPLOS!!!!
NO TRATEN DE IMPLEMENTARLOS EN PRODUCCIÓN!!!!
Todas estas ordenaciones casuales, sin uso de la cláusula ORDER BY, son sólo fruto de su imaginación…



¿Te ha parecido interesante esta entrada? 
Si es así, échale un ojo a mi libro sobre Optimización SQL en Oracle.

Índices basados en funciones. Problemas en migraciones de versión.

Una base de datos Oracle 9i tenía una tabla con un campo fecha y un índice basado en función para localizar los valores nulos. La función NVL asignaba un valor ‘NULO’ a los campos vacíos, con el fin de localizar estas filas nulas, y para no dar un conflicto de tipos, convertía la fecha a TO_CHAR.

De este modo, la consulta se ejecutaba así:
Ejecución en Oracle 9i
SQL>  create index fbi_fecha on test(NVL(TO_CHAR(FECHA),’NULO’));

Índice creado.

SQL> explain plan for
  2  select * from test
  3  where NVL(TO_CHAR(FECHA),’NULO’) = ‘NULO’;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
—————————————————————————

—————————————————————————
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
—————————————————————————
|   0 | SELECT STATEMENT            |             |   130 |  1040 |     5 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |   130 |  1040 |     5 |
|*  2 |   INDEX RANGE SCAN          | FBI_FECHA   |   130 |       |     3 |
—————————————————————————

Predicate Information (identified by operation id):
—————————————————

   2 – access(NVL(TO_CHAR(“TEST”.”FECHA”),’NULO’)=’NULO’)

Note: cpu costing is off

15 rows selected.
No obstante, al migrar esta base de datos a Oracle 11g, esta misma sentencia no usaba el índice basado en función, y hacía un acceso FULL SCAN.
Ejecución en Oracle 11g

SQL>  create index fbi_fecha on test(NVL(TO_CHAR(FECHA),’NULO’));

Índice creado.

SQL> explain plan for
  2  select * from test
  3  where NVL(TO_CHAR(FECHA),’NULO’) = ‘NULO’;

Explicado.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
—————————————————————————-
Plan hash value: 1357081020

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      | 10681 | 85448 |   571   (9)| 00:00:07 |
|*  1 |  TABLE ACCESS FULL| TEST | 10681 | 85448 |   571   (9)| 00:00:07 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

   1 – filter(NVL(TO_CHAR(INTERNAL_FUNCTION(“FECHA”)),’NULO’)=’NULO’)

13 filas seleccionadas.
El motivo: aunque la sintaxis de creación de los índices ha sido la misma, internamente su almacenamiento es ligeramente distinto. Mientras en Oracle9i se almacena la función TO_CHAR sin formato de máscara, en Oracle11g se define con un formato de máscara por defecto.
Ejecución en Oracle 9i
SQL> select index_name, column_expression
  2  from user_ind_expressions
  3  where index_name=’FBI_FECHA’;

INDEX_NAME                     COLUMN_EXPRESSION
—————————— ———————————————–
FBI_FECHA                      NVL(TO_CHAR(“FECHA”),’NULO’)


Ejecución en Oracle 11g
SQL> select index_name, column_expression
  2  from user_ind_expressions
  3  where index_name=’FBI_FECHA’;

INDEX_NAME                     COLUMN_EXPRESSION
—————————— ———————————————–
FBI_FECHA                      NVL(TO_CHAR(“FECHA”,’DD/MM/RR’),’NULO’)



De modo que, para que en Oracle 11g el optimizador considere el uso del íncide basado en función FBI_FECHA, la función de filtrado debe ser idéntica y debe incluir la máscara ‘DD/MM/RR’ que se ha añadido a la expresión del índice.
Ejecución en Oracle 11g

SQL> explain plan for
  2  select * from test
  3  where NVL(TO_CHAR(FECHA,’DD/MM/RR’),’NULO’) = ‘NULO’;

Explicado.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
———————————————————————————
Plan hash value: 3576847778

—————————————————————————————–
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————–
|   0 | SELECT STATEMENT            |           |   130 |  2210 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST      |   130 |  2210 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | FBI_FECHA |   130 |       |     3   (0)| 00:00:01 |
—————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

   2 – access(NVL(TO_CHAR(INTERNAL_FUNCTION(“FECHA”),’DD/MM/RR’),’NULO’)=’NULO’)

14 filas seleccionadas.



¿Te ha parecido interesante esta entrada? 
Si es así, échale un ojo a mi libro sobre Optimización SQL en Oracle.