Full table scans.

Full table scans are usually performed when there's no index on the column you are querying , obviouly in a table having large no of rows this would be costly operation.

But Full table scans are not always bad.

They are good when.
1. You are joining 2 tables and one of the table is very small. Ex. Students and courses the index scan of the course table would not be sensible in this case as the table can be read in one go taking all blocks in memory
2. Even on a large table if we are fetching more than 20% of data a full table scan won't be a bad option.

How to instruct oracle to do a full table scan ?

Use a hint /*+ FULL() */

Ex. Select /*+ FULL(dept) */ e.name , d.dept  from emp e , dept d
    where e.dept_id=d.dept_id
    and dept_name='ACCOUNTS';


You can go further and choose to cache the 2nd small table with cache hint.


Instructs the optimizer to place the blocks retrieved for the table at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This hint is useful for small lookup tables.

/*+ CACHE([@queryblock] ) */

Refer PSOUG for more compiler hints.