开发成功的oracle数据库

前言 1.基于数据库（或依赖于数据库）构建的应用是否成功，这取决于如何使用数据库。另外，从我的经验看，所有应用的构建都围绕 着数据库。如果一个应用未在任何地方持久地存储数据，很难想象这个应用真的有用。 2.应用总是在“来来去去”，而数据不同，它们

前言

1.基于数据库（或依赖于数据库）构建的应用是否成功，这取决于如何使用数据库。另外，从我的经验看，所有应用的构建都围绕
着数据库。如果一个应用未在任何地方持久地存储数据，很难想象这个应用真的有用。
2.应用总是在“来来去去”，而数据不同，它们会永远存在。从长远来讲，我们的目标并不是构建应用，而应该是如何使用这些应用
底层的数据。
3. 开发小组的核心必须有一些精通数据库的开发人员，他们要负责确保数据库逻辑是可靠的，系统能够顺利构建。如果已成事实（应
用已经部署）之后再去调优，这通常表明，在开发期间你没有认真考虑这些问题。

正文

了解数据库

1.数据库的体系结构，数据库如何工作，以及有怎样的表现。
2.并发控制是什么，并发控制对你意味着什么。
3.性能、可扩缩性和安全性都是开发时就应该考虑的需求，必须适当地做出设计，不要指望能碰巧满足这些需求。
4.数据库的特性如何实现。某个特定数据库特性的实际实现方式可能与你想象的不一样。你必须根据数据库实际上如何工作（而不
是认为它应该如何工作）来进行设计。
5.数据库已经提供了哪些特性，为什么使用数据库已提供的特性要优于自行构建自己的特性。
6.为什么粗略地了解SQL还不够，还需要更深入地学习SQL。
7.DBA和开发人员都在为同一个目标努力，他们不是敌对的两个阵营，不是想在每个回合中比试谁更聪明。

如果可能，尽量利用一条SQL语句完成工作。

1. 如果无法用一条SQL语句完成，就通过PL/SQL实现（不过，尽可能少用PL/SQL！）。
2.如果在PL/SQL中也无法做到（因为它缺少一些特性，如列出目录中的文件），可以试试使用Java存储过程来实现。不过，有了Oracle9i 及以上版本后，如今需要这样做的可能性极小。
3.如果用Java 还办不到，那就在C 外部过程中实现。如果速度要求很高，或者要使用采用C 编写的一个第三方API，就常常使用这种做法。
4.如果在C外部例程中还无法实现，你就该好好想想有没有必要做这个工作了。

SQL优化--基本概念

数据库访问表数据的方式

Oracle虽然可以通过各种执行方式存取数据，但是在最后访问数据表时只有两种方式。

?全表扫描

全表扫描就是顺序地访问表中每条记录。Oracle将数据保存在数据块(database block)中，通过一次读入多个数据块的方式优化全表扫描。

数据块是数据库存取数据的最小I/O单位，只有将数据块读到内存中才能查找数据。

?通过ROWID访问表

ROWID是Oracle数据库的一个伪列，唯一标识数据表中的数据行，数据一旦插入数据库，该行的ROWID将不能再被改变。

ROWID是访问数据表的最快方法，通过ROWID，Oracle可以直接定位到数据块上。

索引除了包含索引列值外还存储对应行的ROWID，所以，提供了快速访问ROWID的方法，因此，基于索引的查询性能很高。

索引的代价

索引虽然是提高数据查询最有效的方法，但是无效的索引会造成数据库空间的浪费，甚至大大降低查询性能。

1.索引需要磁盘空间存储

2.执行数据修改操作（INSERT、UPDATE、DELETE）产生索引维护

3.在数据处理时需额外的回退空间

4.索引和数据不在一个数据块上，使用索引会增加系统I/O

建议一张表的索引不要超过3个，但是对于稳定表可以多建立索引提高查询速度。

索引的分类

?索引按功能分类

–Primary key(主关键字)

–Foreign key(外键)

–Unique Index(唯一索引)

–Index(一般索引)

?索引按存储方法分类

–B-树索引

–位图索引

–HASH索引

–索引编排表

–反转键索引

–分区索引

–本地和全局索引

?索引按对象分类

–单列索引（表单个字段的索引）

–多列索引（表多个字段的索引）

函数索引（对字段进行函数运算的索引）

?Primary key(主关键字)

主关键字是保证在一个表中的数据唯一，在创建主关键字时，数据库自动在主关键字上创建唯一索引。一个表只能创建一个主关键字。

?Foreign key(外键)

外键是表和表之间建立主从关系，又叫父子关系，外键只能关联到主表的主键或唯一索引上（因为关系型数据库不支持多对多关系）。外键并不自动创建索引。

对于一对多的两个表，外键建立在多的表上。必须满足“有子必有父”的关系，即插入数据必须先插入主表数据后才能插入子表数据，删除主表数据前必须删除子表数据。

?Unique Index(唯一索引)

创建唯一索引的字段或组合字段在数据上必须唯一。

?Index(一般索引)

一般索引没有数据约束限制，目的是为了加快查询速度。

不能对字段或组合字段重复创建索引，组合字段重复是指字段和字段顺序完全相同。

?B-树索引

B-树索引是最常用的索引，其存储结构类似书的索引结构，有分支和叶两种类型的存储数据块，分支块相当于书的大目录，叶块相当于索引到的具体的书页。一般索引及唯一约束索引都使用B-树索引。

?位图索引

位图索引储存主要用来节省空间，减少数据块的访问，它采用位图偏移方式来与表的行ID号对应，采用位图索引一般是重复值多的表字段。位图索引在OLTP（数据事务处理）中用得比较少，因为OLTP会对表进行大量的删除、修改、新建操作。在OLAP（数据分析处理）中应用位图有优势，因为OLAP中大部分是对数据库的查询操作，而且一般采用数据仓库技术，所以大量数据采用位图索引节省空间比较明显。

注：B-树索引中包含ROWID，Oracle可以在行级别上锁定索引。位图索引被存储为压缩的索引值，是一个范围内的ROWID，因此ORACLE必须针对一个给定值锁定所有范围内的ROWID，极易造成死锁。

单列索引

以单个字段建立的索引

多列索引

已多个字段组合建立的索引

函数索引（对字段进行函数运算的索引）

在索引中使用函数或者表达式，这些函数可以是Oracle的函数，也可以是用户自己的PL/SQL函数等 ，函数索引是大小写敏感的。

在什么情况下应该建立索引

–表主关键字

数据库自动建立索引

–字段唯一性约束

–主从表关联

因为在查询中经常会和其他表关联查询

–经常查询字段或组合查询字段

–查询中排序或分组的字段

索引是排序的，所以，排序字段或分组字段如果通过索引去访问将大大提高查询速度

在什么情况下不应该建立索引

–表记录很少的表

数据库使用索引，必须先访问索引表，再通过索引表访问数据表，一般索引与数据表不在同一个数据块，Oracle至少要读取数据块两次。如果表数据很少，Oracle会将所有的数据一次读出，处理速度显然会比用索引快。

–经常更新的表（不稳定表）

数据更新会造成索引的维护，影响数据更新时间。

–数据重复且分布均匀字段

对于大数据表，如果一个字段只有少量的值，并且分布平均，建立该字段的索引一般不会提高数据库的查询速度。

SQL优化--索引概念

通过Developer看索引使用

Developer提供了Explain plan Window图形窗口，可以方便地看到SQL的执行计划。

启动Explain plan Window有两种方法，第一种在SQL Window下输入sql，按F5，第二种，使用菜单new/Explain plan Window。

?窗体介绍

窗体分为两大部分，上半部分是sql区，可以在此输入要分析的sql，下半部分是SQL的执行计划。Optimizer goal可以选择优化器，其中：

–First_Rows:基于CBO的优化器，侧重于返回一条结果记录，大多数使用Single Block IO（类似于Index Scan）

–All_Rows: 基于CBO的优化器，侧重于返回所有结果记录，大多数使用Mutil Block IO（类似于Full Table Scan）

–Rule: 基于RBO的优化器

–Choose: 根据表是否做过分析来选择使用RBO还是CBO。如果有一个或以上的表做过表分析，则使用CBO；如果都没有做过表分析，则使用RBO。

用导航按键查看执行步骤，执行步骤是从上到下，从内到外。

优化器

目前Oracle的优化器共有三种：

–Rule基于规则-- Rule Based Optimizer

–Cost基于成本-- Cost Based Optimizer

–Choose选择性。

数据库在默认安装情况下，Oracle使用Choose优化器。

Rule（基于规则--Rule Based Optimizer）

From子句从外侧向内侧的顺序检索表，Where子句从下向上解析条件。

根据From规则，在多表查询时，将返回结果集最少的表作为基础表，写在From最外侧（并不是最小的表，应该是查询限定最强的表，一般情况下是查询主体表），然后根据表之间的限定依赖关系依次从外侧写到内侧。Oracle执行时，首先扫描最外侧表（驱动表），并对结果集进行排序，然后扫描内侧表（被探测表），将第二个表检索出的结果集（用Row Source2表示）与第一个表中相应结果集（用Row Source1表示）进行合并后再次向内侧表扫描合并，直到全部表被检索合并，返回结果集。

根据Where规则，先写表连接，表连接从内侧向外侧的顺序写，可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾（结合From规则，末尾的查询条件应该是最外侧的表）。写查询条件时应该按照使用的索引字段顺序从上写到下。

当查询表有N个，表关联应该是N-1个。

Cost（基于成本--Cost Based Optimizer）

CBO是依赖表的统计信息来选择最低成本的执行计划， 这些统计使用ANALYZE命令完成，在使用CBO之前必须将Table分析好。

CBO最多只能排列2000种表的排列关系，也就是说当超过6个以上的表（2的7次方大于2000）进行关联时，CBO就不能把所有的排列关系都计算到，会遗漏一些，所以这个时候生成的执行计划据可能不是最优的。

对于数据变化比较大的表，你必须经常运行analyze 命令,以增加数据库中的对象统计信息(object statistics)的准确性，以提高系统效率。

语法：

analyze table table_name estimate statistics sample x percent;

--抽样估算法，使用x%抽样率对table_name表分析

analyze table table_name compute statistics;

--完全计算法

对表作完全计算所花的时间相当于做全表扫描，抽样估算法由于采用抽样，比完全计算法的生成统计速度要快，如果不是要求要有精确数据的话，尽量采用抽样分析法。建议对表分析采用抽样估算，对索引分析可以采用完全计算。

Choose（选择性）

根据表是否做过分析来选择使用RBO还是CBO。如果有一个以上的表做过表分析，则使用CBO；如果都没有做过表分析，则使用RBO。

索引的扫描分类

?索引唯一扫描(Index unique scan)

?索引范围扫描(Index range scan)

?索引全扫描(Index full scan)

?索引快速扫描(Index fast full scan)

?索引跳跃扫描(Index skip scan)

索引唯一扫描(Index unique scan)

通过唯一索引返回单行数据的查询方法称为索引唯一扫描。如果存在UNIQUE 或PRIMARY KEY 约束（它保证了语句只存取单行）的话，Oracle经常实现唯一性扫描。

如EMP表创建了Id主关键字，如果使用Id作为条件查询，Oracle使用Index Unique Scan。

Select * from emp where id = 2343;

索引范围扫描(index range scan)

使用一个索引返回多行数据的查询方法称为索引范围扫描。使用索引范围扫描的情况有：

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

–在组合索引上，只使用部分字段进行查询，导致查询出多行

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

索引全扫描(index full scan)

全索引扫描只发生在CBO模式下。当CBO根据统计数值判断全索引扫描比全表扫描更有效时，才进行全索引扫描，而且此时查询出的数据都必须从索引中可以直接得到。

如select id from emp order by id;

因为查询字段属于索引字段同时需要索引字段排序，Oracle使用Index full scan。

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

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

如select id from emp;

索引跳跃扫描(Index skip scan)

当查询条件没有使用组合查询的前导列，优化器可以使用跳跃扫描，跳跃扫描比全扫描性能高。

表的关联

在ORACLE中表的关联有三种：

–Sort merger join（排序合并关联）

–Nested Loops（嵌套循环）

–Hash join（哈希关联）

Sort Merger join（排序合并关联）

多出现于大表和大表以索引的方式连接。

执行过程：

1.扫描第一个表返回结果集，然后按照关联列排序；

2.扫描第二个表返回结果集，然后按照关联列排序；

3.两边已排序的行被放在一起执行合并操作。

此连接方式建立在排序的基础上，而排序操作消耗的系统资源很大，所以，这种方式对于结果集已经排序的连接比较有效。

Nested Loops（镶嵌循环）

多出现于小表和大表关联

执行过程：

1.扫描第一个表（驱动表）Row source1；

2.循环Row source1；

3.探索第二个表（被探查表）Row rource2。

4.重复2～3直到循环完Row source。

此连接方式的关键是驱动表的返回数据集要少，同时被探查表的匹配要有索引支持。另外，此连接是最快得到第一个匹配行的方式，所以，可以实现快速的响应时间，因为此连接不必等待所有的连接操作处理完才返回数据。

如果驱动表的数量比较大，查询性能不如全表扫描.

Hash join（哈希关联）

只能用于相等连接，且只能在CBO优化器模式下，多出现于小表和大表关联

执行过程：

1.扫描第一个表（驱动表）返回数据集，构建Hash表；

2.读取第二个表的一条数据，和内存中的数据进行匹配；

此连接方式对于CPU的消耗比较大，并取决于内存的大小，因为Oracle在内存中建立Hash表。

SQL优化--小结

有关SQL的优化涉及的范围很广，相关的知识需要在实践中不断总结和积累。学习完本节需要掌握的知识：

–Oracle的优化器

–索引的不同分类

–表关联的方式

–如果使用Explain Plan Window查看执行计划

–写DML时应该用不同的sql看性能优劣再确定sql

在PC机上正确创建索引并正确使用索引的情况下，上千万条数据的单表查询应该在0.1秒左右，如果使用多表关联查询不应该超过0.6秒，但是对于分组查询可能超过1秒。如果超过你写的查询超过这些参考值，说明你写的SQL还有优化的可能。

数据库的高伸缩性--并行控制

死锁：

当两个事务需要一组有冲突的锁，而不能将事务继续下去的话，就出现死锁 。

查询死锁

select b.username,b.sid,b.serial#,logon_time

from v$locked_objecta,v$session b

where a.session_id = b.sid order by b.logon_time;

也可以在Developer中可以使用tools\Sessions...中的Lock查看锁定情况

杀死死锁

SQL>alter system kill session 'sid,serial#';

--如果有ora-00031错误，则在后面加immediate;

SQL>alter system kill session 'sid,serial#' immediate;

总结