oracle sql like優化（一）

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優化帖子