optimización – Optimización SQL en Oracle

El Show de la Comunidad Hispana. Podcast de Junio 2014.

Ya ha salido el programa del mes de junio del Show de la Comunidad Hispana!

¡Episodio con premios!

El Show de la Comunidad Oracle Hispana sigue dándote sorpresas. Escucha el programa y entérate de los nuevos concursos y premios que preparamos para tí. Te contamos quienes son los ganadores de la Trivia y de los Indicios del programa anterior. Traemos mas Indicios y una nueva Trivia. Jugamos el mundial con una aplicación APEX. Javier Morales nos invita a participar en un nuevo concurso. En la entrevista del Show, Deiby Gomez te cuenta todo acerca de Exadata, las nuevas prestaciones de Oracle 12c, y su ambicioso emprendimiento OraWorld. En el segmento de APEX Clarisa te cuenta cómo funciona esta tecnología dentro de la nueva arquitectura multitenant de Oracle 12c. ¡Programa imperdible!

Además, en mi sección sobre optimización SQL cuento por qué Oracle, en ocasiones, no utiliza los índices que nosotros creemos más convenientes! La respuesta a una de las preguntas más frecuentes sobre temas de optimización SQL!!

150.000 GRACIAS! – Concurso «Optimización SQL en Oracle»

Gracias!
Ciento cincuenta mil gracias!!!

El blog ha llegado a las 150.000 visitas desde su inicio, y ¿qué mejor forma que celebrarlo con un sorteo?!

¿Quieres ganar un ejemplar impreso del libro «Optimización SQL en Oracle»?

Las bases son muy simples. Tan sólo tienes que:

1.- Visitar la tienda de Amazon correspondiente a tu región (amazon.mx si vives en México, amazon.es si vives en España y amazon.com para el resto de paises) y accede al libro en versión Kindle para descargarte gratuitamente el fragmento de principio del libro: unas 45 páginas.

2.- Leerlo! (sí sí sí… es muy importante leerlo! así sabré que si sigues adelante con el concurso es porque eres un apasionado del SQL, de la optimización y del mundo Oracle y te encantaría tener este libro!!).

Si no tienes un kindle, puedes descargarte gratis la app para visualizar Kindle para Windows, MacOSx, iOS (iPad, iPhone), android, etc. o incluso leerlo desde tu navegador desde la nube https://read.amazon.com/. No hay excusas!! 🙂

3.- Mandarme un mail a oraclexperto@gmail.com con el texto «CONCURSO Optimización SQL en Oracle» y los siguientes datos:
- Nick en la Comunidad Oracle Hispana. Si no estás registrado, es el momento! Es la comunidad de habla hispana lider con foros, grupos, y más de 4500 usuarios como tú.
- País en el que vives.
- Motivos por los cuales quieres el libro (venga, sé creativo! 🙂 puedes contarme tanto un caso que te haya pasado, o algo que hayas leído referente al principio del eBook (ya sabes, lo que hiciste en el paso 2).

4.- Yo te responderé con una pregunta personalizada, sólo para ti, haciendo referencia al principio del eBook sobre algo que habrás leído. Ahora ves por qué el paso 2 es tan importante!.

5.- Cuando me respondas a la pregunta, publicaré en el blog tu usuario de la COH, tu país y tu número en el sorteo y A ESPERAR!

El día 15 de julio de 2014 termina el plazo de concurso y el ganador será aquel cuyo número coincida con las últimas cifras del sorteo de la Lotería Nacional (España) del sorteo siguiente del jueves 17 de julio.

http://www.loteriasyapuestas.es/va/loteria-nacional#

En caso de no haber ganador, los mismos números jugarán con el siguiente sorteo (sábado 19 de julio), y si no, en los siguientes!

ADVERTENCIA! El libro se enviará desde USA a la dirección del ganador, de modo que puede haber cargos de aduana… En ese caso el ganador correrá con los gastos si los hubiera.

MUCHA SUERTE!!!

El Show de la Comunidad Hispana. Podcast de Mayo 2014.

Ya ha salido el programa del mes de mayo del Show de la Comunidad Hispana!

Si quieres conocer más sobre los productos cartesianos (contado por un servidor), las nuevas funcionalidades de Oracle12c o el programa ACE, no puedes perdértelo de ningún modo!!

Feliz Sant Jordi!

Hoy es el día del libro! Feliz Sant Jordi!

En Amazon España hay un 10% dto en el libro Optimización SQL en Oracle
(para compras en España no hay gastos de envío ni cargos de aduanas)

Los miembros de COH tienen un 35% dto comprando en la tienda de CreateSpace.
Envío internacional desde USA.

Nueva sección en la Comunidad Oracle Hispana.

A partir de abril, el Show de la Comunidad Oracle Hispana tendrá una nueva sección en el podcast con píldoras para mejorar el rendimiento de código SQL en Oracle.

En el minuto 3 empieza mi sección, que arranca con una entrevista. Muchas gracias a Fernando García y a Clarisa Maman por acogerme en este proyecto.

PD: Vaya, este post se quedó en «borrador»… :/

«Optimización SQL en Oracle» disponible en Kindle.

Hoy ha salido a la venta el libro «Optimización SQL en Oracle» en formato Kindle.

Aun hay esperar a que Amazon vincule la edición impresa y la electrónica (dicen que sobre unas 48 horas), sobre todo porque el libro está en Amazon Matchbook, que permite que quien ha comprado la edición impresa pueda descargarse el libro en Kindle gratis, aunque creo que esta opción sólo está disponible en amazon.com por el momento.

¿Por qué ha tardado tanto en salir la edición Kindle?

Para empezar, porque es un libro concebido para un tamaño concreto. Está lleno de líneas de código y outputs de pantalla (en ocasiones con anchos de línea de 140 o 160 caracteres) para que los planes de ejecución puedan leerse correctamente. El libro impreso tiene un tamaño de ancho y alto de 9.6 pulgadas x 6.7 pulgadas (24,6cm x 17cm) y otras pruebas de impresión a formatos más reducidos perdían legibilidad. ¿Has visto alguna vez un plan de ejecución con explain plan en tu iPhone? Complicado.

¿Por qué una edición en Kindle?

Personalmente, pienso que la edición impresa es de lujo. La calidad de impresión es ideal, los contenidos están indexados por palabras al final del libro y, por lo que nos han comentado los lectores, vale la pena tenerlo impreso y leerlo de principio a fin. La maquetación de los contenidos y las imágenes están ajustadas al tamaño del papel. No obstante, tenerlo en papel implica en ocasiones unos gastos de envío (en ciertos países con fuertes cargos de aduana) y unos tiempos de espera considerables.

Con esta edición, el libro puede estar disponible inmediatamente en los dispositivos digitales, y gratis para quien haya adquirido el libro en Amazon (que imagino que serán los usuarios de Kindle), con actualizaciones de contenido que las impresiones registradas con ISBN en papel no permiten, con todas las ventajas que la lectura electrónica ofrece como búsqueda de palabras, diccionario, lectura práctica, lectura en el PC con copy/paste directamente del código del libro, etc.

¿Cómo se han resuelto estos aspectos de visualización?

Para empezar, el libro incluye embebida una fuente gratuita Courier Prime para que el código y el resultado se vea lo más parecido al libro impreso. La mayoría de problemas de se resuelven reduciendo la fuente o usando la visualización horizontal. ADVERTENCIA! No obstante, en ciertos dispositivos como iPhone o Kindle2 (el más pequeño), algunos planes de ejecución más complejos se pueden leen con cierta dificultad.

En estos casos, lo más recomendable es descargarse el código SQL de ejemplo de la web y seguir la lectura practicando con los ejemplos sobre la base de datos.

Preparando la edición Kindle de «Optimización SQL en Oracle»

Aunque el libro está concebido en formato papel, por el ancho de página y los contenidos (código, trazas, etc.) la edición kindle ya está en el horno!

… ¿qué os parece?, ¿alguien interesado?, ¿alguna sugerencia?, ¿dudas?, ¿comentarios?, …

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

—————————————————————————

—————————————————————————

| 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

————————————————————————–

————————————————————————–

| 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

—————————————————————————————–

—————————————————————————————–

| 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.