Oracle体系结构知识点总结

体系结构：

数据库的体系结构是指数据库的组成、工作过程与原理，以及数据在数据库中的组织与管理机制。

体系结构包括：实例（instence），数据库文件（database），用户进程（user process

，服务器进程（server process），以及其他文件（如参数文件（parameter file），密码文件（password file），归档日志文件（archive log file）等）。

Oracle服务器由数据库文件和数据库实例组成。

其中数据库实例包括SGA（即内存结构的集合）和管理数据库的后台进程。

数据库文件包括三种：数据文件（data files），控制文件（control files）和重做日志文件（redo log files）。 

Oracle中的个主要内存区域是：

– 系统全局区域(system global areaSGA)--当数据库实例启动时会首先分配

– 程序全局区域(program global areaPGA

– 用户全局区域(user global areaUGA

SGA内存结构包括: 共享池（Shared Pool），数据缓冲区（Database Buffer Cache），重做日志缓冲区（Redo Log Buffer Cache）。共享池的作用：共享池由库缓存和数据字典缓存组成。缓存和共享SQLPL/SQL代码；数据缓冲区的作用：存储从数据文件中读入的数据，提高查询速度；重做日志缓冲区的作用：变化前和变化后的数据在写入数据缓冲区前都会先写入重做日志缓冲区，这样在数据恢复时，Oracle就知道哪些需要回滚。日志数据首先产生于重做日志缓冲区，当重做日志缓冲区的日志数据达到一定数量时，由日志写进程LGWR将日志数据写入重做日志文件中。

PGA是为单独的服务器进程存储私有数据的内存区域，只为各个服务器进程提供一个PGAPGA只能由他们的服务器进程访问。

UGA是用于存储会话状态的内存区域。UGA的位置依赖于服务器是运行在共享服务器模式中，还是专用服务器模式中。在专用服务器模式中，UGA会在PGA中分配，只能够由服务器进程访问。 

Oracle进程主要有用户进程、服务器进程和后台进程。

用户进程是一个需要与Oracle服务器进行交互的程序。

服务器进程犹如一个中介，完成用户的各种数据服务请求，而把数据库服务器返回的数据发给客户端。

后台进程主要作用是协调好系统的性能。其主要包括进程监控进程(PMON，服务器进程的管理和维护)、系统监控进程(SMON，在数据库出现故障时进行实例恢复)、数据库写进程（DBWR，主要负责将数据缓冲区内的数据写到数据文件）、重做日志写进程（LGWR，主要负责将重做日志缓冲区内的数据写到重做日志文件）及检查点进程（CKPT，其作用是保证所有修改过的数据库缓冲区都被写入数据库文件）。以上5个进程也是数据库启动过程中必须启动的。 

数据库启动和关闭

数据库的启动3个状态：

1:NOMOUNT 只打开数据库实例 

2:MOUNT 打开实例并读取控制文件 

3:OPEN 打开数据库

按123顺序启动

数据库的关闭3个状态：

1:CLOSE 关闭数据库

2:DISMOUNT 

3:SHUTDOWN 关闭数据库实例 

事务

事务的概念：事务是一组逻辑工作单元，它由一条或多条SQL语句组成。

事务的4个特性：原子性，一致性，隔离性和持久性

事务控制：commit，rollback 

SQL语言

SQL语言分为数据定义语言（DDL）、数据操纵语言（DML）和数据控制语言（DCL）。

DDL用于定义SQL模式、基本表、视图和索引的创建和撤消操作。常用命令有：CREATE、ALTER、DROP。

DML用于数据的插入、修改、删除和查询。常用命令有：INSERT、UPDATE、DELETE、SELECT。

DCL用于对基本表的授权、完整性规则的描述和事务的控制等。常用的命令有：GRANT、REVOKE、COMMIT、SAVEPOINT、ROLLBACK。 

har和varchar2两种数据类型的区别：

在数据库中char(n)表示固定长度n的字符串，当实际数据不足定义长度时，将使用空格补全右边不足位，当实际数据的长度大于其固定长度时，Oracle将不允许数据存储于对应列或者变量中，并抛出异常；

而varchar2(n)是可变长的字符串，意味着该列或变量的最大长度不大于n,但当实际数据小于n时，并不在其右端补齐空格。

delete和truncate的区别。

elete是DML语句，在删除数据时要记录重做信息，且删除数据后不释放表空间，速度慢。runcate是数据定义语言，执行后数据直接删除且释放表空间，不记录日志信息，速度快。 

PL/SQL语言

PL/SQL语言的叙述：

PL/SQL是一种块结构的语言，它将一组语句放在一个块中。构成PL/SQL程序的基本单元是逻辑块（如过程、函数或匿名块），每个逻辑块对应要解决的问题或子问题。PL/SQL块共分为3个部分，分别为声明部分、可执行部分和异常处理部分。

属性数据类型包括两种，分别是%TYPE和%ROWTYPE。

1、%TYPE将某种数据类型的变量或列提供给其他变量。

其语法格式为：var1 table_name.column_name%TYPE;其中声明了变量var1，它的数据类型与table_name表中的column_name字段的数据类型相同。

2、%ROWTYPE提供一种表示表中的某行的记录类型。

其语法格式为：var1 table_name%ROWTYPE;其中声明了变量var1，可以用来存储从table_name表中提取的一个记录。

过程、函数、程序包

过程和函数的区别：过程没返回值，而函数有返回值。

程序包是对相关过程、函数、变量、游标和异常等对象的封装。

程序包由规范和主体两部分组成。规范可以在没有程序包主体的情况下存在，主体不能在没有程序包规格说明的情况下存在。 

游标和触发器

游标的4种属性：%sopen 、%found、%notfound、%rowcount。

使用游标的个步骤：定义游标（declare）、打开游标（open）、循环取数据（fetch）、关闭游标（close）。

显式游标和隐式游标在使用过程中的区别：显示游标使用时要定义、隐式游标不需要定义，在上下文中的dml语句默认使用隐式游标。

触发器定义：触发器是当特定事件出现时自动执行的代码块。

触发器与过程的区别：过程是由用户或应用程序甚至是触发器显式调用的，而触发器是由Oracle根据发生的事件而隐式激活的，它不能被直接调用执行。

触发器的类型及其运行时机：

Oracle触发器分为DML触发器、模式（DDL或用户事件）触发器和数据库级触发器。

1:DML触发器：当DML语句在表中发生时执行。DML语句包括以下3种类型：

a.语句级触发器：无论受触发语句影响的行数是多少，都只激活一次。

b.行级触发器：每当触发器语句影响表时就会激活行级触发器，行级触发器在被修改的每一行上执行一次。

c.INSTEAD OF触发器：允许用户修改不能使用DML语句修改的视图。INSTEAD OF触发器只可用于视图，不可用于表。

2:模式（DDL或用户事件）触发器：在数据库模式中执行DDL语句时激活。

3:数据库（系统事件）触发器：在发生LOGOFF（退出）、LOGON（登录）、STARTUP（打开）、SHUTDOWN（关闭）数据库和SERVERERROR（生成错误消息）等系统事件时执行。

数据库对象（同义词，序列，视图，索引）　

同义词的定义：是现有对象的一个别名。

同义词的作用：

1：简化SQL语句。：隐藏对象的名称和所有者。：提供对对象的公共访问。

同义词的两种类型：

1：公有同义词。CREATE public synonym emp_syn FOR SCOTT.emp;

2：私有同义词。CREATE synonym emp FOR SCOTT.emp;

公有的同义词只能建立在超级用户下定义。

序列是用来生成唯一、连续的整数的数据库对象。

在表中使用序列：

1.创建序列：

CREATE SEQUENCE sequence_name [START WITH startnum [INCREMENT BY step];

2.创建表时使用序列：

CREATE TABLE table_name  uid smallint not null, ...);

3.插入数据时使用序列：

INSERT INTO table_name VALUES(sequence_name.NEXTVAL, ...);

视图是从若干基本表和（或）其他视图构造出来的表。

视图和表的区别是：创建一个视图时，只是把其视图的定义存放在数据字典中，而不存储视图对应的数据，而表会存储对应的数据。

视图的目的和作用：筛选表中数据，简化查询语句，对表起到保护作用。

注：加WITH READ ONLY 选项可使视图不允许DML操作  

用户管理和权限管理

要在超级用户上创建用户权限

--创建用户

create user zw identified by 123456;

--给zw登入的权限

grant connect to zw;

--授权

--用系统权限的方式

grant create table to zw;

--用角色方式

grant resource to zw;

--例：

create user username identified by password;

grant resource,connect to username;

-----------------GRANT语句

--GRANT语句的作用是赋与用户权限。

--常用的数据对象权限有以下六个：

--（1）ALL ON  数据对象名

--（2）SELECT ON  数据对象名

--（3）UPDATE  ON  数据对象名

--（4）DELETE  ON  数据对象名

--（5）INSERT  ON   数据对象名

--（6）ALTER  ON  数据对象名

--例：

--把查询department表权限给zw用户

grant select on department to zw

--把所有操作department表权限给zw用户

grant all on department to zw 

----------------REVOKE语句

--REVOKE语句是回收权限语句。回收以上所赋权限的相应语句为：

--REVOKE CONNECT FROM USER1;

--REVOKE SELECT ON TABLE2 FROM USER2;

--REVOKE SELECT,INSERT,DELETE ON TABLE3 FROM USER1，USER2;

--例：

--回收sun用户对SZD用户的emp表的查询权限

revoke select on emp from sun

--回收sun用户对SZD用户的emp表的所有权限

revoke all on emp from sun

备份与恢复

数据库导出的四种模式full(全库导出), owner(用户导出), table(表导出), tablespace(表空间导出

执行exp 有三种方法交互式命令行和参数文件。

1：交互式直接输入exp 命令用户可以按照exp 提示的信息一步一步操作比较简单. 

2：命令行输入命令行exp username/password parameter=value. 

3：参数文件输入命令行exp username/password parfile=filename exp 所需的参数从参数文件引入。

热备份和冷备份：

热备份针对归档模式的数据库，在数据库仍旧处于工作状态时进行备份。而冷备份指在数据库关闭后，进行备份，适用于所有模式的数据库。

热备份的优点在于当备份时，数据库仍旧可以被使用并且可以将数据库恢复到任意一个时间点。冷备份的优点在于它的备份和恢复操作相当简单，并且由于冷备份的数据库可以工作在非归档模式下,数据库性能会比归档模式稍好。（因为不必将archive log写入硬盘）

索引

索引是一种树状结构，可以通过该结构迅速访问表中的数据。 

索引的类型和其语法：唯一索引，组合索引，反向键索引，位图索引，基于函数的索引。

1、唯一索引可以确保在定义索引的列中，表的任意两行的值都不相同，Oracle自动为表的主键列创建唯一索引。

其语法为：CREATE UNIQUE INDEX index_name ON table_name(column_name);

2、组合索引是在表中的多个列上创建的索引，可以提高检索数据的速度。

其语法为：CREATE INDEX index_name ON table_name(column_name);

3、反向键索引通常建立在一些值连续增长的列上，可以提高读取的性能。

其语法为：CREATE INDEX index_name ON table_name(column_name) REVERSE;

4、位图索引适用于低基数列，也就是不同值的数目比表的行数少的列。

其语法为：CREATE BITMAP INDEX index_name ON table_name(column_name);

5、基于函数的索引是指基于一个或多个列上的函数或表达式创建的索引，便于WHERE子句中包含函数或表达式以计算查询时使用。

其语法为：CREATE INDEX index_name ON table_name(表达式或函数);

racle索引包含B-tree索引、bitmap索引、函数索引等。

B-tree索引类似二叉树结构，数据库表中每一行都对应R-tree索引中的一个节点。

节点中包含数据（索引列）和rowid信息，通过索引列找到对应的rowid信息，在定位到具体的行来查找数据。

如果平凡删除操作，会对相应的索引作对应的修改，会降低性能。建议在操作之前先删除索引再重建索引。 

补充：

创建索引语法：create index index_name on itemfile (itemcode)

删除索引语法：drop index index_name;

重建索引语法：alter index index_name rebuild;

重命名索引：alter index item_index rename to book_index; 

索引的优缺点

优点:某些情况下，数据查找快。

如：用户的查询将要选取记录的2%-5%或更多，那么它将会执行全部扫描，而不考虑索引是否可用

缺点:

a)在某些条件下，全表扫描比索引查找更快

b)索引占用空间惊人，甚至超过表数据所占空间,不利于管理。

c)创建索引后，会降低插入，修改，删除等操作的效率

选择索引

a.身份证这类唯一属性，应建唯一索引 

b.性别,只有男、女、未定等少数几种状态值，应创建位图索引，位图索引更节约空间

c.对字段使用函数，不会使用索引，可创建函数索引。

下列的表不适合建索引:

a.表很小(8K)

b.列不经常在WHERE子句中使用

c.表中的相关列经常被修改 

Oracle优化

SQL优化实例:

– 选择最有效率的表名顺序(只在基于规则的优化器中有效)

– WHERE子句中的连接顺序

– SELECT子句中避免使用 *

– 减少访问数据库的次数

– 最高效的删除重复记录方法 

– 用TRUNCATE替代DELETE 

– 尽量多使用COMMIT

– 用Where子句替换HAVING子句 

– 使用表的别名(Alias)

– 用EXISTS替代IN 

– 用NOT EXISTS替代NOT IN 
用表连接替换EXISTS

– 用EXPLAIN PLAN 分析SQL语句

– 避免在索引列上使用计算

– 用>=替代>

– 避免在索引列上使用IS NULL和IS NOT NULL

– 用UNION-ALL 替换UNION ( 如果有可能的话)

– 需要当心的WHERE子句