Inside Oracle Sorting

Oracle sorting is a very important component of Oracle tuning, yet sorting optimization is often overlooked.

An Oracle database will automatically perform sorting operations on row data under the following circumstances:

• When an index is created
• When using the ORDER BY clause in SQL
• When using the GROUP BY clause in SQL

There are several ways to sequence Oracle output rows:

• Oracle internal sort (in sort_area_size or TEMP tablespace)
• Retrieve the rows in pre-sorted order (using an index)

Using a third-party sort product - For shops that have to sequence millions of rows of output on an hourly basis, external sort products can be faster than using Oracle to sort the data.

ViTech

• Which statement is fast? Commit or Rollback
• RETURN statement is mandatory in a function?
• Difference b/w NVL and NVL2
• How to add a new parameter to a procedure without affecting existing calls
• Can we call a function which has DML in SELECT 
• Write a query to display how many days are there for his/her next birthday 
• For vs For All statements ?
• A select statement retrives 2 million records and took 10 minutes time. Now I executed delete and Commit. Again I executed select * form table, result is no rows selected. what will be the time taken for the second select satement?
• Emp table has 1 million records, Dept table has 4 rows. Now I wrote normal join query. How many hits will go ?
• What is the best practice to get Nth highest salary 

Query to get days for Next B'day :
SELECT HIREDATE,
, TRUNC(
case 
    when   
          TO_DATE (SUBSTR(HIREDATE,1,7)|| TO_CHAR(extract(year from sysdate))) -sysdate > 0 
      then    TO_DATE (SUBSTR(HIREDATE,1,7)|| TO_CHAR(extract(year from sysdate))) -sysdate  
    when
         TO_DATE (SUBSTR(HIREDATE,1,7)|| TO_CHAR(extract(year from sysdate))) -sysdate  < 0 
      then    
         TO_DATE (SUBSTR(HIREDATE,1,7)|| TO_CHAR(extract(year from sysdate)+1)) -sysdate 
  END ) AS   DAYSREMAINFOR_ANNIVERSARY
FROM EMP;