Showing posts with label Analytic Functions. Show all posts
Showing posts with label Analytic Functions. Show all posts

Aug 30, 2010

Advantage of analytic Function

This example shows you that  Usage of  Analytic function will make query good in performance
 
select mxid,obj#,dataobj#,owner#,name,j.namespace,type#,ctime,mtime,stime,status,flags,spare1,spare2 from sys.obj$ j,(select max(dataobj#) mxid,namespace from sys.obj$ where owner#=61 group by namespace)w where owner#=61 and j.dataobj#=w.mxid and obj# in (51606,52492,52602) ;
 
explain plan set statement_id ='r' into plan_table for select mxid,obj#,dataobj#,owner#,name,j.namespace,type#,ctime,mtime,stime,status,flags,spare1,spare2 from sys.obj$ j,(select max(dataobj#) mxid,namespace from sys.obj$ where owner#=61 group by namespace)w where owner#=61 and j.dataobj#=w.mxid  and obj# in (51606,52492,52602) ;
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL));
 
 
Total Cost=718
 
select max(dataobj#) over(partition by owner#,namespace order by owner#,namespace) mx,obj#,dataobj#,owner#,name,namespace,type#,ctime,mtime,stime,status,flags,spare1,spare2 from sys.obj$ where owner#=61 and obj# in (51606,52492,52602);
 

 

 
explain plan set statement_id ='r' into plan_table for select max(dataobj#) over(partition by owner#,namespace order by owner#,namespace) mx, obj#,dataobj#,owner#,name,namespace,type#,ctime,mtime,stime,status,flags,spare1,spare2 from sys.obj$ where owner#=61 and obj# in (51606,52492,52602);
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL));
 
 
Total Cost is 29
 
.`. Usage of second sql script is advisable ; where oracle optimizer works default on cost based (ie:CBO)
-----------------------------

Feb 21, 2009

Instead of Stragg /// Allow Duplicate

SELECT
deptno,
LTRIM(SYS_CONNECT_BY_PATH(ename, ','), ',') employees
FROM (
SELECT deptno,
ename,
row_number() over(PARTITION BY deptno ORDER BY deptno) rnum,
count(*) over(PARTITION BY deptno) tot
FROM scott.emp
)
WHERE rnum=tot
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum -1 AND PRIOR deptno = deptno;



select tb.deptno , substr ( sys_connect_by_path( tb.EMPNO, ',' ) , 2) as string
from ( select deptno ,EMPNO ,row_number() over ( partition by deptno
order by EMPNO ) as val_index from
scott.emp WHERE EMPNO IS NOT NULL ) tb where
connect_by_isleaf = 1 connect by val_index = prior val_index + 1 and
deptno = prior deptno start with val_index = 1 ;



WITH tab AS
(
SELECT &strg str
FROM DUAL)
SELECT REGEXP_SUBSTR (str, '[^,]+', 1, LEVEL) RESULT
FROM tab
CONNECT BY LEVEL <=
((SELECT SUM (CASE
WHEN SUBSTR (str, LEVEL, 1) = ','
THEN 1
WHEN LENGTH (str) = LEVEL
THEN 1
ELSE 0
END
)
FROM tab
CONNECT BY LEVEL <= LENGTH (str)));


SELECT REPLACE(&strg ,',',CHR(13)) FROM DUAL;


FROM 10G ONWARDS

SELECT deptno,wmsys.wm_concat(ename) d
FROM scott.emp group BY deptno


------------------------------------------
To remove duplicate  from a SQL

SELECT JOB, ENAME FROM
(SELECT DISTINCT ENAME,JOB, row_number () OVER (PARTITION BY ENAME ORDER BY ENAME) rn
FROM scott.emp )
WHERE rn = 1

Sep 22, 2008

Alphabets,Level, Analytic Query

Alphabets

WITH AlphaBet AS
( SELECT CHR((ROWNUM + 64)) AS Character
FROM DUAL CONNECT BY level <= 157)
SELECT * FROM AlphaBet;

------------------------------------------------------------------------
Some Analytic Functions

SELECT G.*,ROW_NUMBER() OVER(PARTITION BY G.DEPTNO ORDER BY G.EMPNO)
FROM EMP G;

SELECT G.DEPTNO,G.ENAME,SUM(G.SAL) OVER (PARTITION BY G.DEPTNO ORDER BY G.DEPTNO) FROM EMP G ;

SELECT FIRST_VALUE(G.EMPNO) OVER() FROM EMP G;

---------------------------------------------------------------------------
Level Query


select level,lpad(' ',3*(level)) || VC_designation s,nn_desgcode dsg,nn_repdesgcode rcd,
SYS_CONNECT_BY_path (nn_repdesgcode,'/') op,
substr(SYS_CONNECT_BY_path (nn_repdesgcode,'/'),3,
length((SYS_CONNECT_BY_path (nn_repdesgcode,'/')))) hjdd
from hr_designation_mast where nn_repdesgcode is not null
start with nn_repdesgcode is not null
connect by prior nn_desgcode=nn_repdesgcode