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.

Nth Highest queries. Or Find Nth Row.

Many a times we are asked to find the nth highest row depending on some criteria.

Here I am trying to compile all the shrewd and crude ways to find the 3rd Highest salary from our employees table.

hr@XE> select * from (select * from employees order by salary desc)
  2  where rownum < 4
  3  Minus
  4  Select * from (select * from employees order by salary desc)
  5  where rownum<3;

Notice that this may not produce the exact 3rd highest salary we should use distinct in that case.

select * from employees e
 where 3=(select count(distinct salary) from employees e1 -- Now this should return the 3rd highest.
          where e.salary<=e1.salary);

Using 10G analytics ---
select * from
(select e.* ,dense_rank() over (order by salary desc) rank_Sal
 from employees e)
where rank_sal=3 -- If you don't want to same ranks for two consecutive same salary values.              -------------------------ROW_NUMBER can be used.



The simplest (no same ranks for the same salary)

select * from (select rownum rank,e.* from employees e order by salary desc)
where rank=3;