Выполнять subquerys в материализованном виде

Консультируйся. Следующая - консультация, которая использует три подмостков:

(Все здесь в больше anonimizado, может скользить какая-то непроизвольная ошибка)

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;

Эта консультация - перевод другой консультации, потому что VM не позволяют субконсультации, чтобы строить их (заметьте себе, что PROVINCIA будь определена два раза, потому что не возможно иметь один SELECT укрытый).


Проблема, которая появляется из этого, состоит в том, что конечная консультация заканчивает тем, что делает full scan таблицы t_2, у которого есть местожительство.

И: почему встречается это? Потому что таблица t_3 будь объединена в t_2 из-за cod_provincia = d.id_provincia, но однако, и таблица t_1 он объединяется в t_2 из-за id = d.Id_persona, но t_2 только требуют типа местожительства.

explain план это говорит:

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

Проблема находится в шаге 4. Он делает доступ full в таблицу t_2 , потому что он выполняет этот query:

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

И конечно не используй id_persona как индекс ввода, если не, которому оказывает предпочтение claúsula where. (id_persona это индекс, tipo_domicilio он это не)


Спроси. В нормальном query, помещая один with перед консультацией, или ища провинцию с subquery, проблема не происходит, следовательно вопрос: как возможно строить вид, материализованный для этой схемы базы данных, которая позволяла бы joinear 3 подмостков, и не сделал full scan таблицы 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)
28
задан 01.07.2017, 15:48
0 ответов