逆置与Like匹配
时间:2014-12-08 14:26 来源:linux.it.net.cn 作者:IT
Like匹配是SQL语句的一种重要功能。Like结合通配符使用可以实现模糊查询，解决字符串匹配问题。当查询的数据集合比较大的时候，我们常常希望借助索引来加快检索效率。
默认的索引行为
like查询是否会执行索引路径，很大程度上取决于使用的通配符结构。如果我们仔细分析下B*树索引的结构，就可以判断出：‘XXX%’类型的匹配结构，是可以执行索引搜索的。
通过下面实验证明：
//构造原始数据集合
SQL> create table t as select * from dba_objects;
Table created
//在owner列上建索引
SQL> create index idx_t_owner on t(owner);
Index created
//收集统计信息
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
此时，我们使用通配符进行查询。
SQL> select * from t where owner like 'PE%';
已选择139行。
已用时间: 00: 00: 00.06
执行计划
----------------------------------------------------------
Plan hash value: 1516787156
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2231 | 204K| 67 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 2231 | 204K| 67 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 2231 | | 7 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER" LIKE 'PE%')
filter("OWNER" LIKE 'PE%')
统计信息
----------------------------------------------------------
381 recursive calls
0 db block gets
82 consistent gets
0 physical reads
0 redo size
16321 bytes sent via SQL*Net to client
484 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
139 rows processed
发现，虽然使用了like，但是还是执行了索引路径。
结论：在匹配字段通配符不是在开头的情况下，like走索引列的效率比较高，往往优化器会选择这条路径。
通配符在开头
在一些情况下，我们需要进行通配符在开头的检索。这种情况下，使用一般的索引一般是不会被选择为实际执行路径的。
SQL> select * from t where owner like '%AT';
已选择139行。
已用时间: 00: 00: 00.12
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2666 | 255K| 165 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 2666 | 255K| 165 (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER" LIKE '%AT')
统计信息
----------------------------------------------------------
838 recursive calls
0 db block gets
912 consistent gets
1 physical reads
0 redo size
10424 bytes sent via SQL*Net to client
484 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
24 sorts (memory)
0 sorts (disk)
139 rows processed
Oracle优化器选择了全表扫描作为执行路径。应该说，在索引和全表扫描两种方式下，Oracle选择了全表扫描，原因是因为索引路径的成本Cost更高。
思路：如果匹配的字符串前面不存在通配符，走索引的路径成本相对较低了。那么，怎么将其转化到后面呢？使用逆置的函数索引。
函数索引的原理，就是利用函数处理出一个隐藏列，再对隐藏列进行索引处理。
SQL> create index idx_t_fun on t(reverse(owner));
Index created
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
同时，在搜索的时候，要将匹配的字符串也逆置处理。例如，如果要处理匹配的字符串是’%AT’。那么进行函数匹配的时候，就要使用‘TA%’。
SQL> select * from t where reverse(owner) like reverse('%AT');
已选择139行。
已用时间: 00: 00: 00.04
执行计划
----------------------------------------------------------
Plan hash value: 3350694433
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2132 | 204K| 64 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 2132 | 204K| 64 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_FUN | 2132 | | 7 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(REVERSE("OWNER") LIKE 'TA%')
filter(REVERSE("OWNER") LIKE 'TA%')
统计信息
----------------------------------------------------------
117 recursive calls
0 db block gets
52 consistent gets
0 physical reads
0 redo size
10424 bytes sent via SQL*Net to client
484 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
139 rows processed
修改之后，发现执行路径已经可以走索引路径了。而且执行时间也缩小到原来全表扫描的1/3。
(责任编辑：IT)
| Like匹配是SQL语句的一种重要功能。Like结合通配符使用可以实现模糊查询，解决字符串匹配问题。当查询的数据集合比较大的时候，我们常常希望借助索引来加快检索效率。 默认的索引行为 like查询是否会执行索引路径，很大程度上取决于使用的通配符结构。如果我们仔细分析下B*树索引的结构，就可以判断出：‘XXX%’类型的匹配结构，是可以执行索引搜索的。 通过下面实验证明： //构造原始数据集合 SQL> create table t as select * from dba_objects; Table created //在owner列上建索引 SQL> create index idx_t_owner on t(owner); Index created //收集统计信息 SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true); PL/SQL procedure successfully completed 此时，我们使用通配符进行查询。 SQL> select * from t where owner like 'PE%'; 已选择139行。 已用时间: 00: 00: 00.06 执行计划 ---------------------------------------------------------- Plan hash value: 1516787156 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2231 | 204K| 67 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 2231 | 204K| 67 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 2231 | | 7 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OWNER" LIKE 'PE%') filter("OWNER" LIKE 'PE%') 统计信息 ---------------------------------------------------------- 381 recursive calls 0 db block gets 82 consistent gets 0 physical reads 0 redo size 16321 bytes sent via SQL*Net to client 484 bytes received via SQL*Net from client 11 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 139 rows processed 发现，虽然使用了like，但是还是执行了索引路径。 结论：在匹配字段通配符不是在开头的情况下，like走索引列的效率比较高，往往优化器会选择这条路径。 通配符在开头 在一些情况下，我们需要进行通配符在开头的检索。这种情况下，使用一般的索引一般是不会被选择为实际执行路径的。 SQL> select * from t where owner like '%AT'; 已选择139行。 已用时间: 00: 00: 00.12 执行计划 ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2666 | 255K| 165 (2)| 00:00:02 | |* 1 | TABLE ACCESS FULL| T | 2666 | 255K| 165 (2)| 00:00:02 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OWNER" LIKE '%AT') 统计信息 ---------------------------------------------------------- 838 recursive calls 0 db block gets 912 consistent gets 1 physical reads 0 redo size 10424 bytes sent via SQL*Net to client 484 bytes received via SQL*Net from client 11 SQL*Net roundtrips to/from client 24 sorts (memory) 0 sorts (disk) 139 rows processed Oracle优化器选择了全表扫描作为执行路径。应该说，在索引和全表扫描两种方式下，Oracle选择了全表扫描，原因是因为索引路径的成本Cost更高。 思路：如果匹配的字符串前面不存在通配符，走索引的路径成本相对较低了。那么，怎么将其转化到后面呢？使用逆置的函数索引。 函数索引的原理，就是利用函数处理出一个隐藏列，再对隐藏列进行索引处理。 SQL> create index idx_t_fun on t(reverse(owner)); Index created SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true); PL/SQL procedure successfully completed 同时，在搜索的时候，要将匹配的字符串也逆置处理。例如，如果要处理匹配的字符串是’%AT’。那么进行函数匹配的时候，就要使用‘TA%’。 SQL> select * from t where reverse(owner) like reverse('%AT'); 已选择139行。 已用时间: 00: 00: 00.04 执行计划 ---------------------------------------------------------- Plan hash value: 3350694433 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2132 | 204K| 64 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 2132 | 204K| 64 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_T_FUN | 2132 | | 7 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(REVERSE("OWNER") LIKE 'TA%') filter(REVERSE("OWNER") LIKE 'TA%') 统计信息 ---------------------------------------------------------- 117 recursive calls 0 db block gets 52 consistent gets 0 physical reads 0 redo size 10424 bytes sent via SQL*Net to client 484 bytes received via SQL*Net from client 11 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 139 rows processed 修改之后，发现执行路径已经可以走索引路径了。而且执行时间也缩小到原来全表扫描的1/3。 (责任编辑：IT) |