OracleCBO evaluates like data volume

OracleCBO evaluates like data volume

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) ---------- 79747SQL> select count (1) from test where object_name like 'test % '; COUNT (1) ---------- 0SQL> select count (1) from test where object_name like '% test %'; COUNT (1) ---------- 12SQL> select count (1) from test where object_name like '% test'; COUNT (1) ---------- 2SQL> set autotrace trace exp -- 79747*0.05 = 3987.35SQL> select * from test where object_name like '% test %'; Execution Plan ------------------------------------------plan hash value: 1357081020 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 3987 | 389K | 224 (1) | 00:00:04 | * 1 | table access full | TEST | 3987 | 389K | 224 (1) | 00:00:04 | identified Predicate Information (identified by operation id ): ------------------------------------------------- 1-filter ("OBJECT_NAME" LIKE '% test %' AND "OBJECT_NAME" is not null) -- 79747*0.05 = 3987.35SQL> select * from test where object_name like '% test'; Execution Plan ------------------------------------------------------ Plan hash value: 1357081020 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 3987 | 389K | 224 (1) | 00:00:04 | * 1 | table access full | TEST | 3987 | 389K | 224 (1) | 00:00:04 | identified Predicate Information (identified by operation id ): ------------------------------------------------- 1-filter ("OBJECT_NAME" LIKE '% test' AND "OBJECT_NAME" is not null) -- if the percent sign IS written behind, you cannot select * from test where object_name like 'test % 'According to SQL 5%; Execution Plan ------------------------------------------------------ Plan hash value: 1357081020 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 2 | 200 | 224 (1) | 00:00:04 | * 1 | table access full | TEST | 2 | 200 | 224 (1) | 00:00:04 | identified Predicate Information (identified by operation id ): ----------------------------------------------- 1-filter ("OBJECT_NAME" LIKE 'test % ') SQL> select * from test where object_name like't %'; Execution Plan hash value: 1357081020 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1079 | 105K | 224 (1) | 00:00:04 | * 1 | table access full | TEST | 1079 | 105K | 224 (1) | 00:00:04 | identified Predicate Information (identified by operation id ): ------------------------------------------------- 1-filter ("OBJECT_NAME" LIKE't % ')