2014毕业论文,数据库

ETL中数据抽取的优化方法——基于oracle的通过索引优化SQL语句 摘  要 索引是对数据库 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 中一列或多列的值进行排序的一种结构，例如 employee 表的姓名（name）列。如果要按姓查找特定职员，与必须搜索表中的所有行相比，索引会帮助您更快地获得该信息。 在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的SQL语句执行得更快。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。当表中有大量 记录 混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载 时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引的方式与您使用书籍中的索引的方式很相似：它搜索索引以找到特定值，然后顺指针找到包含该值的行。在数据库关系图中，可以在选定表的“索引/键”属性页中创建、编辑或删除每个索引类型。当保存索引所附加到的表，或保存该表所在的关系图时，索引将保存在数据库中。 关键字：数据库，索引，优化 Abstract Index is the database table ordered by the value of one or more columns of a structure, such as the employee table name (name).If a according to find specific staff, compared with must search all rows in a table, index will help you get the information more quickly. In a relational database, the index is a kind of database structure related to the table, it can make corresponding to the table of SQL statement execution faster.Index directory ACTS as a book, can according to the directory page quickly find the content you need.When a large number of records in the table, if you want to query the table, the first kind of way of searching for information is a full table search, is to take out all the records one by one, and the query conditions compared one by one, and then return to meet the conditions of record, the time it will consume a large amount of database system, and cause a lot of disk I/O operations;Is the second in the table index, and then find conforms to the query conditions index in the index value, finally, stored in the index of the ROWID (page number) to quickly find the corresponding record in the table.Index is a single, physical database structure, it is a table in a column or a number of the set of column values and the corresponding points to the physical identification data page of the values in the table pointer list of logic.Index to provide data stored in the table of the specified column value of a pointer, and then according to you to specify the sort order to sort these Pointers.Database using the index of way and you are using the index of the books in ways similar to: its search index in order to find a specific value, then go find containing the value of the pointer.In the database diagram, can be in the selected table "indexes/keys" attribute of the page to create, edit, or delete each index type.When attached to the table, to preserve the index or save the table in the diagram, the index will be stored in the database. Keywords： Database,Index,Tunning 目录 摘  要    1 Abstract    2 1绪论    3 1.1 研究背景    3 1.2 需求分析    3 1.2.1 需求陈述    3 1.2.2 任务概述    3 2索引详解    4 2.1 索引介绍    4 2.1.1 索引的基本原理    4 2.1.1.1 B*TREE索引简介    4 2.1.1.2 B*树的结构    5 2.1.2 索引的创建语法    7 2.1.3 索引的访问方式    8 2.1.3.1 索引范围扫描 Index Scan (Range)    8 2.1.3.2 索引全扫描: Index  Scan (Full)    9 2.1.3.3 索引快速全扫描: Index Scan(Fast Full)    9 2.1.3.4 索引跳跃扫描: Index Scan (Skip)    10 2.2 索引的开销    11 2.3 索引块的内部结构    11 2.4 聚簇因子    20 2.4.1 聚簇因子定义    20 2.4.2 高聚簇因子的危害    20 2.4.3 如何降低聚簇因子    21 2.5 权衡是否创建索引的因素    21 2.5.1 应该建索引列的特点    21 2.5.2 不应该建索引列的特点    22 3执行 计划 项目进度计划表范例计划下载计划下载计划下载课程教学计划下载     23 3.1 执行计划简介    23 3.2 如何查看执行计划    23 3.3 执行计划阅读顺序    26 3.4 怎样修改执行计划    27 3.4.1 HINT简介    28 3.4.2 几种常见的hint    28 3.4.2.1 FULL    28 3.4.2.2 INDEX    29 3.4.2.3 USE_HASH    30 1绪论 1.1 研究背景 现今最宝贵的资源就是时间，在数据库领域也是如此，随着数据量越来越庞大，查询操作所消耗的时间也变得越来越长。这在很多报表系统中是无法容忍也无法接受的，为了满足客户提高检索性能的需求，诞生了索引。但是如果无法合理利用索引，它不仅不会给查询带来方便快捷，反而会成为数据库拖慢性能的杀手。在此背景下，我们需要去研究索引的内部结构，从而让索引成为帮助我们提高性能的工具。 1.2 需求分析 1.2.1 需求陈述 某些OLAP系统中数据表中的数据量通常大的惊人，是为控制和减少数据抽取的时间，我们通常需要在关键的位置创建索引。它用于加速数据库对数据的扫描效率，从而更快速的得到结果。通过创建索引，将帮助我们更快的收集、抽取、分析琐碎数据，从而达到我们想要的目的和结果。创建索引作为一个重要的优化方法和手段，能有效的帮助人们提高效率。 1.2.2 任务概述 需求分析工作是数据表设计中重要的一步，也是起决定性的一步。我们在这个阶段要准确的回答：我们为什么要建立索引，在哪里建立索引，建立索引能为我们带来多高的效率，是否值得。 2索引详解 2.1 索引介绍 2.1.1 索引的基本原理 索引种类主要有：B*TREE索引，BITMAP索引，函数索引，反转索引等，在这里我们仅讨论最常见的B*TREE索引。 2.1.1.1 B*TREE索引简介 这里的“B”不代表二叉(binary),而是代表平衡(balanced).B*树索引并不是一颗二叉树。 需要了解这个，我们首先需要了解:普通的B树，如图2-1所示： B树: 图2-1 二叉树结构 二叉查找树（Binary Search Tree），或者是一棵空树，或者是具有下列性质的二叉树： 若它的左子树不空，则左子树上所有结点的值均小于它的根结点的值； 若它的右子树不空，则右子树上所有结点的值均大于它的根结点的值； 特点： 1. 所有非叶子结点的左右子树的结点数目均保持差不多，搜索性能接近二分查找。 2. 无法范围查找，每个节点之间无法连接。 3.  经过若干操作，会变成如下线性结构。 例如如图查询：需要查询65，则需要扫描很多块，如图2-2所示： 图2-2 2.1.1.2 B*树的结构 在oracle中B*树的B代表的是balanced，所谓的"Height balanced"。大多数B*树索引的高度都是2或3，即使索引中有数百万行记录也是如此，这说明，一般而言，在索引中找到一个键只需要2到3次I/O ,这是相当高效的。 叶节点间有链指针 如图2-3所示： 图2-3 平衡树结构 特点： l 1.  访问每一个节点的高度一致，就是所谓的平衡 : l 2.  适合范围扫描，因为叶节点有指针。 l 3.  关键字都在叶子结点出现；这样，每个值的搜索都必须访问到叶节点才行。 这个树底层的块称为叶子节点/叶子块, ，其中分别包含各个索引键值以及一个rowid(它是指向所索引的行)。叶子节点之上的内部块称为分支块(branch block)/导航块，这些节点用于实现导航。例如，如果想在索引中找到值20，要从树顶开始，找到左分支，我们检查这个块，并发现需要找到范围"20..25"的块，这个块将是叶子块，其中会指示包含数20的行。索引的叶子节点实际上构成了一个双向链表。一旦发现要从叶子节点中的哪里开始，执行值的有序扫描就会很容易，我们就不必再在索引结构中导航，而只需根据叶子节点向前或向后扫描就可以了（比如要做between 20 to 40，我们只需要先导航找到20，然后顺序扫描直到遇到40即可）。 图2-4索引的内部结构 索引的作用:通过遍历树的方式,迅速定位节点,并确定地址,所以搜索技术最核心的是遍历搜索技术 b*树子索引—复合索引: 复合索引也是一种B*树索引，它由多列组成。当我们拥有使用两列或超过两列的频繁查询时，就使用B*树复合索引，而其所使用的两列或多列在where子句中and逻辑操作符连接。因为复合索引中列的顺序很重要，所以确信以最有效的索引顺序排列他们,尤其是第一位置的顺序:尽量将选择性高的列作为索引的第一列 实际例子： Select * from tab1 where col1=‘A’ and col2=‘B’  2.1.2 索引的创建语法 CREATE UNIUQE | BITMAP INDEX . ON . ( | ASC | DESC, | ASC | DESC,...)TABLESPACE STORAGE LOGGING | NOLOGGINGCOMPUTE STATISTICSNOCOMPRESS | COMPRESSNOSORT | REVERSEPARTITION | GLOBAL PARTITION 相关说明： 1）UNIQUE | BITMAP：指定UNIQUE为唯一值索引，BITMAP为位图索引，省略为B-Tree索引。 2） | ASC | DESC：可以对多列进行联合索引，当为expression时即“基于函数的索引” 3）TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时效率更高) 4）STORAGE：可进一步设置表空间的存储参数 5）LOGGING | NOLOGGING：是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率) 6）COMPUTE STATISTICS：创建新索引时收集统计信息 7）NOCOMPRESS | COMPRESS：是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值) 8）NOSORT | REVERSE：NOSORT表示与表中相同的顺序创建索引，REVERSE表示相反顺序存储索引值 9）PARTITION | NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区 2.1.3 索引的访问方式 Tables 1. 全表扫描 2. Rowid扫描 3. 采样表扫描 Indexes 4. 索引唯一扫描 5. 索引范围扫描 6. 索引全扫描 7. 索引快速全扫描 8. 索引跳跃式扫描 9. 索引连接扫描 10.位图索引扫描     在此我们仅讨论比较典型的索引范围扫描，索引全扫描，索引快速全扫描和索引跳跃式扫描。 2.1.3.1 索引范围扫描 Index Scan (Range) 产生条件：数据库需要查询表中一部分连续的数据，且这一列上有索引。如图 2-5所示： 图2-5  索引范围扫描 这是最常见的索引扫描形式，这种扫描形式适用于查询的数据量不超过表的总数据量的5%。否则考虑用全表扫描。因为索引扫描除了Index Scan(Fast Full)（后面会提到）其他都是单块读，而单块读与多快读所消耗的时间几乎一样。所以，当全表的块数/并行数/每次IO多快读个数>需要扫描的索引块数，则走索引。而这个临界值一般都会在5%左右 2.1.3.2 索引全扫描: Index  Scan (Full) 产生条件：数据库需要查询某些列的所有数据，但是索引所包含的列只有查询列的其中一部分。如图2-6所示： 图2-6 索引全扫描 这是一种效率非常低的扫描方式。之前提到了索引是走单块读，所以把索引中所有的块全部单个扫描一遍是相当消耗资源的，这种方式要比全表扫描效率要低的多。 2.1.3.3 索引快速全扫描: Index Scan(Fast Full) 产生条件：数据库需要查询某些列的所有数据，且索引中所包含的列与查询的列完全一致。如图2-7所示: 2-7 索引快速全扫描 这是一种效率非常高的扫描方式。因为这种扫描方式用多快读。 2.1.3.4 索引跳跃扫描: Index Scan (Skip) 产生条件：数据库需要查询某些列的一部分数据，索引中包含这些列，但是在索引创建时，将选择性低的列放在前面而把选择性高的列放在后面。 。 这是一种效率很低的扫描方式。因为在第一个列过滤一部分后肯定没有过滤干净，在扫描第二个列时仍然需要重新过滤，因此效率很低。 2.2 索引的开销 索引的开销大部分体现在dml操作上，因为索引需要更新维护，这需要时间。 曾经有这样一个案例，开发人员往一个表里插入30w数据竟然插了一个多小时，这在平时不到5分钟就能完成。后来发现他插入的这堆数据在有索引的那一列很杂乱，完全无序。oracle无法在磁盘中修改数据，必须把数据块读进内存中，如果完全无序，那我们考虑最糟糕的情况，每一条数据都属于不同的数据块，那么oracle需要向内存中读取30w个数据块。每一个物理读大概耗费13ms，再加上写数据的时间，那么这样的结果就可想而知了。那么我们再来看如果有序会是什么样子。依旧用30w数据，全部有序，假设一个块中可存160条数据，那么oracle需要的物理读是30w/160=1875个块，物理读的速度整整提高了160倍。 所以我们可以看出索引的开销主要在于两部分： 第一：数据的插入，而插入的数据是否有序则决定着这种开销的大小。 第二：数据的更新。 2.3 索引块的内部结构 我们可以通过块dump技术把索引块中的结构倒出来 下面分别把分支节点的索引结构和叶子节点的索引信息dump出来 创建测试数据 [sql] sys@ORCL> select * from v$version where rownum=1;   BANNER   ----------------------------------------------------------------   Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod   sys@ORCL> drop table tt purge;   drop table tt purge   *   ERROR at line 1:   ORA-00942: table or view does not exist   sys@ORCL> create table tt as select * from dba_objects;   Table created.   sys@ORCL> select count(*) from tt;   COUNT(*)   ----------   50356   sys@ORCL> insert into tt select * from tt;   50356 rows created.    www.2cto.com   sys@ORCL> commit;   Commit complete.   sys@ORCL> select count(*) from tt;   COUNT(*)   ----------   100712   sys@ORCL> create index btree_tt on tt(object_name);   Index created.   查看索引的Blevel、height（blevel：节点的深度。root位于第0层，以此类推。height=blevel+1） [sql] sys@ORCL> select index_name,blevel from dba_indexes where index_name='BTREE_TT';   INDEX_NAME                         BLEVEL   ------------------------------ ----------   BTREE_TT                                2   sys@ORCL> analyze index btree_tt validate structure;   Index analyzed.    www.2cto.com   sys@ORCL> select name,height from index_stats where name='BTREE_TT';   NAME                               HEIGHT   ------------------------------ ----------   BTREE_TT                                3   获得btree_tt的对象号，进行索引结构的dump [sql] sys@ORCL> select object_id from dba_objects where owner='SYS' and object_name='BTREE_TT';   OBJECT_ID   ----------   52614   sys@ORCL> oradebug setmypid   Statement processed.   sys@ORCL> alter session set events 'immediate trace name treedump level 52614';   www.2cto.com   Session altered.   sys@ORCL> oradebug tracefile_name   /u01/app/oracle/admin/orcl/udump/orcl_ora_5234.trc   查看treedump trc文件 [sql] ----- begin tree dump   branch: 0x40efaa 4255658 (0: nrow: 2, level: 2)   branch: 0x40f603 4257283 (-1: nrow: 247, level: 1)   leaf: 0x40efab 4255659 (-1: nrow: 182 rrow: 182)   leaf: 0x40efac 4255660 (0: nrow: 182 rrow: 182)   leaf: 0x40efad 4255661 (1: nrow: 186 rrow: 186)   leaf: 0x40efae 4255662 (2: nrow: 189 rrow: 189)   leaf: 0x40efaf 4255663 (3: nrow: 186 rrow: 186)   leaf: 0x40efb0 4255664 (4: nrow: 190 rrow: 190)   leaf: 0x40efb1 4255665 (5: nrow: 185 rrow: 185)   leaf: 0x40efb2 4255666 (6: nrow: 179 rrow: 179)   leaf: 0x40efb3 4255667 (7: nrow: 187 rrow: 187)   leaf: 0x40efb4 4255668 (8: nrow: 181 rrow: 181)   ............................................   ............................................   branch: 0x40f6fb 4257531 (0: nrow: 248, level: 1)   leaf: 0x40f602 4257282 (-1: nrow: 228 rrow: 228)   leaf: 0x40f604 4257284 (0: nrow: 226 rrow: 226)   leaf: 0x40f605 4257285 (1: nrow: 224 rrow: 224)   leaf: 0x40f606 4257286 (2: nrow: 223 rrow: 223)   leaf: 0x40f607 4257287 (3: nrow: 217 rrow: 217)   leaf: 0x40f608 4257288 (4: nrow: 253 rrow: 253)   leaf: 0x40f609 4257289 (5: nrow: 232 rrow: 232)   ............................................   ............................................   leaf: 0x40f6f8 4257528 (244: nrow: 191 rrow: 191)   leaf: 0x40f6f9 4257529 (245: nrow: 181 rrow: 181)   leaf: 0x40f6fa 4257530 (246: nrow: 99 rrow: 99)   ----- end tree dump    www.2cto.com   解释trc文件 每一行第一列表示：节点类型，branch是分支节点（包括了根节点），而leaf则是叶子节点 第二列表示：节点地址，16进制 第三列表示：节点地址，10进制 第四列表示：相对于前一个节点的位置：根节点从0算起，其他分支节点和叶子节点从1开始算 第五列表示：（nrow）当前节点所含索引条目的数量（包括delete的条目） 第六列表示：（level）分支节点的层级，在oracle的索引中，层级号是倒过来的，也就是说假设某个索引有N层，则根节点的层级号为N，而根节点下一层的分支节点的层级号为N-1 第七列表示：（rrow）有效的索引条目的数量，因为索引条目如果被删除，不会立即被清除出索引块中。所以nrow减rrow的数量就表示已经被删除的索引条目数量 上面这种方式以树状形式转储整个索引。同时，我们可以转储一个索引节点来看看其中存放了些什么。 下面转储根节点的索引块内容。 从trc文件可知：根节点branch: 0x40efaa 4255658 (0: nrow: 2, level: 2) [sql] sys@ORCL> select dbms_utility.data_block_address_file(4255658 ) fno，   2              dbms_utility.data_block_address_block(4255658 ) bno   3         from dual;   FNO        BNO   ---------- ----------   1      61354   sys@ORCL> alter system dump datafile 1 block 61354;   www.2cto.com   System altered.   sys@ORCL> oradebug tracefile_name   /u01/app/oracle/admin/orcl/udump/orcl_ora_5234.trc   查看root节点的trc内容 [sql] header address 230057028=0xdb66444   kdxcolev 2   KDXCOLEV Flags = - - -   kdxcolok 0   kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y   kdxconco 2   kdxcosdc 0   kdxconro 1   kdxcofbo 30=0x1e   kdxcofeo 8026=0x1f5a   kdxcoavs 7996   kdxbrlmc 4257283=0x40f603   kdxbrsno 0   kdxbrbksz 8056    kdxbr2urrc 0   row#0[8026] dba: 4257531=0x40f6fb   col 0; len 18; (18):  41 4c 4c 5f 43 4f 4c 5f 50 52 49 56 53 5f 4d 41 44 45   col 1; len 6; (6):  00 40 ee c5 00 2c    www.2cto.com   ----- end of branch block dump -----   kdxcolev 表示：索引层级号，我们这个例子中，根节点的level是2，叶子该是0 kdxcolok 表示：该索引上是否有DML活动事务 kdxconco 表示：索引条目中列的数量 kdxcosdc 表示：索引结构发生变化的数量，当你修改某个索引键值时，该值加1 kdxconro 表示：当前索引节点中索引条目的数量 kdxcofbo 表示：当前索引节点从第几个字节开始记录 kdxcofeo 表示：当前索引节点可用空间的最尾端在哪个字节 kdxcoavs 表示：当前索引节点可用空间总量。也就是kdxcofeo - kdxcofbo 的值 kdxbrlmc 表示：分支节点的位置 kdxbrsno 表示：最后一个被修改的索引条目号，这里为0，表明是新建索引 kdxbrbksz 表示：可用数据块大小，从这里我们可以知道，即便pctfree为0，对于8k数据块，我们也不能完全用完 [sql] row#0[8026] dba: 4257531=0x40f6fb   col 0; len 18; (18):  41 4c 4c 5f 43 4f 4c 5f 50 52 49 56 53 5f 4d 41 44 45   col 1; len 6; (6):  00 40 ee c5 00 2c   这部分内容就是根节点里面记录的索引条目，总共1行（在B+树的定义里，如果按最小关键码复写原则，则树中每个非叶子节点中有m棵子树必有m-1个关键码）。每个索引条目都指向一个分支节点，其中，col 1表示所链接的分支节点的地址，如果根节点下没有其他的分支节点，则col 1为TERM；col 0表示该分支节点所链接的最小键值。注意一点，这里的col 0; len 18; (18):--列的行号，从0开始，紧接着的就是列的长度以及列的值，那么这个值称之为separator key,这个separator key 可以区分真实的索引值，所以从这里我们也知道 branch block不会存储完整的索引值，只要能区分就行。也就是说，Oracle在 Branch block中只记录 索引键值的前缀，而不是所有值，是因为这样可以节约空间，从而能够存储更多的索引条目。同时，我们也能理解了为什么 查询使用 like '%xxx' 这种方法不会走Btree 索引，因为Branch block 存储的是前缀。 www.2cto.com   下面转储叶子节点块的内容 随便选一叶：leaf: 0x40f6fa 4257530 (246: nrow: 99 rrow: 99) [sql] sys@ORCL> select dbms_utility.data_block_address_file(4257530) fno,   2              dbms_utility.data_block_address_block(4257530) bno   3         from dual;   FNO        BNO   ---------- ----------   1      63226   sys@ORCL> oradebug setmypid   Statement processed.   sys@ORCL> alter system dump datafile 1 block 63226；   sys@ORCL> oradebug tracefile_name   /u01/app/oracle/admin/orcl/udump/orcl_ora_6177.trc   叶子节点的部分内容摘入如下： [sql] Block header dump:  0x0040f6fa   Object id on Block? Y   seg/obj: 0xcd86  csc: 0x00.a3506  itc: 2  flg: -  typ: 2 - INDEX   fsl: 0  fnx: 0x0 ver: 0x01   Itl           Xid                  Uba         Flag  Lck        Scn/Fsc   0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000   0x02   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.000a3506   www.2cto.com   Leaf block dump   ===============   header address 221234268=0xd2fc45c   kdxcolev 0   KDXCOLEV Flags = - - -   kdxcolok 0   kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y   kdxconco 2   kdxcosdc 0   kdxconro 99   kdxcofbo 234=0xea   kdxcofeo 4692=0x1254   kdxcoavs 4458   kdxlespl 0   kdxlende 0   kdxlenxt 0=0x0   kdxleprv 4257529=0x40f6f9   kdxledsz 0   kdxlebksz 8032   row#0[7992] flag: ------, lock: 0, len=40   col 0; len 30; (30):    73 75 6e 2f 74 6f 6f 6c 73 2f 74 72 65 65 2f 53 77 69 74 63 68 53 74 61 74   65 6d 65 6e 74   col 1; len 6; (6):  00 40 f3 25 00 0a   row#1[7953] flag: ------, lock: 0, len=39   col 0; len 29; (29):    73 75 6e 2f 74 6f 6f 6c 73 2f 74 72 65 65 2f 54 68 69 73 45 78 70 72 65 73   73 69 6f 6e   col 1; len 6; (6):  00 40 f0 74 00 31   row#2[7914] flag: ------, lock: 0, len=39   col 0; len 29; (29):    73 75 6e 2f 74 6f 6f 6c 73 2f 74 72 65 65 2f 54 68 69 73 45 78 70 72 65 73   73 69 6f 6e    www.2cto.com   col 1; len 6; (6):  00 40 f0 74 00 32   ............................   ............................   row#97[4727] flag: ------, lock: 0, len=35   col 0; len 25; (25):    79 43 62 43 72 53 75 62 53 61 6d 70 6c 69 6e 67 54 79 70 65 31 37 30 5f 54   col 1; len 6; (6):  00 40 f1 f1 00 0c   row#98[4692] flag: ------, lock: 0, len=35   col 0; len 25; (25):    79 43 62 43 72 53 75 62 53 61 6d 70 6c 69 6e 67 54 79 70 65 31 37 30 5f 54   col 1; len 6; (6):  00 40 f4 a2 00 10   ----- end of leaf block dump -----   和分支节点不同的值解析如下： kdxlespl 表示：当叶子节点被拆分时，未提交的事务数量 kdxlende 表示：被删除的索引条目数量 kdxlenxt 表示：当前叶子节点的下一个叶子节点的地址 kdxlprv  表示：当前叶子节点的上一个叶子节点的地址 kdxledsz 表示：被删除的空间 转储文件中接下来的部分就是索引条目部分。lock: 0 表示ITL中的锁信息 0表示没有被锁 ；len ：表示索引值长度 ；flag 表示 标记，如删除标记等。col 表示列号，从0开始 那么接下来就是索引的键值 以及 rowid中后三部分（相对文件号、块号、行号）即：col 0 是键值， col 1 是rowid。 也就是说，Leaf节点主要存储了完整的索引键值，以及相关索引键值的部分rowid(这个rowid去掉了data object number部分)，同时leaf 节点还存储了2个指针(DBA)，他们分别指向上一个leaf节点以及下一个leaf节点.这样叶子节点便是双向链表的结构。我们看到前面对B树索引的体系结构的描述，可以知道其为一个树状的立体结构。但对应到数据文件里的排列当然还是一个平面的形式，也就是像下面这样。因此，当oracle需要访问某个索引块的时候，势必会在这个结构上跳跃的。 2.4 聚簇因子 2.4.1 聚簇因子定义 是使用B树索引进行区间扫描的成本很重要因素，反映数据在表中分布的随机程度 聚簇因子的计算方法： ①扫描索引 ②比较某行的ROWID和前一行的ROWID，如果这两个ROWID不属于同一个数据块，那么聚簇因子增加1 ③整个索引扫描完毕后，就得到了该索引的集群因子 --对选择最优查询路径影响最大的只有列的选择率和聚簇因子 2.4.2 高聚簇因子的危害    例如，数据行1~10被分散存储在10个数据块与两个数据块相比较，虽然两种情况逻辑读取的数据行数（都是10行）是相同的，但在物理（I/O）读取的数据块数上却相差5倍。在关系型数据库中，不论在何种情况下，每次最少都需要读取一个数据块。尽管我们每次要求读取的是行，但是实际上每次读取的却是数据块。因此，如果能够在内存中命中我们所需要查询的数据行，则在很大程度上就能够减少物理I/O的数量。尽管在不同系统环境下会略有一些差异，但在一般情况下，从内存块上查询数据的速度比从磁盘块上查询数据的速度至少快30倍左右（有些可能数百倍），聚簇因子主要影响着索引的读取。我们假想下：加入有一个表t有1000万行，有10万个块,我们有个provcode(省)是索引，provcode的distinct有32个，那么如果我们取其中的一个省份如果按照平均来计算是不是就是1/32的数据，这个比例接近3%，很多人认为这个应该走provode索引，但是如果这个32个值是平均分布，也就是说很可能导致我们取其中一个省份，由于他分布在所有的数据块里面，导致我们相当于要读取整个表，这个性能是非常差的，这个时候全表就效果更好（这里有多块读等因数）。 2.4.3 如何降低聚簇因子 通过之前我们已经了解到，索引是将原表的某一列或某几列进行排序，从而达到加速查询的目的。那么不难看出，当原表的数据也是排序过的，这样的聚簇因子最低。但是我们不难发现让一个表完全有序基本是不可能的，因为每一列的排序几乎永远不会完全对应。所以，普遍方法是在插入时对需要建立索引的列order by。如果是已经存在的表，最好进行'rebuild'。 2.5 权衡是否创建索引的因素 2.5.1 应该建索引列的特点 1）在经常需要搜索的列上，可以加快搜索的速度； 2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构； 3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度； 4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。 5）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间； 6）在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。 2.5.2 不应该建索引列的特点 1）对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。 2）对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。 3）对于那些定义为blob数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。 4）当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。 3执行计划 3.1 执行计划简介 执行计划是一条sql在执行时扫描的先后顺序，它详细的向我们展示了一条sql从开始扫描到输出结果之间的所有过程。所以，掌握执行计划对sql的优化相当重要。 3.2 如何查看执行计划  首先创建EXPLAIN_PLAN表  不同版本的ORACLE，该表结构可能会不同。请使用的ORACLE中$ORACLE_HOME/rdbms/admin/utlxplan.sql去创建该表。   方法1：使用SQL*PLUS 的SET AUTOTRACE ，如表3-1所示： SQL>SETAUTOTRACEONEXPLAIN 执行sql，且仅显示执行计划 SQL>SETAUTOTRACEON STATISTICS 执行sql，且仅显示执行统计信息 SQL> SETAUTOTRACEON 执行sql，且显示执行统计信息和执行计划 SQL> SETAUTOTRACE TRACEONLY 仅显示执行计划与统计信息，无执行结果 SQL> SETAUTOTRACE OFF 关闭跟踪，显示计划与统计     表3-1 SQL> set autotrace on SQL> select count(*) from emp; COUNT(*) ---------- 14 执行计划 ---------------------------------------------------------- Plan hash value: 2937609675 ------------------------------------------------------------------- | Id  | Operation        | Name  | Rows  | Cost (%CPU)| Time    | ------------------------------------------------------------------- |  0 | SELECT STATEMENT |        |    1 |    1  (0)| 00:00:01 | |  1 |  SORT AGGREGATE  |        |    1 |            |          | |  2 |  INDEX FULL SCAN| PK_EMP |    14 |    1  (0)| 00:00:01 | ------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 305  recursive calls 0  db block gets 58  consistent gets 4  physical reads 0  redo size 527  bytes sent via SQL*Net to client 520  bytes received via SQL*Net from client 2  SQL*Net roundtrips to/from client 6  sorts (memory) 0  sorts (disk) 1  rows processed 方法2：使用PL/SQL Developer工具   PL/SQL Developer可以帮助我们显示执行计划，如图3-1所示 图3-1 PL/SQL Developer显示的执行计划 方法3：使用DBMS_XPLAN.DISPLAY(），方法如下： SQL> explain plan for select * from emp where sal>1000; 已解释。 SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time    | -------------------------------------------------------------------------- |  0 | SELECT STATEMENT  |      |    13 |  494 |    3  (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| EMP  |    13 |  494 |    3  (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 1 - filter("SAL">1000) 已选择13行。方法4：直接查看表：EXPLAIN_TABLE SELECT lpad(' ',level-1)||operation||' '||options||' '|| object_name "Plan" FROM plan_table CONNECT BY prior id = parent_id AND prior statement_id = statement_id START WITH id = 0 AND statement_id = '&1' ORDER BY id； 本人建议用第三种方法去查看执行计划。它有以下几个优点： ⒈它不会真正去执行一条sql，只是给出这条sql将要通过这种顺序去执行，告诉读者顺序是什么，这样与第一种方法比会大幅度降低执行计划生成时间，比如一个OLAP系统中，一个表有几亿甚至十几亿数据，再加上好几个表关联，一条sql跑下来有可能就是几个小时甚至一天，如果用第三种方法就只需要几秒。时间会相差好几个数量级。 ⒉它会显示出执行中的过滤条件，相比第二种方法来说，第三种方法可以告诉你一条sql在哪一步过滤了什么条件，这样可以更好的帮助优化人员定位sql瓶颈，从而找到方法去优化sql。 3.3 执行计划阅读顺序 想做好优化的基本就是需要看懂一个sql语句执行的扫描顺序，如果连顺序都不知道，优化根本无从谈起，因为你不知道本来的顺序，又何谈修改从而达到最优顺序？ 现今很多网站上都有阅读执行计划的文章，但是本人看完后发现其中有很多错误，所以先将正确的执行计划顺序分享出来。 先来看看这个执行计划，如图3-2所示： 图3-2 执行计划例子 网上很多说法都是最从最凹陷处开始执行，在这条执行计划中即是32。但是这是不正确的。在此需要先明白几个概念：父级，子集，同级。拿8、9、10这三条来说，8是9和10的父级，那么9和10是8的子级。而9和10则是同级。真正的规则是子级永远优先于父级执行。同级则靠近父级的优先执行。值得注意的是，靠近父级的与和它同级的子级相比也是靠近父级的先执行。如9会优先于11执行。 那么现在这条执行计划的执行顺序为9→12→11→10→8→16→17→15→14→13→7→18→6→29→32→31→30→28→34→33→27→36→35→26→37→25→38→24→23→22→21→20→19→5→40→39→4→41→3→42→2→1→0。 3.4 怎样修改执行计划 执行计划的修改大体分两种，一种是修改sql语句本身，从而达到改变执行计划的目的。第二种是加HINT来强制改变。第一种方法千变万化，无法选取具有代表性的方法，故而我们这里只讨论第二种方法。 3.4.1 HINT简介 ORACLE的HINT是用来提示ORACLE的优化器，以期选择用户期望的执行计划。在许多情况下，ORACLE默认的执行方式并不总是最优的，只不过我们平时大多数所操作的数据量比较小，好的执行计划与差的执行计划所消耗的时间上的差很少，用户感觉不到而已。但于对书写操作大数据量的SQL而言，其SQL的书写则需要先了解一下执行计划是否最优或满足生产需要。通常从开发环境迁移到生产环境下时，往往会出现此类情况。  例如：假设有一张客户表，在客户类别上有索引。如果想查找某一类别用户，而该类别用户占总数的比例高达90%，则此时采用全表扫描方式将会比索引扫描方式快。如果不使用HINT，ORACLE很可能会选择使用索引方式来执行。  使用HINT可以：  ⒈ 改变SQL中的表的关联顺序  ⒉ 改变SQL中的表的关联方式  ⒊ 使用并行来执行DML、DDL、以及SELECT语句  ⒋ 改变表的访问路径  ⒌ 其他等  HINT的书写方式如下：  {DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */ 如：  select /*+ index(emp)*/ count(*) from emp 3.4.2 几种常见的hint 3.4.2.1 FULL 1)功能： 用于指定对某个表进行全表扫描。语法如下： 原本： SQL> select * from emp where empno=134; 执行计划 ---------------------------------------------------------- Plan hash value: 2949544139 -------------------------------------------------------------------------------- ------ | Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time| -------------------------------------------------------------------------------- ------ |  0 | SELECT STATEMENT            |        |    1 |    38 |    1  (0)| 00:00:01 | |  1 |  TABLE ACCESS BY INDEX ROWID| EMP    |    1 |    38 |    1  (0)| 00:00:01 | |*  2 |  INDEX UNIQUE SCAN        | PK_EMP |    1 |      |    0  (0)| 00:00:01 | -------------------------------------------------------------------------------------- 加HINT后： SQL> select /*+ full(emp) */ * from emp where empno=134; 执行计划 ---------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time    | -------------------------------------------------------------------------- |  0 | SELECT STATEMENT  |      |    1 |    38 |    3  (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| EMP  |    1 |    38 |    3  (0)| 00:00:01 | -------------------------------------------------------------------------- 2)  使用场景： ?  如果查询的结果占全表的数据量的比例比较高（即选择率较高，其经验值 为>30%），则选择使用全表扫描的方式会比索引来得快。因为如果查询结 果字段不是索引字段的话，则将进行两次IO：一次执行索引的选择，另 一次根据其索引中存贮的ROWID来查询表中的结果字段值。 ?  另外一种情况也可能需要使用FULL提示。如两个表进行关联操作，你希 望其中一个表为全表扫描，另一表为索引扫描（如使用NESTED LOOPS 方式）. 3.4.2.2 INDEX 1)  功能： 与FULL相对应，用于指定对某个表进行索引扫描。 INDEX提示的可以不需要指定所使用的索引名，如果一个表有多个索引， 则系统会选择合适的一个。 语法如下： 原本： select empno from emp； 执行计划 ---------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time    | -------------------------------------------------------------------------- |  0 | SELECT STATEMENT  |      |    14 |  532 |    3  (0)| 00:00:01 | |  1 |  TABLE ACCESS FULL| EMP  |    14 |  532 |    3  (0)| 00:00:01 | -------------------------------------------------------------------------- 加HINT后： SQL> select /*+ index(emp) */ empno from emp; 执行计划 ---------------------------------------------------------- Plan hash value: 179099197 --------------------------------------------------------------------------- | Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time    | --------------------------------------------------------------------------- |  0 | SELECT STATEMENT |        |    14 |    56 |    1  (0)| 00:00:01 | |  1 |  INDEX FULL SCAN | PK_EMP |    14 |    56 |    1  (0)| 00:00:01 | --------------------------------------------------------------------------- 2)  使用场景： 查询结果占全表的比例较小或只返回几行，则需要使用INDEX提示。 如果查询的结果字段存在于所提示的索引中，则使用全索引扫描方式也可 能比全表扫描方式快。因为索引的数据量通常比表的数据量要小，因此其 IO的次数也会比扫描表少。如：(可以看出，下图的执行计划将不进行表 的扫描。) 3.4.2.3 USE_HASH 1)  功能 当需要进行多表关联操作时，选择使用全表扫描的方式在其中一个表的关 联字段上创建HASH表（该表一般选择较小的表，以便可以存于SGA内存中， 并达到提高探测的速度的目的），然后对另一表上进行全表扫描且以同样的算 法构建HASH表，同时探测基于第一个表中上HASH表，查找匹配的键值。 语法如下： 原本： SQL> select * from emp a,dept b where a.deptno=b.deptno; 执行计划 ---------------------------------------------------------- Plan hash value: 844388907 -------------------------------------------------------------------------------- -------- | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Ti me    | -------------------------------------------------------------------------------- -------- |  0 | SELECT STATEMENT            |        |    14 |  812 |    6  (17)| 00 :00:01 | |  1 |  MERGE JOIN                  |        |    14 |  812 |    6  (17)| 00 :00:01 | |  2 |  TABLE ACCESS BY INDEX ROWID| DEPT    |    4 |    80 |    2  (0)| 00 :00:01 | |  3 |    INDEX FULL SCAN          | PK_DEPT |    4 |      |    1  (0)| 00 :00:01 | |*  4 |  SORT JOIN                  |        |    14 |  532 |    4  (25)| 00 :00:01 | |  5 |    TABLE ACCESS FULL        | EMP    |    14 |  532 |    3  (0)| 00 :00:01 | -------------------------------------------------------------------------------- -------- 加HINT后： SQL> select /*+ USE_HASH(a,b) */ * from emp a,dept b where a.deptno=b.deptno 执行计划 ---------------------------------------------------------- Plan hash value: 615168685 --------------------------------------------------------------------------- | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time    | --------------------------------------------------------------------------- |  0 | SELECT STATEMENT  |      |    14 |  812 |    7  (15)| 00:00:01 | |*  1 |  HASH JOIN        |      |    14 |  812 |    7  (15)| 00:00:01 | |  2 |  TABLE ACCESS FULL| DEPT |    4 |    80 |    3  (0)| 00:00:01 | |  3 |  TABLE ACCESS FULL| EMP  |    14 |  532 |    3  (0)| 00:00:01 | --------------------------------------------------------------------------- 2)  使用场景： 如果相关联的表的在同一数量级，且数据量比较大。此时应选择该种方式， 而不应选择MERGE方式。 HASH JOIN的执行过程如下： a)  扫描其中的一个表，在关联字段上构建HASH表，该HASH表使用关联字段值作为其寻址方式。一般选择较小的表的作为第一个表 b)  扫描另一个关联表中的每一条记录，同样方式构建HASH记录，并 据此HASH记录中的关联字段的值去探测每一个表上的HASH表， 取该记录的关联的结果 c)  重复步骤b，直到第二个关联表扫描结束。