[原创]如何通过临时表来加快数据库查询的性能

大菠萝

下面是一些问题需求

由动态语句构造的游标如下:
Select field1 , field2 .........................
From tabname
Where filed1 In ( 1 , 2 , 3 , 4 , 5 ................);
用动态的方法构造如上语句,其中条件中的In部分是动态用字符串并在一起的，In 的部分是一定要有的，但是这样写却是低效的，多个客户端调用执行后在缓冲池中会有多条类似的语句，浪费oracle的内存,是否有其他高效的方法可以替换一下，比如说把In后面的变量放到一个数组中(打个比方而已，Insert可以这样做,Select好像不行)以提高语句的服用性,有什么好的办法吗？

我现在要处理下面的情况,大家有什么好的建议:

Oracle数据库中有如下的表
OID NOT NULL NUMBER(3
GEOMETRY NOT NULL MDSYS.SDO_GEOMETRY
STRINGFIELD VARCHAR2(10)
DOUBLEFIELD NUMBER(25,
DOUBLEFIELD2 NUMBER(25,
BLOBFIELD BLOB

OID是主键,用户给了我一堆OID(可能很多,可能很少,不一定)
我要得到这些OID所对应的GEOMETRY对象的数据信息,当然,速度一定要快,
怎样构造这个SQL语句呀!

请大家一定帮忙,谢谢  

下面是测试用例:

1. 
   
2. CREATE TABLE cola_markets (
   
3. mkt_id NUMBER PRIMARY KEY,
   
4. name VARCHAR2(32),
   
5. shape MDSYS.SDO_GEOMETRY);
   
6. declare
   
7. i number;
   
8. begin
   
9. i :=100;
   
10. while i<100000 loop
    
11. i:= i+1;
    
12. INSERT INTO cola_markets VALUES(
    
13. i,
    
14. 'cola_a',
    
15. MDSYS.SDO_GEOMETRY(
    
16. 2003, -- 2-dimensional polygon
    
17. NULL,
    
18. NULL,
    
19. MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3), -- one rectangle (1003 = exterior)
    
20. MDSYS.SDO_ORDINATE_ARRAY(1,1, i,i) -- only 2 points needed to
    
21. -- define rectangle (lower left and upper right) with
    
22. -- Cartesian-coordinate data
    
23. )
    
24. );
    
25. end loop;
    
26. exception
    
27.    when no_data_found then
    
28.    NULL;
    
29. end;
    
30. 上面是创建cola_markets测试表的过程
    
31. 从上面的表cola_markets 中得到50000个oid的数据
    

复制代码

1. 
   
2. declare
   
3. i number;
   
4. begin
   
5. i:= 0;
   
6. while i<50000 loop
   
7. insert into oid_tmp(OID) values(i);
   
8. i:= i+1;
   
9. end loop;
   
10. end;
    

复制代码

上面是插入表oid_tmp50000个数据的过程,下面将会用到.

这两个问题是属于同一种问题,我们该如何解决了,
下面是两种方法我只能想到这两种方法,)
1 构造in语句
2 构造一张表,将数据放入,通过join来操作
构造in的方法,我想就不要试验了,一定是很慢的.尤其是对于50000个的,根本没有办法了.那就是剩下一个方法了,那就有几个问题需要考虑:
1 如何建立一张表,进行比较好的物理设置
2 如何快速的将50000个oid数据插入到表中
3 如何得到50000oid所对应的数据集


对于第一个问题:
如何建立一个表了,下面是对于设置一张表的物理设置:

1. 
   
2. initrans 4 maxtrans 255 storage ( initial 16k Next 8k minextents 1 maxextents  2147483645
   
3. pctincrease 0 freelists 4 Freelist groups 1) pctfree 10 pctused 90
   

复制代码

我只说明pctfree和pctused的含义,其他的大家自己找资料:
在oracle中对于数据的配置和操作系统的配置是一样的,数据分为数据块,
当从磁盘中读取数据的时候,内核试图先从高速缓冲中读.如果数据已经在该高速缓存中,则内核可以不必从磁盘上读.如果数据不再高速缓存中,则内核从磁盘中读数据,并将其缓存下来.
每个高速缓存数据块通过下面的方法来得到和释放参见<unix操作系统设计>

1. 
   
2. 算法 getblk
   
3. 输入:文件系统号
   
4.        块号
   
5. 输出:现在能被磁盘块使用的上了锁的缓存区
   
6. {
   
7.         while(没有找到缓存区)
   
8.                  {
   
9.                        if(块在散列队列中)
   
10.                              {
    
11.                                if(块忙)
    
12.                                  {
    
13.                                  sleep(等待"缓存区变为空闲"事件);
    
14.                                  continue;
    
15.                                  }
    
16.                                  为缓存区标记为"忙";
    
17.                                  从空闲表上摘下缓存区;
    
18.                                  return(缓冲区);
    
19.                                }
    
20.                                else
    
21.                                {
    
22.                                   if(空闲表上无缓存区)
    
23.                                   {
    
24.                                      sleep(等待"任何缓存区变为空闲"事件);
    
25.                                      continue;
    
26.                                  
    
27.                                   }
    
28.                                   从空闲表上摘下缓存区;
    
29.                                   if(缓冲区标记为延迟写)
    
30.                                       {
    
31.                                       把缓存区异步写到磁盘上;
    
32.                                       continue;
    
33.                                       }
    
34.                                   从旧散列队列中摘下缓冲区;
    
35.                                   把缓存区投到新散列队列;
    
36.                                   return (缓冲区);
    
37.                                }  
    
38.                     }
    
39.           }
    
40.          
    
41.          算法 brelse
    
42.          输入:上锁态的缓存区
    
43.          输出:无
    
44.          {
    
45.                  唤醒正在等待"无论那个缓冲区变为空闲"这一事件发生的所有进程;
    
46.                  唤醒正在等待"这个缓冲区变为空闲"这一事件发生的所有进程;
    
47.                   提高处理机执行级以封锁中断;
    
48.                   if(缓冲区内容有效且缓冲区非"旧")
    
49.                         将缓存区送入空闲表尾部
    
50.                   else
    
51.                         将缓冲区送入空闲表头部
    
52.                   降低处理机执行级以允许中断;
    
53.                   给缓存区解锁;       
    
54.          }
    
55.          

复制代码

         说明:
         1 缓冲区的数据块是放入一个散列队列中的,注意是全部的数据块,不管是空闲和忙的,空闲的数据
         块是放在一个freelist的双向链表中的,当内核把一个缓冲区还给缓冲区池时,它通常把该缓冲区附
         加到空闲表的头部,以保证最近最少使用的原则.
         2 数据块有三种状态:
                  1 忙 (有用户在用)
                  2 闲 (没有用户用)
                  3 延迟写 (当内核把缓冲区重新分配出去之前必须将该缓冲区内容写到磁盘上)
         3 "提高处理机执行级以封锁中断;"
             "降低处理机执行级以允许中断;"
             的意思就是为临界区的enter和left的含义
         
          上面说的这么多,只是让大家对"pctfree 10 pctused 90"有理解,pctfree的含义是当一个数据块中的
          内容为小于10%的时候,将其放入freelist中;当一个数据块中的内容大于90%的时候,将其设置为忙,多的
          空间用于数据的扩展.
         
          对于我们这个表,当如何进行设置了,因为,这个表做的工作为要么都insert,要delete allrows,那我们当
          将其pctfree设置为2,pctused 98,来减少io操作.
  

1. 
   
2.           create table oid_tmp(oid number  PRIMARY KEY)initrans 4 maxtrans 255 storage ( initial 16k \
   
3.                                                 Next 8k minextents 1 maxextents 2147483645 pctincrease 0 freelists 4 \
   
4.                                                 Freelist groups 1) pctfree 2 pctused 98 ;
   
5.          

复制代码

         
          表设置好了,可是我们忘记了一条:这个表是一个小表,经常进行insert操作,那我们可以将其长放在内存中,
          用如下的命令:
  

1. 
   
2.           alter table oid_tmp storage(buffer_pool keep);
   
3.          

复制代码

         
          通过上面的设置,我门可以看看插入50000个OID所要的时间:
  

1. 
   
2.         10:41:57 SQL>; declare
   
3.         10:44:30   2  i number;
   
4.         10:44:30   3  begin
   
5.         10:44:30   4  i:=0;
   
6.         10:44:30   5  while i<50000 loop
   
7.         10:44:30   6  insert into oid_tmp(OID) values(i);
   
8.         10:44:30   7  i:= i+1;
   
9.         10:44:30   8  end loop;
   
10.         10:44:30   9  end;
    
11.         10:44:31  10  /
    
12.        
    
13.         PL/SQL 过程已成功完成。
    
14.        
    
15.         10:44:48 SQL>;
    
16.        

复制代码

       
        一共用了48-31 = 17秒钟的时间
       
        上面都没有讲到临时表,只是用一般的表进行比较好的设置,现在,我们来看看临时表,和上面的方法进行一个比较:
        创建临时表

1. 
   
2.         Create GLOBAL temporary table oid_tmp(oid number  PRIMARY KEY) on commit delete rows;
   
3.         

复制代码

        
      

1. 
   
2.         10:49:13 SQL>;  declare
   
3.         10:49:20   2   i number;
   
4.         10:49:20   3   begin
   
5.         10:49:20   4   i:= 0;
   
6.         10:49:20   5   while i<50000 loop
   
7.         10:49:20   6   insert into oid_tmp(OID) values(i);
   
8.         10:49:20   7   i:= i+1;
   
9.         10:49:20   8   end loop;
   
10.         10:49:20   9   end;
    
11.         10:49:21  10  /
    
12.        
    
13.         PL/SQL 过程已成功完成。
    
14.        
    
15.         10:49:29 SQL>;
    
16.        

复制代码

        一共用到了29-21 = 8秒的时间,比上面通过设置快了一倍,说明我们当用临时表来达到我们的目的;
        第一个问题解决了,那我们来看看第二个问题
       
        对于第二个问题,好像没有什么可以研究的了,不就是用上面的进行插入吗?
        问题,就是我们用的oid数据都是外部的数据,不是pl/sql的内部数据,是要通过bind到oracle数据中的,
        如果向上面的这个样子,一条一条的bind,insert的话,需要很长的时间,我测试了一下,
        ......................140秒.
        但是如果象下面的进行insert的话:
        declare
        begin
        insert into oid_tmp(OID) values(id1);
        insert into oid_tmp(OID) values(id2);
        insert into oid_tmp(OID) values(id3);
        insert into oid_tmp(OID) values(id4);
        insert into oid_tmp(OID) values(id5);
        .....................
        end
        通过上面的方式来插入的话,时间为15秒,提高了9倍左右,......呵呵,这个问题解决了
       
        第三个问题:如何得到50000oid所对应的数据集,因为有了上面的基础,就很简单了,通过

1. 
   
2.         select * from cola_markets L oid_tmp R where L.MKT_ID = R.OID;
   
3.        

复制代码

        得到记录集,根本不费时间.
       
       
        说明:
        对于上面的问题,我们通过临时表进行了比较好的解决,临时表还可以用到其他的方面,有时间,
        我在写下来.
       
       
        这几天,对于这个问题,研究了一下,不知道大家有没有什么更好的方法,望指教.

snowpy

我觉得这样做法不一定比下面做法快
sFilter[50000];
for(int n = 0 ; n < 50000; ++n)
{
Bind sFilter[n] to :V0;
  Select * from table where oid=:V0
}
  有没有测试过？
另外我建议临时表不要使用PK,因为只有一个字段，unique scan和Full table scan应该没有太大区别吧？

大菠萝

sFilter[50000];
for(int n = 0 ; n < 50000; ++n)
{
Bind sFilter[n] to :V0;
Select * from table where oid=:V0
}

这没有上面的快,因为上面的时间都在外部变量到数据库中的时间,
select没有费时间,我经过测试了的.

rollingpig

晕
看起来很熟悉
原来是in问题

li2002

不错，支持原创！