- 浏览: 92687 次
- 性别:
- 来自: 北京
最新评论
-
flyhaoheng:
在JAVA程序中应该怎么调用?
IP地址、手机归属和身份证查询接口 -
yobuke:
谢谢!
Thinking in UML 读后感 -
yangleilt:
还是不太明白
james邮件服务器配置详解 -
streamfly:
你好,我的openfire用的3.6.4,按照你上篇和这篇帖子 ...
openfire3.6.2集成现有系统用户 -
onhp:
我连的是oracle数据库,怎么设置数据库连接属性?
openfire3.6.2集成现有系统用户
drop table test;
select count(*) from test;
--创建测试表
create table test
(
id number(9),
nick varchar2(30)
);
--插入测试数据
begin
for i in 1..100000 loop
insert into test(id) values(i);
end loop;
commit;
end;
select * from test;
--更新nick字段,使数据发生严重倾斜
update test set nick='abc' where rownum<99999;
--创建索引
create index idx_test_nick on test(nick);
update test set nick='def' where nick is null;
--只对索引进行分析
analyze index idx_test_nick compute statistics;
select * from user_indexes;
--查看索引名,对应存储的数据块,不同的key数量,记录数(行数)的分析信息
select index_name, LEAF_BLOCKS, DISTINCT_KEYS, NUM_ROWS
from user_indexes
where index_name = 'IDX_TEST_NICK';
--dba_tab_col_statistics
--查看表的统计信息
select COLUMN_NAME, NUM_BUCKETS, num_distinct
from USER_tab_columns
where table_name = 'TEST';
select * from test where nick ='abc';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE)
select * from test where nick ='def';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE)
--由上可以看到,对索引分析之后,sql的执行路径都是基于规则的,索引的字段的偏移
--先根据索引找到rowid,然后再根据rowid读取记录,这个过程肯定比全表扫描读取记录要慢
--user_part_col_statistics 分区分析信息
--分析表的第二列nick
analyze table test compute statistics for columns size 2 nick;
select * from test where nick ='abc';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE)
--根据上面的执行计划,还是按照规则来执行的
--分析表
analyze table test compute statistics for table;
select * from test where nick ='abc';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=99998 Bytes=
1499970)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=49 Card=99998 Bytes=14
99970)
--分析表之后,完全按照成本来执行
--删除所有的统计数据,并只对表与列进行分析,不分析索引,
--ORACLE使用CBO的优化器,并产生了正确的执行计划
analyze table test delete statistics;
--分析列nick
analyze table test compute statistics for table for columns size 2 nick;
select * from test where nick ='abc';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=99998 Bytes=
1499970)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=49 Card=99998 Bytes=14
99970)
--
select * from test where nick ='def';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=30)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=2 Byt
es=30)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE) (Cost
=1 Card=2)
--创建TEST表ID列上的索引,但不对索引进行分析
create index idx_test_id on test(id);
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=15
000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1000
Bytes=15000)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_ID' (NON-UNIQUE) (Cost=1
Card=400)
--当条件中即有id,又有nick时,因为nick上有直方图,ORACLE知道nick='abc'的值特别的多,所以不走IDX_TEST_NICK索引,走IDX_TEST_ID上的索引
select * from test where id=5 and nick='abc';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=15
000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1000
Bytes=15000)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_ID' (NON-UNIQUE) (Cost=1
Card=400)
--当条件中即有id,又有nick时,因为nick上有直方图,ORACLE知道nick='def'的值特别的少,所以走IDX_TEST_NICK上的索引,不走IDX_TEST_ID索引
select * from test where id=5 and nick='def';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
es=15)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE) (Cost
=1 Card=2)
select * from test where nick='def' and id=5;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
es=15)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE) (Cost
=1 Card=2)
--在分析ID列后,ORACLE发现ID列的选择度更高,所以不再选择IDX_TEST_NICK索引,而是选择IDX_TEST_ID
analyze table test compute statistics for columns size 1 id;
select * from test where id=5 and nick='def';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=7)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
es=7)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_ID' (NON-UNIQUE) (Cost=1
Card=1)
/*
下面来看另外一种情况,我们删除所有的统计数据,然后在ID列上创建唯一索引,在此条件下,
只分析表与分析列nick,我们看到ORACLE走了正确的执行计划,
走了UK_TEST_ID,其实从这里也给我们带来很多的启示:
在主键与唯一键约束的列上是否需要直方图的问题?
如果在这些列上有像这样的查询where id > 100 and id < 1000,
我们还是需要有直方图的,但除此之外,好像真的没有直方图的必要了!
*/
analyze table test delete statistics;
drop index idx_test_id;
create unique index uk_test_id on test(id);
--分析表的第二列nick
analyze table test compute statistics for table for columns size 2 nick;
select * from test where id=5 and nick='def';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
es=15)
2 1 INDEX (UNIQUE SCAN) OF 'UK_TEST_ID' (UNIQUE) (Cost=1 Car
d=100000)
从以上一系列的实验可以看出,对ORACLE的优化器CBO来说,表的分析与列的分析才是最重要的,索引的分析次之。还有我们可以考虑我们的哪些列上需要直方图,对于bucket的个数问题,oracle的默认值是75个,所以根据你的应用规则,选择合适的桶数对性能也是有帮助的。因为不必要的桶的个数的大量增加,必然会带来SQL语句硬解析时产生执行计划的复杂度问题。
完全计算法: analyze table abc compute statistics;
抽样估算法(抽样20%): analyze table abc estimate statistics sample 20 percent
对表作完全计算所花的时间相当于做全表扫描,抽样估算法由于采用抽样,比完全计算法的生成统计速度要快,假如不是要求要有精确数据的话,尽量采用抽样分析法。建议对表分析采用抽样估算,对索引分析可以采用完全计算。
我们可以采用以下方法,对数据库的表和索引及簇表定期分析生成统计信息,保证应用的正常性能。
发表评论
-
oracle行转列
2014-12-02 16:01 561在写一些复杂的SQL的时候,经常需要使用到行变列的技巧,一些帐 ... -
oracle创建DB_link
2013-09-02 15:10 588create database link TTMS conne ... -
将Oracle中的表构造导出到word
2013-01-08 10:49 820将Oracle中的表结构导出到word 写开发文档时要用到数 ... -
Oracle 10g schedule job的常用操作
2012-05-10 22:05 793-- job 权限 grant cre ... -
oracle数据表闪回技术
2012-04-09 20:25 754第一步: show parameter undo --显示数 ... -
PL_SQL程序打包
2012-04-01 10:53 652PL/SQL 可以让我们把逻辑相关的类型、变量、游标和子程序放 ... -
存储过程异常处理
2012-04-01 10:47 672子程序很像一段小型的程序,由可选的声明部分,执行控制部分和可选 ... -
Oracle数据库的闪回技术
2012-01-03 10:07 25show parameter undo --显示数据库的闪回 ... -
开发软件常用工具包
2011-12-02 19:25 6161、jxls不错的处理excle工具包。 http://jxl ... -
查看Oracle执行计划的几种方法
2011-11-05 20:55 789一、通过PL/SQL Dev工具 1、直接Fil ... -
tomcat内存溢出的三种情况
2011-11-05 17:48 1089Tomcat内存溢出的原因 ... -
啤酒与尿布的故事
2011-08-30 12:59 1176(转)对于啤酒与尿 ... -
思维DW架构设计中的数据流架构规划
2011-08-30 12:56 1106数据仓库的架构看起 ... -
DB2的备份与恢复
2011-08-04 14:56 7631\ 数据库备份DB2 备份命令允许您在该命令执行时获取一个您 ... -
DB2递归查询
2011-07-05 15:28 1203在db2可以使用sql语句来进行递归查询,就是使用with语句 ... -
java实现文件转码
2011-05-11 22:57 1331非常好,解决了我的一个大问题。真是感谢。代码生成的java和j ... -
Group BY 的另一种写法
2011-04-20 19:39 729select * from temp_317 t1 执行 ... -
backup(自动备份数据库).cmd
2011-04-18 18:00 615if not exist %date:~0,4%%date: ... -
oracle常用视图查询
2011-01-27 23:13 947分析表 analyze table tablename com ... -
创建Oracle数据库
2011-01-24 21:30 764表空间CREATE SMALLFILE TABLESPACE ...
相关推荐
Oracle分析函数的示例数据 其实也就是Mastering oracle sql书的SQL脚本
这个是Oracle分析函数部分示例数据,希望可以方便大家学习(请不要下载,此资源错误)
主要介绍了Java使用Jdbc连接Oracle执行简单查询操作,结合实例形式详细分析了java基于jdbc实现Oracle数据库的连接与查询相关操作技巧,需要的朋友可以参考下
文档详细介绍了oracle的分析函数,包括功能说明、sql示例等。分析函数功能强大,在报表或数据迁移的时候可能会使用到。分析函数用法看上去有点复杂,最好使用的时候,参考文档
Oracle分析函数,常见的基本统计函数里面都有,大家可以通过安装Oracle的示例数据库来一步一步跟进学习!
详解Oracle分析函数,主用于OLAP,以实例讲解分析函数. 如: 排序用Rank, Dense_rank, row_number 1.带空值的排列 2.Top/Bottom N查询 3.First/Last排名查询 4.按层次查询 1.窗口函数简介 2.窗口函数示例-全统计 3....
Oracle 示例 通常与开发相关的简单 Oracle 代码示例。 例1/: 简单的 VArray 和 Java 安装 SQL:使用现有的 ... 连接到:Oracle Database 12c 企业版 12.1.0.2.0 版 - 具有分区、OLAP、高级分析和实际应用测试选项
OracleBI应用技术架构之预制分析模型概述及示例
oracle基础教程 课程说明 1 课程介绍 1 课程目标 1 相关资料 1 第1章 ORACLE数据库概述 2 ...4.14 怎样分析ORACLE故障 59 小结 61 附录A ORACLE数据字典与视图 62 附录B 动态性能表 68 附录C SQL语言运算符与函数 70
全书结构合理、内容翔实、示例丰富、语言简洁。不仅适合作为高等院校本/专科计算机软件、信息系统、电子商务等相关专业的数据库课程教材,同时还适合作为各种数据库技术培训班的教材以及数据库开发人员的参考资料...
主要介绍了jdbc连接oracle数据库功能,结合实例形式详细分析了java基于jdbc连接Oracle数据库的具体操作步骤与相关实现技巧,需要的朋友可以参考下
书中深入分析了Oracle数据库体系结构,包括文件、内存结构以及构成Oracle数据库和实例的底层进程,利用具体示例讨论了一些重要的数据库主题,同时分析了数据库中的物理结构,如表、索引和数据类型,并介绍采用哪些...
用大量示例详尽分析oracle 中现有的各种查询转换技术,先分析oracle如何收集、统计系统和对象的数据,然后推导各种代价估算公式,给出各种情形下的代价计算演示。 第三篇“sql调优技术”深入剖析oracle提供的各项...