Oracle 查询优化

Oracle 查询优化

在大表中，进行模糊查询，一般情况下是用LIKE'%%',但是这个东西走的是全表扫描，如果在数据量非常大的情况下，效率特别慢，因此，尝试用ORACLE函数INSTR（）来解决。

实验步骤如下：

首先构造一张百万行的表。

SQL> insert into emp2 select * from emp2;

1032192 rows inserted

如上所示，构造完成

接下来连续运用多个LIKE查询来模糊匹配

SQL> select * from emp2 where job like '%RE%' and ename like '%A%' and mgr like '%3%';

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------

Executed in 1.859 seconds

如上所示，LIKE查询一次，就走一次全表扫描，效率非常慢

同样的效果，现在来换做INSTR函数来执行

SQL> select * from emp where instr(job,'RE')>0 and instr(ename,'A')>0 and instr(mgr,'3')>0;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------

Executed in 0.063 seconds

看到了吧，，时间上的差异很明显，INSTR在一瞬间执行完成，因为这个是查找的字段，而非走全表扫描

看来,oracle 内部函数效率还是高些。

因此，大家以后碰到同样的问题，除了全文检索外，这个也是个好方式

注意：

    select   id, name from users where instr(id, '101') > 0;  

         等价于

          select   id, name from users where id like '%101%'

 

如：

用 like 查询时，索引会失效，like 是全文查询 ，不建议使用，可用 instr 代替，对单个字段查询，

 

 select * from   xb_port p where 1=1 and  instr(p.assemblename,'大通') >0
      and instr(p.assemblename,'铝厂') >0
      and instr(p.assemblename,'05') >0  ;

 

二，也以用

Oracle表分区

见：http://love-flying-snow.iteye.com/blog/573303 

http://www.cnblogs.com/advocate/archive/2010/09/03/1816628.html

三：对单个大表的优化，百万以上：

oracle 10g 以上 也提供了 全文检索，和 lucene相似 。

见：

  http://www.iteye.com/topic/1118055 

http://yangtingkun.itpub.net/post/468/246823  这个里面有详细Oracle 的全文检索，非常好，对单个大表

百万以上，很有用 。