Execute subqueries in materialized view

Query. The following is a query that uses three tables:

(Everything from here on is anonymous, some involuntary error can slip)

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;

This query is the translation of another query, because the VMs do not allow subqueries to build them (note that PROVINCIA is defined twice, because you can not have a SELECT nested).

The problem that arises from this, is that the final query ends with a full scan of the table t_2 , which has the addresses.

And, why is this happening? Because the t_3 table is linked to t_2 for cod_provincia = d.id_provincia , but nevertheless, and the t_1 table joins the t_2 for id = d.Id_persona , but for t_2 only one type of address.

The explain plan says this:

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

The problem is in step 4. Make a full access to the t_2 table, because you are running this query :

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

And of course you are not using id_persona as the input index, but prioritize the% clause where . ( id_persona is index, tipo_domicilio is not)

Question. In a normal query , putting a with before the query, or looking for the province with a subquery, the problem does not occur, therefore the question is, how can you build a materialized view for this database schema that allows joinear the 3 tables, and does not make a full scan of the t_2 table?

 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)