Oracle CBO評估like的資料量，oraclecbo評估like

Oracle CBO評估like的資料量，oraclecbo評估like

   對Like，有兩種形式的寫法是按照資料總量的5%評估。

SQL> drop table test purge;

SQL> create table test as select * from dba_objects;
SQL> exec dbms_stats.gather_table_stats(user,'test');
SQL> select count(1) from test;
  COUNT(1)
----------

     79747

SQL> select count(1) from test where object_name like 'test%';
  COUNT(1)
----------
         0
SQL> select count(1) from test where object_name like '%test%';
  COUNT(1)
----------
        12
SQL> select count(1) from test where object_name like '%test';
  COUNT(1)
----------
         2

SQL> set autotrace trace exp

--79747*0.05=3987.35
SQL> select * from test where object_name like '%test%';
執行計畫
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  3987 |   389K|   224   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| TEST |  3987 |   389K|   224   (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_NAME" LIKE '%test%' AND "OBJECT_NAME" IS NOT NULL)
--79747*0.05=3987.35
SQL> select * from test where object_name like '%test';
執行計畫
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  3987 |   389K|   224   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| TEST |  3987 |   389K|   224   (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_NAME" LIKE '%test' AND "OBJECT_NAME" IS NOT NULL)

--如果是百分比符號寫在後面，不能按照5%的

SQL> select * from test where object_name like 'test%';
執行計畫
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |   200 |   224   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| TEST |     2 |   200 |   224   (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_NAME" LIKE 'test%')

SQL> select * from test where object_name like 't%';
執行計畫
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1079 |   105K|   224   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| TEST |  1079 |   105K|   224   (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_NAME" LIKE 't%')