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;

Dare to use cast.

Have you ever wanted to convert one data type to other.

Most of the times we would rely on oracle's ability to implicitly convert them for you.

But if you want it fool proof take the task in your hand .

Here's How it's done.

CAST converts one built-in datatype or collection-typed value into another built-in datatype or collection-typed value.

You can cast an unnamed operand (such as a date or the result set of a subquery) or a named collection (such as a varray or a nested table) into a type-compatible datatype or named collection. The type_name must be the name of a built-in datatype or collection type and the operand must be a built-in datatype or must evaluate to a collection value.

Examples :