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

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

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

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语句呀! 

请大家一定帮忙,谢谢  
[/quote:5d25875cb1]

下面是测试用例:
[code:1:5d25875cb1]
CREATE TABLE cola_markets ( 
mkt_id NUMBER PRIMARY KEY, 
name VARCHAR2(32), 
shape MDSYS.SDO_GEOMETRY); 
declare 
i number; 
begin 
i :=100; 
while i<100000 loop 
i:= i+1; 
INSERT INTO cola_markets VALUES( 
i, 
'cola_a', 
MDSYS.SDO_GEOMETRY( 
2003, -- 2-dimensional polygon 
NULL, 
NULL, 
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3), -- one rectangle (1003 = exterior) 
MDSYS.SDO_ORDINATE_ARRAY(1,1, i,i) -- only 2 points needed to 
-- define rectangle (lower left and upper right) with 
-- Cartesian-coordinate data 
) 
); 
end loop; 
exception 
   when no_data_found then 
   NULL; 
end; 
上面是创建cola_markets测试表的过程
从上面的表cola_markets 中得到50000个oid的数据
[/code:1:5d25875cb1]

[code:1:5d25875cb1]
declare 
i number; 
begin 
i:= 0; 
while i<50000 loop 
insert into oid_tmp(OID) values(i); 
i:= i+1;
end loop; 
end; 
[/code:1:5d25875cb1]
上面是插入表oid_tmp50000个数据的过程,下面将会用到.

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


对于第一个问题:
如何建立一个表了,下面是对于设置一张表的物理设置:
[code:1:5d25875cb1]
initrans 4 maxtrans 255 storage ( initial 16k Next 8k minextents 1 maxextents  2147483645 
pctincrease 0 freelists 4 Freelist groups 1) pctfree 10 pctused 90 
[/code:1:5d25875cb1]
我只说明pctfree和pctused的含义,其他的大家自己找资料:
在oracle中对于数据的配置和操作系统的配置是一样的,数据分为数据块,
当从磁盘中读取数据的时候,内核试图先从高速缓冲中读.如果数据已经在该高速缓存中,则内核可以不必从磁盘上读.如果数据不再高速缓存中,则内核从磁盘中读数据,并将其缓存下来.
每个高速缓存数据块通过下面的方法来得到和释放参见<unix操作系统设计>
[code:1:5d25875cb1]
算法 getblk
输入:文件系统号
       块号
输出:现在能被磁盘块使用的上了锁的缓存区
{
        while(没有找到缓存区)
                 {
                       if(块在散列队列中)
                             {
                               if(块忙)
                                 {
                                 sleep(等待"缓存区变为空闲"事件);
                                 continue;
                                 }
                                 为缓存区标记为"忙";
                                 从空闲表上摘下缓存区;
                                 return(缓冲区);
                               }
                               else
                               {
                                  if(空闲表上无缓存区)
                                  {
                                     sleep(等待"任何缓存区变为空闲"事件);
                                     continue;
                                 
                                  }
                                  从空闲表上摘下缓存区;
                                  if(缓冲区标记为延迟写)
                                      {
                                      把缓存区异步写到磁盘上;
                                      continue;
                                      }
                                  从旧散列队列中摘下缓冲区;
                                  把缓存区投到新散列队列;
                                  return (缓冲区);
                               }  
                    }
          }
          
         算法 brelse
         输入:上锁态的缓存区
         输出:无
         {
          唤醒正在等待"无论那个缓冲区变为空闲"这一事件发生的所有进程;
          唤醒正在等待"这个缓冲区变为空闲"这一事件发生的所有进程;
   提高处理机执行级以封锁中断;
   if(缓冲区内容有效且缓冲区非"旧")
         将缓存区送入空闲表尾部
   else
         将缓冲区送入空闲表头部
   降低处理机执行级以允许中断;
   给缓存区解锁;
         }
         [/code:1:5d25875cb1]

         说明:
         1 缓冲区的数据块是放入一个散列队列中的,注意是全部的数据块,不管是空闲和忙的,空闲的数据
         块是放在一个freelist的双向链表中的,当内核把一个缓冲区还给缓冲区池时,它通常把该缓冲区附
         加到空闲表的头部,以保证最近最少使用的原则.
         2 数据块有三种状态:
                  1 忙 (有用户在用)
                  2 闲 (没有用户用)
                  3 延迟写 (当内核把缓冲区重新分配出去之前必须将该缓冲区内容写到磁盘上)
         3 "提高处理机执行级以封锁中断;"
      "降低处理机执行级以允许中断;"
      的意思就是为临界区的enter和left的含义
  
   上面说的这么多,只是让大家对"pctfree 10 pctused 90"有理解,pctfree的含义是当一个数据块中的
   内容为小于10%的时候,将其放入freelist中;当一个数据块中的内容大于100%的时候,将其设置为忙,多的
   空间用于数据的扩展.
  
   对于我们这个表,当如何进行设置了,因为,这个表做的工作为要么都insert,要delete allrows,那我们当
   将其pctfree设置为2,pctused 98,来减少io操作.
   [code:1:5d25875cb1]
   create table oid_tmp(oid number  PRIMARY KEY)initrans 4 maxtrans 255 storage ( initial 16k \
Next 8k minextents 1 maxextents 2147483645 pctincrease 0 freelists 4 \
Freelist groups 1) pctfree 2 pctused 98 ;
   [/code:1:5d25875cb1]
  
   表设置好了,可是我们忘记了一条:这个表是一个小表,经常进行insert操作,那我们可以将其长放在内存中,
   用如下的命令:
   [code:1:5d25875cb1]
   alter table oid_tmp storage(buffer_pool keep);
   [/code:1:5d25875cb1]
  
   通过上面的设置,我门可以看看插入50000个OID所要的时间:
   [code:1:5d25875cb1]
10:41:57 SQL> declare 
10:44:30   2  i number; 
10:44:30   3  begin 
10:44:30   4  i:=0; 
10:44:30   5  while i<50000 loop 
10:44:30   6  insert into oid_tmp(OID) values(i); 
10:44:30   7  i:= i+1; 
10:44:30   8  end loop;
10:44:30   9  end; 
10:44:31  10  /

PL/SQL 过程已成功完成。

10:44:48 SQL> 
[/code:1:5d25875cb1]

一共用了48-31 = 17秒钟的时间

上面都没有讲到临时表,只是用一般的表进行比较好的设置,现在,我们来看看临时表,和上面的方法进行一个比较:
创建临时表
[code:1:5d25875cb1]
Create GLOBAL temporary table oid_tmp(oid number  PRIMARY KEY) on commit delete rows; 
        [/code:1:5d25875cb1]
        
        [code:1:5d25875cb1]
10:49:13 SQL>  declare 
10:49:20   2   i number; 
10:49:20   3   begin 
10:49:20   4   i:= 0; 
10:49:20   5   while i<50000 loop 
10:49:20   6   insert into oid_tmp(OID) values(i);
10:49:20   7   i:= i+1; 
10:49:20   8   end loop;
10:49:20   9   end; 
10:49:21  10  /

PL/SQL 过程已成功完成。

10:49:29 SQL> 
[/code:1:5d25875cb1]
一共用到了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&n&nbssp;oid_tmp(OID) values(id3);
insert into oid_tmp(OID) values(id4);
insert into oid_tmp(OID) values(id5);
.....................
end
通过上面的方式来插入的话,时间为15秒,提高了9倍左右,......呵呵,这个问题解决了

第三个问题:如何得到50000oid所对应的数据集,因为有了上面的基础,就很简单了,通过
[code:1:5d25875cb1]
select * from cola_markets L oid_tmp R where L.MKT_ID = R.OID;
[/code:1:5d25875cb1]
得到记录集,根本不费时间.


说明:
对于上面的问题,我们通过临时表进行了比较好的解决,临时表还可以用到其他的方面,有时间,
我在写下来.


这几天,对于这个问题,研究了一下,不知道大家有没有什么更好的方法,望指教.

snowpy 回复于：2003-08-03 15:51:31

我觉得这样做法不一定比下面做法快 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应该没有太大区别吧？

大菠萝 回复于：2003-08-04 08:55:03

[quote:08c5bc21a9] sFilter[50000];  for(int n = 0 ; n < 50000; ++n)  {  Bind sFilter[n] to :V0;  Select * from table where oid=:V0  }  [/quote:08c5bc21a9] 这没有上面快,因为厦娴氖奔涠荚谕獠勘淞康绞?菘庵械氖奔? select没有费时间,我经过测试了的.

rollingpig 回复于：2003-09-28 14:12:27

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

li2002 回复于：2003-09-29 09:27:46

不错，支持原创！

原文转自：http://www.ltesting.net