用户名:   自动登录 找回密码
密   码:    * 注册




发表新帖 回复这个主题  [ 3 篇帖子 ] 
作者 内容
 文章标题 : Oracle LIKE 什么时候走索引
帖子发表于 : 2012-05-28 23:43 
离线
头像

注册: 2011-05-01 9:15
帖子: 120
SQL> set autotrace traceonly
SQL> select count(*) from awen.ob_bak where owner='AWEN';


执行计划
----------------------------------------------------------
Plan hash value: 3442547862

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 1860 (4)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX RANGE SCAN| OB_INDEX | 774K| 4538K| 1860 (4)| 00:00:03 |
------------------------------------------------------------------------------

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

2 - access("OWNER"='AWEN')


统计信息
----------------------------------------------------------
274 recursive calls
0 db block gets
46 consistent gets
8 physical reads
0 redo size
409 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(*) from awen.ob_bak where owner like '%AWEN%';


执行计划
----------------------------------------------------------
Plan hash value: 961138420

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 47284 (6)| 00:00:58 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX FULL SCAN| OB_INDEX | 968K| 5673K| 47284 (6)| 00:00:58 |
-----------------------------------------------------------------------------

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

2 - filter("OWNER" LIKE '%AWEN%')


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
44622 consistent gets
44636 physical reads
0 redo size
409 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(*) from awen.ob_bak where owner like '%AWE%';


执行计划
----------------------------------------------------------
Plan hash value: 961138420

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 47284 (6)| 00:00:58 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX FULL SCAN| OB_INDEX | 968K| 5673K| 47284 (6)| 00:00:58 |
-----------------------------------------------------------------------------

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

2 - filter("OWNER" LIKE '%AWE%')


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
44622 consistent gets
44614 physical reads
0 redo size
409 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(*) from awen.ob_bak where owner like 'AWE%';


执行计划
----------------------------------------------------------
Plan hash value: 3442547862

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 1860 (4)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX RANGE SCAN| OB_INDEX | 774K| 4538K| 1860 (4)| 00:00:03 |
------------------------------------------------------------------------------

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

2 - access("OWNER" LIKE 'AWE%')
filter("OWNER" LIKE 'AWE%')


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
409 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(*) from awen.ob_bak where owner like 'AWEN';


执行计划
----------------------------------------------------------
Plan hash value: 3442547862

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 1860 (4)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX RANGE SCAN| OB_INDEX | 774K| 4538K| 1860 (4)| 00:00:03 |
------------------------------------------------------------------------------

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

2 - access("OWNER" LIKE 'AWEN')


统计信息
----------------------------------------------------------
149 recursive calls
0 db block gets
30 consistent gets
5 physical reads
0 redo size
409 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed


like 'AWEN' 走索引
like 'AWE%' 走索引
like '%AWE' 不走
like '%AWE%' 不走


页首
 用户资料  
 
 文章标题 : Re: Oracle LIKE 什么时候走索引
帖子发表于 : 2016-01-26 17:30 
离线
头像

注册: 2016-01-26 15:12
帖子: 2
You made me a different perspective on this issue.

_________________
ibcbet


页首
 用户资料  
 
 文章标题 : Re: Oracle LIKE 什么时候走索引
帖子发表于 : 2018-04-26 13:20 
离线
头像

注册: 2018-04-26 10:24
帖子: 2
我想知道它有多少索引。 多少钱? 你能告诉我吗?


页首
 用户资料  
 
显示帖子 :  排序  
发表新帖 回复这个主题  [ 3 篇帖子 ] 


在线用户

注册用户: 没有注册用户


查找:
前往 :  
cron
Powered by OraSql © 2011, 2012, oracle_awen