Re: Cursor Expressions

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 13 Oct 2001 06:15:39 -0700
Message-ID: <9q9epr0a1j@drn.newsguy.com>

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

    end loop

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 Corp 

Received on Sat Oct 13 2001 - 08:15:39 CDT