Oracle CBO evaluates the like data volume, and oraclecbo evaluates like

Oracle CBO evaluates the like data volume, and oraclecbo evaluates like

There are two forms of Like Statement, which is evaluated by 5% of the total data volume.

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 % ';
Execution Plan
----------------------------------------------------------
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 ';
Execution Plan
----------------------------------------------------------
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)

-- If it is written after the percentage sign, it cannot be written according to the 5%

SQL> select * from test where object_name like 'test % ';
Execution Plan
----------------------------------------------------------
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 % ';
Execution Plan
----------------------------------------------------------
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 % ')