Some Oracle functions which we do not use daily

Hi Guys,

Below is a post on some oracle functions which most people would not have tried

1) Pivot
2) connect by
3) Levels
4) Grouping sets


addmonths

Consider you are at march 15 and you want to go to Feb 15 then you cant just write -30 directly. because feb might be having 28 days or 29 days in this case you need to use addmonth to go back a month.

Max and min function 

 select emp, max(hiring_date) from emp

This will not give you employee with max hiring date it will give the max date next to each employee. If you want to get employee having max hiring date then go for rank function and get the first rank



Below is a good article on Parallel hints 

http://www.oracle.com/technetwork/articles/database-performance/geist-parallel-execution-2-1872405.html

Note -- Parallel hints works best when using full table scans as

parallel hints only helps in two cases -- your disk can provide enough i/o for both of processors to use

2) The query is so complex that it is saturating cpu so getting in another cpu will work

In  most cases cpu is waiting for i/o in such cases parallel hints will not help.

 We need example here where using parallel hints has lead to increase in the speed of query.