[转载]oracle索引类型及扫描方式大整理

原文地址：oracle索引类型及扫描方式大整理作者：Ryan

oracle索引类型及扫描方式大整理

1.         存储类型

索引在各种关系型数据库系统中都是举足轻重的组成部分，其对于提高检索数据的速度起至关重要的作用。在Oracle中，索引基本分为以下几种：B*Tree索引，反向索引，降序索引，位图索引，函数索引，interMedia全文索引等。本文主要就前6种索引进行分析。

　　首先给出各种索引的简要解释：

　　b*tree index：几乎所有的关系型数据库中都有b*tree类型索引，也是被最多使用的。其树结构与二叉树比较类似，根据rid快速定位所访问的行。

　　反向索引：反转了b*tree索引码中的字节，是索引条目分配更均匀，多用于并行服务器环境下，用于减少索引叶的竞争。

　　降序索引：8i中新出现的索引类型，针对逆向排序的查询。

　　位图索引：使用位图来管理与数据行的对应关系，多用于OLAP系统。

　　函数索引：这种索引中保存了数据列基于function返回的值，在select * from table where function（column）=value这种类型的语句中起作用。

 

B*Tree索引

　　B*Tree索引是最常见的索引结构，默认建立的索引就是这种类型的索引。B*Tree索引在检索高基数数据列（高基数数据列是指该列有很多不同的值）时提供了最好的性能。当取出的行数占总行数比例较小时B-Tree索引比全表检索提供了更有效的方法。但当检查的范围超过表的10%时就不能提高取回数据的性能。B-Tree索引是基于二叉树的，由分支块（branch block）和叶块（leaf block）组成。在树结构中，位于最底层底块被称为叶块，包含每个被索引列的值和行所对应的rowid。在叶节点的上面是分支块，用来导航结构，包含了索引列（关键字）范围和另一索引块的地址，如图26-1所示。

　　假设我们要找索引中值为80的行，从索引树的最上层入口开始，定位到大于等于50，然后往左找，找到第2个分支块，定位为75－100，最后再定位到叶块上，找到80所对应的rowid，然后根据rowid去读取数据块获取数据。如果查询条件是范围选择的，比如where column >20 and column <80，那么会先定位到第一个包含20的叶块，然后横向查找其他的叶块，直到找到包含80的块为止，不用每次都从入口进去再重新定位。

　　反向索引

　　反向索引是B*Tree索引的一个分支，它的设计是为了运用在某些特定的环境下的。Oracle推出它的主要目的就是为了降低在并行服务器（Oracle Parallel Server）环境下索引叶块的争用。当B*Tree索引中有一列是由递增的序列号产生的话，那么这些索引信息基本上分布在同一个叶块，当用户修改或访问相似的列时，索引块很容易产生争用。反向索引中的索引码将会被分布到各个索引块中，减少了争用。反向索引反转了索引码中每列的字节，通过dump（）函数我们可以清楚得看见它做了什么。举个例子：1，2，3三个连续的数，用dump()函数看它们在Oracle内部的表示方法。

SQL> select 'number',dump(1,16) from dual
  2  union all select 'number',dump(2,16) from dual
  3  union all select 'number',dump(3,16) from dual;

'NUMBE DUMP(1,16)
------ -----------------
number Typ=2 Len=2: c1,2 （1）
number Typ=2 Len=2: c1,3 （2）
number Typ=2 Len=2: c1,4 （3）

　　再对比一下反向以后的情况：

SQL> select 'number',dump(reverse(1),16) from dual
  2  union all select 'number',dump(reverse(2),16) from dual
  3  union all select 'number',dump(reverse(3),16) from dual;

'NUMBE DUMP(REVERSE(1),1
------ -----------------
number Typ=2 Len=2: 2,c1 （1）
number Typ=2 Len=2: 3,c1 （2）
number Typ=2 Len=2: 4,c1 （3）

　　我们发现索引码的结构整个颠倒过来了，这样1，2，3个索引码基本上不会出现在同一个叶块里，所以减少了争用。不过反向索引又一个缺点就是不能在所有使用常规索引的地方使用。在范围搜索中其不能被使用，例如，where column>value,因为在索引的叶块中索引码没有分类，所以不能通过搜索相邻叶块完成区域扫描。

 

函数索引

　　基于函数的索引也是8i以来的新产物，它有索引计算列的能力，它易于使用并且提供计算好的值，在不修改应用程序的逻辑上提高了查询性能。使用基于函数的索引有几个先决条件：

　　（1）必须拥有QUERY REWRITE（本模式下）或GLOBAL QUERY REWRITE（其他模式下）权限。

　　（2）必须使用基于成本的优化器，基于规则的优化器将被忽略。

　　（3）必须设置以下两个系统参数：

QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED

　　可以通过alter system set,alter session set在系统级或线程级设置，也可以通过在init.ora添加实现。

　　这里举一个基于函数的索引的例子：

SQL> create index test.ind_fun on test.testindex(upper(a));

　　索引已创建。

SQL> insert into testindex values('a',2);

　　已创建 1 行。

SQL> commit;

　　提交完成。

SQL> select *  FROM test.testindex where upper(a)='A';
A　　     B
-- ----------
a　　     2
Execution Plan
----------------------------------------------------------
   0　　SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (FULL) OF 'TESTINDEX'

　　(优化器选择了全表扫描)

--------------------------------------------------------------------
SQL> select *  FROM test.testindex where upper(a)='A';
A　　     B
-- ----------
a　　     2
Execution Plan
----------------------------------------------------------
   0　　SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=2 Card=
　　  1 Bytes=5)
   2    1     INDEX (RANGE SCAN) OF 'IND_FUN' (NON-UNIQUE) (Cost=1 Car
　　    d=1)(使用了ind_fun索引)

 

降序索引

    降序索引是8i里面新出现的一种索引，是B*Tree的另一个衍生物,它的变化就是列在索引中的储存方式从升序变成了降序，在某些场合下降序索引将会起作用。举个例子，我们来查询一张表并进行排序：

SQL> select * from test where a between 1 and 100 order by a desc，b asc;

    已选择100行。

Execution Plan
----------------------------------------------------------
   0    SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=400)
   1  0  SORT(ORDER BY)(Cost=2 Card=100 Bytes=400)
   2  1 INDEX (RANGE SCAN) OF 'IND_BT' (NON-UNIQUE) (Cost=2 Card=100 Bytes=400)

    这里优化器首先选择了一个索引范围扫描,然后还有一个排序的步骤。如果使用了降序索引，排序的过程会被取消。

SQL> create index test.ind_desc on test.testrev(a desc，b asc);

    索引已创建。

SQL> analyze index test.ind_desc compute statistics;

    索引已分析

    再来看下执行路径：

SQL> select * from test where a between 1 and 100 order by a desc，b asc;

    已选择100行。

Execution Plan（SQL执行计划，稍后会讲解如何使用）。
----------------------------------------------------------
   0    SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=400)

1  0 INDEX (RANGE SCAN) OF 'IND_DESC' (NON-UNIQUE) (Cost=2 Card=100 Bytes=400)

    我们看到排序过程消失了，这是因为创建降序索引时Oracle已经把数据都按降序排好了。

    另外一个需要注意的地方是要设置init.ora里面的compatible参数为8.1.0或以上，否则创建时desc关键字将被忽略。

    位图索引

    位图索引主要用于决策支持系统或静态数据，不支持行级锁定。位图索引最好用于低cardinality列（即列的唯一值除以行数为一个很小的值，接近零），例如又一个“性别”列，列值有“Male”，“Female”，“Null”等3种，但一共有300万条记录，那么3/3000000约等于0，这种情况下最适合用位图索引。

    位图索引可以是简单的（单列）也可以是连接的（多列），但在实践中绝大多数是简单的。在这些列上多位图索引可以与AND或OR操作符结合使用。位图索引使用位图作为键值，对于表中的每一数据行位图包含了TRUE（1）、FALSE（0）、或NULL值。位图索引的位图存放在B-Tree结构的页节点中。B-Tree结构使查找位图非常方便和快速。另外，位图以一种压缩格式存放，因此占用的磁盘空间比B-Tree索引要小得多。位图索引的格式如表26-1所示。

    表26-1  位图索引的格式
       行
值 1 2 3 4 5 6 7 8 9 10
Male 1 0 0 0 0 0 0 0 1 1
Female 0 1 1 1 0 0 1 1 0 0
Null 0 0 0 0 1 1 0 0 0 0

    如果搜索where gender=’Male’,要统计性别是”Male”的列行数的话，Oracle很快就能从位图中找到共3行即第1，9，10行是符合条件的；如果要搜索where gender=’Male’ or gender=’Female’的列的行数的话，也很容易从位图中找到共8行即1，2，3，4，7，8，9，10行是符合条件的。如果要搜索表的值的话，那么Oracle会用内部的转换函数将位图中的相关信息转换成rowid来访问数据块。

 

2.         索引扫描方式

这里介绍CBO根据统计数值得知进行全Oracle索引扫描比进行全表扫描更有效时，才进行全Oracle索引扫描，而且此时查询出的数据都必须从索引中可以直接得到。

学习Oracle时，你可能会遇到Oracle索引扫描问题，这里将介绍Oracle索引扫描问题的解决方法，在这里拿出来和大家分享一下。根据索引的类型与where限制条件的不同，有4种类型的Oracle索引扫描：

 

　　◆索引唯一扫描(index unique scan)

 

　　◆索引范围扫描(index range scan)

 

　　◆索引全扫描(index full scan)

 

　　◆索引快速扫描(index fast full scan)

 

　　(1) 索引唯一扫描(index unique scan)

 

　　通过唯一索引查找一个数值经常返回单个ROWID。如果该唯一索引有多个列组成(即组合索引)，则至少要有组合索引的引导列参与到该查询中，如创建一个索引：create index idx_test on emp(ename, deptno, loc)。则select ename from emp where ename = ‘JACK’ and deptno = ‘DEV’语句可以使用该索引。如果该语句只返回一行，则存取方法称为索引唯一扫描。而select ename from emp where deptno = ‘DEV’语句则不会使用该索引，因为where子句种没有引导列。如果存在UNIQUE 或PRIMARY KEY 约束(它保证了语句只存取单行)的话，Oracle经常实现唯一性扫描。

 

　　使用唯一性约束的例子：

 

　　(2) 索引范围扫描(index range scan)

 

　　使用一个索引存取多行数据，同上面一样，如果索引是组合索引，如(1)所示，而且select ename from emp where ename = ‘JACK’ and deptno = ‘DEV’语句返回多行数据，虽然该语句还是使用该组合索引进行查询，可此时的存取方法称为索引范围扫描。在唯一索引上使用索引范围扫描的典型情况下是在谓词(where限制条件)中使用了范围操作符(如>、<、<>、>=、<=、between)

 

　　使用索引范围扫描的例子：

 

　　在非唯一索引上，谓词col = 5可能返回多行数据，所以在非唯一索引上都使用索引范围扫描。

 

　　使用index rang scan的3种情况：

 

　　(a) 在唯一索引列上使用了range操作符(> < <> >= <= between)

 

　　(b) 在组合索引上，只使用部分列进行查询，导致查询出多行

 

　　(c) 对非唯一索引列上进行的任何查询。

 

　　(3) 索引全扫描(index full scan)

 

　　与全表扫描对应，也有相应的全Oracle索引扫描。在某些情况下，可能进行全Oracle索引扫描而不是范围扫描，需要注意的是全Oracle索引扫描只在CBO模式下才有效。 CBO根据统计数值得知进行全Oracle索引扫描比进行全表扫描更有效时，才进行全Oracle索引扫描，而且此时查询出的数据都必须从索引中可以直接得到。

 

　　全Oracle索引扫描的例子：

 

　　(4) 索引快速扫描(index fast full scan)

 

　　扫描索引中的所有的数据块，与 index full scan很类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是以排序顺序被返回。在这种存取方法中，可以使用多块读功能，也可以使用并行读入，以便获得最大吞吐量与缩短执行时间。

 

　　索引快速扫描的例子：

 

 

index full scan和Index Fast Full Scan（全索引扫描和快速全索引扫描）

index full scan和index fast full scan是指同样的东西吗？答案是no。两者虽然从字面上看起来差不多，但是实现的机制完全不同。我们一起来看看两者的区别在哪里?
首先来看一下IFS,FFS能用在哪里：在一句sql中，如果我们想搜索的列都包含在索引里面的话，那么index full scan 和 index fast full scan 都可以被采用代替full table scan。比如以下语句：
　　
　　SQL> CREATE TABLE TEST AS SELECT * FROM dba_objects WHERE 0=1;
　　
　　SQL> CREATE INDEX ind_test_id ON TEST(object_id);
　　
　　SQL> INSERT INTO TEST
　　SELECT　 *
　　FROM dba_objects
　　WHERE object_id IS NOT NULL AND object_id > 10000
　　ORDER BY object_id DESC;
　　
　　17837 rows created.
　　
　　SQL> analyze table test compute statistics for table for all columns for all indexes;
　　
　　Table analyzed.
　　SQL> set autotrace trace;
　　
　　SQL> select object_id from test;
　　
　　17837 rows selected.
　　
　　Execution Plan
　　----------------------------------------------------------
　　　 0　　　SELECT STATEMENT ptimizer=CHOOSE (Cost=68 Card=17837 Bytes=71348)
　　　 1　　0　 TABLE ACCESS (FULL) OF 'TEST' (Cost=68 Card=17837 Bytes=71348)
　　
　　这时候Oracle会选择全表扫描，因为 object_id 列默认是可以为null的，来修改成 not null:
　　
　　
　　SQL>alter table test modify(object_id not null);
　　
　　SQL> select object_id from test;
　　
　　17837 rows selected.
　　
　　Execution Plan
　　----------------------------------------------------------
　　　 0　　　SELECT STATEMENT ptimizer=CHOOSE (Cost=11 Card=17837 Bytes=71348)
　　　 1　　0　 INDEX (FAST FULL SCAN) OF 'IND_TEST_ID' (NON-UNIQUE) (Cost=11 Card=17837 Bytes=71348)
　　
　　当然我们也可以使用index full scan:
　　
　　
　　SQL> select object_id from test;
　　
　　17837 rows selected.
　　
　　Execution Plan
　　----------------------------------------------------------
　　　 0　　　SELECT STATEMENT ptimizer=CHOOSE (Cost=41 Card=17837 Bytes=71348)
　　　 1　　0　 INDEX (FULL SCAN) OF 'IND_TEST_ID' (NON-UNIQUE) (Cost=101 Card=17837 Bytes=71348)
　　
　　我们看到了两者都可以在这种情况下使用，那么他们有什么区别呢？有个地方可以看出两者的区别，来看一下两者的输出结果，为了让大家看清楚一点，我们只取10行。
　　
　　INDEX FAST FULL SCAN
　　
　　SQL> select object_id from test where rownum<11;
　　
　　 OBJECT_ID
　　----------
　　　　 66266
　　　　 66267
　　　　 66268
　　　　 66269
　　　　 66270
　　　　 66271
　　　　 66272
　　　　 66273
　　　　 66274
　　　　 66275
　　10 rows selected.
　　
　　
　　INDEX FULL SCAN
　　
　　SQL> select object_id from test　where rownum<11;
　　
　　 OBJECT_ID
　　----------
　　　　 10616
　　　　 12177
　　　　 12178
　　　　 12179
　　　　 12301
　　　　 13495
　　　　 13536
　　　　 13539
　　　　 13923
　　　　 16503
　　10 rows selected.
　　
　 　可以看到两者的结果完全不一样，这是为什么呢？这是因为当进行index full scan的时候oracle定位到索引的root block，然后到branch block（如果有的话），再定位到第一个leaf block, 然后根据leaf block的双向链表顺序读取。它所读取的块都是有顺序的，也是经过排序的。
　　
　　而index fast full scan则不同，它是从段头开始，读取包含位图块，root block,所有的branch block, leaf block，读取的顺序完全有物理存储位置决定，并采取多块读，没次读取db_file_multiblock_read_count个块。

使用这两种索引扫描需要表的索引字段至少有一个是not null限制。

快速全索引扫描比普通索引扫描速度快是因为快速索引扫描能够多块读取，并且能并行处理。

普通快速索引扫描可以减少排序操作。

6）Index Join索引连接

我们都知道表与表之间可以连接，那么索引与索引之间呢？当然也可以。索引连接是一种索引之间的hash连接，当查找的字段都已经包括在索引中时，就不需要去访问表的内容，直接通过访问多个索引就能得到结果。Index join只能在CBO使用的情况下进行。

 

 

 

 

 

3.         索引散记

3.1优化器模式
   ORACLE的优化器共有3种:
   a.  RULE (基于规则)   b. COST (基于成本)  c. CHOOSE (选择性)
   为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须定期更新统计信息，以保证数据库中的对象统计信息(object statistics)的准确性.
   如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关. 如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器。

3.2访问Table的方式
ORACLE 采用两种访问表中记录的方式:
a.  全表扫描 
      全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描。
    
b.  索引扫描
   你可以采用基于ROWID的访问方式情况,提高访问表的效率, ROWID包含了表中记录的物理位置信息.ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.

其中ORACLE对索引又有两种访问模式.
a)索引唯一扫描 ( INDEX UNIQUE SCAN)
大多数情况下, 优化器通过WHERE子句访问INDEX.
例如:
表LOADING有两个索引 : 建立在LOADING列上的唯一性索引LOADING_PK和建立在MANAGER列上的非唯一性索引IDX_MANAGER. 
SELECT loading  
FROM LOADING
WHERE LOADING = ‘ROSE HILL’;
   在内部 , 上述SQL将被分成两步执行, 首先 , LOADING_PK 索引将通过索引唯一扫描的方式被访问 , 获得相对应的ROWID, 通过ROWID访问表的方式执行下一步检索.
   如果被检索返回的列包括在INDEX列中,ORACLE将不执行第二步的处理(通过ROWID访问表). 因为检索数据保存在索引中, 单单访问索引就可以完全满足查询结果. 
   下面SQL只需要INDEX UNIQUE SCAN 操作.
       SELECT LOADING
       FROM  LOADING
WHERE LOADING = ‘ROSE HILL’;
 
  b)索引范围查询(INDEX RANGE SCAN)
     适用于两种情况:
1. 基于一个范围的检索
2. 基于非唯一性索引的检索
 例1:
      SELECT LOADING
      FROM  LOADING
WHERE LOADING LIKE ‘M%’;
 
WHERE子句条件包括一系列值, ORACLE将通过索引范围查询的方式查询LODGING_PK . 由于索引范围查询将返回一组值, 它的效率就要比索引唯一扫描
低一些.  
例2:
      SELECT LOADING
      FROM  LOADING
WHERE MANAGER = ‘BILL GATES’;
这个SQL的执行分两步, IDX_MANAGER的索引范围查询(得到所有符合条件记录的ROWID) 和下一步同过ROWID访问表得到LOADING列的值. 由于IDX_MANAGER是一个非唯一性的索引,数据库不能对它执行索引唯一扫描. 
 
  由于SQL返回LOADING列,而它并不存在于IDX_MANAGER索引中, 所以在索引范围查询后会执行一个通过ROWID访问表的操作. 
  WHERE子句中, 如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始, 索引将不被采用.
SELECT LOADING
      FROM  LOADING
WHERE MANAGER LIKE ‘％HANMAN’;
在这种情况下，ORACLE将使用全表扫描.


3.3 SQL调优的本质就是调整执行计划。
在好多情况下，oracle自动选择的执行计划并不是最优的，这时需要我们人工去干预。(什么是执行计划?)


对SQL调优基本步骤：
a) 捕获SQL语句
b) 产生SQL语句的执行计划；
c) 验证统计信息(SQL语句涉及到的表格是否做过分析)，表格信息(结果集的记录数，索引)，字段上面数据分布特点
d) 通过手工收集到的信息，形成自己理想的执行计划。
e) 如果做过分析，则重新分析相关表格或者做柱状图分析。
f) 如果没有做过分析，则通过尝试不同的Hint，从而获得合适的执行计划。
g) 当我们正常无法调优到位时，可以打开10053事件打开优化器的跟踪，看看Oracle如何选择的.
alter session set events='10053 trace name context forever,level 2';

3.4 如何捕获SQL语句
捕获SQL语句的方法有如下几种：
1．SQL TRACE或10046跟踪某个模块。
2．PERFSTAT性能统计包，使用方法见附录二。
3．V＄SQL，V＄SESSION_WAIT，V＄SQL_TEXT
3.5 如何查看执行计划
查看SQL语句的执行计划有以下几种：
1．Set autotrace on(set autotrace traceonly exp)
2．Explain plan for …..
@?/rdbms/admin/utlxpls.sql
3．V＄SQL_PLAN视图
column operation format a16 
column "Query Plan" format a60 
column options format a15 
column object_name  format a20 
column id  format 99 

select id,lpad(' ',2*(level-1))||operation||' '||options||' '||object_name||' ' 
       ||decode(id,0,'Cost = '||position) "Query Plan" 
from (select * 
from v＄sql_plan  
where address='&a') sql_plan 
start with id = 0 
connect by prior id = parent_id
/

4．第三方工具，如pl/sql developer,TOAD

3.6 SQL语句主要的连接方法

a) Nested-loop join
适合于小表(几千条，几万条记录)与大表做联接
在联接列上有索引。

分内表和外表(驱动表)，靠近from子句的是内表。从效率上讲，小表应该作外表，大表应该作内表，即大表查询时走索引。

COST= Access cost of A(驱动表) + (access cost of B * number of rows from A)

成本计算方法：
设小表100行，大表100000行。

两表均有索引：
如果小表在内，大表在外(驱动表)的话，则扫描次数为：
100000+100000*2 (其中2表示IO次数，一次索引，一次数据)
如果大表在内，小表在外(驱动表)的话，则扫描次数为：
100+100*2.

两表均无索引：
如果小表在内，大表在外的话，则扫描次数为：
100000+100*100000
如果大表在内，小表在外的话，则扫描次数为：
100+100000*100

注意：如果一个表有索引，一个表没有索引，ORACLE会将没有索引的表作驱动表。如果两个表都有索引，则外表作驱动表。如果两个都没索引的话，则也是外表作驱动表。

基本的执行计划如下所示：
NESTED LOOPS
           TABLE ACCESS (BY ROWID)  OF  our_outer_table
                   INDEX (..SCAN) OF outer_table_index(….)
           TABLE ACCESS (BY ROWID)  OF  our_inner_table
             INDEX (..SCAN) OF inner_table_index(….)

b) Hash join 

适合于大表与大表，小表(几十万，几百万)与大表之间的联连。
联接列上不需要索引。

基本执行计划如下：
HASH JOIN
              TABLE ACCESS (….)  OF  tableA
              TABLE ACCESS (….)  OF  tableB

cost= (access cost of A * number of hash partitions of B) + access cost of B

可以看出主要成本在于A表是否可以被Cache。Hash_area_size的大小将决定Hash Join的主要成本。可以看出Hash Join的成本和返回集合并没有直接的关系，所以当返回结果集比较大的时候一般具有较好的性能。

为了加快hash join的速度，可以调大hash_area_size和pga_aggregate_target（默认为25M）的值。


c) Sort Merge join

每一个Row Source在Join列上均排序。
然后两个排序后的Row Source合并后，作一个结果集返回。
Sort/Merge Join仅仅对equal Join有效。

基本执行计划
MERGE (JOIN)
        SORT (JOIN) 
                 TABLE ACCESS (….)  OF  tableA
        SORT (JOIN) 
                 TABLE ACCESS (….)  OF  tableB

cost= access cost of A + access cost of B +(sort cost of A + sort cost of B)

可以看出Sort的成本是Merge Join的主要构成部分。这样sort_area_size的大小将很大程度决定Merge Join的大小。同样如果A表或者B表已经经过排序的，那么Merge Join往往具有很好的性能。其不会走索引。

没有驱动表的概念，即时响应能力较差。



3.7 一般情况下最常见的5种问题

1. Statement not written for indexes 25%
2. Indexes are missing or inappropriate 16%
3. Use of single-column index merge 15%
4. Misuse of nested loop, sort merge, or hash join 12%
5. Misuse of IN, EXISTS, NOT IN, NOT EXISTS, or table joins 8%

不过在我们这里，最常见的问题是在第2条，第3条，第4条。

1． Statement not written for indexes
类似于这样的：
SELECT account_name, trans_date, amount 
FROM transaction 
WHERE SUBSTR(account_name,1,7) = ' CAPITAL'; 

WHERE account_name LIKE 'CAPITAL%'; 

Account_date 日期

To_char(Account_date,’YYYY-MM-DD:HH24:MI:SS’)=’200508XXX’;

Account_date=to_date(‘200508….’,’yyyy-mm-dd);


2．Indexes are missing or inappropriate

例如REP_C021中有这样一句：
select SUBSIDIARYID,260,'    300电话卡',
   sum(decode(feetype, 1, ceil(duration / 60))) +
         sum(decode(feetype, 0, ceil(duration / 60))),
         sum(decode(feetype, 1, ceil(duration / 60))),
         sum(decode(feetype, 0, ceil(duration / 60))),0
    from cardsusage200508 a, service b
   where a.caller = b.servicecode and
         (b.property = i_property or i_property is null) and 
         a.cdrtype = 102
   group by SUBSIDIARYID, 260, '    300电话卡';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   SORT (GROUP BY)
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'CARDSUSAGE200508'
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'SERVICE'
   5    4         INDEX (UNIQUE SCAN) OF 'SERVICE_CODE'

我们取其中的select语句进行调优。在调整之前，原select语句需要6分钟左右。

12:19:20 SQL> select cdrtype,count(*) from cardsusage200508
12:20:12   2  group by cdrtype;

CDRT   COUNT(*)
---- ----------
102         637
106     1973757
107     2390097
112       46016
113          20

针对cardsuage200508表格的特性，我们在CDRTYPE字段上建立一个位图索引CARDSUSAGE_CDRTYPE_BTIDX。
将SQL语句加上以下Hint：
  select 
         SUBSIDIARYID,260,'    300电话卡',
         sum(decode(feetype, 1, ceil(duration / 60))) +
         sum(decode(feetype, 0, ceil(duration / 60))),
         sum(decode(feetype, 1, ceil(duration / 60))),
         sum(decode(feetype, 0, ceil(duration / 60))),0
    from cardsusage200508  a, service b
   where a.caller = b.servicecode and
         (b.property = i_property or i_property is null) and 
         a.cdrtype = 102
   group by SUBSIDIARYID, 260, '    300电话卡';
这样调整后，只需要几秒钟即可出来。

3.  Use of single-column index merge
复合索引有的时候比单列索引效率更高。根据where子句中的具体情况，有 时可以建立复合索引。例如：
 select a.AccountNum,a.ChargeID,a.Total,b.ItemID,
      b.Amount,c.billingcycle
  from charge_bill a, chargedetail_bill b, Account c
 where a.AccountNum > 1 and a.AccountNum <= 1969618 and
       a.status = '0' and a.InvoiceID is null and c.paymentmethod != '7' and
       a.Total > 0 and a.AccountNum = c.AccountNum and
       a.ChargeID = b.ChargeID
 order by a.AccountNum, a.ChargeID, b.ItemID;
这样的SQL语句执行需要3分27秒。

我们做了以下优化：
在charge_bill表格的accountnum,status,total,invoiceid列上建立一个复合索引。这样上述SQL语句需要40秒左右。

Resume Service过程中有这么一句：
SELECT NVL(SUM(A.FEE),0)   
FROM ACCOUNTBALANCE A,INVOICE B  
WHERE A.OBJECTID = B.INVOICEID  AND A.ACCOUNTNUM = :b1 
AND B.BILLINGBEGINDATE < TO_DATE(:b2,'yyyymmdd');
该语句需要执行大概72000次。整个过程执行大概需要100分钟左右。

将:b1以具体的值代替，这条SQL语句执行很快，大概0.1秒左右。

我们做了以下优化：
在invoiceid,billingbegindate列上创建了一个索引idx_invoice_hc。
将上述SQL语句改成：
select   nvl(sum(a.fee),0)
from accountbalance a,invoice b
where a.objectid=b.invoiceid  and a.accountnum=m_accountnum
and b.billingbegindate<to_date(m_date,'yyyymmdd');

这样一来，该过程的执行时间快的时候大概在10分钟左右，慢的时候(IO异常紧张的时)大概在30分钟左右。


4. Misuse of nested loop, sort merge, or hash join
表格之间的连接方式和连接顺序都将极大的影响SQL语句的性能。这种问题在平时最常见。ORACLE在处理5张或5张以上的表格的连接时候，很容易出问题。一般情况下，谨记前面表格之间的连接原则，即可以处理此类问题。

   例如：
select b.SUBSIDIARYID,
       c.paymentmethod || ':' || nvl(subscribertype, '9999999'),
       'gsm',count(*),sum(decode(untelLOCALCHARGE,
                  0,decode(duration,0,1,
                         decode(sign(duration - 1800),
                                1, 2 + trunc((duration - 1201) / 600),
                                2)), trunc((duration + 599) / 600))),
       sum(nvl(GSMCHARGE, 0)),nvl(property, '0'),
       SUM(trunc((duration + 599) / 600))
  from  rt_untelecomusage a ,service b, account c 
 where a.starttime >
       to_date(to_char(add_months(to_date('200508 ', 'YYYYMM'), -1),
                       'YYYYMM') || '20235959',
               'YYYYMMDDHH24MISS') and
       a.starttime < to_date('200508 ' || '21', 'YYYYMMdd') and
       gsmcharge > 0 and a.serviceid = b.serviceid and
       b.accountnum = c.accountnum
 group by b.SUBSIDIARYID,
          c.paymentmethod || ':' || nvl(subscribertype, '9999999'),
          'gsm',nvl(property, '0'); 
该语句原先需要4，5个小时左右。

优化：
alter session set hash_area_size=300000000;

select   b.SUBSIDIARYID,c.paymentmethod || ':' || nvl(subscribertype, '9999999'),
     'gsm',count(*), sum(decode(untelLOCALCHARGE,0,decode(duration,0, 1,
        decode(sign(duration - 1800), 1,2 + trunc((duration - 1201) / 600), 2)),
     trunc((duration + 599) / 600))),sum(nvl(GSMCHARGE, 0)),
       nvl(property, '0'),SUM(trunc((duration + 599) / 600))
  from service b, account c,untelecomusage_200508  a 
 where a.starttime >
       to_date(to_char(add_months(to_date('200508', 'YYYYMM'), -1),
                       'YYYYMM') || '20235959',
               'YYYYMMDDHH24MISS') and
       a.starttime < to_date('200508' || '21', 'YYYYMMdd') and
       gsmcharge > 0 and a.serviceid = b.serviceid and
       b.accountnum = c.accountnum
 group by b.SUBSIDIARYID,c.paymentmethod || ':' || nvl(subscribertype, '9999999'),'gsm',nvl(property, '0');  

这样优化后，只需要40分钟左右即可。

3.8 案例
1． 循环Update操作

  以下过程太慢了， 半个小时连5000条记录都未处理，总 共有7万多条。
declare
    cursor c1 is 
    select caller 
    from zxx_sms_step where chargemonth=200504 and fee is null;
    icnt number;
begin
icnt:=0;
for m_c1 in c1 loop
update zxx_sms_step a set fee=
(select nvl(sum(pascharge),0) from ipasimport_200504 where caller=m_c1.caller and pastag in (1243,1251))
where caller=m_c1.caller and chargemonth=200504;
icnt:=icnt+1;
if icnt=500 then
exit;
end if;
end loop;
end;

   这样的SQL语句，建议先将update中的子查询生成一张中间表，然后再update。
alter session set hash_area_size=400000000 ;

select  b.caller,nvl(sum(a.pascharge),0) from ipasimport_200504 a,zxx_sms_step b 
where b.chargemonth=200504 and b.fee is null 
and a.caller=b.caller and a.pastag in (1243,1251) 
group by b.caller;
 这样10分钟不到就可产生中间表，然后再update只需几分钟即可。


2． 部分表格未做统计信息分析

网通OA系统自从oracle服务器从pc服务器上迁到小型机上后，其CPU利用率经常冲到很高。而其中每一个进程在某个瞬间将占用40%左右的CPU。这些进程都是通过jdbc thin client 连过来的。

通过抓取其sql_text，发现以下两条SQL语句不正常。
1.
 SQL>  select D.flow_inid,D.step_inco,D.deal_man,D.agen_men,D.time_set,D.peri_man,
  2   S2.fsub_set,S2.fsub_id,F.mtbl_stru,F.doc_name,F.svr_name 
  3   from deal_info D,step_inst S1,step_def S2,flow_inst F 
  4   where D.step_inco=S1.step_inco and S1.flow_id=S2.flow_id 
  5   and S1.step_code=S2.step_code and S1.flow_inid=F.flow_inid and D.step_type=5
  6   and D.fsub_flag is not null and D.fsub_flag=1 and rownum<=1;

其执行计划和统计信息如下：

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=1 Bytes=1077)
   1    0   COUNT (STOPKEY)
   2    1     NESTED LOOPS (Cost=22 Card=1 Bytes=1077)
   3    2       NESTED LOOPS (Cost=21 Card=1 Bytes=360)
   4    3         NESTED LOOPS (Cost=20 Card=1 Bytes=150)
   5    4           TABLE ACCESS (FULL) OF 'STEP_INST' (Cost=2 Card=9  Bytes=153)
   6    4           TABLE ACCESS (BY INDEX ROWID) OF 'DEAL_INFO' (Cost=2 Card=1 Bytes=133)
   7    6             INDEX (RANGE SCAN) OF 'DEAL_INFO_STEP_INCO' (NON-UNIQUE) (Cost=2 
   8    3         TABLE ACCESS (BY INDEX ROWID) OF 'FLOW_INST' (Cost=1 Card=1 Bytes=210)
   9    8           INDEX (UNIQUE SCAN) OF 'PK_FLOW_INST' (UNIQUE)
  10    2       TABLE ACCESS (BY INDEX ROWID) OF 'STEP_DEF' (Cost=1 Card=1 Bytes=717)
  11   10         INDEX (UNIQUE SCAN) OF 'STEP_DEF_PK11119358638593' (UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     270626  consistent gets
        273  physical reads
          0  redo size
       1079  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

这条SQL语句执行的时间也不长，就几秒钟，但是我们看到consistent gets很高有27万多，这个操作就是消耗CPU的祸首。从执行计划来看，其执行计划显然不可理，问题出在表格的连接顺序上面，应该是deal_info表格做为驱动表先访问。

检查这些表格的统计分析，发现step_def表格未做分析，对该表格做统计信息分析，并对deal_info表做柱状图分析后：
analyze table deal_info compute statistics for all indexed columns;

其执行计划正是我们所想要的，同时consistent gets也只有200左右，该操作所消耗的CPU也下降到了1%。

2.表格的柱状图信息没有分析：
SELECT SO.SO_NBR, so_type.name,STATUS.STS_WORDS, SO.REMARKS, SO.CHECK_TYPE,CTRL_ASGN.DISPATCHED_DATE, 
CTRL_ASGN.PRE_ALARM_DATE, CTRL_ASGN.ALARM_DATE
from SO,SO_HANDLE, CTRL_ASGN,so_type,status 
WHERE SO_HANDLE.SO_NBR=SO.SO_NBR AND SO.SO_NBR=CTRL_ASGN.SO_NBR 
AND SO_HANDLE.HANDLE_TYPE_ID=1017
and so.so_type_id=so_type.so_type_id and so.PRIORITY=status.sts_id and status.table_name='SO'
 AND STATUS.column_name ='PRIORITY' AND SO_HANDLE.WORK_AREA_ID= 300101
AND SO.STATE= 'B' AND SO.HALT ='N'
AND CTRL_ASGN.STATE = 'B'
AND CTRL_ASGN.STS = 'D';

该SQL语句执行时间要2分钟左右。
执行计划如下：
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   NESTED LOOPS
   2    1     NESTED LOOPS
   3    2       NESTED LOOPS
   4    3         NESTED LOOPS
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'STATUS'
   6    5             INDEX (RANGE SCAN) OF 'PK_STATUS' (UNIQUE)
   7    4           TABLE ACCESS (BY INDEX ROWID) OF 'CTRL_ASGN'
   8    7             INDEX (RANGE SCAN) OF 'CTRL_ASGN_0002'
   9    3         TABLE ACCESS (BY INDEX ROWID) OF 'SO'
  10    9           INDEX (UNIQUE SCAN) OF 'PK_SO' (UNIQUE)
  11    2       TABLE ACCESS (BY INDEX ROWID) OF 'SO_TYPE'
  12   11         INDEX (UNIQUE SCAN) OF 'PK_SO_TYPE' (UNIQUE)
  13    1     TABLE ACCESS (BY INDEX ROWID) OF 'SO_HANDLE'
  14   13       INDEX (RANGE SCAN) OF 'PK_SO_HANDLE' (UNIQUE)

我们收集表格信息和结果集的信息:
SQL> select count(*) from CTRL_ASGN;
  COUNT(*)
----------
   1832469
SQL> select count(*) from status;
  COUNT(*)
----------
      1718

SQL> select count(*) from so;
  COUNT(*)
----------
    300296

SQL> select count(*) from so_type;
  COUNT(*)
----------
       265

SQL> select count(*) from so_handle;
  COUNT(*)
----------
   1296263   

select count(*) from ctrl_asgn where  CTRL_ASGN.STATE = 'B' AND CTRL_ASGN.STS = 'D';
  COUNT(*)
----------
    331490
       
select count(*) from so where SO.STATE= 'B' AND SO.HALT ='N';
  COUNT(*)
----------
       361
       
select count(*) from so_handle where SO_HANDLE.HANDLE_TYPE_ID=1017 and SO_HANDLE.WORK_AREA_ID= 300101;
  COUNT(*)
----------
     30086

通过对上面这些信息进行分析，我们可以发现这个问题也可以归结为表格之间的连接顺序上面。通过将SO表做柱状图分析后，该SQL语句只需1秒钟即可出来。
Analyze table so compute statistics for all indexed columns;

执行计划变成如下：
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=273 Card=32 Bytes=3936)
   1    0   NESTED LOOPS (Cost=273 Card=32 Bytes=3936)
   2    1     NESTED LOOPS (Cost=153 Card=30 Bytes=2730)
   3    2       HASH JOIN (Cost=33 Card=30 Bytes=2130)
   4    3         NESTED LOOPS (Cost=31 Card=30 Bytes=1620)
   5    4           TABLE ACCESS (FULL) OF 'STATUS' (Cost=2 Card=1 Bytes=25)
   6    4           TABLE ACCESS (BY INDEX ROWID) OF 'SO' (Cost=29 Card=59 Bytes=1711)
   7    6             INDEX (RANGE SCAN) OF 'SO_0003' (NON-UNIQUE) (Cost=2 Card=59)
   8    3         TABLE ACCESS (FULL) OF 'SO_TYPE' (Cost=1 Card=128 Bytes=2176)
   9    2       TABLE ACCESS (BY INDEX ROWID) OF 'SO_HANDLE' (Cost=4 Card=280 Bytes=5600)
  10    9         INDEX (RANGE SCAN) OF 'PK_SO_HANDLE' (UNIQUE) (Cost=3 Card=280)
  11    1     TABLE ACCESS (BY INDEX ROWID) OF 'CTRL_ASGN' (Cost=4 Card=13620 Bytes=435840)
  12   11       INDEX (RANGE SCAN) OF 'CTRL_ASGN_0003' (NON-UNIQUE) (Cost=2 Card=13620)



3． Not exists的使用
--停机保号用户数(除欠费)
select 'XJ'||1||'180','停机保号用户数',count(distinct serviceid),1,'200509',groupid from cbq_lch_usage0 
where subsidiaryid=1 and subid<>'02'  and subid<>'06' and status='7' and 
serviceid not in (select serviceorderid from cbq_qf_usage1  where status<>'3' and status <> '8') 
group by 'XJ'||1||'180','停机保号用户数',1,'200509',groupid ;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   SORT (GROUP BY)
   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'CBQ_LCH_USAGE0'
   4    2       TABLE ACCESS (FULL) OF 'CBQ_QF_USAGE1'

Elapsed: 13:48:26.85

调整：
not in 改成not exists
create index idx_serviceorderid on cbq_qf_usage1(serviceorderid) nologging;

select 'XJ'||1||'180','停机保号用户数',count(distinct serviceid),1,'200509',a.groupid 
from cbq_lch_usage0 a
where a.subsidiaryid=1 and a.subid<>'02'  and a.subid<>'06' and a.status='7' 
and not exists(select 1 from cbq_qf_usage1 b where status<>'3' and status<>'8' and a.serviceid=b.serviceorderid)
group by 'XJ'||1||'180','停机保号用户数',1,'200509',a.groupid;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   SORT (GROUP BY)
   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'CBQ_LCH_USAGE0'
   4    2       TABLE ACCESS (BY INDEX) OF 'CBQ_QF_USAGE1'
   5    4         INDEX (RANGE SCAN) OF 'IDX_SERVICEORDERID' 

Elapsed: 00:00:01.36


3.9 其他
1．SELECT子句中避免使用 ‘ * ‘
当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*’ 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间. 
2．用TRUNCATE替代DELETE
3．使用表的别名(Alias)
当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.


4.索引的等级
一般情况索引等级如下：
a) 等式比较比范围比较要高。
b) 唯一性索引比非唯一性索引要高。
c) 一般情况下单列索引等级要比复合索引高，但如果where子句中包含所有复合索引的字段，则复合索引等级高。
例如：
SELECT col1, ... 
FROM emp 
WHERE emp_name = 'GURRY' 
AND emp_no = 127 
AND dept_no = 12 

Index1 (emp_name) 
Index2 (emp_no, dept_no, emp_name) 
ORACLE将使用索引Index2。

5.统计信息分析
在现实当中，有关analyze分析有以下两种误区：

a) 只要对主要的或者关键的表格做分析即可。其实正确的应该是需要对所有涉及到的表格都做过分析。

b) 做一次分析后即可高枕无忧。事实上，一旦做过分析后，就应该定期更新这些统计信息，以保证统计信息的正确性。

6．Exists总比In快
有许多人认为用Exists总比用In要快,这也是一个误区。有时用in反而比用Exists快。
他们之间的区别如下：
IN subquery，首先执行subquery，由subquery来驱动父查询。而Exists子查询则由父查询来驱动子查询。这就是两者之间的区别。
所以如果子查询小的话，则可以采用in会快一些，如果子查询大的话，则采用exists会快一些。

7．>与>=
大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A，
30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。
那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出
为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。

8. 使用索引来避免排序
  索引是排好序的，在某些情况下可以使用索引来避免排序。
  SELECT acc_name, acc_surname
  FROM account acct
  ORDER BY 1;

  SELECT  acc_name,acc_surname
  FROM account acct;


9.大对象操作

a)Big Insert
(1)direct insert(serial and parallel) 
insert into tab1 select * from tab2;
      Insert  into emp  select * from emp_bak;
(2)nologging
         insert into tab1 nologging select * from tab2;
    (3)Large extent size
    更大的extent可以获得更好的insert性能。
 (5)Large rollback segment

b)Large Index Create
  大的索引extent size值
    大的Sort_area_size值
  采用nologging
  采用parallel 
  大的临时表空间

alter session sort_area_size=100000000;
create index xxx on aa(ab) nologging parallel 2;

 c)Large Delete
分几次delete。






附录一
Hint全集
174. 

　　表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.例如:
SELECT  EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';

　　175. 

　　表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.例如:
SELECT  EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';

　　176. 

　　表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量;表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;例如:
SELECT  EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';

　　177. 

　　表明对语句块选择基于规则的优化方法.例如:
SELECT  EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';  

　　178. 

　　表明对表选择全局扫描的方法.例如:
SELECT  EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='CCBZZP';

　　179. 

　　提示明确表明对指定表根据ROWID进行访问.例如:
SELECT  * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'
AND EMP_NO='CCBZZP';

　　180.  
　
　　提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效.例如:
SELECT  BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

181. 

表明对表选择索引的扫描方法.例如:
SELECT  FROM BSEMPMS WHERE SEX='M';

　　182. 

　　表明对表选择索引升序的扫描方法.例如:
SELECT  FROM BSEMPMS WHERE DPT_NO='CCBZZP';

　　183. 

　　为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.例如:
SELECT  * FROM BSEMPMS
WHERE SAL<5000000 AND HIREDATE<SYSDATE;

　　184. 

　　提示明确命令优化器使用索引作为访问路径.例如:
SELECT  SAL,HIREDATE
FROM BSEMPMS WHERE SAL<60000;

　　185. 

　　表明对表选择索引降序的扫描方法.例如:
SELECT  FROM BSEMPMS WHERE DPT_NO='CCBZZP';

　　186. 

　　对指定的表执行快速全索引扫描,而不是全表扫描的办法.例如:
SELECT  * FROM BSEMPMS WHERE DPT_NO='TEC305';

　　187. 

　　提示明确进行执行规划的选择,将几个单列索引的扫描合起来.例如:
SELECT  * FROM BSEMPMS WHERE EMP_NO='CCBZZP' AND DPT_NO='TDC306';

　　188. 

　　对查询中的WHERE后面的OR条件进行转换为UNION ALL的组合查询.例如:
SELECT  * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';

　　189. 

　　对于WHERE后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展.例如:
SELECT  * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';

　　190. 

　　禁止对查询块的查询重写操作.

191. 

　　可以将视图作为参数.

　　192. 

　　能够对视图的各个查询进行相应的合并.例如:
SELECT  A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO
,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) Va WHERE A.DPT_NO=V.DPT_NO
AND A.SAL>V.AVG_SAL;

　　193. 

　　对于有可合并的视图不再合并.例如:
SELECT  A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO
,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO
AND A.SAL>V.AVG_SAL;

　　194. 

　　根据表出现在FROM中的顺序,ORDERED使ORACLE依此顺序对其连接.例如:
SELECT  A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C
WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

　　195. 

　　将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.例如:
SELECT  BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

　　196. 

　　将指定的表与其他行源通过合并排序连接方式连接起来.例如:
SELECT  * FROM BSEMPMS,BSDPTMS WHERE
BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

　　197. 

　　将指定的表与其他行源通过哈希连接方式连接起来.例如:
SELECT  * FROM BSEMPMS,BSDPTMS WHERE
BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

　　198. 

　　强制与ORACLE所选择的位置不同的表进行查询执行.例如:
SELECT  * FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;

　　199. 

　　将指定的表作为连接次序中的首表.

200. 

　　当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端例如:
SELECT  EMP_NAM FROM BSEMPMS;

　　201. 

　　当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端，例如:
SELECT  EMP_NAM FROM BSEMPMS;

　　202. 

　　直接插入到表的最后,可以提高速度.
insert  into test1 select * from test4 ;

　　203. 

　　通过在插入语句生存期内停止并行模式来启动常规插入.
insert  into test1 select * from test4;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

oracle索引的类型

1、B-树索引

B-树索引可以是一个列的索引也可以是组合/复合（多个列）的索引。B-树索引最多可以包括32列。
树叶块包含了索引值、ROWID，以及前一个和后一个树叶块的数据块地址（DBA）。Oracle可以从两个方向遍历这个二叉树。B-树索引保存了在索引列上有值得每个数据行的ROWID值。Oracle不会对索引列上存有空值的行进行索引。如果索引是多个列的组合索引，而其中列上包含有空值，这一行就会处于包含空值的索引列中，且将被处理为空（视为NULL）。
2、位图索引
在位图索引中，Orale为每个惟一键创建一个位图，然后把与键值所关联的ORWID保存为位图。位图索引对于DSS和数据仓库来说非常合适。
当一个表内包含了多个位图索引时，您可以体会到位图索引的真正威力。Oracle可以合并从每个位图索引得到的结果集，快速删除不必要的数据。
技巧：对那些有较低基数的列要使用位图索引。位图对于低基数（少量的不同值）列来说非常快，这是因为索引的大小相对于B-树索引来说小了很多。因为这些索引是低基数的B-树索引，所以非常小，因此可以经常检索表中超过半数的行，并且仍适用位图索引。
技巧：在一个查询中合并多个B-树位图索引后，可以使性能显著提高。位图索引使用固定长度的数据类型要比可变长度的数据类型要好。大的块也会提高对位图索引的存储和读取性能。

查询索引的类型，B-树索引作为NORMAL列出，位图索引的类型值为BITMAP。
SQL> select index_name,index_type from user_indexes;

INDEX_NAME                     INDEX_TYPE
—————————— —————————
PK_DEPT                               NORMAL
PK_EMP                               NORMAL
SYS_C005947                   NORMAL
EMPT_ID1                           NORMAL
EMPT_ID2                           NORMAL
BANK_ID                             NORMAL
SKIP1                                    NORMAL
EMP2_JOB_BM                  BITMAP
EMP2_DEPTNO_BM               BITMAP

已选择9行。

建议不要在一些OLTP应用程序中使用位图索引。B-树索引的索引值中包含ROWID，这样Oracle就可以在行级别上锁定索引。位图索引被存储为压缩的索引值，其中包含了一个范围的ROWID，因此Oracle必须针对一个给定值锁定所有范围内的ROWID。这种锁定可能在某些DML语句中造成死锁。SELECT语句不会受到这种锁定的影响。

3、HASH索引
使用HASH索引必须要使用HASH群集。建立一个群集或HASH群集的同时，也就定义了一个群集键。这个键告诉Oracle如何在群集上存储表。在存储数据时，所有与这个群集键相关的行都被存储在一个数据库块上。若数据都存储在同一个数据库块上，并且使用了HASH索引，Oracle就可以通过执行一个HASH函数和I/O来访问数据——而通过适用一个二元高度为4的B-树索引来访问数据，则需要在检索数据时使用4个I/O。
技巧：HASH索引在有限制条件（需要指定一个确定的值而不是一个值范围）的情况下非常有用。

4、索引组织表
索引组织表通常都按照主键顺序存储，而一个表通常都存储在一个未经编排的堆上。索引组织表会把表的存储结构改成B-树结构，以表的主键进行排序。这种特殊的表和其他类型的表一样。可以在表上执行所有的DML和DDL语句。由于表的特殊结构，ROWID并没有被关联到表的行上。
索引组织表对于一些涉及精确匹配和范围搜索的语句来说，提供了一种基于键的快速访问机制。UPDATE和DELETE语句的性能也同样得以提高，这是因为只需要修改一个结构。由于键列的值在表中没有重复，存储所需要的空间也随之减少。
Oracle提供了一个更快的索引编排表，其中包括一个行溢出区。基于设定的参数值，Oracle把这些额外的行（超出主键和其他不能满足所设定参数的其他非键列）存储到一个溢出区域里，这个区域通过一个物理ROWID连接到索引组织表。可以设定的参数包括PCTTHRESHOLD，该参数指定每个块的比例。还可以设定参数INCLUDING，它命名了一个特殊的列，该列通常是在建立索引组织表的CREATE TABLE语句的最后一个非键列，剩余的就会放到溢出区域中。其目的就是为了保证溢出区域内的数据很少被用到和访问到。显然，如果要用到这些溢出的数据，那么对这些数据的访问就会很慢。

技巧：对于总是通过对主键的精确匹配或范围搜索进行访问的表要考虑使用索引组织表。

5、反转键索引
当载入一些有序数据时，索引肯定会碰到与I/O相关的一些瓶颈。在数据载入其间，某部分索引和磁盘肯定会比其他部分使用频繁得多。为了解决这个问题，可以把经过索引后的表空间存放在能够把文件物理分割在多个磁盘上的磁盘结构上。
Oracle为解决这个问题提供了一种反转键索引的方法。如果数据以反转键存储，这些数据的值就会与原先存储的数值相反，结果就是索引会为每次新插入的行更新不同的索引块。
注意：对于数据载入操作（INSERT操作），反转键索引可能比B-索引要慢2.5~3倍。

6、基于函数的索引
如果没有基于函数的索引，任何在列上执行了函数的查询都不能使用这个列的索引。
Create index EMP$UPPER_JOB on emp(upper(job));

注意：对于那些优化器所使用的基于函数的索引来说，必须把初始化参数query_rewrite_enabled设定为TRUE。

7、分区索引
分区索引就是简单地把一个索引分成多个片断，这样可以访问更小的片断，并且可以把这些片断分别存放在不同的硬盘上（避免I/O问题）。B-数索引和位图索引都可以被分区，HASH索引不可以被分区。
有两种类型的分区索引：本地分区索引和全局分区索引。每个类型都有两个子类型，有前缀索引和无前缀索引。如果使用了位图索引就必须是本地索引。
把索引分区最主要的原因是可以减少所需读取的索引的大小，另外把分区放在不同的表空间中可以提高分区的可用性和可靠性。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

14个数据库的设计技巧

 

1. 原始单据与实体之间的关系
　　可以是一对一、一对多、多对多的关系。在一般情况下，它们是一对一的关系：即一张原始单据对应且只对应一个实体。在特殊情况下，它们可能是一对多或多对一的关系，即一张原始单证对应多个实体，或多张原始单证对应一个实体。这里的实体可以理解为基本表。明确这种对应关系后，对我们设计录入界面大有好处。

　　〖例1〗：一份员工履历资料，在人力资源信息系统中，就对应三个基本表：员工基本情况表、社会关系表、工作简历表。这就是“一张原始单证对应多个实体”的典型例子。


2. 主键与外键
　　一般而言，一个实体不能既无主键又无外键。在E—R 图中, 处于叶子部位的实体, 可以定义主键，也可以不定义主键(因为它无子孙), 但必须要有外键(因为它有父亲)。
　　主键与外键的设计，在全局数据库的设计中，占有重要地位。当全局数据库的设计完成以后，有个美国数据库设计专家说：“键，到处都是键，除了键之外，什么也没有”，这就是他的数据库设计经验之谈，也反映了他对信息系统核心(数据模型)的高度抽象思想。因为：主键是实体的高度抽象，主键与外键的配对，表示实体之间的连接。


3. 基本表的性质
　　基本表与中间表、临时表不同，因为它具有如下四个特性：
　　 (1) 原子性。基本表中的字段是不可再分解的。
　　 (2) 原始性。基本表中的记录是原始数据（基础数据）的记录。
　　 (3) 演绎性。由基本表与代码表中的数据，可以派生出所有的输出数据。
　　 (4) 稳定性。基本表的结构是相对稳定的，表中的记录是要长期保存的。
理解基本表的性质后，在设计数据库时，就能将基本表与中间表、临时表区分开来。


4. 范式标准
　　基本表及其字段之间的关系, 应尽量满足第三范式。但是，满足第三范式的数据库设计，往往不是最好的设计。为了提高数据库的运行效率，常常需要降低范式标准：适当增加冗余，达到以空间换时间的目的。

　　〖例2〗：有一张存放商品的基本表，如表1所示。“金额”这个字段的存在，表明该表的设计不满足第三范式，因为“金额”可以由“单价”乘以“数量”得到，说明“金额”是冗余字段。但是，增加“金额”这个冗余字段，可以提高查询统计的速度，这就是以空间换时间的作法。
　　在Rose 2002中，规定列有两种类型：数据列和计算列。“金额”这样的列被称为“计算列”，而“单价”和“数量”这样的列被称为“数据列”。
　　表1 商品表的表结构
　　商品名称 商品型号 单价 数量 金额
　　电视机 29吋 2,500 40 100,000
　　 
5. 通俗地理解三个范式
　　通俗地理解三个范式，对于数据库设计大有好处。在数据库设计中，为了更好地应用三个范式，就必须通俗地理解三个范式(通俗地理解是够用的理解，并不是最科学最准确的理解)：
　　第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；
　　第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；
　　第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余.
　　没有冗余的数据库设计可以做到。但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。


6. 要善于识别与正确处理多对多的关系
　　若两个实体之间存在多对多的关系，则应消除这种关系。消除的办法是，在两者之间增加第三个实体。这样，原来一个多对多的关系，现在变为两个一对多的关系。要将原来两个实体的属性合理地分配到三个实体中去。这里的第三个实体，实质上是一个较复杂的关系，它对应一张基本表。一般来讲，数据库设计工具不能识别多对多的关系，但能处理多对多的关系。

　　〖例3〗：在“图书馆信息系统”中，“图书”是一个实体，“读者”也是一个实体。这两个实体之间的关系，是一个典型的多对多关系：一本图书在不同时间可以被多个读者借阅，一个读者又可以借多本图书。为此，要在二者之间增加第三个实体，该实体取名为“借还书”，它的属性为：借还时间、借还标志(0表示借书，1表示还书)，另外，它还应该有两个外键(“图书”的主键，“读者”的主键)，使它能与“图书”和“读者”连接。


7. 主键PK的取值方法
　　 PK是供程序员使用的表间连接工具，可以是一无物理意义的数字串, 由程序自动加1来实现。也可以是有物理意义的字段名或字段名的组合。不过前者比后者好。当PK是字段名的组合时，建议字段的个数不要太多，多了不但索引占用空间大，而且速度也慢。


8. 正确认识数据冗余
　　主键与外键在多表中的重复出现, 不属于数据冗余，这个概念必须清楚，事实上有许多人还不清楚。非键字段的重复出现, 才是数据冗余！而且是一种低级冗余，即重复性的冗余。高级冗余不是字段的重复出现，而是字段的派生出现。

　　〖例4〗：商品中的“单价、数量、金额”三个字段，“金额”就是由“单价”乘以“数量”派生出来的，它就是冗余，而且是一种高级冗余。冗余的目的是为了提高处理速度。只有低级冗余才会增加数据的不一致性，因为同一数据，可能从不同时间、地点、角色上多次录入。因此，我们提倡高级冗余(派生性冗余)，反对低级冗余(重复性冗余)。


9. E--R图没有标准答案
　　信息系统的E--R图没有标准答案，因为它的设计与画法不是惟一的，只要它覆盖了系统需求的业务范围和功能内容，就是可行的。反之要修改E--R图。尽管它没有惟一的标准答案，并不意味着可以随意设计。好的E—R图的标准是：结构清晰、关联简洁、实体个数适中、属性分配合理、没有低级冗余。


10. 视图技术在数据库设计中很有用
　　与基本表、代码表、中间表不同，视图是一种虚表，它依赖数据源的实表而存在。视图是供程序员使用数据库的一个窗口，是基表数据综合的一种形式, 是数据处理的一种方法，是用户数据保密的一种手段。为了进行复杂处理、提高运算速度和节省存储空间, 视图的定义深度一般不得超过三层。 若三层视图仍不够用, 则应在视图上定义临时表, 在临时表上再定义视图。这样反复交迭定义, 视图的深度就不受限制了。

　　对于某些与国家政治、经济、技术、军事和安全利益有关的信息系统，视图的作用更加重要。这些系统的基本表完成物理设计之后，立即在基本表上建立第一层视图，这层视图的个数和结构，与基本表的个数和结构是完全相同。并且规定，所有的程序员，一律只准在视图上操作。只有数据库管理员，带着多个人员共同掌握的“安全钥匙”，才能直接在基本表上操作。请读者想想：这是为什么？


11. 中间表、报表和临时表
　　中间表是存放统计数据的表，它是为数据仓库、输出报表或查询结果而设计的，有时它没有主键与外键(数据仓库除外)。临时表是程序员个人设计的，存放临时记录，为个人所用。基表和中间表由DBA维护，临时表由程序员自己用程序自动维护。


12. 完整性约束表现在三个方面
　　域的完整性：用Check来实现约束，在数据库设计工具中，对字段的取值范围进行定义时，有一个Check按钮，通过它定义字段的值城。参照完整性：用PK、FK、表级触发器来实现。用户定义完整性：它是一些业务规则，用存储过程和触发器来实现。


13. 防止数据库设计打补丁的方法是“三少原则”　　 (1) 一个数据库中表的个数越少越好。只有表的个数少了，才能说明系统的E--R图少而精，去掉了重复的多余的实体，形成了对客观世界的高度抽象，进行了系统的数据集成，防止了打补丁式的设计；
　　 (2) 一个表中组合主键的字段个数越少越好。因为主键的作用，一是建主键索引，二是做为子表的外键，所以组合主键的字段个数少了，不仅节省了运行时间，而且节省了索引存储空间；
　　 (3) 一个表中的字段个数越少越好。只有字段的个数少了，才能说明在系统中不存在数据重复，且很少有数据冗余，更重要的是督促读者学会“列变行”，这样就防止了将子表中的字段拉入到主表中去，在主表中留下许多空余的字段。所谓“列变行”，就是将主表中的一部分内容拉出去，另外单独建一个子表。这个方法很简单，有的人就是不习惯、不采纳、不执行。
　　数据库设计的实用原则是：在数据冗余和处理速度之间找到合适的平衡点。“三少”是一个整体概念，综合观点，不能孤立某一个原则。该原则是相对的，不是绝对的。“三多”原则肯定是错误的。试想：若覆盖系统同样的功能，一百个实体(共一千个属性) 的E--R图，肯定比二百个实体(共二千个属性) 的E--R图，要好得多。
　　提倡“三少”原则，是叫读者学会利用数据库设计技术进行系统的数据集成。数据集成的步骤是将文件系统集成为应用数据库，将应用数据库集成为主题数据库，将主题数据库集成为全局综合数据库。集成的程度越高，数据共享性就越强，信息孤岛现象就越少，整个企业信息系统的全局E—R图中实体的个数、主键的个数、属性的个数就会越少。
　　提倡“三少”原则的目的，是防止读者利用打补丁技术，不断地对数据库进行增删改，使企业数据库变成了随意设计数据库表的“垃圾堆”，或数据库表的“大杂院”，最后造成数据库中的基本表、代码表、中间表、临时表杂乱无章，不计其数，导致企事业单位的信息系统无法维护而瘫痪。
　　 “三多”原则任何人都可以做到，该原则是“打补丁方法”设计数据库的歪理学说。“三少”原则是少而精的原则，它要求有较高的数据库设计技巧与艺术，不是任何人都能做到的，因为该原则是杜绝用“打补丁方法”设计数据库的理论依据。


14. 提高数据库运行效率的办法
　　在给定的系统硬件和系统软件条件下，提高数据库系统的运行效率的办法是：
　　 (1) 在数据库物理设计时，降低范式，增加冗余, 少用触发器, 多用存储过程。
　　 (2) 当计算非常复杂、而且记录条数非常巨大时(例如一千万条)，复杂计算要先在数据库外面，以文件系统方式用C++语言计算处理完成之后，最后才入库追加到表中去。这是电信计费系统设计的经验。
　　 (3) 发现某个表的记录太多，例如超过一千万条，则要对该表进行水平分割。水平分割的做法是，以该表主键PK的某个值为界线，将该表的记录水平分割为两个表。若发现某个表的字段太多，例如超过八十个，则垂直分割该表，将原来的一个表分解为两个表。
　　 (4) 对数据库管理系统DBMS进行系统优化，即优化各种系统参数，如缓冲区个数。
　　 (5) 在使用面向数据的SQL语言进行程序设计时，尽量采取优化算法。
　　总之，要提高数据库的运行效率，必须从数据库系统级优化、数据库设计级优化、程序实现级优化，这三个层次上同时下功夫。

　　上述十四个技巧，是许多人在大量的数据库分析与设计实践中，逐步总结出来的。对于这些经验的运用，读者不能生帮硬套，死记硬背，而要消化理解，实事求是，灵活掌握。并逐步做到：在应用中发展，在发展中应用。