oracle中like优化的方法

最新推荐文章于 2025-05-21 16:27:40 发布

转载最新推荐文章于 2025-05-21 16:27:40 发布 · 2.6k 阅读

文章标签：

#PLSQL #Oracle #SQL #索引

SQL优化专栏收录该内容

19 篇文章

订阅专栏

本文介绍了一种在Oracle数据库中优化含有前后模糊匹配条件的LIKE查询的方法。通过将大表变小并利用索引，有效降低了I/O成本，提高了查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在oracle中对于like操作时,如果是前后都是模糊查询的时候(类似于col01 like ‘%xxx%’)是没有办法用到索引的,这里提供一种对于这种情况的优化思路,主要的思路是把大表变小,把查询的实体表变窄,把需要的数据放到索引里.

常规的写法:

create table t01 as select * from dba_objects;

SQL> createindext01_01
ont01(object_name);

Indexcreated.

SQL> setautot trace

SQL> select*
fromt01
whereobject_name
like'%EMP%';

176 rowsselected.

Execution Plan 

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

Plan hash value: 3295674804 

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

| Id  | Operation         | Name|
Rows | Bytes | Cost (%CPU)|
Time    |

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

|   0 | SELECTSTATEMENT  |      |  7091 |   775K|   522   (1)| 00:00:07 |

|*  1 |  TABLEACCESS
FULL| T01  |  7091 |   775K|   522   (1)| 00:00:07 |

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

Predicate Information (identified 
byoperation id):

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

   1 - filter("OBJECT_NAME"LIKE
'%EMP%')

Statistics

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

          1  recursive calls

          0  db block gets

       2335  consistent gets

          0  physical reads

          0  redosize

      14552  bytes sent via SQL*Nettoclient

        608  bytes received via SQL*Netfromclient

         13  SQL*Net roundtripsto/fromclient

          0  sorts (memory)

          0  sorts (disk)

        176 rowsprocessed

优化后的写法:

SQL> select/*+ use_nl(t01 v01) */*

fromt01, (selectrowid
fromt01
whereobject_name
like'%EMP%') v01

wheret01.rowid = v01.rowid;

176 rowsselected.

Execution Plan 

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

Plan hash value: 73453348 

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

| Id  | Operation                   |Name  |
Rows | Bytes | Cost (%CPU)|
Time    |

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

|   0 | SELECTSTATEMENT            |        |  7091 |  1017K|  7241   (1)| 00:01:27 |

|   1 |  NESTED LOOPS               |        |  7091 |  1017K|  7241   (1)| 00:01:27 |

|*  2 |   INDEXFAST
FULLSCAN      | T01_01 |  7091 |   242K|   147   (2)| 00:00:02 |

|   3 |   TABLEACCESS
BYUSER
ROWID| T01    |     1 |   112 |     1   (0)| 00:00:01 | 

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

Predicate Information (identified 
byoperation id):

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

   2 - filter("OBJECT_NAME"LIKE
'%EMP%')

Statistics

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

          1  recursive calls

          0  db block gets

        857  consistent gets

          0  physical reads

          0  redosize

      18049  bytes sent via SQL*Nettoclient

        608  bytes received via SQL*Netfromclient

         13  SQL*Net roundtripsto/fromclient

          0  sorts (memory)

          0  sorts (disk)

        176 rowsprocessed