Oracle CBO评估like的数据量
来源:互联网 发布:js字符串不等于 编辑:程序博客网 时间:2024/05/14 17:01
对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.35SQL> 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%')
- Oracle CBO评估like的数据量
- Oracle的RBO和CBO
- Oracle的RBO和CBO
- Oracle的RBO和CBO
- Oracle的RBO和CBO
- Oracle的RBO和CBO
- 详介oracle的RBO/CBO优化器
- Oracle的RBO和CBO优化
- 详介oracle的RBO/CBO优化器
- 详介oracle的RBO/CBO优化器
- Oracle的RBO/CBO优化器
- ORACLE的CBO及表分析
- 详介oracle的RBO/CBO优化器
- 详介oracle的RBO/CBO优化器
- oracle CBO 的可传递性
- 【Oracle】CBO版本带来的视图INVALID
- 数据量大的操作注意:SHOW VARIABLES like 'max_allowed_packet'
- oracle CBO 优化
- 设计模式简介
- IIS7.5 URL文件名有加号或空格显示404错误的解决办法
- opencv2.3绘制抛物线
- java 实例教程
- elf文件分析-程序员的自我修养第三章学习
- Oracle CBO评估like的数据量
- [bzoj1070][SCOI2007]修车
- aes加密算法java代码实现
- 杭电acm 1863 畅通工程
- 使用类前置声明的好处-结合Qt 4一个主窗口实例讲解
- birt报表开发
- 双向链表的实现
- JDBC: 处理事务 & 事务的隔离级别
- UVA 572- Oil Deposits(简单dfs)