¿Podemos hacer algo más que dar la recomendación obvia de crear un nuevo índice global?
Seguro!, por lo menos hacer estas dos revisiones rápidas.
Lo primero es ver si se usa la columna de particionamento en la condición del where.
Si se usa, la consulta solo leerá datos de esa partición (el optimizador aplica partition pruning), por lo tanto un nuevo índice podría ser útil siempre que las particiones tengan un volumen de datos que lo justifique (muchas particiones con pocos registros no se van a beneficiar de un nuevo índice).
Si no se usa la columna de particionamento en el where, todavía queda algo más a analizar: ¿el criterio de búsqueda tiene alguna correlación con el criterio de particionamiento? Esto es una relación funcional sobre los datos y que no podemos inferir a partir del texto de la consulta.
Podemos analizar los datos buscando este vínculo, y si se llega a confirmar podríamos hacer que la consulta sea más eficiente (recorra menos registros) modificandola, agregando una simple condición columna_partición=valor.
¿Cómo podemos hacer este análisis? Con la ayuda de la función DBMS_MVIEW.PMARKER, que dado un registro (rowid) retorna el nombre de la partición donde está almacenado.
Todo esto se puede ver mejor con un ejemplo. Usé Oracle 11.2.0.2 Enterprise Edition
Tenemos la tabla bigtbl_part particionada por rangos de la columna tipo, y un índice global:
12:40:43 PRU@ent11g> create table bigtbl_part (id, estado, tipo, clase, dato)
partition by range(tipo) (
partition p0 values less than (1),
partition p1 values less than (2),
partition p2 values less than (3),
partition p3 values less than (4),
partition p4 values less than (5)
) as
select rownum id,
floor(dbms_random.value(1,20)) estado,
mod(rownum,5) tipo,
mod(rownum,10) clase,
'relleno' dato
from all_objects, all_objects
Table created.
Elapsed: 00:00:53.14
12:41:37 PRU@ent11g> create index idx1 on bigtbl_part(id) local;
Index created.
Elapsed: 00:00:15.90
12:42:20 PRU@ent11g> execute dbms_stats.gather_table_stats(user,'bigtbl_part')
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.99
Vemos cómo quedaron generados los datos. Nos vamos a enfocar en las clases y tipos:
12:42:40 PRU@ent11g>select count(*), clase from bigtbl_part group by clase order by clase;
COUNT(*) CLASE
---------- ----------
100000 0
100000 1
100000 2
100000 3
100000 4
100000 5
100000 6
100000 7
100000 8
100000 9
10 rows selected.
Los valores distintos de CLASE por cada partición (columna TIPO):
12:46:29 PRU@ent11g> select count(distinct clase), tipo
from bigtbl_part
group by tipo
order by tipo;
COUNT(DISTINCTCLASE) TIPO
-------------------- ----------
2 0
2 1
2 2
2 3
2 4
Y cómo quedaron distribuidos los datos en cada partición:
12:47:40 PRU@ent11g> col partition_name for a10
12:47:41 PRU@ent11g> select partition_name, num_rows, high_value
from user_TAB_PARTITIONS
where table_name='BIGTBL_PART';
PARTITION_ NUM_ROWS HIGH_VALUE
---------- ---------- --------------------------------------------------------------------------------
P0 200000 1
P1 200000 2
P2 200000 3
P3 200000 4
P4 200000 5
Esta es la consulta que nos interesa mejorar:
SELECT count(*)
FROM bigtbl_part
WHERE clase = :clase and estado = :estado;
Para fijar ideas usamos una combinación de valores cualquiera: buscamos registros de clase 3 y estado 15:
12:49:52 PRU@ent11g> var clase number;
12:49:52 PRU@ent11g> var estado number;
12:49:52 PRU@ent11g> exec :clase := 3;
PL/SQL procedure successfully completed.
12:49:52 PRU@ent11g> exec :estado := 15;
PL/SQL procedure successfully completed.
12:49:53 PRU@ent11g> set autotrace on explain
SELECT count(*)
FROM bigtbl_part
WHERE clase = :clase and estado = :estado;
COUNT(*)
----------
5265
Execution Plan
----------------------------------------------------------
Plan hash value: 1165574620
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 1047 (2)| 00:00:13 | | |
| 1 | SORT AGGREGATE | | 1 | 6 | | | | |
| 2 | PARTITION RANGE ALL| | 5263 | 31578 | 1047 (2)| 00:00:13 | 1 | 5 |
|* 3 | TABLE ACCESS FULL | BIGTBL_PART | 5263 | 31578 | 1047 (2)| 00:00:13 | 1 | 5 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ESTADO"=TO_NUMBER(:ESTADO) AND "CLASE"=TO_NUMBER(:CLASE))
Vemos que para resolverla el optimizador tuvo que recorrer todas las particiones de la tabla, y cada una sin usar índice.
Podemos tener facilmente una idea de la cantidad máxima de registros que se necesitan analizar para responder esta consulta (peor caso):
12:53:04 PRU@ent11g>
select count(*), min(count(*)), max(count(*))
from BIGTBL_part
group by clase, estado;
COUNT(*) MIN(COUNT(*)) MAX(COUNT(*))
---------- ------------- -------------
190 5071 5500
Pero si queremos además saber en qué particiones están estos datos, no alcanza con ver la cantidad de registros que tiene cada partición (columna num_rows de DBA_TAB_PARTITIONS), ya que no estamos buscando dentro de una sola partición.
Tenemos que agregar a la consulta original el uso de la función dbms_mview.pmarker, y así ver si hay afinidad de los datos buscados (de la condición de agrupación) con las particiones.
Esta es una primera versión simple de la consulta, donde podemos ver los distintos valores de CLASES almacenados en cada partición:
12:54:12 PRU@ent11g>
select count(*) registros
,dbms_mview.pmarker(rowid) data_object_id
,count(distinct clase) clase_x_tipo
FROM bigtbl_part
group by dbms_mview.pmarker(rowid);
REGISTROS DATA_OBJECT_ID CLASE_X_TIPO
---------- -------------- ------------
200000 75537 2
200000 75539 2
200000 75540 2
200000 75538 2
200000 75536 2
Elapsed: 00:00:05.93
Agregando la condición original, podemos ver en qué particiones están los datos obtenidos:
12:56:51 PRU@ent11g>
select count(*) registros
,dbms_mview.pmarker(rowid) data_object_id
FROM bigtbl_part
WHERE clase = :clase and estado = :estado
12:56:51 5 group by dbms_mview.pmarker(rowid);
REGISTROS DATA_OBJECT_ID
---------- --------------
5265 75539
Los 5265 registros que retorna la consulta se obtienen de la partición 75539.
Podemos ver el nombre de la partición consultando USER_OBJECTS:
12:58:17 PRU@ent11g>
select subobject_name
from user_objects
WHERE data_object_id=75539;
SUBOBJECT_NAME
------------------------------------------------------------------------------------------
P3
En resumen, aunque los datos se obtienen todos de la misma partición, la consulta recorre todas las particiones porque no conoce la relación entre los datos (clase y tipo).
En este ejemplo los datos se generaron de esta forma para ilustrar el caso donde se puede mejorar la consulta con la ayuda de los programadores de la aplicación, quienes pueden agregar una condición más al where sobre la columna TIPO con el valor correspondiente (ya que la relación entre los valores de estas columnas pueden ser conocidos), y así mejorar la peformance de la misma.
La consulta modificada quedaría así:
12:59:28 PRU@ent11g>
SELECT count(*)
FROM bigtbl_part
WHERE clase = :clase and estado = :estado and tipo = :tipo;
Plan hash value: 4104149755
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 211 (2)| 00:00:03 | | |
| 1 | SORT AGGREGATE | | 1 | 9 | | | | |
| 2 | PARTITION RANGE SINGLE| | 1053 | 9477 | 211 (2)| 00:00:03 | KEY | KEY |
|* 3 | TABLE ACCESS FULL | BIGTBL_PART | 1053 | 9477 | 211 (2)| 00:00:03 | KEY | KEY |
-------------------------------------------------------------------------------------------------------
Si bien se mantiene el acceso sin índice, ahora es sobre la partición y no sobre todas las particiones.
Esto todavía puede mejorarse incluyendo un índice local por clase y estado si el volumen de las particiones lo amerita.
Si no se hubiera podido modificar la consulta, la recomendación clásica habría sido un índice global por clase y estado.
Otro tema relacionado a mejorar la performance de consultas sobre tablas particionadas, es la utilidad de tener índices que incluyan la columna de partición (conocidos como prefixed local index). Una buena discusión sobre el tema se puede encontrar en este thread de los foros de OTN
Espero les sea útil.
Un saludo y feliz comienzo de año!
