Oracle function-based index对于数据更新处理延时问题。

[新鲜鱼排]

问题描述：根据tableA中Col2列数据对其他数据表进行更新，并且把Col2输具由0变成1。为了提高查询速度将数据表tableA中Col2使用了function-based index，现在使用嵌套游标来更新Col2的数据，并且为了提高执行速度使用两个程序来更新数据。偶尔会出现对其它数据表进行两次更新的情况，分析可能是由于多个程序调用的原因，使用了10个Server同时调用，结果出现问题的几率达到了25%。下面介绍一下具体的代码。

由于不能贴图，我将问题发在Blog上，
http://blog.csdn.net/dutguoyi/archive/2007/06/09/1645762.aspx

数据表tableA，数据结构如下：
Col1 Col2
Aa 0
Bb 1
Cc 1

步骤：
1. 建立Cur1
CURSOR cur1
IS
SELECT Col1 FROM tableA WHERE CEIL (col2) = 0 ORDER BY col3;
2. 建立Cur2
CURSOR cur2
IS
SELECT 一些数据（不列举了） FROM tableA WHERE col2 = 0 AND Col1 = 由Cur1中Col1变量得到的值 ORDER BY col3 FOR UPDATE SKIP LOCKED;
3. Cur1中嵌套Cur2

FETCH cur2 INTO 由Cur1中Col1变量得到的值;
FOR cur_row IN cur2 LOOP
UPDATE tableA SET col2 = 1 WHERE col1 = parameter_col1;
p_count := p_count + 1;
COMMIT
END LOOP;

为了更好的提高查询速度针对Col2列建立了一个function based index，建立的方法如下。Col2列的数据只有1和0两种数据，而且主要是1。
Cur1主要是获取到需要处理的数据。
Cur2主要是获取每一条数据，由于多个程序的调用，所以Cur1中的过滤条件也加了进去，因为某一条数据在一个程序循环过程中可能被另外一个程序执行结束。FOR UPDATE SKIP LOCKED语句的作用是对于数据表更新操作的锁定。

原因分析：可能是function-based index的数据更新有延迟问题，一条已经处理结束的数据状态仍然没有变换为1，导致另外一个程序查询到这条数据然后又进行了一次更新。

解决方案：去掉function-based index。

不知道function-based index是否是这个问题的原因，请解决过类似问题的兄弟多多指点。非常感谢。
参考资料;
Creating a Function-Based Index
http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96521/indexes.htm#2943

[新鲜鱼排]

To bobfang(匆匆过客)：非常感谢。
有几个问题：
1. 如果我把Funciton-based index 去掉以后就没有出现多次更新的问题
2. FOR UPDATE SKIP LOCKED 如果没有权限的话是不是应该出现错误了。但是我们没有出现错误。

[bobfang]

"FOR UPDATE SKIP LOCKED "不需要授权。我所说的“执行上面脚本前需要用sys给用户授予执行dbms_lock的权限”是指执行我所贴的那段脚本。

[新鲜鱼排]

1. 那么如果没有dbms_lock权限的话会对程序执行有什么影响哪？
2. 怎么快速查询用户是否由dbms_lock的权限？
3. Funciton-based index 去掉以后就没有出现多次更新的问题，这个又是为什么哪？

[新鲜鱼排]

dbms_lock是将整个表进行锁定吧？
我现在不希望做这样的锁定，我用多个程序来更新数据就是为了提高更新的速度。
重要的逻辑就是被一个程序放在游标中的数据还可以被其他程序更新。

[paoluo]

幫頂下

[新鲜鱼排]

等

[bobfang]

不应该是函数索引的问题。你程序的逻辑上有些问题。做如下测试
create table t_test1(c1 number, c2 date);
insert into t_test1 select rownum,sysdate from all_objects where rownum<=100;
commit;
set serveroutput on size 1000000
declare
cursor cur1 is select c1,c2 from t_test1;
v_c1 number;
v_c2 date;
begin
open cur1;
while 0=0 loop
fetch cur1 into v_c1,v_c2;
exit when cur1%NOTFOUND;
dbms_output.put_line(to_char(v_c1)||':'||to_char(v_c2,'yyyy-mm-dd hh24:mi:ss'));
commit;
dbms_lock.sleep(1);
end loop;
close cur1;
end;
/
当此会话执行时，在另一个会话中执行
update t_test1 set c2=sysdate-1;
commit;
你看看会话1执行的结果，结果所查询出的c2还都是修改前的值。这应该是oracle为了保证读一致性，所以即使在循环中有已提交的数据，但cursor查询查询出的依然是cursor open时的记录。
在执行上面脚本前需要用sys给用户授予执行dbms_lock的权限。

[新鲜鱼排]

up

[sdsxlj]

找冰mm解决啊,高手.

[青锋-SS]

别沉了

[新鲜鱼排]

怎么找冰mm

[青锋-SS]

在咱们Sql也挺活跃的

[icedut]

我测试了一下

在两个不同的会话里面
FOR UPDATE SKIP LOCKED 没有起作用
两个游标的查询结果一样
不过听说需要8.1.7.4.16以上的版本，否则不支持，或有core文件的
我的是9.2的版本
declare
cursor cur1 is select shopno,brand from tmp_shop where shopno<100 FOR UPDATE SKIP LOCKED ;
v_c1 number;
v_c2 varchar2(20);
begin
open cur1;
fetch cur1 into v_c1,v_c2;
loop

exit when cur1%NOTFOUND;
dbms_output.put_line(to_char(v_c1)||':'||to_char(v_c2));
fetch cur1 into v_c1,v_c2;
end loop;
close cur1;
end;

[青锋-SS]

高手终于出现了.

[icedut]

FOR UPDATE SKIP LOCKED
在不同的连接里到是起作用了

[新鲜鱼排]

这个、这个、这个、这个、这个、这个、这个、这个~~~~~~~~~~~~~~~~~~~~~
我对Oracle不是很熟悉，多个程序同时运行相同的语句来完成更新。我们就是为了提高效率。
加上索引就是为了提速，去掉了索引就不会出现问题了。

[新鲜鱼排]

冰mm~~~~~~~~

[新鲜鱼排]

Can anybody hear me?~~~~~~~~~~~~~

[chanet]

很复杂...-_-!!!

通常像这类问题，一般情况下不会再深入研究，而是想：是否有简单明了的方法来实现。
不推荐使用函数索引，如要改变（或转换）表字段的内容的话，建议用改变外值来适应表字段，如一条件： (f1 为 date 类型)

WHERE to_char(f1,'yyyy-mm-dd') = '2007-08-10'

直观来说是建一个函数索引to_char(...)，但更好的方法是：

WHERE f1 BETWEEN to_date(..) AND...

大概思路是这样。

PS：我很少上csdn,你的消息我今天才收到，抱歉。:)

[新鲜鱼排]

To chanet(牧师):
非常感谢，自己比较熟悉SQLServer中的优化，对Oracle中的函数索引理解不深。我以为可以实现更好的性能。再次感谢。