Hi,
I was trying to learn how hints affects the table access paths.
The table COUNTRIES in HR schema has one index on column COUNTRY_ID.This table doesn't have any index on region_id . So I could n't understand why the following query considers COUNTRY_C_ID_PK . because i didn't give any filter on country_id
I gave the command
explain plan for select region_id from countries where region_id=1;
select plan_table_output from table( dbms_xplan.display());
Output
Plan hash value: 1253225340
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 24 | 1 (0)| 00:00:01 |
|* 1 | INDEX FULL SCAN | COUNTRY_C_ID_PK | 8 | 24 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("REGION_ID"=1)
I thought of giving a full table scan hint and I was expecting a full table scan .
explain plan for select /*+ Full(countries) */region_id from countries where upper(region_id)=1;
select plan_table_output from table( dbms_xplan.display());
Plan hash value: 3996818343
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| COUNTRY_C_ID_PK | 1 | 3 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(UPPER(TO_CHAR("REGION_ID")))=1)
Please help me to understand below 2 questions
1. Why it considers index COUNTRY_C_ID_PK ? COUNTRY_C_ID_PK does not include column REGION_ID
2. After giving hint for full table scan , why it considers INDEX FAST FULL SCAN instead of TABLE ACCESS FULL?
Thanks in advance.