Use of Hint to use Index.

1.Create Two Tables.
-----------------------
create table table_a(n number ,k varchar2(15));
create table table_b(k varchar2(15), b number);
/

2.Create Index.
------------------------
create index table_a_I_K on table_a(k);
create index table_b_I_K on table_b(K);
/

3.Insert Random values in the Table.
--------------------------------------------
In table_a at column n I inserted 1 to 10000 values and in column k I inserted strings contains 'pc-' + random integer between 1 to 20000.

begin
for i in 1 .. 10000
loop
insert into table_a values(i,'pc-'||round(dbms_random.value(1,20000),0));
end loop;
end;
/


In table_b I inserted 100000 rows. In column b I inserted 1 to 100000 values and in column k I inserted strings contained 'pc-' + random integer between 1 to 40000.

begin
for i in 1 .. 100000
loop
insert into table_b(b,k) values(i,'pc-'||round(dbms_random.value(1,40000),0));
end loop;
end;
/

4.Enable Tracing to see whether index used or not.

SQL>SET AUTOT TRACE

5. See explain plan.

SQL>select a.n, b.b from table_a a , table_b b where a.k=b.k;
24981 rows selected.


Execution Plan
----------------------------------------------------------

Plan hash value: 1725158669

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29970 | 1287K| 79 (3)| 00:00:01 |
|* 1 | HASH JOIN | | 29970 | 1287K| 79 (3)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TABLE_A | 10000 | 214K| 8 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TABLE_B | 84053 | 1805K| 70 (2)| 00:00:01 |


Why my query did not used indexes? Because query optimizer got a explain plan in which full table scan takes less time than index scan.
However we can force to use index. Then we have to use Index hint. Like, Index(table_name) entry within /* and */.

SQL> set timi on
SQL> select /*+INDEX (b)*/ a.n, b.b from table_a a , table_b b where a.k=b.k;

24981 rows selected.

Elapsed: 00:00:00.55

Execution Plan
----------------------------------------------------------
Plan hash value: 1071190786

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29970 | 1287K| 1181 (1)| 00:00:15 |
|* 1 | HASH JOIN | | 29970 | 1287K| 1181 (1)| 00:00:15 |
| 2 | TABLE ACCESS FULL | TABLE_A | 10000 | 214K| 8 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| TABLE_B | 84053 | 1805K| 1172 (1)| 00:00:15 |
| 4 | INDEX FULL SCAN | TABLE_B_I_K | 84053 | | 371 (0)| 00:00:05 |
--------------------------------------------------------------------------------------------

Related Documentation:
--------------------------
When you will make index and when not