@dbsnake-用合适的函数索引来避免看似无法避免的全表扫描
@dbsnake-用合适的函数索引来避免看似无法避免的全表扫描
昨天听了@dbsnake的SQL方易做图,感觉比第一次要更有感觉,希望对实际工作能有帮助。
昨天讲到一处利用reverse函数建立索引,避免全表扫描的case,颇有感触,拿出来试一下。
SQL> create table rev (id number, name varchar2(5)); Table created. SQL> select * from rev; ID NAME ---------- ----- 1 abc 2 bc 3 c SQL> create index rev_idx0 on rev(name); Index created. SQL> set autot on SQL> select id, name from rev where name like '%bc'; ID NAME ---------- ----- 1 abc 2 bc Execution Plan ---------------------------------------------------------- Plan hash value: 3205185662 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 34 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| REV | 2 | 34 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("NAME" LIKE '%bc') Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 16 consistent gets 0 physical reads 0 redo size 633 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed
这里建立了name的B树索引,但由于使用了%bc为条件,所以不会用索引,这里用了全表扫描。
如何能让%bc条件使用索引呢?这里讲到%bc不能用索引的原因是因为索引键值按照索引二进制的顺序排序,%在前就无法精确定位,因此无法使用索引。既然%在后面可以使用索引,那就想办法将%的条件放在后面组织。
SQL> create index rev_idx on rev(reverse(name)); Index created. SQL> select id, name from rev where reverse(name) like reverse('%bc'); ID NAME ---------- ----- 2 bc 1 abc Execution Plan ---------------------------------------------------------- Plan hash value: 2418054352 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 34 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| REV | 2 | 34 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | REV_IDX | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(REVERSE("NAME") LIKE 'cb%') filter(REVERSE("NAME") LIKE 'cb%') Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 28 recursive calls 0 db block gets 14 consistent gets 0 physical reads 0 redo size 633 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed
这里用了reverse函数,相当于方向匹配字符串bc,这样就将前面的%放到了后面。从执行计划看cost从3变为2。
注:
这里可以看到无论哪次执行,物理读都是0,原因我觉得就是第一次执行过一个select * from rev;,因为数据量比较小,第一次select之后,记录就从data file缓存到buffer cache,即使根据LRU算法,负载不是太大的DB,很快的时间内这些数据还可能在其中,没被age out,所以再次执行SQL时,就可能物理读是0。
总结:
以上的示例就是@dbsnake讲的“用合适的函数索引来避免看似无法避免的全表扫描“。