Useful functions in Oracle 11g Part02

Introduction

In this article, i have given example for another four useful functions and tips to oracle datatbase.

• Simulating Dense_Rank Analytical Functions (Manual Dense_Rank)
• Simulating Rank Analytical Functions (Manual Rank)
• Simulating Analytical Functions KEEP DENSE_RANK OVER PARTITION BY
• Global Temporary Table

Simulating Dense_Rank Analytical Functions (Manual Dense_Rank)

http://nimishgarg.blogspot.com/2010/06/oracle-simulating-denserank-analytical.html

ORIGINAL QUERY

selectempno,ename,deptno,sal,dense_rank() over(partition by deptno order by sal) drnk
from 
    scott.emp

SIMULATED QUERY

selectempno,ename,deptno,sal,(select count(distinct sal) from scott.emp a where a.deptno=b.deptno and a.sal <= b.sal) drnk
from 
    scott.emp b
order by 
    deptno, sal

 Output 

Simulating Rank Analytical Functions (Manual Rank)

http://nimishgarg.blogspot.com/2010/06/oracle-simulating-rank-analytical.html

ORIGINAL QUERY

selectempno,ename,deptno,sal,rank() over(partition by deptno order by sal) rnk
from 
    scott.emp

 SIMULATED QUERY

select empno,ename,deptno,sal,(select count(sal)+1 from scott.emp b where b.deptno=a.deptno and b.sal < a.sal) rnk
from 
    scott.emp aorder by 
    deptno, sal

 Output 

Simulating Analytical Functions KEEP DENSE_RANK OVER PARTITION BY

Original Query

select    empno,     deptno, to_char(hiredate,'yyyy') hire_yr, sal,sum(sal) keep (dense_rank first order by to_char(hiredate,'yyyy') ) over (partition by deptno) avg_sal_yr1_hire
from     scott.emp
where deptno in (20, 10)order by deptno,  empno, hire_yr;

 Simulated Query

select    empno, deptno, to_char(hiredate,'yyyy') hire_yr, sal,(select sum(sal) from scott.emp a where a.deptno = b.deptno and a.to_char(hiredate,'yyyy') = (select to_char(min(hiredate),'yyyy') from scott.emp c where a.deptno=c.deptno)) avg_sal_yr1_hire
from scott.emp b
where deptno in (20, 10)order by deptno, empno, hire_yr;

 Output 

Global Temporary Table

http://nimishgarg.blogspot.com/2010/06/oracle-global-temporary-table.html

Global temporary table exists solely for your session or whose data persists for the duration of your transaction.

When you create a temporary table, you can specify whether

• it should last for the duration of your session (on commit preserve rows)
• or whether its rows should be deleted when the transaction completes (on commit delete rows)

Unlike a permanent table, a temporary table does not automatically allocate space when it is created. Space will be dynamically allocated for the table as rows are inserted:

create global temporary table YEAR_ROLLUP 
(EMPNO NUMBER(4),ENAME VARCHAR2(9),SALARY NUMBER)

 on commit preserve rows;

That's all, i hope these tips and functions help to you. i will post some more functions soon.