OracleCBO评估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%')