ORACLE优化Word格式文档下载.docx

ORACLE优化Word格式文档下载.docx

《ORACLE优化Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《ORACLE优化Word格式文档下载.docx（53页珍藏版）》请在冰豆网上搜索。

2.用户通过简单的查询可以从复杂查询中得到结果。

3.维护数据的独立性，试图可从多个表检索数据。

4.对于相同的数据可产生不同的视图。

视图分为简单视图和复杂视图：

1、简单视图只从单表里获取数据，复杂视图从多表；

2、简单视图不包含函数和分组，复杂视图包含；

3、简单视图可以实现DML操作，复杂视图不可以。

视图的创建：

CREATE[ORPRPLACE][FORCE|NOFORCE]VIEWview_name

[（alias[,alias]...）]

ASsubquery

[WITHCHECKOPTION[CONSTRAINTconstraint]]

[WITHREADONLY[CONSTRAINTconstraint]];

其中：

ORREPLACE：

若所创建的试图已经存在，ORACLE自动重建该视图；

FORCE：

不管基表是否存在ORACLE都会自动创建该视图；

NOFORCE：

只有基表都存在ORACLE才会创建该视图：

alias：

为视图产生的列定义的别名；

subquery：

一条完整的SELECT语句（可以是复杂的SELECT语句），可以在该语句中定义视图列表的别名；

WITHCHECKOPTION：

插入或修改的数据行必须满足视图定义的约束；

WITHREADONLY：

该视图上不能进行任何DML操作。

视图定义的原则：

1.视图的查询可以使用复杂的SELECT语法，包括连接/分组查询和子查询；

2.在没有WITHCHECKOPTION和READONLY的情况下，查询中不能使用ORDERBY子句；

3.如果没有为CHECKOPTION约束命名，系统会自动为之命名，形式为SYS_Cn；

4.ORREPLACE选项可以不删除原视图便可更改其定义并重建，或重新授予对象权限。

修改视图：

使用CREATEORREPLACEVIEW子句修改视图，CREATEORREPLACEVIEW子句中各列的别名应和子查询中各列相对应。

删除视图：

删除视图只是删除视图的定义，并不会删除基表的数据。

只有视图所有者和具备DROPVIEW权限的用户可以删除视图。

视图被删除后，基于被删除视图的其他视图或应用将无效。

DROPVIEWview_name;

WITHCHECKOPTIONconstraintview_name_ck子句限定：

通过视图执行的INSERT和UPDATE操作不能创建该视图检索不到的数据行，因为它会对插入或修改的数据行执行完整性约束和数据有效性检查。

使用WITHCHECKOPTION子句确保DML只能在特定的范围内执行，任何违反WITHCHECKOPTION约束的请求都会失败。

视图的查询：

视图创建成功后，可以从视图中检索数据，这点和从表中检索数据一样。

屏蔽DML操作：

可以使用WITHREADONLY选项屏蔽对视图的DML操作。

视图中使用DML的规定：

可以在简单视图中执行DML操作

当视图定义中包含以下元素之一时不能使用delete:

–组函数

–GROUPBY子句

–DISTINCT关键字

–ROWNUM伪列

当视图定义中包含以下元素之一时不能使用update:

–列的定义为表达式

当视图定义中包含以下元素之一时不能使用insert:

–表中非空的列在视图定义中未包括

查询最大的几个值的Top-N分析:

SELECT[column_list],ROWNUM

FROM（SELECT[column_list]FROMtableORDERBYTop-N_column）

WHEREROWNUM<

=N;

Oracle序列的创建，维护和使用

序列（SEQUENCE）是一数据库对象。

利用它可生成唯一的整数，一般使用序列自动地生成主码值。

一个序列的值是由特殊的Oracle程序自动生成。

Oracle序列允许同时生成多个序列号，而每一个序列号是唯一的。

当一个序列号生成时，序列是递增，独立于事务的提交或回滚。

允许设计缺省序列，不需指定任何子句。

该序列为上升序列，由1开始，增量为1，没有上限。

创建序列

CREATESEQUENCEsequence_name

[INCREMENTBYn]

[STARTWITHn]

[{MAXVALUEn|NOMAXVALUE}]

[{MINVALUEn|NOMINVALUE}]

[{CYCLE|NOCYCLE}]

[{CACHEn|NOCACHE}];

INCREMENTBY：

指定序列号之间的间隔，该值可为正的或负的整数，但不可为0。

序列为升序。

忽略该子句时，缺省值为1。

STARTWITH：

指定生成的第一个序列号。

在升序时，序列可从比最小值大的值开始，缺省值为序列的最小值。

对于降序，序列可由比最大值小的值开始，缺省值为序列的最大值。

MAXVALUE：

指定序列可生成的最大值。

NOMAXVALUE：

为升序指定最大值为1027，为降序指定最大值为-1。

MINVALUE：

指定序列的最小值。

NOMINVALUE：

为升序指定最小值为1。

为降序指定最小值为-1026。

CYCLE：

当指定了CYCLE之后，则当序列达到最大值之后，就会返回并从头开始。

CACHEn：

如果指定CACHE值，那么oracle预先就在内存中存放了sequece，这样就提高了存取的速度。

查询序列

查询数据字典视图USER_SEQUENCES获取序列定义信息，如果指定NOCACHE选项，则列LAST_NUMBER显示序列中下一个有效的值。

SELECTsequence_name,min_value,max_value,increment_by,last_number

FROMuser_sequences;

修改序列

可以修改序列的增量,最大值,最小值,循环选项,或是否装入内存等。

ALTERSEQUENCE[user.]sequence_name

[MAXVALUEn|NOMAXVALUE]

[MINVALUEn|NOMINVALUE]...；

删除序列

使用DROPSEQUENCE语句删除序列，删除之后，序列不能再次被引用。

DROPSEQUENCE[user.]sequence_name；

使用序列

序列提供两个方法，NextVal和CurrVal。

顾名思义，NextVal为取序列的下一个值，一次NEXTVAL会增加一次sequence的值；

CURRVAL为取序列的当前值。

-NEXTVAL返回序列中下一个有效的值，任何用户都可以引用：

sequence_name.NEXTVAL

-CURRVAL中存放序列的当前值：

sequence_name.CURRVAL

例子：

INSERTINTOdepartments（department_id,department_name,location_id）

VALUES（dept_deptid_seq.NEXTVAL,'

Support'

2500）;

序列DEPT_DEPTID_SEQ的当前值：

SELECTdept_deptid_seq.CURRVAL

FROMdual;

//dual是Oracle中的一张虚拟表

Orcle中的索引

索引

-一种数据库对象

-通过指针加速Oracle服务器的查询速度

-通过快速定位数据的方法，减少磁盘I/O

-索引与表相互独立

-Oracle服务器自动使用和维护索引

创建索引

-自动创建:

在定义PRIMARYKEY或UNIQUE约束后系统自动在相应的列上创建唯一性索引

-手动创建:

用户可以在其它列上创建非唯一的索引，以加速查询

手动在表的一个或者多个列上创建索引：

CREATEINDEXindexnameONtablename（column[,column]...）;

修改表时创建索引：

ALTERTABLEtablenameADDINDEX[索引的名字]（列的列表）;

创建表时创建索引：

CREATETABLEtablename（[...],INDEX[索引的名字]（列的列表））;

查询索引

可以使用数据字典视图USER_INDEXES和USER_IND_COLUMNS查看索引的信息。

SELECTic.index_name,ic.column_name,ic.column_positioncol_pos,ix.uniqueness

FROMuser_indexesix,user_ind_columnsic

WHEREic.index_name=ix.index_name

ANDic.table_name='

EMPLOYEES'

删除索引

使用DROPINDEX命令删除索引。

语法：

DROPINDEXindex_name;

Oracle索引的分类

逻辑上：

Singlecolumn单列索引

Concatenated多列索引

Unique唯一索引

NonUnique非唯一索引

Function-based函数索引

Domain域索引

物理上：

Partitioned分区索引

NonPartitioned非分区索引

B-tree：

Normal正常型B树

ReverKey反转型B树

Bitmap位图索引

索引结构：

最常见的索引结构，默认建立的索引就是这种类型的索引，适合于大量的增、删、改（OLTP）；

不能用包含OR操作符的查询；

适合高基数的列（唯一值多）

典型的树状结构；

每个结点都是数据块；

大多都是物理上一层、两层或三层不定，逻辑上三层；

叶子块数据是排序的，从左向右递增；

在分支块和根块中放的是索引的范围；

Bitmap：

适合于决策支持系统；

做UPDATE代价非常高；

非常适合OR操作符的查询；

基数比较少的时候才能建位图索引；

树型结构：

索引头

开始ROWID，结束ROWID（先列出索引的最大范围）

BITMAP每一个BIT对应着一个ROWID，它的值是1还是0，如果是1，表示着BIT对应的ROWID有值；

反向B-tree：

适用于OPS或RAC环境；

反转了索引码中每列的字节，降低索引叶块的争用；

什么时候创建索引

以下情况可以创建索引:

-列中数据值分布范围很广

-列中包含大量空值

-列经常在WHERE子句或连接条件中出现

-表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%

什么时候不要创建索引

下列情况不要创建索引:

-表很小

-列不经常作为连接条件或不出现在WHERE子句中

-查询的数据大于2%到4%

-表经常更新

-加索引的列包含在表达式中

Oralce的同义词

使用同义词访问相同的对象:

-方便访问其它用户的对象

-缩短对象名字的长度

创建同义词

CREATE[PUBLIC]SYNONYMsynonym_nameFORobject;

删除同义词

DROPSYNONYMsynonym_name;

Oracle的DBLINK

同一个数据库中的多个用户之间，可以通过同义词来共享表数据，但是如果两个数据库位于不同的服务器或者相同服务器的不同实例下的时候，共享表数据就要通过DBLINK这种方式了

看一个例子

创建dblink一般有两种方式，不过在创建dblink之前用户必须有创建dblink的权限。

想知道有关dblink的权限，以sys用户登录到本地数据库：

select*fromuser_sys_privst

wheret.privilegelikeupper（'

%link%'

）;

1SYSCREATEDATABASELINKNO

2SYSDROPPUBLICDATABASELINKNO

3SYSCREATEPUBLICDATABASELINKNO

可以看出在数据库中dblink有三种权限

CREATEDATABASELINK（所创建的dblink只能是创建者能使用）；

CREATEPUBLICDATABASELINK（public表示所创建的dblink所有人都可用）；

DROPPUBLICDATABASELINK。

在sys用户下，把CREATEPUBLICDATABASELINK，DROPPUBLICDATABASELINK权限授予给你的用户（假设用户是scott）

grantCREATEPUBLICDATABASELINK，DROPPUBLICDATABASELINKtoscott;

然后以scott用户登录本地数据库

1.创建dblink的第一种方式，是在本地数据库tnsnames.ora文件中配置了要远程访问的数据库。

createpublicdatabaselink

to_bylwconnecttoscottidentifiedbytigerusing'

bylw'

其中to_bylw是你创建的dblink名字，bylw是远程数据库的实例名，scott/tiger是登录到远程数据库的用户/密码。

然后在本地数据库中通过dblink访问远程数据库'

中scott.tb_test表，sql语句如下所示

select*fromscott.tb_test@to_bylw;

2.创建dblink的第二种方式，是在本地数据库tnsnames.ora文件中没有配置要访问的远程数据库，

createdatabaselinkto_test

connecttoscottidentifiedbytiger

using'

（DESCRIPTION=

　　（ADDRESS_LIST=

　　（ADDRESS=（PROTOCOL=TCP）（HOST=192.168.1.5）（PORT=1521）））

　　（CONNECT_DATA=

　　（SERVER=DEDICATED）

　　（SERVICE_NAME=bylw））

）'

第二种是把第一种配置在tnsnames.ora文件中的信息，直接放在创建dblink语句后面。

第一种情况tnsnames.ora文件中信息如下：

bylw=

　　（DESCRIPTION=

　　（ADDRESS_LIST=

　　（ADDRESS=（PROTOCOL=TCP）（HOST=192.168.1.5）（PORT=1521））

　　）

　　（CONNECT_DATA=

　　（SERVER=DEDICATED）

　　（SERVICE_NAME=bylw）

　）

触发器

功能：

1、允许/限制对表的修改

2、自动生成派生列，比如自增字段

3、强制数据一致性

4、提供审计和日志记录

5、防止无效的事务处理

6、启用复杂的业务逻辑

开始

createtriggerbiufer_employees_department_id

beforeinsertorupdate

ofdepartment_id

onemployees

referencingoldasold_value

newasnew_value

foreachrow

when（new_value.department_id<

>

80）

begin

:

new_mission_pct:

=0;

end;

/

触发器的组成部分：

1、触发器名称

2、触发语句

3、触发器限制

4、触发操作

命名习惯：

biufer（beforeinsertupdateforeachrow）

employees表名

department_id列名

比如：

表或视图上的DML语句

DDL语句

数据库关闭或启动,startupshutdown等等

说明：

1、无论是否规定了department_id，对employees表进行insert的时候

2、对employees表的department_id列进行update的时候

限制不是必须的。

此例表示如果列department_id不等于80的时候，触发器就会执行。

其中的new_value是代表跟新之后的值。

是触发器的主体

主体很简单，就是将更新后的commission_pct列置为0

触发：

insertintoemployees（employee_id,

last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct）

values（12345,'

Chen'

'

Donny'

sysdate,12,‘donny@'

60,10000,.25）;

selectcommission_pctfromemployeeswhereemployee_id=12345;

触发器不会通知用户，便改变了用户的输入值。

触发器类型：

1、语句触发器

2、行触发器

3、INSTEADOF触发器

4、系统条件触发器

5、用户事件触发器

是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。

能够与INSERT、UPDATE、

DELETE或者组合上进行关联。

但是无论使用什么样的组合，各个语句触发器都只会针对指定语句激活一次

。

比如，无论update多少行，也只会调用一次update语句触发器。

需要对在表上进行DML操作的用户进行安全检查，看是否具有合适的特权。

Createtablefoo（anumber）;

Createtriggerbiud_foo

Beforeinsertorupdateordelete

Onfoo

Begin

Ifusernotin（‘DONNY'

）then

Raise_application_error（-20001,‘Youdon'

thaveaccesstomodifythistable.'

Endif;

End;

二、ORACLESQL性能优化

1.选用适合的ORACLE优化器

ORACLE的优化器共有3种:

a.RULE（基于规则）

b.COST（基于成本）

c.CHOOSE（选择性）

设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种

声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS.你当然也在SQL句

级或是会话（session）级对其进行覆盖.

为了使用基于成本的优化器（CBO,Cost-BasedOptimizer）,你必须经常运行

analyze命令,以增加数据库中的对象统计信息（objectstatistics）的准确性.

如果数据库的优化器模式设置为选择性（CHOOSE）,那么实际的优化器模式将和