Re: update statement PL/SQL

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Fri, 29 Apr 2005 10:23:08 -0400
Message-ID: <3deuafF6ggk0dU1@individual.net>

Paul wrote:
>
> Serge Rielau <srielau_at_ca.ibm.com> wrote:
>
>
>

>>>Sorry, I may be confused here - am trying to learn PL/SQL. The book
>>>Learning Oracle PL/SQL by Bill Pribyl with Steven Feuerstein makes use
>>>of cursors - what's so bad about them?

>
>
>

>>In a nutshell a cursor is an API that connects the relation model of SQL 
>>with the procedural model. Whenever you cross models there's an 
>>impedance mismatch.
>>Statistically developers learn procedural concepts first (VB, C, Java, 
>>..) and have a hard time internalizing relational concepts, so they tend 
>>to code procedural.
>>However RDBMS shine in relational optimization and execution. So using 
>>an excess of procedural code (resulting in many cursors) is an indicator 
>>for bad exploitation of the RDBMS.

> But Daniel wrote "almost never" - surely there are times when it is
> necessary to trawl through a result set and modify/process the data in
> some way. Of course one can do this programatically
I don't see where "almost never" and "there are times when" conflict.
>
> Query1.first
> while not Query1.end
> begin
> processing here;
> Query1.next;
> end;
>
> or in a stored proc on the server. I would like to understand why this
> sort of logic "almost never" applies to Oracle.
Depends on what "processing" does. If "processing" is any sort of aggregation then that's what aggregation functions are for. If it involves selects from tables then that's what joins are for. If it's complex math, then that's what user defined functions are for. If it's SQL the RDNMS has a chance to parallelize. That is the query can be broken down and run simultaneously on multiple CPUs (SMP), possibly across multiple systems (RAC). Also the RDBMS may choose join methods other than nested loop (which is whata nested cursor is).

The compiler will have great difficulties recognizing such options in procedural logic because it must prove that order of execution does not matter. In pure SQL order of processing within a set does not matter by defintion unless explicitly specified (e.g. OLAP using an ORDER BY clause). In procedural logic the opposite holds true. Compiler technology for high performance parallel logic is a black art for good reason.

Cheers
Serge

-- 
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Received on Fri Apr 29 2005 - 09:23:08 CDT