Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Cursor Expressions
In article <wiWx7.9838$YC3.3534016_at_typhoon.southeast.rr.com>, "Linda says...
>
>After seeing some posts in this group, and looking at the docs
>(8.1.7), I am wondering how cursor expressions are used. The
>following, from the Oracle doc, appears to me to be very similar to a
>couple of nested in-line views.
>
>What advantage do these have over in-line views?
>
>
>
>SELECT d.deptno, CURSOR(SELECT e.empno, CURSOR(SELECT p.projnum,
> p.projname
> FROM projects p
> WHERE p.empno = e.empno)
> FROM TABLE(d.employees) e)
> FROM dept d
> WHERE d.dno = 605;
>
>
>
>
They return irregularly shaped result sets.
Normal SQL returns "square" results -- rows and columns. This does too but the cursor columns are result sets themselves.
Say there are 10 deptnos. Say you really got 100 bytes of DEPT info/row (not just deptno as in your example)
Each deptno has 100 employees. Say you got 100 bytes of EMP info/row.
Each emp has 5 projects. Each project is 50 bytes.
Normally to return that result set there would be 10 x 100 x 5 (5000) records at 250 bytes each or 1,250,000 bytes.
Using the above you would retrieve instead:
10*100 (dept) + 1000*100 (emp) + 5000*50 (proj) 1,000 + 100,000 + 250,000 = 351,000 bytes
Since the dept info comes back just 10 times, not 5000 times and the emp info comes be just 1,000 times, not 5,000 times.
Additionally, if the application only SOMETIMES needs the emp info or the proj info -- the savings can be larger. The client only retrieves the EMP or PROJ info when it wants to. Just using a flat (square) result set -- you would get back ALL of the data whether you wanted it or not.
This is a more efficient way to code (psuedo code here)
for x in ( select * from dept )
loop
if (somecondition) then for y in ( select * from emp where emp.col = X.col ) loop if ( someothercondition ) then for z in ( select * from proj where proj.col = Y.col ) loop ... end if end loop end if
in one query.
With the above strucutre, there would be
10 x 4 + 1000 x 4 + 5000 x 200
-- Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle CorpReceived on Sat Oct 13 2001 - 08:15:39 CDT
![]() |
![]() |