Oracle两个分页存储过程的比较

由于公司项目上的问题，在数据量很大的时候，在SELECT 语句中包含了很多 function 致使查询进行了全表扫描，例如查询语句：select function1(col1) as a1,function2(col2) as a2,function3(col3) as a3,...... from  table1 t1 where col5 = function(col5) 像这种SQL语句查询的过程中会变得巨慢，因为在function中又去其他表（比如字典表）中做了查询，一个只有10W记录的表，查询一个类似的SQL语句需要25～30秒的样子，原来的分页存储过程：

 1 create   or   replace  package body PK_Pager
 2 as
 3 procedure  GetPager
 4 (      
 5        p_PageSize  int ,           -- 每页记录数
 6        p_PageNo  int ,             -- 当前页码,从 1 开始
 7        p_SqlSelect  varchar2 ,     -- 查询语句,含排序部分
 8        p_OutRecordCount out  int , -- 返回总记录数
 9        p_OutCursor out mytype
10 )
11 as
12     v_sql  varchar2 ( 3000 );
13     v_count  int ;
14     v_heiRownum  int ;
15     v_lowRownum  int ;
16 begin
17    -- --取记录总数
18   v_sql : =   ' select count(*) from ( '   ||  p_SqlSelect  ||   ' ) ' ;
19    execute  immediate v_sql  into  v_count;
20   p_OutRecordCount : =  v_count;
21    -- --执行分页查询
22   v_heiRownum : =  p_PageNo  *  p_PageSize;
23   v_lowRownum : =  v_heiRownum  -  p_PageSize  +   1 ;
24
25   v_sql : =   ' SELECT * 
26             FROM (
27                   SELECT A.*, rownum rn 
28                   FROM  ( ' ||  p_SqlSelect  || ' ) A
29                   WHERE rownum <=  ' ||  to_char(v_heiRownum)  ||   '
30                  ) B
31             WHERE rn >=  '   ||  to_char(v_lowRownum) ;
32              -- 注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn
33   
34    OPEN  p_OutCursor  FOR   v_sql;
35 end  GetPager;
36
37 end  PK_Pager;
38

应该说这个分页存储过程还是比较快的，但因为业务逻辑中的SQL语句写的太复杂了，以致于就像我上面所说的SQL语句查询也会变得比较慢，为了解决这个问题，我对分页的存储过程又做了修改，主要思想是在最内层仅取出所要查询记录的rowid,然后再在外层对要取出的pagesize调记录调用那些sql方法，这样做效果肯定是提高了，但解析不同的SQL语句就成了一个比较复杂的问题，改动后存储过程仅仅解析SQL语句就需要花费500～700毫秒，但读出记录集确是快了不少，相反以前的存储过程解析SQL语句仅需要50毫秒，而返回出记录集却要20～30秒，相比之下改动后整体上还是提高了不少，下面请看存储过程，我个人觉得在取具体单条记录的效率上是还存在点问题，有时间我将会再改造改造。

  1 CREATE   OR   REPLACE  Package Body PKAge_Pager_XxjV2  As
  2
  3 -- ----------------------------------------------------------------------------------------------------------------------------------
  4 -- -获得记录集
  5 -- ----------------------------------------------------------------------------------------------------------------------------------
  6 Procedure  GetRecords(p_PageSize  Int ,  -- 每页记录数
  7 p_PageIndex  Int ,  -- 当前页码,从 1 开始
  8 p_SqlSelect  Varchar2 ,  -- 查询语句,含排序部分
  9 p_RecordIndex     Int   Default   0 , -- 单条记录ROWNUM索引编号：如果值为0则返回分页的记录集，如果值不为0则返回具体的记录
 10 p_OutRecordCount Out  Int ,  -- 返回总记录数
 11 p_OutPageCount Out  Int ,  -- 返回总的页数
 12 p_OutCursor Out mytype  -- 返回的记录集游标
 13 )  As
 14
 15 v_sql  Varchar2 ( 8000 );
 16 v_count  Number ; v_lowRownum  Number ; v_heiRownum  Number ;
 17 v_selectfields  Varchar2 ( 4000 );        -- 要查询的字段
 18 v_tablenames  Varchar2 ( 1000 );          -- 多个表名称
 19 v_wherecondition  Varchar2 ( 3000 );      -- where条件子句
 20 v_posfrom  Number ;                -- from的位置
 21 v_posselect  Number ;              -- select的位置
 22 v_poswhere  Number ;               -- where条件的位置
 23 v_posorderby  Number ;             -- order by的位置
 24 v_primaryTable  Varchar2 ( 1000 );    -- 表示记录中的主记录的表
 25 v_tmpwhereint  Number ;                 -- 临时保存where的位置
 26 v_tmpfromint  Number ;                  -- 临时保存from的位置
 27 v_tmpint  Number ;
 28 v_tmpstr  Varchar2 ( 8000 );
 29 v_flag  Number ;
 30 v_tablecountsflag  Number ;
 31 Begin
 32 v_flag : =   0 ;  -- 默认状态
 33 v_tablecountsflag : = 0 ;
 34 -- ----------------------------------------------------------先处理相关变量
 35 v_posfrom : =  instr( lower (p_SqlSelect),  ' from  ' ,  1 , 1 );  -- 查找第一个from的位置
 36 v_selectfields : =  substr(p_SqlSelect, 1 ,v_posfrom - 1 );  -- 取出第一个from之前的select ###### 
 37 -- 判断语句是否还有select子句
 38 v_tmpfromint : =  instr( lower (v_selectfields), ' from  ' ,v_posfrom + 5 , 1 );  -- 查找出第二个from的位置
 39 If   v_tmpfromint  >   0   Then
 40 -- 如果第二个from存在，需要判断是否第一个select和第一个from之间是否有select
 41   v_posselect : =  instr( lower (substr(p_SqlSelect, 1 ,v_posfrom)), ' select  ' , 1 , 2 );
 42    If  v_posselect  >   0   Then
 43    -- 说明在查询的字段中有 select from 子语句，情况较复杂
 44    -- 格式：S--(S--F)----F--.
 45     v_flag : =   1 ;
 46       Goto  do_flag;
 47    Else
 48    -- 说明第二个from是 视图或者where条件中的从句，第一个from就是最外层的 from 
 49    -- 格式：S--F--(..F..)--
 50    -- v_selectfields := substr(p_SqlSelect,1,v_posfrom-1); --取出第一个from之前的select ###### 
 51    -- 判断两个from之间是否还有where条件
 52   v_poswhere : =  instr( lower (p_SqlSelect),  ' where  ' , v_posfrom + 5 , 1 );  -- 查找两个from之间的where，这也是第一个where
 53      If  v_poswhere  <  v_tmpfromint  And  v_poswhere  >  v_posfrom  Then
 54        -- 两个from之间有where ,说明第二个from是where条件中的 select from子句 
 55        -- 格式：S--F--W--(..F..)--
 56       v_wherecondition : =  substr(p_SqlSelect,v_poswhere);
 57       v_tablenames : =  substr(p_SqlSelect,v_posfrom + 5 ,v_poswhere - (v_posfrom + 5 ));
 58        Goto  do_flag;
 59     Elsif v_poswhere  >  v_tmpfromint  Then  
 60        -- where的位置大于第二个from ,说明 from的视图或表是一个子查询 
 61        -- 格式：S--F--(S--F--W)
 62        -- 这里需要判断在第一个where之后是否还有where,
 63       v_tmpwhereint : =  instr( lower (p_SqlSelect),  ' where  ' , v_poswhere + 6 , 1 );  -- 取出全局中第二个where的位置
 64        If  v_tmpwhereint  >   0   Then
 65        -- 现在的格式：S--F--S--F--W--..W..-- 情况交复杂
 66         v_flag : =   1 ;
 67           Goto  do_flag;
 68        Else
 69          -- 全局中仅有一个where,这里需要判断where是全局查询条件 还是 from的视图查询条件
 70          -- 通过判断第二个from和第一个where之间是否有‘）’符号
 71         v_tmpstr : =  substr(p_SqlSelect,v_tmpfromint + 5 ,v_poswhere - (v_tmpfromint + 5 ));
 72          If   instr(v_tmpstr, ' ) ' , - 1 , 1 )  >   0   Then
 73            -- 说明where是全局查询条件
 74            Goto  do_sfw1;
 75          Else
 76           -- 说明where是from视图的查询条件
 77           Goto  do_orderby;
 78          End   If ;
 79        End   If ;
 80      Else   -- 没有查询到where条件的位置，说明一个where条件都没有
 81        Goto  do_orderby;
 82      End   If ;
 83    End   If ;
 84 End   If ;
 85   -- -----如果没有select from子句,全局找不到第二个 from -----------------------------------
 86 << do_sfw1 >>
 87 v_selectfields : =  substr(p_SqlSelect, 1 ,v_posfrom - 1 );  -- 取出第一个from之前的select ###### 
 88 -- 如果没有select from子句,查找where的位置
 89 v_poswhere : =  instr( lower (p_SqlSelect),  ' where  ' , v_posfrom + 4 , 1 );  -- 查找第一个where的位置
 90 -- 判断语句中第一个where是否存在
 91 If  v_poswhere  >   0   Then   -- 存在
 92    v_wherecondition : =  substr(p_SqlSelect,v_poswhere);
 93    v_tablenames : =  substr(p_SqlSelect,v_posfrom + 5 ,v_poswhere - (v_posfrom + 5 ));
 94     Goto  do_flag;    
 95 End   If ;
 96
 97 << do_orderby >>
 98 -- 如果语句中没有where条件,先查询是否有order by 排序条件 从字符串最后一个字符查找
 99 v_posorderby : =  instr( lower (p_SqlSelect),  ' order by  ' ,  - 1 , 1 );  -- 查找order by的位置
100 If  v_posorderby  > 0   Then
101 -- 如果语句中有order by
102  v_wherecondition : =  substr(p_SqlSelect,v_posorderby);
103  v_tablenames : =  substr(p_SqlSelect,v_posfrom + 5 ,v_posorderby - (v_posfrom + 5 ));
104 Else
105 -- 如果语句中也没有order by
106   v_wherecondition : =   '' ;
107   v_tablenames : =  substr(p_SqlSelect,v_posfrom + 5 );
108 End   If ;
109 << do_flag >>
110 If  v_flag  <=   0   Then
111     -- 这里要处理获得primarytable主视图表的名称
112    << do_primarytable >>
113   v_tablenames : =  trim(v_tablenames);
114   v_tmpint : =  instr(v_tablenames, ' , ' , 1 , 1 );
115    If  v_tmpint  >   0   Then   -- 判断是否是多个表的联合查询
116      v_primaryTable : =  substr(v_tablenames, 1 ,v_tmpint - 1 );
117      v_tablecountsflag : =   1 ;  -- 标识符说明有多个表
118    Else
119       v_tmpint : =  instr( lower (v_tablenames), ' join ' , 1 , 1 );
120        If  v_tmpint  >   0   Then
121          v_primaryTable : =  substr(v_tablenames, 1 ,v_tmpint - 1 );
122          v_primaryTable : =   Replace (v_primaryTable, ' out ' , '' );
123          v_primaryTable : =   Replace (v_primaryTable, ' left ' , '' );
124          v_primaryTable : =   Replace (v_primaryTable, ' right ' , '' );
125          v_primaryTable : =   Replace (v_primaryTable, ' inner ' , '' );
126        Else  
127          v_primaryTable : =  v_tablenames;
128        End   If ;
129    End   If ;
130   v_primaryTable : =   lower (trim(v_primaryTable));
131   v_primaryTable : =   replace ( replace (v_primaryTable,chr( 13 )),chr( 10 ));     -- 去除换行符
132    -- 去除多个空格的情况
133   v_primaryTable : =   Replace (v_primaryTable, '     ' , '   ' );  -- 去除3个连续空格
134   v_primaryTable : =   Replace (v_primaryTable, '    ' , '   ' );  -- 去除2个连续空格
135   v_tmpint : =  instr(v_primaryTable, '   ' , 1 , 1 );
136    If  v_tmpint  >   0   Then
137      v_primaryTable : =  trim(substr(v_primaryTable,v_tmpint + 1 ));
138    Else   -- 当没有设置表的别名, 自己给定别名
139      v_tmpstr : =  v_primaryTable  ||   '  xxjmytb ' ;
140      v_tablenames : =   Replace ( lower (v_tablenames),v_primaryTable,v_tmpstr);
141       -- 先将WHERE和SELECT变量中的'转义
142       -- v_wherecondition := Replace(v_wherecondition,chr(39),'''');
143       -- v_selectfields := Replace(v_selectfields,chr(39),'''');
144        -- 还要替换掉where条件中的表名称的
145       v_wherecondition : =   lower (v_wherecondition);
146       v_wherecondition : =   Replace (v_wherecondition,v_primaryTable || ' . ' , ' xxjmytb. ' );
147        -- 还有替换掉selectfields中的表名称
148       v_selectfields : = lower (v_selectfields);
149       v_selectfields : =   Replace (v_selectfields,v_primaryTable || ' . ' , ' xxjmytb. ' );
150       v_tmpint : =  instr(v_primarytable, ' . ' , - 1 , 1 );  -- 查找带点的表名
151        If   v_tmpint  >   0   Then
152         v_tmpstr : =  trim(substr(v_primarytable,v_tmpint + 1 ));
153         v_tmpstr : =  v_tmpstr || ' . ' ;
154         v_wherecondition : =   Replace (v_wherecondition,v_tmpstr, ' xxjmytb. ' );
155         v_selectfields : =   Replace (v_selectfields,v_tmpstr, ' xxjmytb. ' );  -- 替换掉selectfields中的表名称
156        End   If ;
157      v_primarytable : =   ' xxjmytb ' ;
158    End   If ;
159   v_tablenames : =   '   '   ||  v_tablenames  ||   '   ' ;
160    -- -取分页总数-----------------------目的是为了在统计的时候将字段中的相关方法出去
161   v_sql : =   ' select count(*) from  '   ||  v_tablenames  ||   v_wherecondition;
162    Execute  Immediate v_sql  Into  v_count; p_OutRecordCount : =  v_count;  -- 将总记录数赋值给返回的参数
163   p_OutPageCount : =  ceil(v_count / p_PageSize);  -- 将总页数赋值给返回的参数
164   v_heiRownum : =  p_PageIndex  *  p_PageSize; v_lowRownum : =  v_heiRownum  -  p_PageSize  +   1 ;  -- 计算开始和起始位置
165   v_sql : =   ' select  ' ||  v_primaryTable  || ' .rowid as xxjid,rownum as rownn_xxj from  '   ||  v_tablenames  ||  v_wherecondition;
166   v_sql : =   ' select xxjid,rownum as rownn_xxj from ( '   ||  v_sql  ||   ' ) where rownn_xxj <= '   ||  to_char(v_heiRownum);
167   v_sql : =   ' select xxjid from ( ' ||  v_sql  || ' ) where rownn_xxj >=  '   ||  to_char(v_lowRownum);
168    If  v_tablecountsflag  >   0   Then
169      v_sql : =  v_selectfields  ||   ' ,rownum as rownn_xxj from  '   ||  v_tablenames  ||   ' ,( ' ||  v_sql  || ' ) xxjlast where xxjlast.xxjid= ' ||  v_primaryTable  ||   ' .rowid and  ' ||  substr(trim(v_wherecondition), 6 );
170    Else
171       v_sql : =  v_selectfields  ||   ' ,rownum as rownn_xxj from  '   ||  v_tablenames  ||   ' ,( ' ||  v_sql  || ' ) xxjlast where xxjlast.xxjid= ' ||  v_primaryTable  ||   ' .rowid ' ;
172    End   If ;
173 Else
174   v_sql : =   ' select count(*) from ( '   ||  p_SqlSelect  ||   ' ) ' ;
175    execute  immediate v_sql  into  v_count;
176   p_OutRecordCount : =  v_count;
177   p_OutPageCount : =  ceil(v_count / p_PageSize);  -- 将总页数赋值给返回的参数
178   v_heiRownum : =  p_PageIndex  *  p_PageSize; v_lowRownum : =  v_heiRownum  -  p_PageSize  +   1 ;
179   v_sql : =   ' SELECT * FROM (SELECT A.*, rownum rownn_xxj FROM  ( ' ||  p_SqlSelect  || ' ) A WHERE rownum <=  ' ||  to_char(v_heiRownum)  ||   '  ) B WHERE rownn_xxj >=  '   ||  to_char(v_lowRownum) ;
180 End   If ;
181 -- #############如果是具体某条记录的详细信息################################################
182 If  p_RecordIndex  >   0   Then
183    v_sql : =   ' select * from ( ' ||  v_sql  || ' ) where rownn_xxj= ' || to_char(p_RecordIndex); 
184 End   If ;
185 -- #########################################################################################
186 Open  p_OutCursor  For  v_sql;
187
188 End  GetRecords;
189
190 -- -------------------------------------------------------------------------------------------------------------------------
191
192 End  PKAge_Pager_XxjV2;
193

可能以上存储过程看上去比较恐怖，但我确实做了 ，唯一觉得不理想的是觉得取单条记录做的不好，然后就是对 在ORACLE中做如此多的字符串查找和截取的效率感到不满意，不知道有没有什么方法可以提高一下，提出来大家探讨一个。

我的技术博客写的比较少，不知道这种文章能不能放到首页上，我只是想让更多人看到探讨一下，就在首页放半天，请DuDu谅解！

转载于:https://www.cnblogs.com/DrkBreeze/archive/2007/04/26/727840.html