對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%')
