Skip to main content
Database Administrators

Return to Answer

improved formatting
Source Link
atokpas
atokpas
  • 8.7k
  • 1
  • 17
  • 27

I found that the table 'ADIO_UNIVERSOS'ADIO_UNIVERSOS is accessed by local index 'IXN_ADIOUNIVERSOS_ARINOPERLOC'a Local Index IXN_ADIOUNIVERSOS_ARINOPERLOC (INDEX RANGE SCAN) which has returned 2039K rows, scans several partitions(1-17) and in. In the fast running query the same table is accessed by global index 'PK_ADIOUNIV_IDUNIV'Global Index PK_ADIOUNIV_IDUNIV(INDEX UNIQUE SCAN) has returned 1 row, which doesntdidn't run through all the partitions.
Index

Index Range Scan obviously returns more rows than Index Unique Scan. Here, in the Slowslow running query execution plan, Index Range Scan is influencing the Nested Loop(Cost 269).

I found that the table 'ADIO_UNIVERSOS' is accessed by local index 'IXN_ADIOUNIVERSOS_ARINOPERLOC'(INDEX RANGE SCAN) which has returned 2039K rows, scans several partitions(1-17) and in the fast running query the same table is accessed by global index 'PK_ADIOUNIV_IDUNIV'(INDEX UNIQUE SCAN) has returned 1 row, which doesnt run through all the partitions.
Index Range Scan obviously returns more rows than Index Unique Scan. Here in the Slow running plan Index Range Scan is influencing the Nested Loop(Cost 269).

I found that the table ADIO_UNIVERSOS is accessed by a Local Index IXN_ADIOUNIVERSOS_ARINOPERLOC (INDEX RANGE SCAN) which has returned 2039K rows, scans several partitions(1-17). In the fast running query the same table is accessed by Global Index PK_ADIOUNIV_IDUNIV(INDEX UNIQUE SCAN) has returned 1 row, which didn't run through all the partitions.

Index Range Scan obviously returns more rows than Index Unique Scan. Here, in the slow running query execution plan, Index Range Scan is influencing the Nested Loop(Cost 269).

Source Link
atokpas
atokpas
  • 8.7k
  • 1
  • 17
  • 27

My findings:
Slow running query:

            ---------------------------------------------------------------------------------------------------------------------------------------------------
            | Id  | Operation                                         | Name                          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
            ---------------------------------------------------------------------------------------------------------------------------------------------------
        |  48 |                 PARTITION RANGE ALL               |                               |     1 |    82 |    34   (0)| 00:00:01 |     1 |    17 |
            |* 49 |                  TABLE ACCESS BY LOCAL INDEX ROWID| ADIO_UNIVERSOS                |     1 |    82 |    34   (0)| 00:00:01 |     1 |    17 |
            |* 50 |                   INDEX RANGE SCAN                | IXN_ADIOUNIVERSOS_ARINOPERLOC |  2039K|       |    34   (0)| 00:00:01 |     1 |    17 |
        
              49 - filter(("AU"."PROGRAMA_ID"=1 OR "AU"."PROGRAMA_ID"=2) AND "AU"."CARGA_VALIDA"=1)
              50 - access("AAI"."ID_ARCHIVO_INSUMO"="AU"."ID_ARCHIVO_INSUMO"

Fast running query:

            ---------------------------------------------------------------------------------------------------------------------------------------------------
            | Id  | Operation                                         | Name                          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
            ---------------------------------------------------------------------------------------------------------------------------------------------------
            |* 29 |              TABLE ACCESS BY GLOBAL INDEX ROWID    | ADIO_UNIVERSOS                |     1 |    71 |     2   (0)| 00:00:01 | ROWID | ROWID |
            |* 30 |               INDEX UNIQUE SCAN                    | PK_ADIOUNIV_IDUNIV            |     1 |       |     1   (0)| 00:00:01 |       |       |
            
              29 - filter("AU"."CARGA_VALIDA"=1)
              30 - access("AU"."ID_UNIVERSO"="PPU"."ID_UNIVERSO")

I found that the table 'ADIO_UNIVERSOS' is accessed by local index 'IXN_ADIOUNIVERSOS_ARINOPERLOC'(INDEX RANGE SCAN) which has returned 2039K rows, scans several partitions(1-17) and in the fast running query the same table is accessed by global index 'PK_ADIOUNIV_IDUNIV'(INDEX UNIQUE SCAN) has returned 1 row, which doesnt run through all the partitions.
Index Range Scan obviously returns more rows than Index Unique Scan. Here in the Slow running plan Index Range Scan is influencing the Nested Loop(Cost 269).