反向索引與模糊查詢

安佰勝發表於2011-05-06

反向索引與模糊查詢
---------------------

版本

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

測試資料
SQL> conn an/an
已連線。

SQL> create table atest(id int,name varchar2(100));
表已建立。

SQL> insert into atest select 1,'anbaisheng' from dual;
已建立 1 行。

SQL> insert into atest select 2,'xiangxiang' from dual;
已建立 1 行。

SQL> insert into atest select 3,'baby' from dual;
已建立 1 行。

SQL> commit;
提交完成。

SQL> select * from atest;
        ID NAME
---------- --------------------
         1 anbaisheng
         2 xiangxiang
         3 baby

建立正續索引
SQL> create index ind_name on atest(name);

索引已建立。

SQL> set autotrace on
SQL> select * from atest where name like 'a%';

        ID NAME
---------- --------------------
         1 anbaisheng

--------------------------------------------------------------------------------
| Id | Operation                   | Name     | Rows | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    65 |     2   (0)| 00
|   1 | TABLE ACCESS BY INDEX ROWID| ATEST    |     1 |    65 |     2   (0)| 00
|* 2 |   INDEX RANGE SCAN          | IND_NAME |     1 |       |     1   (0)| 00
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("NAME" LIKE 'a%')
       filter("NAME" LIKE 'a%')

SQL> select * from atest where name like '%y';

        ID NAME
---------- --------------------
         3 baby

---------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |    65 |     3   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| ATEST |     1 |    65 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("NAME" LIKE '%y')

正向索引後模糊查詢有效，前模糊無效

建立反向索引
SQL> drop index ind_name;

索引已刪除。

SQL> create index ind_name on atest reverse(name);

索引已建立。

SQL> select * from atest where name like '%y';

        ID NAME
---------- --------------------
         3 baby

SQL> select * from atest where name like reverse ('%y');

未選定行

--------------------------------------------------------------------------------
| Id | Operation                   | Name     | Rows | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    65 |     0   (0)| 00
|   1 | TABLE ACCESS BY INDEX ROWID| ATEST    |     1 |    65 |     0   (0)| 00
|* 2 |   INDEX RANGE SCAN          | IND_NAME |     1 |       |     0   (0)| 00
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("NAME" LIKE 'y%')
       filter("NAME" LIKE 'y%')

就算建上反向索引，不使用reverse關鍵字前模糊也沒用，後模糊無效

聯合部分反向索引
這個conception裡都沒說，自己試了下，還成
SQL> create index ind_idname on atest (id ,reverse(name));

索引已建立。

SQL> select * from atest where id=1 and name like reverse ('%y');

未選定行

--------------------------------------------------------------------------------
| Id | Operation                   | Name     | Rows | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    65 |     0   (0)| 00
|* 1 | TABLE ACCESS BY INDEX ROWID| ATEST    |     1 |    65 |     0   (0)| 00
|* 2 |   INDEX RANGE SCAN          | IND_NAME |     1 |       |     0   (0)| 00
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=1)
   2 - access("NAME" LIKE 'y%')
       filter("NAME" LIKE 'y%')

SQL> select * from atest where id=1 and name like '%y';

未選定行

--------------------------------------------------------------------------------
| Id | Operation                   | Name       | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    65 |     2   (0)|
|* 1 | TABLE ACCESS BY INDEX ROWID| ATEST      |     1 |    65 |     2   (0)|
|* 2 |   INDEX RANGE SCAN          | IND_IDNAME |     1 |       |     1   (0)|
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("NAME" LIKE '%y')
   2 - access("ID"=1)

就算是聯合索引，不使用reverse的話意義也不大

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/13177610/viewspace-694601/，如需轉載，請註明出處，否則將追究法律責任。

【索引】反向索引--條件範圍查詢
2010-09-07
索引
【索引】反向索引--條件範圍查詢（二）
2010-09-07
索引
【最佳化】模糊查詢索引問題
2018-03-29
索引
拋棄mysql模糊查詢，使用sphinx做專業索引
2020-09-17
MySql索引
MongoDB慢查詢與索引
2022-07-16
MongoDB索引
oracle 精確查詢和模糊查詢
2019-11-19
Oracle
pgsql查詢優化之模糊查詢
2019-07-20
SQL優化
elasticsearch的模糊查詢
2019-01-04
Elasticsearch
MyBatis模糊查詢LIKE
2018-07-10
MyBatis
sql日期模糊查詢
2009-10-22
SQL
MySQL索引與查詢優化
2019-04-13
MySql索引優化
Java ——MongDB 插入資料、模糊查詢、in查詢
2018-08-10
Java
【索引】Oracle查詢指定索引提高查詢效率
2012-04-25
索引Oracle
sql 模糊查詢問題
2021-05-31
SQL
mybatis做like模糊查詢
2016-10-14
MyBatis
Mybatis各種模糊查詢
2013-08-01
MyBatis
模型欄位與序列化正反向查詢
2020-10-12
模型
js精準查詢與模糊查詢,總有一種適合你的需求
2020-04-03
JS
int 被當作模糊查詢
2021-08-26
LINQ中的模糊查詢
2011-08-29
Mysql高效的模糊查詢(轉)
2007-08-12
MySql
報表怎麼做模糊查詢
2019-11-21
【PostgreSQL】字首模糊查詢級優化
2019-07-08
SQL優化
mybatis-模糊查詢like CONCAT
2018-10-18
MyBatis
Mybatis中Oracle的拼接模糊查詢
2018-08-07
MyBatisOracle
Mybatis模糊查詢結果為空
2020-11-14
MyBatis
mybatis 對特殊字元的模糊查詢
2020-11-05
MyBatis字元
PostgreSQL DBA(192) - 整行模糊查詢
2022-11-01
SQL
模糊查詢區分大小寫嗎？
2022-05-23
Peewee Sqlite3 中文模糊查詢
2020-10-15
SQLite
Oracle特殊符號的模糊查詢
2013-08-26
Oracle符號
儲存過程模糊查詢(like)
2009-07-14
儲存過程
python 當中的模糊查詢
2024-03-21
Python
MySQL第六篇：索引與子查詢
2017-10-28
MySql索引
elasticsearch之多索引查詢
2021-12-31
Elasticsearch索引
Elasticsearch（三）：索引查詢
2020-10-21
Elasticsearch索引
查詢索引常用SQL
2015-04-24
索引SQL
查詢相似的索引
2008-02-03
索引

反向索引與模糊查詢

相關文章