【有奖讨论】PL/SQL编程要点和注意点

dingjun123

tom对于limit值的观点

You Asked
Hi Tom, could you please tell me if exists any difference with LIMIT 1000 and for example LIMIT 100000? If I

increase the number of the limit... my process could run faster?

Thanks!

and we said...
it might

a) run faster (doubtful)
b) the same (doubtful)
c) slower


somewhere between 100 and 500 is typically "the best", getting 100k records INTO MEMORY and managing them take

"work"

What is the limit or the upper end of LIMIT.
At what threshold of LIMIT , will we get optimum performance.

i.e. what would you recommend the LIMIT to be ,  1000 or 2000, or 3000 or 5000 or 10,000



Followup   October 10, 2001 - 11am Central time zone:

I'd suggest hundreds, not thousands.  There are ram concerns, you want to keep the interaction
between you and database going back and forth -- not "ok database do tons of work, then I'll do
tons of work and then I'll give you tons of work again".  You want things to be flowing between you
and the database -- not "bursting" between you and the db.

Consider this -- if you array insert 10k rows - you might have to wait for LGWR to make room in the
redo buffer cache (sync writes to the file system).  If you send him 100 or 500 rows -- you won't
wait and LGWR will flush the buffer cache in the background whilst you are off doing the next
100/500 rows.

"Consider this -- if you array insert 10k rows - you might have to wait for LGWR to make room in
the redo buffer cache (sync writes to the file system).  If you send him 100 or 500 rows -- you
won't wait and LGWR will flush the buffer cache in the background whilst you are off doing the next
100/500 rows."

Firstly, YES, I will limit it to a figures between 100 and 500, but what can be the max number, out
of academic interest.

Secondly, what if the redo buffer has a lot of memory, then even in the case I set the limit at
5000, then it should not slow down the process, Iam I right.

Thirdly, Setting the LIMIT to a lesser number, does it also mean faster execution. , then when you
set the LIMIT to a hight number.

KIndly expand on the below--
"if you array insert 10k rows - you might have to wait for LGWR to make room in the redo buffer
cache (sync writes to the file system).  "

xilangxiangwa

lastwinner 发表于 2012-1-10 01:58
编程规范我还想到一点——空格，记得在某个版本的pl/sql中，如果你写了如下一行代码
k:=0;
for i in 1... ...

没看出来是怎么回事啊,
执行报错啊.

newkid

xilangxiangwa 发表于 2012-2-9 23:04
没看出来是怎么回事啊,
执行报错啊.

你没看到他FOR的那一行露了三点？

xilangxiangwa

是啊，所以报错啊，
那因空格导致结果出人意料是怎么个说法？

xilangxiangwa

关了电脑，准备上了厕所睡觉，
哪知突然想到 lastwinner 说的应该是指三点中最后一个点与倒数第二点之间有个空格，然后系统误认为是
0.255
这样才会出现结果认人大跌眼镜

上完厕所开机一试果然如此:
declare
k number(30,4);
begin
k:=0;
for i in 1.. .255 loop
    k:=k+i;
end loop;

dbms_output.put_line(k);
end;

0

lastwinner

xilangxiangwa 发表于 2012-2-9 23:04
没看出来是怎么回事啊,
执行报错啊.

你的服务器端和客户端的版本各是啥？
有的版本不报错，有的版本报错，这个事情很好玩
报错的，会认为 1...255 中的 ... 是非法操作符
不报错的，会将 1...255 解析为 1 .. .25，即1 .. 0.25，所以，结果你也肯定知道了吧？呵呵

newkid

哈哈，野花的阴谋被你识破了。

lastwinner

newkid 发表于 2012-2-10 00:10
哈哈，野花的阴谋被你识破了。

没完全识破呀
某版本下是不用带空格也能识别为0.255的，记不清什么版本了

在发现我所说的两种不同表现时，我曾用过的数据库版本是8.1.5  8.1.6  8.1.7  9.2.0.1  9.2.0.4  9.2.0.8。

xilangxiangwa

哈哈
不错

yiwenvtech

简单说几句
1.sql不是过程语言，仅是数据库操作命令集合。pl/sql增加了过程控制等编程语言特点(原型是PASCAL语言-最早面向对象的语言)
2.使用标准sql通常不会因为数据库升级而出错，有时使用pl/sql内置包在升级后由于包的改变可能导致需要修改原程序
3.什么时候使用cursor需要因情况而定，数据量大小、索引的使用、内存、存储等很多因素影响性能，不能简单说cursor不好。
  针对处理大数据量，还是建议使用cursor，并在处理了一部分数据(如1000条或5000条记后)及时提交有时会大大提高性能。
  其实任何时候处理数据时都是使用cursor的，只是sql语句一般使用的是隐性cursor，我们所说的cursor是显性定义的cursor而已。关键要看explain plan是否有效率。

另外,
oralce pl/sql可以调用java来做更多扩展，有时候使用文件处理方式会优过在数据库处理，比如先导出大量数据，使用文件处理方式处理数据后再导入数据库等。
oracle 10g,11g也提供了许多新特性，如分区表，materilize view，pivot查询等等...