SQL> create table t as select * from dba_objects;
表已创建。
SQL> create index ind_t_object_name on t(object_name);
索引已创建。
SQL> select count(0) from t;
COUNT(0)
----------
72168
SQL> select * from t where object_name='GV_$RESULT_CACHE_MEMORY';
执行计划
----------------------------------------------------------
Plan hash value: 3840353680
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OBJECT_NAME | 1 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
SQL> select * from t where object_name like 'GV_$R%';
已选择38行。
执行计划
----------------------------------------------------------
Plan hash value: 3840353680
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 38 | 7866 | 23 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 38 | 7866 | 23 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OBJECT_NAME | 38 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
这个也走索引,但是当字符小于4个是,走全表了。
SQL> select * from t where object_name like 'GV_%';
已选择973行。
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 973 | 196K| 289 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 973 | 196K| 289 (1)| 00:00:04 |
--------------------------------------------------------------------------
SQL> select * from t where object_name like '%GV_$RESULT_CACHE_MEMORY';
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 2484 | 289 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 12 | 2484 | 289 (1)| 00:00:04 |
--------------------------------------------------------------------------
SQL> create index ind_reverse_t_object_name on t(reverse(object_name));
索引已创建。
SQL> select * from t where reverse(object_name) like reverse('%GV_$RESULT_CACHE_MEMORY');
执行计划
----------------------------------------------------------
Plan hash value: 3013310751
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3625 | 966K| 286 (0)| 00:00:04 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 3625 | 966K| 286 (0)| 00:00:04 |
|* 2 | INDEX RANGE SCAN | IND_REVERSE_T_OBJECT_NAME | 652 | | 6 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------