Re: Multiple updates w. static SQL vs. single dynamic SQL update

From: max <cccp.forever_at_gmail.com>
Date: 17 Jul 2006 13:52:50 -0700
Message-ID: <1153169570.495037.204690@h48g2000cwc.googlegroups.com>

Daniel, thanks for your reply. I got this from the the "Complex IN Demo":

DECLARE i PLS_INTEGER;
InStr VARCHAR2(20) := '10,30';

BEGIN
  SELECT COUNT(*)
  INTO i
  FROM emp
  WHERE deptno IN (InStr);

  dbms_output.put_line(i);
END;
/

This will never work. Assuming deptno is a number you'll get ORA-01722 Invalid Number.
What happens is Oracle tries to convert InStr which is '10,30' to a number and it can't. It will not try to parse it into 10 and 30 and put those as the IN clause parameters.

The CAST example is good but we found it would kill the indexes, making update expensive.

The question is more theoretic. I would like to know what is more expensive hitting the table a 1000 times to update 1 record at a time or go with a dynamic SQL with the expense of it being compiled along with the execution plan being calculated every time the procedure is executed.

Thanks.

DA Morgan wrote:
> max wrote:
> > Hi,
> >
> > What would be better (faster):
> >
> > 1.have a stored procedure loop thru a list of values and execute a
> > statement:
> >
> > update customers
> > set active = 1
> > where customer_code = value
> >
> > 2. create a comma delimited string of values like so:
> >
> > list := value1 || ',' || value2 || ',' || value3....
> >
> > and using it in a dynamic SQL like so:
> >
> > v_sql = 'update customers
> > set active = 1
> > where customer_code in ( ' || list || ')'
> >
> > Thanks.

>

> Single row processing, by definition, is always a bad idea so throw away
> the loop if there is an alternative.

>

> Native dynamic SQL is never a first choice so that's not necessarily the
> best idea either.

>

> What is impossible to tell from what you've written is why you need
> either. Go to www.psoug.org and click on Morgan's Library. Click on
> "Conditions" and scroll down to "Complex IN Demo."

>

> Why won't this work?
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org
Received on Mon Jul 17 2006 - 15:52:50 CDT