Oracle 9i/10g/11g编程艺术（2e）学习笔记【第1章】

第一章  开发成功的Oracle应用

1、关于位图索引

       位图索引适用于低基数（low-cardinality）列。低基数列就是指这个列只有很少的可取值。采用位图索引，一个键指向多行，可能数以百计甚至更多。如果更新一个位图索引，那么这个键所指向的数百条记录也会锁定。所以，如果插入一条记录到表t（表t有被位图索引的列，该列的值只允许Y、N，假设新插入记录对应的该列值为N），就会锁定位图索引中的N键，而这会同时锁定另外数百条为N的记录。此时，想要读取这个表并处理记录的进程就无法将N记录修改为Y记录。原因是，要想把这个列从N更新为Y，需要锁定同一个位图索引键。实际上，想在这个表中插入新记录的其他会话也会阻塞，因为它们同样想对这个位图索引键锁定。下面例子就能证明这一点。

create table t 
( processed_flag varchar2(1) 
);
create bitmap index
t_idx on t(processed_flag);
insert into t values ( 'N' );
declare
    pragma autonomous_transaction;
begin
    insert into t values ( 'N' );
    commit;
end;
/

执行上面sql，将出现“ORA-00060：等待资源时检测到死锁”，第二个自治事务insert语句被第一个insert语句阻塞。但如果使用两个不同的会话，就不会发生死锁，此时第二个insert会被阻塞并一直等待第一个insert事务提交或回滚。在上面例子中，有两个并发事务，自治事务将在会话中开始一个完全独立于第一个事务的“子事务”。自治事务表现得就好像在一个完全不同的会话中一样，它可以由父事务阻塞，也不会看到父事务做出的尚未提交的修改。

2、并发串行化访问处理方案

        现有一张排队表t（有一列processed_flag，值只能为N和Y），需并发串行化实现把该列为N的值更新为Y（记录“出队”）。根据上面对位图索引的描述，需对该列建立一个传统的B*树索引。假设对于标志为Y的列我们不想使用索引，只对值为N的列建立索引。建立基于函数的索引：

t_idx on 
t( decode( processed_flag, 'N', 'N' ) );

函数decode( processed_flag, 'N', 'N' )返回N或NULL，因为完全为NULL的键不会放在传统的B*树索引中，这样我们只对值为N的记录建立了索引。

        为实现并发串行化处理记录“出队”，我们必须并发查找第一条未锁定、未处理的记录，然后锁定该记录并进行处理。10g及以前版本、11g有不同的方式。

        首先创建带有B*树索引的表及插入一些数据：

drop table t purge;

create table t 
( id       number primary key,
  processed_flag varchar2(1),
  payload  varchar2(20)
);
create index 
t_idx on 
t( decode( processed_flag, 'N', 'N' ) );

insert into t 
select r, 
       case when mod(r,2) = 0 then 'N' else 'Y' end, 
       'payload ' || r
  from (select level r 
          from dual  
       connect by level <= 5)
/ 
select * from t;

       第二步，找到所有未处理的记录，并给它加锁（ 注意FOR UPDATE 部分实现加锁功能 ），实现并发串行化处理：

10g及以前版本：

下面代码实现加锁功能。

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;
    end;
    end loop;
    return null;
end;
/

下面代码对上述加锁代码进行测试。现在如果使用两个不同的事务，可以看到，她们会得到不同的记录。另外还可以看到，这个两个事务会并发地得到不同记录（这里再次使用自治事务展示并发问题）。

declare
    l_rec  t%rowtype;
begin
    l_rec := get_first_unlocked_row;
    dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );
end;
/

declare
    pragma autonomous_transaction;
    l_rec  t%rowtype;
begin
    l_rec := get_first_unlocked_row;
    dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );
    commit;
end;
/

上述测试代码输出以下结果：

I got row 2, payload 2
I got row 4, payload 4

11g：11g里新增一个SKIP LOCKED特性，它允许多个会话并发查找第一个未锁定、未处理的记录，然后锁定该记录并进行处理。以下为代码：

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;
/

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_aq.dequeue也能实现并发串行化问题，并且会容易很多，有机会试试。

3、绑定变量

     使用绑定变量，能大大减少sql硬解析次数，提高程序性能，防止sql注入攻击。下面列示sql注入攻击的例子。

创建一个过程，用于实现sql注入。

create or replace procedure inj( p_date in date )
as
	l_rec   all_users%rowtype;
	c       sys_refcursor;
	l_query long;
begin
	l_query := '
	select * 
	  from all_users 
	 where created = ''' ||p_date ||'''';

	dbms_output.put_line( l_query );
	open c for l_query;

	for i in 1 .. 5
	loop
		fetch c into l_rec;
		exit when c%notfound;
		dbms_output.put_line( l_rec.username || '.....' );
	end loop;
	close c;
end;
/

create table user_pw
( uname varchar2(30) primary key,
  pw    varchar2(30)
);
insert into user_pw
( uname, pw )
values ( 'TKYTE', 'TOP SECRET' );
commit;

注意下面这条sql，它可以使nls_date_format包含字符串常量。

alter session set nls_date_format = '"''union select tname,0,null from tab--"';

declare
begin
  inj(sysdate);
end;

4、小结

（1）要理解Oracle体系结构，知道数据库如何工作，弄清楚它能为你做什么，并最大限度加以利用；

（2）要理解锁定和并发控制特性，而且知道每个数据库都以不同的方式实现这些特性；理解Oracle中的多版本（multi-versioning）、读一致（read-consistent）查询、非阻塞查询；

          多版本：指Oracle能够从数据库同时物化多个版本的数据；

          读一致查询：对于一个时间点（point in time），查询会产生一致的结果，如打开一个游标（open v_x for select * from t，该游标实现从表t  select数据），但并不读取数据，然后delete表t中的数据并commit，然后再访问该游标，此时还会查询表t中在执行delete动作前的数据，这表明该游标在打开的那个时间点就已确定能读取哪些数据；

         非阻塞查询：查询不会被写入器（writer）阻塞，但在其他数据库中可能不是这样。

（3）阅读每个Oracle版本的Oracle Database Concepts文档，这是新功能指南，全面介绍了某一特定版本Oracle数据库提供的功能。