模糊查询(1) - 尾号查询

最新推荐文章于 2022-08-08 19:05:24 发布

cong870624

最新推荐文章于 2022-08-08 19:05:24 发布

阅读量1.3k

点赞数

文章标签：数据库

在应用项目中，经常要用到模糊查询。其中有些模糊查询是为了快速查询业务关键号的(主键/外键或者其它非键的关键列）。但是经过我对多个应用项目的最终用户使用过程的观察，用户查询是使用的最主要的还是尾号来查询。

举个人员编码的例子，比如说某个系统中人员编码的格式为：机构码+部门码+n位的流水号，用户在使用所谓的模糊查询功能时，绝大多数时间他会输入一个类似'00234'这样的尾号，在此他只是希望查到尾号对应的人，而不是要查找一个xxx0机构+0234部门的人。

从SQL的角度看，尾号查询对应的数据库查询，就是形如 colname like '%00234'这样的百分号前置的查询

我们都知道对于百分号后置的情形，数据库能够使用已存在的索引，从而提高查询效率。那么对于前置的情形，有没有好的解决办法呢？本文利用ORACLE的函数索引(FBI)来解决这个问题。

首先，准备我们的测试表，通过下面的脚本，我们构造了一个26万记录的表，其中object_id改为了字符型.

scott@O9I.US.ORACLE.COM> drop table t;


表已丢弃。


scott@O9I.US.ORACLE.COM> create table t as select * from all_objects;


表已创建。


scott@O9I.US.ORACLE.COM> alter table t modify(object_id number null);


表已更改。


scott@O9I.US.ORACLE.COM> update t set object_id = null;


已更新32953行。


scott@O9I.US.ORACLE.COM> alter table t modify(object_id varchar2(20));


表已更改。


scott@O9I.US.ORACLE.COM> insert into t select * from t;


已创建32953行。


scott@O9I.US.ORACLE.COM> insert into t select * from t;


已创建65906行。


scott@O9I.US.ORACLE.COM> insert into t select * from t;


已创建131812行。


scott@O9I.US.ORACLE.COM> update t set object_id =  to_char(Rownum, 'FM0000009');



已更新263624行。


scott@O9I.US.ORACLE.COM> commit;


提交完成。


scott@O9I.US.ORACLE.COM> create unique index pk_t on t(object_id);


索引已创建。


scott@O9I.US.ORACLE.COM>

然后，我们测试通常的使用like '%00123'的方式下的性能。

scott@O9I.US.ORACLE.COM> set timing on

scott@O9I.US.ORACLE.COM> set autotrace on

scott@O9I.US.ORACLE.COM> select object_id, object_name from t where object_id li

ke '%00123';


OBJECT_ID            OBJECT_NAME

-------------------- ------------------------------

0000123              /14a54f10_OracleResultSetMetaD

0100123              /90625075_AbstractButtonForwar

0200123              /66550812_JSClassTypeFactoryEl


已用时间:  00: 00: 01.06


Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   TABLE ACCESS (FULL) OF 'T'





Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets
       3464  consistent gets

       1662  physical reads
          0  redo size
        587  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

scott@O9I.US.ORACLE.COM>

尽管我们在object_id上创建了唯一索引，但这个索引对我们的查询没有丝毫帮助。当然，这一点都不奇怪。

下面我们创建一个函数f_revstr,它的功能就是将一个字符串"反转", 利用这个函数，我们可以将尾号反转成"首号"，然后在这个函数上创建函数索引。

scott@O9I.US.ORACLE.COM> create or replace function f_revstr(pSTR Varchar2)

  2  return varchar2 Deterministic is

  3    Result varchar2(100);

  4  begin

  5    For i In  1 .. lengthb(pSTR) Loop

  6       Result :=  substrb(pstr, i, 1) || Result;

  7    End Loop;

  8    return(Result);

  9  end f_revstr;

 10  /

函数已创建。


已用时间:  00: 00: 00.09

scott@O9I.US.ORACLE.COM>


scott@O9I.US.ORACLE.COM> create index fbi_t_object_id on t (substrb(f_revstr(obj

ect_id), 1, 20));

索引已创建。

注意这里使用了substrb函数，关于为何使用substr(b)函数，可以参考tom kyte的这篇文章(http://asktom.oracle.com/~tkyte/article1/index.html)

为了使ORACLE能够使用函数索引，需要满足以下条件:

QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED
用户具有QUERY REWRITE权限
必须使用CBO,因此表必须被ANALYZE
所使用的函数必须是确定性的(Deterministic)

scott@O9I.US.ORACLE.COM>

scott@O9I.US.ORACLE.COM> @connect system/manager

scott@O9I.US.ORACLE.COM> set echo off

已连接。

system@O9I.US.ORACLE.COM> ALTER SYSTEM SET QUERY_REWRITE_ENABLED=TRUE SCOPE=BOTH

;


系统已更改。


已用时间:  00: 00: 00.00

system@O9I.US.ORACLE.COM> ALTER SYSTEM SET QUERY_REWRITE_INTEGRITY=TRUSTED SCOPE

=BOTH;


系统已更改。


已用时间:  00: 00: 00.00

system@O9I.US.ORACLE.COM> grant QUERY REWRITE  to scott;


授权成功。


已用时间:  00: 00: 00.00

system@O9I.US.ORACLE.COM>system@O9I.US.ORACLE.COM>  @connect scott/tiger

system@O9I.US.ORACLE.COM> set echo off

已连接。

scott@O9I.US.ORACLE.COM> set autotrace on

scott@O9I.US.ORACLE.COM> analyze table t compute statistics for table for all in

dexes;


表已分析。

已用时间:  00: 00: 11.02

下面，用改写过的语句进行等价的查询:

scott@O9I.US.ORACLE.COM> Select object_id, object_name from t Where substrb(f_re

vstr(object_id),1,20) Like f_revstr('00123') || '%' ;


OBJECT_ID            OBJECT_NAME

-------------------- ------------------------------

0000123              /14a54f10_OracleResultSetMetaD

0100123              /90625075_AbstractButtonForwar

0200123              /66550812_JSClassTypeFactoryEl

已用时间:  00: 00: 00.03

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=334 Card=13181 Bytes
          =382249)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=334 Card=13181
          Bytes=382249)

   2    1     INDEX (RANGE SCAN) OF 'FBI_T_OBJECT_ID' (NON-UNIQUE) (Co
          st=9 Card=2373)





Statistics
----------------------------------------------------------
         74  recursive calls
          0  db block gets
         19  consistent gets

          3  physical reads
          0  redo size
        587  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          3  rows processed

scott@O9I.US.ORACLE.COM>

结论:通过执行计划可以看出，新的查询使用了函数索引，同时，查询的时间和IO都大大降低。

注意: 函数索引(FBI)是Oracle 企业版的功能，在标准版中不能使用。那么在不能使用FBI的情况下，如何解决这个问题呢？下面给出替代的解决方法:

在表中添加一列rev_object_id
添加一个trigger,当insert/update时，将f_revstr(object_id)写入rev_object_id列
需要尾号查询时，使用where rev_object_id like f_revstr('00123') || '%'

关键词: oracle fbi 尾号

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/32/viewspace-184197/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/32/viewspace-184197/

cong870624

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
模糊查询(1) - 尾号查询

在应用项目中，经常要用到模糊查询。其中有些模糊查询是为了快速查询业务关键号的(主键/外键或者其它非键的关键列）。但是经过我对多个应用项目的最终用户使用过程的观察，用户查询是使用的最主要的还是尾号来查询。举个人员编码的例子，比如...
复制链接

扫一扫