Regarding difference in execution time of queries

These are two explain plans of two different queries that return the same results but at a drastically different speed (280 second vs 1-7 seconds).
I want some help knowing what the fast query has so that I can replicate it in the future.

EDIT:

Here is the explain plan for the fast query:

```
Plan hash value: 3147806849

----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 528 | 46 (9)| 00:00:01 | | |
| 1 | NESTED LOOPS SEMI | | 1 | 528 | 46 (9)| 00:00:01 | | |
| 2 | NESTED LOOPS OUTER | | 1 | 526 | 43 (10)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 338 | 26 (4)| 00:00:01 | | |
|* 4 | HASH JOIN | | 1 | 333 | 25 (4)| 00:00:01 | | |
|* 5 | HASH JOIN | | 1 | 324 | 23 (5)| 00:00:01 | | |
| 6 | NESTED LOOPS | | 1 | 264 | 19 (0)| 00:00:01 | | |
| 7 | NESTED LOOPS | | 1 | 264 | 19 (0)| 00:00:01 | |

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

StackExchange Database Administrators Q#130080, answer score: 3