Oracle CBO评估like的数据量

朴灿烈づ我的快乐病毒、 2022-08-07 01:34 53阅读 0赞

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