查看: 3568|回复: 10

大家讨论一下:iot表和其上的索引相关的sql优化

[复制链接]
认证徽章
论坛徽章:
58
生肖徽章2007版:马
日期:2009-11-06 23:12:33授权会员
日期:2013-01-10 14:38:592013年新春福章
日期:2013-02-25 14:51:24马自达
日期:2013-08-07 10:54:45红旗
日期:2013-08-09 13:48:48劳斯莱斯
日期:2013-09-12 15:56:37萤石
日期:2013-10-31 08:44:19优秀写手
日期:2013-12-18 09:29:13Jeep
日期:2014-01-14 10:53:432014年新春福章
日期:2014-02-18 16:43:09
发表于 2011-7-4 17:19 | 显示全部楼层 |阅读模式
大家讨论一下:iot表和其上的索引相关的sql优化需要注意什么?

和普通的堆表和其上的所以相比
iot表和其上的索引,在进行sql优化时
需要注意什么?

[ 本帖最后由 yyp2009 于 2011-7-4 17:28 编辑 ]
认证徽章
论坛徽章:
58
生肖徽章2007版:马
日期:2009-11-06 23:12:33授权会员
日期:2013-01-10 14:38:592013年新春福章
日期:2013-02-25 14:51:24马自达
日期:2013-08-07 10:54:45红旗
日期:2013-08-09 13:48:48劳斯莱斯
日期:2013-09-12 15:56:37萤石
日期:2013-10-31 08:44:19优秀写手
日期:2013-12-18 09:29:13Jeep
日期:2014-01-14 10:53:432014年新春福章
日期:2014-02-18 16:43:09
发表于 2011-7-4 17:23 | 显示全部楼层
有个环境是:

主机:
Vendor (e.g. HP, SUN)        IBM
Operating System        AIX
O/S Version and Release        5300-05
CPU Model and Number         2097 MHz*4
O/S Block Size       
Memory (Megs)        15936  MB
Raw Devices? Number of Megs?        FileSystem


Usage (OLTP, DSS, etc.)        OLTP
RDBMS Version/Release        10.2.0.4EE


NLS_TERRITORY        AMERICA
NLS_LANGUAGE        AMERICAN
NLS_CHARACTERSET        UTF8
NLS_NCHAR_CHARACTERSET        UTF8
DB_BLOCK Size        8192
Number of Tablespaces        9个表空间
Number of Datafiles        13普通文件+1临时文件
TEMP Tablespace Size        15G
SGA Size(M)
Database Buffers   
Fixed Size         
Redo Buffers      
Variable Size      
Total SGA                                                             Fixed Size        2097800
Variable Size        4563406200
Database Buffers        4009754624
Redo Buffers        14675968

UNDO Tablespace Size        24096M
Number of control files        3
Redo Log Size        100M
Number of redo log groups        4
Number of redo log members per group        2
Are the on-line redo logs the same size?        YES
Are the redo logs being multiplexed by Oracle?        YES
Archiving Enabled?        YES



前台部署erp
数据存储都是设计为iot表

使用道具 举报

回复
认证徽章
论坛徽章:
58
生肖徽章2007版:马
日期:2009-11-06 23:12:33授权会员
日期:2013-01-10 14:38:592013年新春福章
日期:2013-02-25 14:51:24马自达
日期:2013-08-07 10:54:45红旗
日期:2013-08-09 13:48:48劳斯莱斯
日期:2013-09-12 15:56:37萤石
日期:2013-10-31 08:44:19优秀写手
日期:2013-12-18 09:29:13Jeep
日期:2014-01-14 10:53:432014年新春福章
日期:2014-02-18 16:43:09
发表于 2011-7-4 18:52 | 显示全部楼层
up

使用道具 举报

回复
论坛徽章:
188
红宝石
日期:2014-05-09 08:24:37萤石
日期:2014-01-03 10:25:39奥运会纪念徽章:羽毛球
日期:2008-07-01 10:46:06奥运会纪念徽章:马术
日期:2008-07-07 17:43:24奥运会纪念徽章:射箭
日期:2008-07-25 18:07:39奥运会纪念徽章:皮划艇激流回旋
日期:2008-07-30 10:02:57奥运会纪念徽章:花样游泳
日期:2008-09-26 13:02:43奥运会纪念徽章:排球
日期:2008-12-03 11:23:272010新春纪念徽章
日期:2010-01-04 08:33:082010年世界杯参赛球队:澳大利亚
日期:2010-02-26 11:08:44
发表于 2011-7-5 08:34 | 显示全部楼层
iot 表最大的特点就是按照索引来组织表,按照他排序.

其他索引包含主键.

比如表T按照a字段建立IOT,那就是按照a排序存在.
如果你在b字段建立索引,这个索引包含主键a
如果select a from t where b:=x;
这样不用访问IOT表.

使用道具 举报

回复
论坛徽章:
9
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:00ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51蛋疼蛋
日期:2011-08-09 14:26:55ITPUB十周年纪念徽章
日期:2011-11-01 16:24:51ITPUB 11周年纪念徽章
日期:2012-10-09 18:09:19奥迪
日期:2013-09-12 15:57:042014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09
发表于 2011-7-5 10:02 | 显示全部楼层

回复 #4 lfree 的帖子

最重要的是因为索引是有序的,所以当你要插入数据,会导致数据库要花大力气去插入这个数据。某种意义上说,索引组织表减少了大小,表和索引集合起来,表就是索引,索引就是表。 但就是经不起折腾。

使用道具 举报

回复
论坛徽章:
51
ITPUB十周年纪念徽章
日期:2011-11-01 16:25:22铁扇公主
日期:2012-02-21 15:03:13最佳人气徽章
日期:2012-03-13 17:39:18ITPUB季度 技术新星
日期:2012-05-22 15:10:11ITPUB 11周年纪念徽章
日期:2012-10-09 18:13:332013年新春福章
日期:2013-02-25 14:51:24ITPUB社区12周年站庆徽章
日期:2013-08-12 09:34:36itpub13周年纪念徽章
日期:2014-09-28 10:55:55
发表于 2011-7-5 10:05 | 显示全部楼层
其实在查询的时候优势很明显~
但是缺点就是写的时候比较麻烦~如果一个频繁DML的表是不能用IOT的

使用道具 举报

回复
论坛徽章:
86
2015中国数据库技术大会纪念徽章
日期:2015-04-24 16:04:24马上有车
日期:2014-02-19 11:55:14马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11优秀写手
日期:2013-12-18 09:29:11日产
日期:2013-10-17 08:44:39马自达
日期:2013-08-26 16:28:022013年新春福章
日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章
日期:2012-10-23 16:55:51马上有房
日期:2014-02-19 11:55:14
发表于 2011-7-5 15:44 | 显示全部楼层
奇怪,难道这个表只有一种查询?

抛开INSERT/UPDATE/DELETE,如果以不同的字段座位谓词查询,IOT怎么处理?

使用道具 举报

回复
论坛徽章:
9
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:00ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51蛋疼蛋
日期:2011-08-09 14:26:55ITPUB十周年纪念徽章
日期:2011-11-01 16:24:51ITPUB 11周年纪念徽章
日期:2012-10-09 18:09:19奥迪
日期:2013-09-12 15:57:042014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09
发表于 2011-7-5 20:30 | 显示全部楼层

回复 #7 sundog315 的帖子

如果真说抛开DML语句,IOT可以建立别的索引啊,其实很多表的查询也就某几种查询,所以建立相应的索引是可以解决这个问题的。

使用道具 举报

回复
论坛徽章:
188
红宝石
日期:2014-05-09 08:24:37萤石
日期:2014-01-03 10:25:39奥运会纪念徽章:羽毛球
日期:2008-07-01 10:46:06奥运会纪念徽章:马术
日期:2008-07-07 17:43:24奥运会纪念徽章:射箭
日期:2008-07-25 18:07:39奥运会纪念徽章:皮划艇激流回旋
日期:2008-07-30 10:02:57奥运会纪念徽章:花样游泳
日期:2008-09-26 13:02:43奥运会纪念徽章:排球
日期:2008-12-03 11:23:272010新春纪念徽章
日期:2010-01-04 08:33:082010年世界杯参赛球队:澳大利亚
日期:2010-02-26 11:08:44
发表于 2011-7-5 20:40 | 显示全部楼层
IOT 当然仅仅适合只读的表,不适合经常dml操作。

使用道具 举报

回复
认证徽章
论坛徽章:
58
生肖徽章2007版:马
日期:2009-11-06 23:12:33授权会员
日期:2013-01-10 14:38:592013年新春福章
日期:2013-02-25 14:51:24马自达
日期:2013-08-07 10:54:45红旗
日期:2013-08-09 13:48:48劳斯莱斯
日期:2013-09-12 15:56:37萤石
日期:2013-10-31 08:44:19优秀写手
日期:2013-12-18 09:29:13Jeep
日期:2014-01-14 10:53:432014年新春福章
日期:2014-02-18 16:43:09
发表于 2011-7-5 21:30 | 显示全部楼层
谢谢各自分享!

我从tom9i10arch看到tom的论述曰:

1     Oracle会 读取索引,然后按rowid来访问表,得到余下的行数据。由于我加载表所采用的方式,获取的每100行会在一个不同的数据库块上,所有每获取100行可能 就是一个物理I/O。下面考虑IOT中有同样的数据。这是这个查询,不过现在只需要读取相关的索引块,这个索引块中已经有所有的数据。在此不仅不存在表访 问,而且一段时期内对于ORCL的所有行物理存储在相互“邻近”的位置。因此引入的逻辑I/O和物理I/O都更少。

2    IOT本身可以有一个索引,就像在索引之上再加索引,这称为二次索引(secondary index)。 正常情况下,索引包含了所指向的行的物理地址,即rowid。而IOT二次索引无法做到这一点;它必须使用另外某种方法来指示行的地址。这是因为IOT中 的行可以大量移动,而且它不像堆组织表中的行那样“迁移”。IOT中的行肯定在索引结构中的每个位置上,这取决于它的主键值;只有当索引本身的大小和形状 发生改变时行才会移动。为了适应这种情况,Oracle引入了一个逻辑rowid(logical rowid)。 这些逻辑rowid根据IOT主键建立。对于行的当前位置还可以包含一个“猜测”,不过这个猜测几乎是错的,因为稍过一段时间后,IOT中的数据可能就会 移动。这个猜测是行第一次置于二次索引结构中时在IOT中的物理地址。如果IOT中的行必须移动到另外一个块上,二次索引中的猜测就会变得“过时”。因 此,与常规表相比,IOT上的索引效率稍低。在一个常规表上,索引访问通常需要完成一个I/O来扫描索引结构,然后需要一个读来读取表数据。对于IOT, 通常要完成两个扫描;一次扫描二次结构,另一次扫描IOT本身。除此之外,IOT上的索引可以使用非主键列提供IOT数据的快速、高效访问。 索引组织表小结 在 建立IOT时,最关键的是适当地分配数据,即哪些数据存储在索引块上,哪些数据存储在溢出段上。对溢出条件不同的各种场景进行基准测试,查看对 INSERT、UPDATE、DELETE和SELECT分别有怎样的影响。如果结构只建立一次,而且要频繁读取,就应该尽可能地把数据放在索引块上(最 合适获取),要么频繁地组织索引中的数据(不适于修改)。堆表的freelist相关考虑对IOT也同样适用。PCTFREE和PCTUSED在IOT中 是两个重要的角色。不过,PCTFREE对于IOT不像对于堆表那么重要,另外PCTUSED一般不起作用。不过,考虑OVERFLOW段时, PCTFREE和PCTUSED对于IOT的意义将与对于堆表一样重大;要采用与堆表相同的逻辑为溢出段设置这两个参数。 10.5索引聚簇表 我常常发现,人们对Oracle中聚簇的理解是不正确的。许多人都把聚簇与SQL Server或Sybase中的“聚簇索引”相混淆。但它们并不一样。聚簇(cluster)是指:如果一组表有一些共同的列,则将这样一组表存储在相同 的数据库块中;聚簇还表示把相关的数据存储在同一个块上。SQL Server中的聚簇索引(clustered index)则要求行按索引键有序的方式存储,这类似于前面所述的IOT。利用聚簇,一个块可能包含多个表的数据。从概念上讲,这是将数据“预联结”地存 储。聚簇还可以用于单个表,可以按某个列将数据分组存储。例如,部门10的所有员工都存储在同一个块上(或者如果一个块放不下,则存储在尽可能少的几个块 上)。聚簇并不是有序地存储数据(这是IOT的工作),它是按每个键以聚簇方式存储数据,但数据存储在堆中。所以,部门100可能挨在部门1旁边,而与部 门101和99离得很远(这是指磁盘上的物理位置)。 如 同10-8所示,图的左边使用了传统的表,EMP会存储在它的段中。DEPT也存储在自己的段中。它们可能位于不同的文件和不同的表空间,而且绝对在单独 的区段中。从图的右边可以看到将这两个表聚簇起来会是什么情况。方框表示数据库块。现在将值10抽取出来,只存储一次。这样聚簇的所有表中对应部门10的 所有数据都存储在这个块上。如果部门10的所有数据在一个块上放不下,可以为原来的块串链另外的块,来包含这些溢出的部分,这与IOT的溢出块所用的方式 类似。




我以前都面对的是堆表和b树,bitmap索引sql优化

现在突然是iot,没经历过

谢谢讨论分享

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


----------------------------------------

大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表