SQL> explain plan for select country_name,city from warehouses a inner join locations b on a.location_id=b.location_id inner join countries c on b.country_id=c.country_id where state='Geneve' and warehouse_name ='San Francisco';
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2598622397
----------------------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 239 | 3 (0)| 00:00:01|
| 1 | NESTED LOOPS | | 1 | 239 | 3 (0)| 00:00:01|
| 2 | NESTED LOOPS | | 1 | 239 | 3 (0)| 00:00:01|
| 3 | NESTED LOOPS | | 1 | 213 | 2 (0)| 00:00:01|
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| LOCATIONS | 1 | 71 | 2 (0)| 00:00:01|
|* 5 | INDEX RANGE SCAN | ST_CON | 1 | | 1 (0)| 00:00:01|
|* 6 | INDEX RANGE SCAN | LOC_NAME | 1 | 142 | 0 (0)| 00:00:01|
|* 7 | INDEX UNIQUE SCAN | SYS_C008130 | 1 | | 0 (0)| 00:00:01|
| 8 | TABLE ACCESS BY INDEX ROWID | COUNTRIES | 1 | 26 | 1 (0)| 00:00:01|
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("B"."STATE"='Geneve')
6 - access("A"."WAREHOUSE_NAME"='San Francisco' AND "A"."LOCATION_ID"="B"."LOCATION_ID")
7 - access("B"."COUNTRY_ID"="C"."COUNTRY_ID")
based on the explain we know how oracle run the query
1.index range scan on st_con index
2.using row id from st_con index to get data from location table
3.range scan loc_name with warehouse_name and join condition
4.join result from location and index loc_name
5.unique scan on sys_c008130 index
6.join againt with previous result set based on country_id
7.access data from country based on row id we get from sys_C008130 index
8.return the data
how does oracle access index SYS_C008130 and loc_name without knowing the A.location_id(for loc_name access predicate) and B.country_id(for SYS_C008130 access predicate) since they have not been joined yet oracle didnt know the data about those column right?
or am i wrong?. and oracle do a full scan on index then join with previous result set to exclude useless information that not gonna appear on the result set