Ejecutar subquerys en vista materializada

Question

Consulta. La siguiente es una consulta que utiliza tres tablas:

(Todo de acá en más está anonimizado, puede deslizarse algún error involuntario)

SELECT id
    ,id_dependencia
    ,descripcion_corta NOMBRE
    ,to_char(fecha_fallecimiento, 'YYYY-MM-DD') FECHA_FALLECIMIENTO
    --DOMICILIO
    ,d.direccion DOMICILIO
    ,d.localidad
    --,(select nombre_provincia from t_3 where cod_provincia = d.id_provincia) PROVINCIA
    ,p.nombre_provincia PROVINCIA
FROM t_1
    ,t_2 d
    ,t_3 p
WHERE id = d.Id_persona
    AND d.id_tipo_domicilio = 1
    AND cod_provincia = d.id_provincia;

Esta consulta es la traducción de otra consulta, porque las VM no permiten subconsultas para construirlas (nótese que PROVINCIA está definida dos veces, porque no se puede tener un SELECT anidado).

El problema que surge de esto, es que la consulta final termina haciendo un full scan de la tabla t_2, que tiene los domicilios.

Y, ¿por qué se da esto? Porque la tabla t_3 está unida a t_2 por cod_provincia = d.id_provincia, pero sin embargo, y la tabla t_1 se une a la t_2 por id = d.Id_persona, pero de t_2 solo se requiere un tipo de domicilio.

El explain plan dice esto:

| Id  | Operation           | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |  9081K|  1688M|       |   615K  (1)| 02:03:03 |        |      |
|*  1 |  HASH JOIN          |             |  9081K|  1688M|       |   615K  (1)| 02:03:03 |        |      |
|   2 |   REMOTE            | t_3         |    24 |   384 |       |    23   (0)| 00:00:01 | PADRRO | R->S |
|*  3 |   HASH JOIN         |             |  9081K|  1550M|   989M|   615K  (1)| 02:03:02 |        |      |
|   4 |    REMOTE           | T_2         |  9020K|   886M|       |   430K  (1)| 01:26:07 | PADRRO | R->S |
|   5 |    TABLE ACCESS FULL| T_1         |    14M|  1025M|       | 76389   (2)| 00:15:17 |        |      |
-----------------------------------------------------------------------------------------------------------

El problema está en el paso 4. Hace un acceso full a la tabla t_2 , porque está ejecutando este query:

SELECT "ID_PERSONA",
       "LOCALIDAD",
       "ID_PROVINCIA",
       "DIRECCION" 
FROM t_2 "D" 
WHERE "ID_TIPO_DOMICILIO" = 1

Y por supuesto no está usando id_persona como índice de entrada, si no que prioriza la claúsula where. (id_persona es índice, tipo_domicilio no lo es)

Pregunta. En un query normal, poniendo un with antes de la consulta, o buscando la provincia con un subquery, el problema no ocurre, por lo tanto la pregunta es, ¿cómo se puede construir una vista materializada para este esquema de base de datos que permita joinear las 3 tablas, y no haga un full scan de la tabla t_2?

 PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID  0
-------------------------------------
SELECT "ID_PERSONA","LOCALIDAD","ID_PROVINCIA","DIRECCION" FROM t_2 WHERE
"ID_TIPO_DOMICILIO"=1 AND :1="ID_PERSONA" AND :2="ID_PROVINCIA"

Plan hash value: 3060586354

-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |       |       |     5 (100)|          |
|*  1 |  MAT_VIEW ACCESS BY INDEX ROWID| t_2          |     1 |    53 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN             | PK_DO1       |     1 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   1 - filter("ID_PROVINCIA"=:2)
   2 - access("ID_PERSONA"=:1 AND "ID_TIPO_DOMICILIO"=1)


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5
-------------------------------------
SELECT "ID_PERSONA",,"LOCALIDAD","ID_PROVINCIA","DIRECCION" FROM t_2 WHERE
"ID_TIPO_DOMICILIO"=1

Plan hash value: 2225703109

-------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |       |       |   429K(100)|          |
|*  1 |  MAT_VIEW ACCESS FULL| t_2          |  9020K|   455M|   429K  (1)| 01:18:45 |
-------------------------------------------------------------------------------------

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

   1 - filter("ID_TIPO_DOMICILIO"=1)

No soy un experto en el tema, pero se que ORACLE tiene hints que podés agregarle a los queries para por ejemplo sugerire el uso de un indice. Probaste por ese lado? — Juan, Jul 07 '17 at 16:49
Se pueden agregar hints a oracle para que utilice particularmente algun indice. En este caso no sirven para nada porque esta entrando por indice a la tabla que corresponde, solo que uno de los campos no esta en un indice y no le sirve para nada. — gbianchi, Jul 07 '17 at 17:14
Una alternativa para sacar la tabla de provincias del medio, asumiendo que el número es finito y relativamente chico, es utilizar un decode. — Juan, Jul 07 '17 at 18:16
@Juan es un workaround posible.. siempre y cuando no se les ocurra recodificar los id de esa tabla... — gbianchi, Jul 07 '17 at 18:45
Una pregunta, el query que pusiste abajo con tiempos de 00:00:01 son para un id de persona en particular a para toda la tabla t_1 como en es el caso de la vm? — Juan, Jul 07 '17 at 19:17
Si pero corré el mismo query sin el and id_persona a ver como sale. No va a usar el indice si tiene que barrer toda la tabla como es lo que le pedis a la vm. — Juan, Jul 07 '17 at 19:23
si le saco eso, las tablas no van a joinear.. no entiendo tu punto.. — gbianchi, Jul 07 '17 at 19:26
Perdoname, estaba haciendo referencia al query que figura abajo donde dice "Pregunta" en negrita. "En un query normal ...." y despues entiendo que esta el plan de ese select con los tiempos 00:00:01. — Juan, Jul 07 '17 at 19:32
Perdón, no estaría comprendiendo la consulta. ¿La consulta es porque tarda la consulta sobre la vista materializada? ¿O lo que está demorando es la construcción de la misma? Muchas gracias! — El Asiduo, Aug 01 '17 at 17:48
Entiendo que tarda porque hace full scan, no me queda claro que estás analizando para ver el mismo. La generación de la vista materializada o la consulta sobre la misma. — El Asiduo, Aug 01 '17 at 17:56
las dos cosas.. la consulta no deberia hacer full scan en teoria. no se entiende porque oracle decide hacer full scan cuando tiene los indices a mano. — gbianchi, Aug 01 '17 at 18:09
Abandonamos el proyecto.. pero se podria ver el problema.. en cuanto pueda lo miro.. @fredyfx — gbianchi, Mar 27 '18 at 02:08
Faltaría más información sobre la estructura de las tablas y sus relaciones para estar seguro pero la primera condición del WHERE me parece que no es correcta y de ser como creo es la que te estaría trayendo problemas al filtrar t_2. En resumen, ahí estás filtrando t_1.id con el valor de t_2.Id_Persona y por lo que decís querés hacer exactamente lo opuesto... — Roberto Vaccaro, Apr 20 '18 at 18:14
@RobertoVaccaro las condiciones estan bien. es un join implicito. que otra informacion sobre las tablas te hace falta? — gbianchi, Apr 20 '18 at 18:35
Aumm solo por curiosidad, si intentas colocar la primera instrucción del where id = d.Id_persona entre paréntesis y las demás en otro? a ver si cambia el plan de ejecución... where ( id = d.Id_persona) AND (d.id_tipo_domicilio = 1 AND cod_provincia = d.id_provincia), lo otro que se me ocurre es colocar t_1 inner join t_2 on t_1.id = t_2.Id_persona y luego en el where la condición del tipo de domicilio, eso podría cambiar el plan. Saludos! — zerocool, Apr 23 '18 at 17:12
@gbianchi ¿puedes hacer los subquerys mediante una `view` normal? — bercklyn Carlosviza, Apr 23 '18 at 23:22
me quedé con la expectativa :D, si me das los DLL con los índices puedo probar a ver si me sale. — zerocool, Apr 24 '18 at 18:09
puedes forzar índices select /*+ index(e,dept_idx) */ * from emp e; https://docs.oracle.com/cd/B19306_01/server.102/b14211/hintsref.htm#i8327 — Alejandro Teixeira Muñoz, Apr 24 '18 at 22:36

Alejandro Teixeira Muñoz · Answer 1 · 2018-04-24T23:29:17.857

En Oracle se pueden forzar los índices.

Has probado con esto?

SELECT /*+  INDEX (t_2 PK_DO1) */  id
    ,id_dependencia
    ,descripcion_corta NOMBRE
    ,to_char(fecha_fallecimiento, 'YYYY-MM-DD') FECHA_FALLECIMIENTO
    --DOMICILIO
    ,d.direccion DOMICILIO
    ,d.localidad
    --,(select nombre_provincia from t_3 where cod_provincia = d.id_provincia) PROVINCIA
    ,p.nombre_provincia PROVINCIA
FROM t_1
    ,t_2 d
    ,t_3 p
WHERE id = d.Id_persona
    AND d.id_tipo_domicilio = 1
    AND cod_provincia = d.id_provincia;

NOTA

Por lo que veo en la consulta, realmente no estás filtrando el Id_persona, como cuando buscas en el query plan en el que filtras inicialmente y sí te coge el índice. Parece que de todos modos, al juntar las tablas y no pedir un registro en particular, asume que le estás pidiendo la relación completa de las mismas, y por tanto, necesita hacer un fullscan. Posiblemente, en ese momento, Oracle decide hacerlo de la más pequeña, o simplemente, la que considera más óptima para la tarea

no es el nombre de la primary key que aparece en el query plan de t_2 que pegas? — Alejandro Teixeira Muñoz, Apr 24 '18 at 22:56
ahh.. tenes razon.. cuando pueda te debo probar esto y algo de otro comentario... — gbianchi, Apr 25 '18 at 01:42

Ejecutar subquerys en vista materializada

1 Answers1