Tuning of complex query using “WITH” statement on Oracle.

    This query is a complex query that was figure as one of top 5 on my AWR reports, the complexity of him is caused because is a Auto Join query (Hierarchical query) and the rows returned by query are a mix of columns from first resultset and second resultset.
    Even being a partitioned table by nu_uf column, it is hard work to Oracle retrieve rows doing a Full Table Scan even in one of several big partitions.
The Original query

SELECT
        p.familia
        , p.municipio
        , p.nis AS numnis
        , p2.nis AS numnis2
        , p2.pessoa AS tutor
        , p.pessoa AS nome
        , p.dt_nasc AS dt_nasc
FROM
       tb_pessoa p
       , tb_pessoa p2
WHERE
       p.nu_uf = ’15’
AND p.municipio = ‘1505536’
AND p2.nu_uf = ’15’
AND p.valida = 17
AND p.responsavel = p2.pessoa
ORDER BY municipio, nis

Explain Plan

——————————————————————————————————
| Id  | Operation                                            | Name                 | Rows  | Bytes | Cost  | Pstart| Pstop |
——————————————————————————————————
|   0 | SELECT STATEMENT                              |                         | 14912 |  2009K| 12197 |       |       |
|   1 |  SORT ORDER BY                                   |                         | 14912 |  2009K| 12197 |       |       |
|   2 |   HASH JOIN                                         |                         | 14912 |  2009K| 11736 |       |       |
|   3 |    PARTITION RANGE SINGLE                    |                         | 14912 |  1194K|  1540 |     5 |     5 |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID | TB_PESSOA        | 14912 |  1194K|  1540 |     5 |     5 |
|   5 |      INDEX RANGE SCAN                           | IN_PESSOA_MUN |  1281 |           |    59  |     5 |     5 |
|   6 |    PARTITION RANGE SINGLE                    |                         |  1840K|    98M| 10171 |     5 |     5 |
|   7 |     TABLE ACCESS FULL                          | TB_PESSOA         |  1840K|    98M| 10171 |     5 |     5 |
——————————————————————————————————

     Here, the suggested query, instead one auto join, the query was broken on three queries:

1) Retrieve all rows that I need;
2) Catch only columns of first resultset that I need to do a JOIN;
3) The final JOIN with temporary tables created on execution time by Oracle to retrieve rows
at way that I need.

WITH benef AS
            ( SELECT
                      familia familia
                      , municipio municipio
                      , nis nis_benef
                      , pessoa codigo
                      , valida vigencia
                      , pessoa nome
                      , dt_nasc dt_nasc
                      , responsavel co_resp
               FROM
                      tb_pessoa p
               WHERE
                      p.municipio = ‘1505536’ AND
                      p.nu_uf = ’15’ AND
                      p.valida = 17
            ),
resp AS
         ( SELECT
                   co_resp
                   , nome
                   , codigo
                   , nis_benef
           FROM
                   benef
          )
SELECT
         b.familia familia
         , b.municipio municipio
         , b.nis_benef nis_benef
         , a.nis_benef nis_resp
         , a.nome nome_resp
         , b.vigencia vigencia
         , b.nome nome
         , b.dt_nasc dt_nasc
FROM
         resp a
         , benef b
WHERE
         b.co_resp = a.codigo
ORDER BY
         municipio
         , nis_resp;

Explain Plan
———————————————————-

——————————————————————————————————————–
| Id  | Operation                                              | Name                    | Rows  | Bytes   | Cost  | Pstart| Pstop |
——————————————————————————————————————–
|   0 | SELECT STATEMENT                                |                            |   351  | 48438  |  1632  |       |       |
|   1 |  TEMP TABLE TRANSFORMATION                |                            |          |           |          |       |       |
|   2 |   LOAD AS SELECT                                  |                             |          |           |          |       |       |
|   3 |    PARTITION RANGE SINGLE                     |                             | 14912 |  1398K |  1540  |     5 |     5 |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID  | TB_PESSOA            | 14912 |  1398K |  1540  |     5 |     5 |
|   5 |      INDEX RANGE SCAN                            | IN_PESSOA_MUN     |  1281  |          |    59    |     5 |     5 |
|   6 |   SORT ORDER BY                                   |                             |   351  | 48438  |    91    |       |       |
|   7 |    HASH JOIN                                         |                             |   351  | 48438  |    90    |       |       |
|   8 |     VIEW                                               |                             | 14912 |   844K  |    45   |       |       |
|   9 |      TABLE ACCESS FULL                          | SYS_TEMP_0FD9F    | 14912 |  1354K |    45    |       |       |
|  10 |     VIEW                                              |                              | 14912 |  1165K|    45    |       |       |
|  11 |      TABLE ACCESS FULL                         | SYS_TEMP_0FD9FC8 | 14912 |  1354K |    45    |       |       |
——————————————————————————————————————–

With this tuning, the response time from report was downgraded considerably.
All envolved stayed happy:

The database for work most efficiently
The Developer because your report now is more faster
Me cause solve this with a simple query.

As showed above the cost was downgraded from 12197 to 1632.