Hola,
Este es un caso de prueba que creo podran reproducir sin problemas.
Código:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Tengo dos tablas con una cantidad interesante de registros
Código:
SQL> select count(*) from t1;
COUNT(*)
----------
7989248
1 fila seleccionada.
Transcurrido: 00:00:03.87
SQL> select count(*) from t2;
COUNT(*)
----------
7994247
1 fila seleccionada.
Transcurrido: 00:00:03.54
Plan de ejecucion del primer JOIN
Código:
SQL> explain plan for
2 select *
3 from
4 t1, t2
5 where t1.object_name = t2.object_name
6 and t1.object_name = 'T1';
Explicado.
Transcurrido: 00:00:00.01
SQL> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Plan hash value: 2109126321
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2040K| 334M| 2597 (1)| 00:00:32 |
|* 1 | HASH JOIN | | 2040K| 334M| 2597 (1)| 00:00:32 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1390 | 116K| 1263 (1)| 00:00:16 |
|* 3 | INDEX RANGE SCAN | IND_T1_NAME | 1390 | | 8 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T2 | 1467 | 123K| 1316 (1)| 00:00:16 |
|* 5 | INDEX RANGE SCAN | IND_T2_NAME | 1467 | | 8 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_NAME"="T2"."OBJECT_NAME")
3 - access("T1"."OBJECT_NAME"='T1')
5 - access("T2"."OBJECT_NAME"='T1')
19 filas seleccionadas.
Transcurrido: 00:00:00.07
Plan de ejecucion del segundo JOIN
Código:
SQL> explain plan for
2 select *
3 from
4 t1
5 join t2 on t1.object_name = t2.object_name
6 where t1.object_name = 'T1';
Explicado.
Transcurrido: 00:00:00.01
SQL> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
Plan hash value: 2109126321
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2040K| 334M| 2597 (1)| 00:00:32 |
|* 1 | HASH JOIN | | 2040K| 334M| 2597 (1)| 00:00:32 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1390 | 116K| 1263 (1)| 00:00:16 |
|* 3 | INDEX RANGE SCAN | IND_T1_NAME | 1390 | | 8 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T2 | 1467 | 123K| 1316 (1)| 00:00:16 |
|* 5 | INDEX RANGE SCAN | IND_T2_NAME | 1467 | | 8 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_NAME"="T2"."OBJECT_NAME")
3 - access("T1"."OBJECT_NAME"='T1')
5 - access("T2"."OBJECT_NAME"='T1')
19 filas seleccionadas.
Transcurrido: 00:00:00.09
Plan de ejecucion del tercer JOIN
Código:
SQL> explain plan for
2 select *
3 from
4 t1
5 join t2 on t1.object_name = t2.object_name
6 and t1.object_name = 'T1';
Explicado.
Transcurrido: 00:00:00.01
SQL> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 2109126321
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2040K| 334M| 2597 (1)| 00:00:32 |
|* 1 | HASH JOIN | | 2040K| 334M| 2597 (1)| 00:00:32 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1390 | 116K| 1263 (1)| 00:00:16 |
|* 3 | INDEX RANGE SCAN | IND_T1_NAME | 1390 | | 8 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T2 | 1467 | 123K| 1316 (1)| 00:00:16 |
|* 5 | INDEX RANGE SCAN | IND_T2_NAME | 1467 | | 8 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_NAME"="T2"."OBJECT_NAME")
3 - access("T1"."OBJECT_NAME"='T1')
5 - access("T2"."OBJECT_NAME"='T1')
19 filas seleccionadas.
Transcurrido: 00:00:00.07
Presten especial atencion al valor de
Plan hash value en cada uno de los planes de ejecucion. Los tres son iguales.
Ahora, segun la documentacion, la definicion de PLAN_HASH_VALUE es
Numerical representation of the SQL plan for the cursor. Comparing one PLAN_HASH_VALUE to another easily identifies whether or not two plans are the same (rather than comparing the two plans line by line).
Representación numérica del plan SQL del cursor. Comparando un PLAN_HASH_VALUE a otro identifica facilmente si dos planes son los mismos (en lugar de comparar los dos planes línea por línea)
En conclusion, es solo una cuestion de sintaxis, no tiene impacto en la consulta.
Saludos