oracle数据库：开发成功的Oracle应用【笔记4】
1.2黑盒方法（ oracle数据库：开发成功的Oracle应用【笔记3】http://www.52ij.com/jishu/5129.html oracle数据库：开发成功的Oracle应用【笔记2】http://www.52ij.com/jishu/5128.html oracle数据库：开发成功的Oracle应用【笔记1】http://www.52ij.com/jishu/5127.html）
幸好只要发现这个问题，纠正起来是很容易的，我们确实需要在PROCESSED_FLAG列上建立一个索引，但不是位图索引，这里需要一个传统的B*树索引。
只对标志为N的处理标志列创建一个索引，也就是说，只对感兴趣的值加索引。一般如果处理标志为Y，我们可能不想使用索引，因为表中大多数记录处理标志的值都可能是Y。注意这里的用词，没有说“我们绝对不想使用索引”，如果出于某种原因需要频繁地统计已处理记录的数目，对已处理记录加索引可能也很有用。
在函数decode( processed_flag,'N','N')上使用了一个基于函数的索引，从而返回N或NULL。因为完全为NULL的键不会放在传统的B*树索引中，这样一来，我们只对值为N的记录建立了索引。
到此就结束了吗？没有，绝对没有结束。客户仍然需要串行化实现未处理记录的“出队”。我们可以很容易地找到第一个未处理的记录，而且很快就能找到，只需使用SELECT * FROM QUEUE_TABLE WHERE DECODE(PROCESSED_FLAG,'N','N')='N' FOR UPDATE,不过一次只能有一个会话执行这个操作。这个项目使用的是Oracle Database 10g，所以还无法使用更新的SKIP LOCKED特性，这是Oracle Database 11g Release 1 中才增加的特性。SKIP LOCKED允许多个会话并发地查找第一个未锁定、未处理的记录，然后锁定该记录并进行处理。
sys@ORCL>create table t(id number primary key,processed_flag varchar2(1),payload varchar2(20));
Table created.
sys@ORCL>create index t_idx on t(decode(processed_flag,'N','N'));
Index created.
sys@ORCL>l
1 insert into t
2 select r,
3 case when mod(r,2)=0 then 'N' else 'Y' end,
4 'payload' || r
5 from (select level r
6 from dual
7* connect by level <=5)
sys@ORCL>/
5 rows created.
sys@ORCL>select * from t;
ID PRO PAYLOAD
---------- --- ------------------------------------------------------------
1 Y payload1
2 N payload2
3 Y payload3
4 N payload4
5 Y payload5
接下来实际上需要找到所有未处理的记录。我们要逐行地问数据库：“这一行已经加锁了吗？如果没有，给它加锁，并把它交给我”
create or replace
function get_first_unlocked_row
return t%rowtype
as
resource_busy exception;
pragma exception_init(resource_busy,-54);
l_rec t%rowtype;
begin
for x in (select rowid rid
from t
where decode(processed_flag,'N','N')='N')
loop
begin
select * into l_rec
from t
where rowid=x.rid and processed_flag='N'
for update nowait;
return l_rec;
exception
when resource_busy then null;
when no_data_found then null;
end;
end loop;
return null;
end;
/
基于Oracle Database 10g和以前的版本中，必须实现代码来查找第一个未加锁的记录，然后对其手动加锁。
现在，如果使用两个不同的事务，可以看到，它们会得到不同的记录。
sys@ORCL>declare
2 l_rec t%rowtype;
3 begin
4 l_rec :=get_first_unlocked_row;
5 dbms_output.put_line('i get row' || L_REC.id || ',' || l_rec.payload);
6 end;
7 /
i get row2,payload2
PL/SQL procedure successfully completed.
sys@ORCL>l
1 declare
2 pragma autonomous_transaction;
3 l_rec t%rowtype;
4 begin
5 l_rec :=get_first_unlocked_row;
6 dbms_output.put_line('I get row' || l_rec.id || ',' || l_rec.payload);
7* end;
sys@ORCL>/
I get row4,payload4
declare
*
ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at line 7
(测试环境为11g,运行以上代码报ora-06519)
在Oracle Database 11g Release 1 及以上版本中，可以使用SKIP LOCKED子句实现上述逻辑。
sys@ORCL>@20130817create_dec.sql
I got row2,payload2
PL/SQL procedure successfully completed.
declare
l_rec t%rowtype;
cursor c
is
select *
from t
where decode(processed_flag,'N','N')='N'
for UPDATE
SKIP LOCKED;
begin
open c;
fetch c into l_rec;
if (c%found)
then
dbms_output.put_line('I got row' || l_rec.id || ',' || l_rec.payload);
end if;
close c;
end;
/
sys@ORCL>@20130817create_deca.sql
I got row4,payload4
PL/SQL procedure successfully completed.
declare
pragma autonomous_transaction;
l_rec t%rowtype;
cursor c
is
select *
from t
where decode(processed_flag,'N','N')='N'
for UPDATE
SKIP LOCKED;
begin
open c;
fetch c into l_rec;
if (c%found)
then
dbms_output.put_line('I got row' || l_rec.id || ',' || l_rec.payload);
end if;
close c;
commit;
end;
/
？？？以上两种“解决方案”都有助于解决客户处理消息时遇到的第二个串行化问题。不过，如果客户能使用高级排队（Advanced Queuing）并调用DBMS_AD.DEQUEUE,解决方案会容易得多！要解决消息生产者的串行化问题，必须实现一个基于函数的索引。要解决消费者的串行化问题，则必须使用这个基于函数的索引来获取记录和编码代码。
这个项目的问题大致如此，所以需要解决以下方面的问题。
如何对SQL调优而不修改SQL
如何测量性能
如何查看哪里出现了瓶颈
如何建立索引，对什么建立索引...
关键是：数据库通常是应用的基石。如果它不能很好地工作，那其他的都没有什么意义了。
提倡的方法是：了解你的数据库，掌握它是怎么工作的，弄清楚它能为你做什么，并且最大限度地加以利用。
本文来源 我爱IT技术网 http://www.52ij.com/jishu/5130.html 转载请保留链接。
- 评论列表（网友评论仅供网友表达个人看法，并不表明本站同意其观点或证实其描述）
-