SQL> select count(*) from t;

COUNT(*)
----------
51838

SQL> insert /*+append*/ into t select * from t;

已创建51838行。

SQL> commit;

提交完成。

SQL> insert /*+append*/ into t select * from t;

已创建103676行。

SQL> commit;

提交完成。

SQL> insert /*+append*/ into t select * from t;

已创建207352行。

SQL> commit;

提交完成。

SQL> insert /*+append*/ into t select * from t;

已创建414704行。

SQL> commit;

提交完成。

SQL> select count(*) from t;

COUNT(*)
----------
829408

SQL> set autot traceonly exp stat
SQL> select * from t where object_type like '%TYPE%';

已选择34064行。

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

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      | 2592 |   235K|   160   (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T    | 2592 |   235K|   160   (2)| 00:00:02 |
--------------------------------------------------------------------------

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

1 - filter("OBJECT_TYPE" LIKE '%TYPE%')

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      13623 consistent gets
      11364 physical reads
          0 redo size
    1466572 bytes sent via SQL*Net to client
      25370 bytes received via SQL*Net from client
       2272 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
      34064 rows processed

SQL> SQL> select /*+index(t,t_idx)*/ * from t where object_type like '%TYPE%';

已选择34064行。

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

-------------------------------------------------------------------------------------
| Id | Operation                   | Name | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 2592 |   235K|   260   (2)| 00:00:04 |
|   1 | TABLE ACCESS BY INDEX ROWID| T     | 2592 |   235K|   260   (2)| 00:00:04 |
|* 2 |   INDEX FULL SCAN           | T_IDX | 2592 |       |   146   (2)| 00:00:02 |
-------------------------------------------------------------------------------------

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

2 - filter("OBJECT_TYPE" LIKE '%TYPE%')

统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
      14131 consistent gets
       1982 physical reads
     290188 redo size
    1477872 bytes sent via SQL*Net to client
      25370 bytes received via SQL*Net from client
       2272 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
      34064 rows processed

SQL> SQL>
SQL>
SQL>
SQL> select * from t where instr(object_type,'TYPE')>0;

已选择34064行。

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

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      | 2592 |   235K|   161   (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T    | 2592 |   235K|   161   (3)| 00:00:02 |
--------------------------------------------------------------------------

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

1 - filter(INSTR("OBJECT_TYPE",'TYPE')>0)

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      13623 consistent gets
       9952 physical reads
          0 redo size
    1466572 bytes sent via SQL*Net to client
      25370 bytes received via SQL*Net from client
       2272 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
      34064 rows processed

SQL> SQL> SELECT * from t where rowid in(select /*+index_ffs(t,t_idx)*/ rowid from t where object_type like '%TYPE%');

已选择34064行。

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

-------------------------------------------------------------------------------
| Id | Operation             | Name | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       | 2592 |   288K|   197   (4)| 00:00:03 |
|* 1 | HASH JOIN            |       | 2592 |   288K|   197   (4)| 00:00:03 |
|* 2 |   INDEX FAST FULL SCAN| T_IDX | 2592 | 54432 |    35   (6)| 00:00:01 |
|   3 |   TABLE ACCESS FULL   | T     | 51838 | 4707K|   161   (3)| 00:00:02 |
-------------------------------------------------------------------------------

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

1 - access(ROWID=ROWID)
2 - filter("OBJECT_TYPE" LIKE '%TYPE%')

统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
      17559 consistent gets
       9991 physical reads
          0 redo size
    1466572 bytes sent via SQL*Net to client
      25370 bytes received via SQL*Net from client
       2272 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
      34064 rows processed

小结：

1，目前看instr比rowid及全表报描的性能更高及index_ffs更高

2,index_ffs虽强制走了索引，但成本最高

补一个精华sql like优化帖子

http://www.itpub.net/thread-375999-1-1.html

ITPub博客

oracle sql like优化（一）

最新文章