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)
-----------------------------

No comments: