研发人员ORACLE培训材料.docx

1、研发人员ORACLE培训材料研发人员ORACLE培训材料综合资源产品线2010-12-29修订历史记录版本/状态作者参与者起止日期备注目录研发人员ORACLE培训材料 11 简介 51.1 编写目的 51.2 范围 51.3 定义、首字母缩写词和缩略语 51.4 参考资料 52 案例分析 62.1 综合资源项目的案例 63 SQL执行过程及执行计划 93.1 SQL执行过程 93.2 执行计划 93.2.1 执行计划相关参数 103.2.2 执行计划的类型 104 索引基础知识 114.1 类型类型 114.1.1 B-tree索引 114.1.2 位图索引 124.2 不会使用索引的情况 1

2、24.3 组合索引 135 数据扫描 155.1 全表扫描（Full Table Scans, FTS） 155.2 通过rowid存取 155.3 索引扫描 155.3.1 索引唯一扫描（index unique scan） 165.3.2 索引范围扫描（index range scan） 165.3.3 索引全扫描（index full scan） 175.3.4 索引快速扫描（index fast full scan） 176 表连接 186.1 数据库表连接类型 186.2 嵌套循环连接（NESTED LOOP JOIN） 196.3 排序合并连接（SORT MERGE JOIN） 2

3、06.4 哈希连接（HASH JOIN） 216.5 索引连接（INDEX JOIN） 226.6 主要连接方式比较 237 其他知识 247.1 IN和EXISTS的区别 247.2 子查询 247.3 视图合并 257.4 递归展开执行计划（connect by） 258 使用企业管理器进行性能监控 278.1 打开EM 278.2 进入性能页 288.3 进入顶级活动 298.4 查询耗资源的SQL语句 301 简介编写目的范围定义、首字母缩写词和缩略语参考资料1 文档编号（如果有的话） 文档名称，作者，资料来源/出版单位，发表日期表 11 表格标题表头表体图 11 图形标题2 案例分析

4、综合资源项目的案例典型的SQL语句SELECT /*+ ordered use_nl(TPSD TPRD TPSC TPRC TPP1 TLP1)*/ TPF1.SEQUENCE AS SHELF_NUM, TPSD.SEQUENCE AS SLOT_NUM, TPSC.SEQUENCE AS CARD_NUM, TPP1.SEQUENCE AS PORT_NUM, TLP1.CUID, TLP1.LABEL_CN, TLP1.RELATED_BMCLASSTYPE_CUID, TLP1.RELATED_CARD_CUID, TLP1.RELATED_LOGIC_DEVICE_CUID, TL

5、P1.RELATED_BANDWIDTH_CUID FROM T_PHY_SHELF TPF1, T_PHY_SLOT TPSD, T_PHY_SLOT2CARD TPRD, T_PHY_SLOT TPSC, T_PHY_SLOT2CARD TPRC, T_PHY_PORT TPP1, T_LOGIC_PORT TLP1 WHERE TPF1.CUID = TPSD.RELATED_SHELF_CUID AND TPSD.CUID = TPRD.RELATED_SLOT_CUID AND TPRD.RELATED_CARD_CUID = TPSC.RELATED_CARD_CUID AND T

6、PSC.CUID = TPRC.RELATED_SLOT_CUID AND TPRC.RELATED_CARD_CUID = TPP1.RELATED_CARD_CUID AND TPP1.RELATED_LOGIC_PORT_CUID = TLP1.CUID AND TPF1.RELATED_DEVICE_CUID = T_PHY_HW_NE_80-8a03be2c2b2460ff012b246f570f0051;SQL改造案例改造前SELECT DS.SEQUENCE AS SLOT_NUM, CS.SEQUENCE AS CARD_NUM, C.* FROM T_PHY_SLOT DS

7、LEFT JOIN T_PHY_SLOT2CARD DS2C ON DS2C.RELATED_SLOT_CUID = DS.CUID LEFT JOIN T_PHY_SLOT CS ON CS.RELATED_CARD_CUID = DS2C.RELATED_CARD_CUID LEFT JOIN T_PHY_SLOT2CARD R ON R.RELATED_SLOT_CUID = DS.CUID OR R.RELATED_SLOT_CUID = CS.CUID LEFT JOIN T_PHY_CARD C ON C.CUID = R.RELATED_CARD_CUID LEFT JOIN T

8、_MD_CARDTYPE CT ON CT.BM_CLASSID = C.RELATED_BMCLASSTYPE_CUID, T_PHY_SHELF F WHERE DS.RELATED_SHELF_CUID = F.CUID AND F.RELATED_DEVICE_CUID = T_PHY_DEVICE_ERICSSON_SE800-8a03be2c2bb3b541012bb3f508102c75 AND DS.RELATED_CARD_CUID IS NULL AND (CT.IS_SUBCARD IS NULL OR CT.IS_SUBCARD 1);改造后SELECT /*+orde

9、red use_nl(ds ds2c c ct)*/ DS.SEQUENCE AS SLOT_NUM, AS CARD_NUM, C.* from T_PHY_SHELF F, T_PHY_SLOT DS, T_PHY_SLOT2CARD DS2C, T_PHY_CARD C, T_MD_CARDTYPE CT where F.CUID = DS.RELATED_SHELF_CUID and f.cuid = ds.related_shelf_cuid(+) and ds.cuid = ds2c.related_slot_cuid(+) and ds2c.related_card_cuid =

10、 c.cuid(+) and c.related_bmclasstype_cuid = ct.bm_classid(+) and ds.related_card_cuid is null and (ct.is_subcard is null or ct.is_subcard 1) and f.related_device_cuid = T_PHY_DEVICE_ERICSSON_SE800-8a03be2c2bb3b541012bb3f508102c75union allSELECT /*+ ordered use_nl(ds ds2c cs r c ct)*/ DS.SEQUENCE AS

11、SLOT_NUM, CS.SEQUENCE AS CARD_NUM, C.* from T_PHY_SHELF f, T_PHY_SLOT DS, T_PHY_SLOT2CARD DS2C, T_PHY_SLOT CS, T_PHY_SLOT2CARD R, T_PHY_CARD C, T_MD_CARDTYPE CT where f.cuid = ds.related_shelf_cuid and ds.cuid = ds2c.related_slot_cuid(+) and ds2c.related_card_cuid = cs.related_card_cuid(+) and cs.cu

12、id = r.related_slot_cuid(+) and r.related_card_cuid = c.cuid(+) and c.related_bmclasstype_cuid = ct.bm_classid(+) and ds.related_card_cuid is not null and (ct.is_subcard is null or ct.is_subcard 1) and f.related_device_cuid = T_PHY_DEVICE_ERICSSON_SE800-8a03be2c2bb3b541012bb3f508102c75;调整方法（1）调整表的顺序

13、（2）调整表的连接条件（3）检查各连接条件上是否有索引（4）使用提示，固定执行计划3 SQL执行过程及执行计划SQL执行过程执行计划查看SQL语句的执行计划，在PL/SQL Developer中F5键，查看语句对应的执行计划。 执行计划相关参数基数（Card）：指计划中这一步所处理的行数。耗费（Cost）：指cbo中这一步耗费的资源，这个值是相对值。字节（bytes）：指cbo中这一步所处理的所有记录的字节数，是估算出来的一组值。执行计划的类型4 索引基础知识类型类型B-tree索引我们使用的大部分都是这种索引（1） 适合于大量的增、删、改操作（2） 适合高基数的列（唯一值多）（3） 典型的树

14、状结构（4） 每个节点都是数据块（5） 大多都在物理上一层、两层或三层不定。（6） 叶子块数据是排序的，从左到右递增（7） 在分支块或根块中放的是索引的范围。位图索引（1） 适合于决策支持系统（OLAP）；（2） 做update操作时代价非常高；（3） 基数比较少的时候才适合建位图索引。不会使用索引的情况 存在数据类型隐形转换的select * from emp where emp_id=123; -emp_id是number型 列上有数学运算的select * from emp where salary*210000； 使用不等于( )运算的（包括NOT运算）select * from em

15、p where dept_no2001；-可以改写成where dep_no2001注：在Oracle的高版本中如9i，数据库引擎将自动进行类似转换。所以程序中可以考虑使用运算以简化程序代码。 使用substr字符串函数的，如： select * from emp where substr(last_name，1，4)=FRED； %通配符在第一个字符的，如: select * from staff_member where first_name like %DON；然而当通配符出现在字符串其他位置时，优化器就能利用索引。 字符串连接(|)的，如： select * from emp wher

16、e first_name|=DONALD; IS NULL 与 IS NOT NULL 不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。 函数的索引 日期类型也是很容易用到的，而且在SQL语句中会使用to_char函数以查询具体的的范围日期。如：select * from emp where TO_CHAR(birth_day，YYYY) =

17、2003；如果频繁使用类似语句，可以建立基于此函数的索引如：CREATE INDEX Ind_emp_birth ON emp (to_char(birth_day，YYYY)；组合索引在oracle中可以创建组合索引，即同时包含两个或两个以上的列的索引。在组合索引的使用方面，oracle有以下特点： 当使用基于规则的优化器（RBO）时，只有组合索引的前导列出现在SQL语句的where子句时，才会使用到该索引；A、B、C 在使用oracle9i之前基于成本的优化器（CBO）时，只有组合索引的前导列出现在SQL语句的where子句时，才会使用到该索引，这取决于优化器计算的使用索引的成本和使用全表

18、扫描的成本，Oracle会自动选择成本低的访问路径； 从Oracle9i起，Oracle引入了一种新的索引扫描方式索引跳跃扫描（index skip scan），这种扫描方式只有基于成本的优化器（CBO）才能使用。这样，当SQL语句的where子句中即使没有组合索引的前导列，并且索引跳跃扫描的成本低于其他扫描方式的成本时，Oracle就会使用该方式扫描组合索引；Oracle优化器有时会做出错误的选择，因为它再“聪明”，也不如我们SQL语句编写人员更清楚表中数据的分布，在这种情况下，通过使用提示（hint），我们可以帮助Oracle优化器作出更好的选择。5 数据扫描全表扫描（Full Table

19、 Scans, FTS）在全表扫描时，oracle读取表中的所有行，并检查每一行是否满足语句的WHERE限制条件。一个多块读操作可以一次IO读取多块数据（db_block_multiblock_read_count参数设定），而不是一次只读取一个数据块，这极大的减少了IO总次数，提供系统的吞吐量，所以多块读的方法可以十分高效地实现全表扫描，而且只有在全表扫描的情况下才能使用多块读操作。使用FTS的前提条件：在较大的表上不建议使用全表扫描，除非取出数据量比较多，超过总量的5%10%。通过rowid存取行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID来存取数据

20、可以快速定位到目标数据上，是oracle存取单行数据的最快方法。这种存取方法不会用到多块读操作，一次IO只能读取一个数据块。我们会经常在执行计划中看到该存取方法，如通过索引查询数据。索引扫描先通过index查询到数据对应的rowid（对于非唯一索引可能返回多个rowid值），然后根据rowid直接从表中得到具体的数据，这种查找方式称为索引扫描。一个rowid唯一表示一行数据，该行对应的数据块是通过一次IO得到的，在这种情况下oracle只会读取一个数据库块。在索引中，存储索引值和索引值对应的行rowid值。索引扫描由两步组成：（1）扫描索引得到对应的rowid。（2）通过找到的rowid从表中

21、读出具体的数据。每步都是单独的一次IO，对于索引，由于经常使用，绝大多数已经cache到内存中，所以第一步的IO经常是逻辑IO，即数据可以从内存中得到。但是对于第二步，如果表比较大，其数据不可能全在内存中，所以其IO很有可能是物理IO，这是一个机械操作，相对于逻辑IO来说，是机器费时间的。所以如果大表进行索引扫描，取出的数据如果大于总量的5%10%，使用索引扫描效率会下降。如果查询的数据全在索引中找到，就可以避免第二步操作，避免了不必要的IO，此时即使通过索引扫描取出的数据比较多，效率还是很高的。如果sql语句中对索引列进行排序，因为索引已经预先排好序了，那么在执行计划中不需要再对索引列进行排

22、序。根据索引的类型以及where限制条件的不同，有4种类型的索引扫描：（1）索引唯一扫描（index unique scan）;（2）索引范围扫描（index range scan）；（3）索引全扫描（index full scan）（4）索引快速扫描（index fast full scan）索引唯一扫描（index unique scan）通过唯一索引查找一个数值返回单个rowid，如果存在unique或primary key约束，oracle经常使用唯一性扫描。索引范围扫描（index range scan）使用index range scan的3种情况：（1）在唯一所列列上使用了ran

23、ge操作符（、=、=、between）。（2）在组合索引上只使用部分列进行查询，导致查询出多行。（3）对非唯一索引列上进行的任何查询。索引全扫描（index full scan）与全表扫描对应，也有相应的全索引扫描。索引快速扫描（index fast full scan）扫描索引中所有的数据块，与index full scan很类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是以排序顺序被返回。在这种存取方法中，可以使用多块读功能，也可以进行并行读入，以便获得最大吞吐量，缩短执行时间。6 表连接假定给出下面的两个表，我们有几种方式来手工检索出员工的姓名和工资。 第一种方式：从表

24、1按顺序读取每一个员工编号，针对每一个记录到表2中查询对应员工编号的工资。第二种方式：把表1和表2对员工编号进行排序，然后对表1和表2按从上到下的顺序依次查询。第三种方式：对表1的全部员工编号进行hash计算，将计算结果和行的位置保持下来。然后开始读取表2的数据，对每一条记录的员工编号，进行hash计算，算出对应表1的行位置。进行匹配。 表的连接是指在一个SQL语句中通过表与表之间的关联，从一个表或多个表检索出相关的数据。 连接是通过SQL语句中FROM从句的多个表名，以及WHERE从句定义的表之间的连接条件来实现的。 如果SQL语句的关联表超过两个，ORACLE会首先连接其中的两个表，产生一

25、个结果集；然后将产生的结果集与下一个表再进行关联；继续这个过程，直到所有的表都连接完成；最后产生所需的数据。数据库表连接类型数据库表的连接有以下种类型：（1） 嵌套循环连接（NESTED LOOP JOIN）（2） 排序合并连接（SORT MERGE JOIN）（3） 哈希连接（HASH JOIN）（4） 索引连接（INDEX JOIN）（5） 群集连接（CLUSTER JOIN）（6） 笛卡尔连接（CARTESIAN JOIN）按照连接符的不同还可以将表连接分为等值连接、非等值连接和外连接。驱动表：在进行数据库连接时，首先进行存取的表称为驱动表。一般将限制性条件加在驱动表上。嵌套循环连接（N

26、ESTED LOOP JOIN）嵌套循环连接过程（1） Oracle优化器根据基于规则RBO或基于成本CBO的原则，选择两个表中的一个作为驱动表，并指定其为外部表。（2） Oracle优化器再将另外一个表指定为内部表。（3） Oracle从外部表读取第一行，然后和内部表中的数据逐一进行对比，所有匹配的记录放在结果集中。（4） Oracle读取外部表的第二行，在和内部表的数据注意进行对比，所有匹配的记录添加到结果集中。（5） 重复上述步骤，直到外部表中所有记录全部处理完。（6） 最后产生满足要求的结果集。嵌套循环连接分析通过查询语句的执行计划，可以看出哪个表是外部表，哪个表是外部表。上面的表（T

27、ABLE_A）是外部表（驱动表），下面的表（TABLE_B）是内部表。SELECT STATEMENT Optimizer=CHOOSENESTED LOOPSTABLE ACCESS (FULL) OF TABLE_ATABLE ACCESS (FULL) OF TABLE_B优点：使用嵌套循环连接是一种从结果集中提取第一批记录最快速的方法。在驱动表较小或内部表已连接的列有唯一索引或者高度可选的非唯一索引时，嵌套循环连接效果是比较理想的。嵌套循环连接比其他连接方法有优势，它可以快速地从结果集中提取第一批记录，而不用等待整个结果集完全确定下来。这样，在理想的情况下，终端用户就可以通过屏幕查看第

28、一批记录，而在同时读取其他记录。不管如何定义连接的条件或者模式，任何两表都可以使用嵌套循环连接，所以嵌套循环连接是非常灵活的。 缺点：如果内部表连接列不包含索引，或者索引不是高度可选时，嵌套循环连接效率是很低的。如果驱动表的记录非常庞大时，其他的链接方法可能更加有效。 可以通过在SQL语句中添加HINTS，强制ORACLE优化器产生嵌套循环连接的执行计划。select /*+ ordered use_nl(b) */ a.user_name,b.dev_no from user_info a, dev_info b where a.user_id = b.user_id;排序合并连接（SORT

29、 MERGE JOIN）排序合并连接过程（1） 首先提取表A需要的数据，然后对这些数据按照连接操作关联列进行排序。（2） 然后提取表B需要的数据，然后对这些数据按照连接操作关联列进行排序。（3） 最后两边已排序的行被放在一起执行合并操作，即将2个表按照连接条件连接起来。排序合并连接分析在缺乏数据的选择性或者可用的索引时，或者两个表都过于庞大（所选的数据超过表记录的5%）时，排序合并连接将比嵌套循环连接更加有效。排序合并连接需要比较大的临时内存块，用于排序，这将导致在临时表空间占用更多的内存和磁盘IO。可以使用HINTS强制ORACLE优化器产生排序合并连接的执行计划。select /*+ us

30、e_merge(a b) */ a.user_name,b.dev_no from user_info a, dev_info b where a.user_id = b.user_id;哈希连接（HASH JOIN）当内存能够提供足够的空间时，哈希（HASH）连接是Oracle优化器通常的选择。哈希连接中，优化器根据统计信息，首先选择两个表中的小表，在内存中建立这张表的基于连接键的哈希表；优化器再扫描表连接中的大表，将大表中的数据与哈希表进行比较，如果有相关联的数据，则将数据添加到结果集中。当表连接中的小表能够完全cache到可用内存的时候，哈希连接的效果最佳。哈希连接的成本只是两个表从硬盘读入到内存的成本。但是，如果哈希表过大而不能全部cache到可用内存时，优化器将会把哈希表分成多个分区，再将分区逐一cache到内存中。当表的分区超过了可用内存时，分区的部分数据就会临时地写到磁盘上的临时表空间上。因此，分区的数据写磁盘时，比较大的区间（EXTENT）会提高I/O性能。ORACLE推荐的临时表空间的区间是1MB。临时表空间的区间大小由UNIFORM SIZE指定。