Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › select 查询使用instr函数要比使用like匹配快很多，是神 ...
发新帖
ShineCQY

84

积分

1

好友

27

主题

select 查询使用instr函数要比使用like匹配快很多，是神话吗？

1#
发表于 2012-8-14 11:12:17 | 查看: 6775| 回复: 4
最近在做SQL优化，在网上看到有网友说“使用instr函数要比使用like匹配快很多“,持着怀疑的态度测试了一下。
测试结果：使用instr就是比like匹配快，虽然like走的是索引，而我并没有建立基于instr函数的索引。
测试过程：
1、表dm_accntab  信息
SQL> select count(*) from dm_accntab;

  COUNT(*)
----------
    828128
SQL> select status,LAST_ANALYZED from user_tables where table_name='DM_ACCNTAB';

STATUS   LAST_ANALYZED
-------- -------------
VALID    8/13/2012 10:
SQL> select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name=i.index_name and t.table_name=i.table_name and t.table_name='DM_ACCNTAB';

INDEX_NAME                     TABLE_NAME                     COLUMN_NAME                                                                      COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND INDEX_TYPE
------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------------- ------------- ----------- ------- ---------------------------
IDX_DM_ACCNTAB                 DM_ACCNTAB                     ACCT_CODE                                                                                      1            64          64 ASC     NORMAL
IDX_DM_ACCNTAB                 DM_ACCNTAB                     DATA_DATE                                                                                      2            10          10 ASC     NORMAL
2、对比
select acct_code,data_date from dm_accntab where acct_code like '450231%';
5.265 seconds
SQL> select acct_code,data_date from dm_accntab where instr(acct_code,'450231')=1;
4.172 seconds
3、查看执行计划
SQL> explain plan for select acct_code,data_date from dm_accntab where acct_code like '450231%';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3997140732
--------------------------------------------------------------------------------
| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |    67 |  1541 |     3   (0)| 00:00:0
|*  1 |  INDEX RANGE SCAN| IDX_DM_ACCNTAB |    67 |  1541 |     3   (0)| 00:00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ACCT_CODE" LIKE '450231%')
       filter("ACCT_CODE" LIKE '450231%')

14 rows selected
SQL> explain plan for select acct_code,data_date from dm_accntab where instr(acct_code,'450231')=1;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3646046415
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |  8290 |   186K|  4791   (1)| 00:00:58 |
|*  1 |  TABLE ACCESS FULL| DM_ACCNTAB |  8290 |   186K|  4791   (1)| 00:00:58 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(INSTR("ACCT_CODE",'450231')=1)

13 rows selected

我想请教下刘大，这真的是神话吗？ 如果是，那以后见到Like就把它替换成instr ...
5#
发表于 2012-8-14 21:46:16
恩，在走索引方面性能几乎一样（instr稍占优势），但如果都没有索引，我觉得还是instr函数性能比较好，个人见解。 oracle在淡化这方面的区别，就好像我在网上看到网友说 在嵌套子查询中使用exists效果要比in 好，但我测试了下(oracle版本 11.1.0.7 )，几乎一样，没有什么区别。

[ 本帖最后由 ShineCQY 于 2012-8-14 21:49 编辑 ]

回复 只看该作者 道具 举报

4#
发表于 2012-8-14 20:01:57
  1. SQL> create table maclean tablespace users as select * from dba_objects;

  3. Table created.

  5. SQL> select object_name from maclean where object_name like 'MACLEAN%';

  7. OBJECT_NAME
  8. --------------------------------------------------------------------------------
  9. MACLEAN1
  10. MACLEAN
  11. MACLEAN


  14. SQL> create index instr_index on maclean ( instr(object_name,'MACLEAN'));

  16. Index created.

  18. SQL> create index like_index on maclean(object_name);

  20. Index created.

  22. SQL> exec dbms_stats.gather_table_stats('','MACLEAN',cascade=>true);

  24. PL/SQL procedure successfully completed.

  26. SQL>
  27. SQL>
  28. SQL>
  29. SQL>
  30. SQL> set timing on;
  31. SQL> set autotrace on;


  34. SQL> select object_name from maclean where object_name like 'MACLEAN%';

  36. OBJECT_NAME
  37. --------------------------------------------------------------------------------------------------------------------------------
  38. MACLEAN
  39. MACLEAN
  40. MACLEAN1

  42. Elapsed: 00:00:00.00

  44. Execution Plan
  45. ----------------------------------------------------------
  46. Plan hash value: 972104630

  48. -------------------------------------------------------------------------------
  49. | Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
  50. -------------------------------------------------------------------------------
  51. |   0 | SELECT STATEMENT |            |     2 |    50 |     3   (0)| 00:00:01 |
  52. |*  1 |  INDEX RANGE SCAN| LIKE_INDEX |     2 |    50 |     3   (0)| 00:00:01 |
  53. -------------------------------------------------------------------------------

  55. Predicate Information (identified by operation id):
  56. ---------------------------------------------------

  58.    1 - access("OBJECT_NAME" LIKE 'MACLEAN%')
  59.        filter("OBJECT_NAME" LIKE 'MACLEAN%')


  62. Statistics
  63. ----------------------------------------------------------
  64.           0  recursive calls
  65.           0  db block gets
  66.           4  consistent gets
  67.           0  physical reads
  68.           0  redo size
  69.         607  bytes sent via SQL*Net to client
  70.         524  bytes received via SQL*Net from client
  71.           2  SQL*Net roundtrips to/from client
  72.           0  sorts (memory)
  73.           0  sorts (disk)
  74.           3  rows processed
  75.                   
  76.                   
  77.   1* select /*+ index (maclean instr_index) */  object_name from maclean where instr(object_name,'MACLEAN')=1
  78. SQL>
  79. SQL> /

  81. OBJECT_NAME
  82. --------------------------------------------------------------------------------------------------------------------------------
  83. MACLEAN1
  84. MACLEAN
  85. MACLEAN

  87. Elapsed: 00:00:00.00

  89. Execution Plan
  90. ----------------------------------------------------------
  91. Plan hash value: 474644752

  93. -------------------------------------------------------------------------------------------
  94. | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
  95. -------------------------------------------------------------------------------------------
  96. |   0 | SELECT STATEMENT            |             | 25425 |   695K|   412   (1)| 00:00:01 |
  97. |   1 |  TABLE ACCESS BY INDEX ROWID| MACLEAN     | 25425 |   695K|   412   (1)| 00:00:01 |
  98. |*  2 |   INDEX RANGE SCAN          | INSTR_INDEX | 25425 |       |    47   (0)| 00:00:01 |
  99. -------------------------------------------------------------------------------------------

  101. Predicate Information (identified by operation id):
  102. ---------------------------------------------------

  104.    2 - access(INSTR("OBJECT_NAME",'MACLEAN')=1)


  107. Statistics
  108. ----------------------------------------------------------
  109.           0  recursive calls
  110.           0  db block gets
  111.           6  consistent gets
  112.           0  physical reads
  113.           0  redo size
  114.         599  bytes sent via SQL*Net to client
  115.         524  bytes received via SQL*Net from client
  116.           2  SQL*Net roundtrips to/from client
  117.           0  sorts (memory)
  118.           0  sorts (disk)
  119.           3  rows processed                 
复制代码
不清楚，这是否和你想表达的演示意义一致，但就以上测试看， instr并没有获得更好的效果，而且请注意 instr用了函数索引

回复 只看该作者 道具 举报

3#
发表于 2012-8-14 17:59:25

创建基于函数的索引

SQL> create index instr_idx_dm_accntab on dm_accntab (instr(acct_code,'450231'));

Index created
SQL> select acct_code,data_date from dm_accntab where instr(acct_code,'450231')=1;
3.017 seconds
执行计划
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2985422358
--------------------------------------------------------------------------------
| Id  | Operation                   | Name                 | Rows  | Bytes | Cos
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |  8290 |   291K|   8
|   1 |  TABLE ACCESS BY INDEX ROWID| DM_ACCNTAB           |  8290 |   291K|   8
|*  2 |   INDEX RANGE SCAN          | INSTR_IDX_DM_ACCNTAB |  3316 |       |   7
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(INSTR("ACCT_CODE",'450231')=1)

14 rows selected

回复 只看该作者 道具 举报

2#
发表于 2012-8-14 16:27:54
对比不是关看时间的
  你看你的逻辑读 CPU啊等等

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-11-16 05:36 , Processed in 0.047522 second(s), 22 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569