一、用户的创建和管理
创建和删除用户是Oracle用户管理中的常见操作,但这其中隐含了Oracle数据库系统的系统权限与对象权限方面的知识。掌握还Oracle用户的授权操作和原理,可以有效提升我们的工作效率。Oracle数据库的权限系统分为系统权限与对象权限。系统权限( Database System Privilege )可以让用户执行特定的命令集。例如,CREATE TABLE权限允许用户创建表, GRANT ANY PRIVILEGE 权限允许用户授予任何系统权限。对象权限( Database Object Privilege )可以让用户能够对各个对象进行某些操作。例如DELETE权限允许用户删除表或视图的行, SELECT权限允许用户通过select从表、视图、序列(sequences)或快照(snapshots)中查询信息。
每个Oracle用户都有一个名字和口令,并拥有一些由其创建的表、视图和其他资源。Oracle 角色(role)就是一组权限(privilege)(或者是每个用户根据其状态和条件所需的访问类型)。用户可以给角色授予或赋予指定的权限,然后将角色赋给相应的用户。一个用户也可以直接给其他用户授权。
1、创建用户
Oracle内部有两个建好的用户:SYSTEM和SYS。用户可直接登录到SYSTEM用户以创建其他用户,因为SYSTEM具有创建别的用户的权限。在安装Oracle时,用户或系统管理员首先可以为自己建立一个用户。例如:
create user user01 identified by u01;
该命令还可以用来设置其他权限,详细情况参见自学资料。要改变一个口令,可以使用alter user命令:
alter user user01 identified by usr01;
现在user01的口令已由“u01”改为“usr01”。
2、删除用户
删除用户,可以使用drop user命令,如下所示:
drop user user01;
如果用户拥有对象,则不能直接删除,否则将返回一个错误值。指定关键字CASCADE,可删除用户所有的对象,然后再删除用户。下面的例子用来删除用户与其对象:
drop user user01 CASCADE;
3、三种
标准
excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载
角色
Oracle为了兼容以前的版本,提供了三种标准的角色(role):CONNECT、RESOURCE和DBA。
1. CONNECT Role(连接角色)
临时用户,特别是那些不需要建表的用户,通常只赋予他们CONNECTrole。CONNECT是使用Oracle的简单权限,这种权限只有在对其他用户的表有访问权时,包括select、insert、update 和delete等,才会变得有意义。拥有CONNECT role的用户还能够创建表、视图、序列(sequence)、簇(cluster)、同义词(synonym )、会话(session)和与其他数据库的链(link)。
2. RESOURCE Role(资源角色)
更可靠和正式的数据库用户可以授予RESOURCE role。RESOURCE提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)和簇(cluster)。
3. DBA Role(数据库管理员角色)
DBA role拥有所有的系统权限----包括无限制的空间限额和给其他用户授予各种权限的能力。SYSTEM由DBA用户拥有。下面介绍一些DBA经常使用的典型权限。
(1)grant(授权)命令
下面对刚才创建的用户user01授权,命令如下:
grant connect, resource to user01;
(2)revoke(撤消)权限
已授予的权限可以撤消。例如撤消(1)中的授权,命令如下:
revoke connect, resource from user01;
(3)常用的数据对象权限有以下五个:
ALL ON 数据对象名,SELECT ON 数据对象名,UPDATE ON 数据对象名,DELETE ON 数据对象名, INSERT ON 数据对象名,ALTER ON 数据对象名
GRANT SELECT ON 表名 TO 用户名;
GRANT SELECT, INSERT, DELETE ON 表名 TO 用户名1,用户名2;
一个具有DBA角色的用户可以撤消任何别的用户甚至别的DBA的CONNECT、RESOURCE 和DBA的其他权限。当然,这样是很危险的,因此,除非真正需要,DBA权限不应随便授予那些不是很重要的一般用户。
撤消一个用户的所有权限,并不意味着从Oracle中删除了这个用户,也不会破坏用户创建的任何表;只是简单禁止其对这些表的访问。其他要访问这些表的用户可以象以前那样地访问这些表。
4、创建角色
除了前面讲到的三种系统角色----CONNECT、RESOURCE和DBA,用户还可以在Oracle创建自己的role。用户创建的role可以由表或系统权限或两者的组合构成。为了创建role,用户必须具有CREATE ROLE系统权限。下面给出一个create role命令的实例:
create role STUDENT;
这条命令创建了一个名为STUDENT的role。
一旦创建了一个role,用户就可以给他授权。给role授权的grant命令的语法与对对用户的语法相同。在给role授权时,在grant命令的to子句中要使用role的名称,如下所示: grant select on CLASS to STUDENT;
现在,拥有STUDENT 角色的所有用户都具有对CLASS 表的select权限。
5、删除角色
要删除角色,可以使用drop role命令,如下所示:
drop role STUDENT;
指定的role连同与之相关的权限将从数据库中全部删除。
二、数据文件和表空间的介绍
一个Oracle数据库是数据的集合,被处理成一个单位。一个Oracle数据库有一个物理结构和一个逻辑结构。
物理数据库结构是由构成数据库的操作系统文件所决定。每一个Oracle数据库是由三种类型的文件组成:数据文件、日志文件和控制文件。数据库的文件为数据库信息提供真正的物理存储。逻辑数据库结构是用户所涉及的数据库结构。一个ORACLE数据库的逻辑结构由下列因素决定:
一个或多个表空间
数据库模式对象(即表、视图、索引、聚集、序列、存储过程)
逻辑存储结构如表空间(dataspace)、段(segment)和范围将支配一个数据库的物理空间如何使用。模式对象(schema object)用它们之间的联系组成了一个数据库的关系设计。
1)物理结构
(1)数据文件
每一个ORACLE数据库有一个或多个物理的数据文件(data file)。一个数据库的数据文件包含全部数据库数据。逻辑数据库结构(如表、索引)的数据物理地存储在数据库的数据文件中。数据文件有下列特征:
一个数据文件仅与一个数据库联系。
一旦建立,数据文件不能改变大小
一个表空间(数据库存储的逻辑单位)由一个或多个数据文件组成。
数据文件中的数据在需要时可以读取并存储在Oracle内存储区中。例如:用户要存取数据库一表的某些数据,如果请求信息不在数据库的内存存储区内,则从相应的数据文件中读取并存储在内存。当修改和插入新数据时,不必立刻写入数据文件。为了减少磁盘输出的总数,提高性能,数据存储在内存,然后由Oracle后台进程DBWR决定如何将其写入到相应的数据文件。
2)逻辑结构
数据库逻辑结构包含表空间、段、范围(extent)、数据块和模式对象。
(1)表空间
一个数据库划分为一个或多个逻辑单位,该逻辑单位称为表空间(TABLESPACE)。一个表空间可将相关的逻辑结构组合在一起。DBA可利用表空间作下列工作:
控制数据库数据的磁盘分配。
将确定的空间份额分配给数据库用户。
通过使单个表空间在线或离线,控制数据的可用性。
执行部分数据库后备或恢复操作。
为提高性能,跨越设备分配数据存储。
每个数据库可逻辑划分为一个或多个表空间
每一个表空间是由一个或多个数据文件组成,该表空间物理地存储表空间中全部逻辑结构的数据。DBA可以建立新的表空间,可为表空间增加数据文件或可删除数据文件,设置或更改缺省的段存储位置。
每一个Oracle数据库包含有一个名为SYSTEM的表空间,在数据库建立是自动建立。在该表空间中总包含有整个数据库的数据字典表。最小的数据库可只需要SYSTEM表空间。该表空间必须总是在线。表和存储的PL/SQL程序单元(过程、函数、包和触发器)的全部存储数据是存储在SYSTEM表空间中。如果这些PL/SQL对象是为数据库建的,DBA在SYSTEM表空间中需要规划这些对象所需要的空间。
表空间利用增加数据文件扩大表空间,表空间的大小为组成该表空间的数据文件大小的和。表空间实质是组织数据文件的一种途径,Oracle就是通过表空间这个数据库对象完成对数据的组织的。在将数据插入Oracle数据库之前,必须首先建立表空间,然后将数据插入表空
间的一个对象中。解释数据库、表空间、数据文件、表、数据文件的最好办法就是想象一个装满东西的柜子。数据库其实就是柜子,柜中的抽屉是表空间,抽屉中的文件夹是数据文件,文件夹中的纸是表,写在纸上的信息就是数据。
根据表空间的用途可分为五类:
1、目录表空间
每个数据库只有一个目录表空间,它是在发出CREATE DATABASE 命令时创建的。目录表空间被DB2 命名为SYSCATSPACE,它保存了系统目录表。总是在创建数据库时创建该表空间。(CAT:目录)
-常规表空间
常规表空间:保存表数据和索引。它还可以保存诸如大对象(Large Object,LOB)之类的长数据,除非这些数据显式地存储在长表空间中。如果某些表空间是数据库管理的空间(Database Managed Space,DMS),则可以将表及其索引分别放到单独的常规表空间中。我们将在本文后面定义DMS 和系统管理的空间(System Managed Space,SMS)之间的区别。每个数据库中必须至少有一个常规表空间。创建数据库时指定该表空间的缺省名为USERSPACE1。
2、长表空间
长表空间:用于存储长型或LOB 表列,它们必须驻留在DMS 表空间中。它们还可以存储结构化类型的列或索引数据。如果没有定义长表空间,那么将把LOB 存储在常规表空间中。长表空间是可选的,缺省情况下一个都不创建。
3、系统临时表空间
系统临时表空间:用于存储SQL 操作(比如排序、重组表、创建索引和连接表)期间所需的内部临时数据。每个数据库必须至少有一个系统临时表空间。随数据库创建的系统临时表空间的缺省名为TEMPSPACE1。
4、用户临时表空间
用户临时表空间:存储已声明的全局临时表。创建数据库时不存在用户临时表空间。至少应当创建一个用户临时表空间以允许定义已声明的临时表。用户临时表空间是可选的,缺省情况下一个都不创建。
用户临时表空间用来存储:已说明全局临时表(已说明全局临时表:存储的是应用程序临时数据)。
创建用户临时表空间的语法:
CODE:
CREATE USER TEMPORARY TABLESPACE usr_tbsp
MANAGED BY DATABASE
USING (FILE 'd:\db2data\user_tbsp' 5000,
FILE 'e:\db2data\user_tbsp' 5000)
与常规表空间一样,用户临时表空间可以在除IBMTEMPGROUP之外的任何节点组中创建。创建用户临时表空间使用的默认节点组是IBMDEFAULTGROUP。DECLARE GLOBAL TEMPORARY TABLE语句定义供在用户临时表空间中使用的已说明临时表。
5、数据表空间
表空间的作用能帮助DBA用户完成以下工作:
1.决定数据库实体的空间分配;
2.设置数据库用户的空间份额;
3.控制数据库部分数据的可用性;
4.分布数据于不同的设备之间以改善性能;
5.备份和恢复数据。
用户创建其数据库实体时其必须于给定的表空间中具有相应的权力,所以对一个用户来说,其要操纵一个Oracle数据库中的数据,应该:
1.被授予关于一个或多个表空间中的RESOURCE特权;
2.被指定缺省表空间;
3.被分配指定表空间的存储空间使用份额;
4.被指定缺省临时段表空间。
表空间的操作与维护
表空间的维护是由Oracle数据库系统管理员DBA通过SQL*PLUS语句实现的,其中表空间创建与修改中的文件名是不能带路径的,因此DBA必须在Oracle/DBS目录中操作。
oracle表空间(C:\oraclexe\oradata\XE\*.dbf)
一个表空间就是一片磁盘区域,他又一个或者多个磁盘文件组成,一个表空间可以容纳许多表、索引或者簇等。
每个表空间又一个预制的打一磁盘区域称为初始区间用完这个区间厚在用下一个,知道用完表空间,这时候需要对表空间进行扩展,增加数据文件或者扩大已经存在的数据文件
创建立表空间
CREATE TABLESPACE cheng DATAFILE 'C:\Oraclexe\oradata\XE\cheng.dbf' SIZE 500M UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定,区尺寸默认为64k
删除表空间
DROP TABLESPACE cheng INCLUDING CONTENTS AND DATAFILES
修改表空间大小
alter database datafile 'c:\Oraclexe\oradata\XE\cheng.dbf' resize 50M
移动表至另一表空间
alter table move tablESPace room1;
二、建立UNDO表空间
CREATE UNDO TABLESPACE UNDOTBS02
DATAFILE '/Oracle/oradata/db/UNDOTBS02.dbf' SIZE 50M
#注意:在OPEN状态下某些时刻只能用一个UNDO表空间,如果要用新建的表空间,必须切换到该表空间:
ALTER SYSTEM SET undo_tablespace=UNDOTBS02;
UNDO表空间的管理
1.概念
回滚段概述
回滚段用于存放数据修改之前的值(包括数据修改之前的位置和值)。回滚段的头部包含正在使用的该回滚段事务的信息。一个事务只能使用一个回滚段来存放它的回滚信息,而一个回滚段可以存放多个事务的回滚信息。
回滚段的作用
1。事务回滚:当事务修改表中数据的时候,该数据修改前的值(即前影像)会存放在回滚段中,当用户回滚事务(ROLLBACK)时,ORACLE将会利用回滚段中的数据前影像来将修改的数据恢复到原来的值。
2。事务恢复:当事务正在处理的时候,例程失败,回滚段的信息保存在重做日志文件中,ORACLE将在下次打开数据库时利用回滚来恢复未提交的数据。
3。读一致性:当一个会话正在修改数据时,其他的会话将看不到该会话未提交的修改。而且,当一个语句正在执行时,该语句将看不到从该语句开始执行后的未提交的修改(语句级读一致性)。当ORACLE执行SELECT语句时,ORACLE依照当前的系统改变号(SYSTEM CHANGE NUMBER-SCN)来保证任何当前SCN的未提交的改变不被该语句处理。可以想象:
当一个长时间的查询正在执行时,若其他会话改变了该查询要查询的某个数据块,Oracle 将利用回滚段的数据前影像来构造一个读一致性视图。
三、建立临时表空间
CREATE TEMPORARY TABLESPACE temp_data
TEMPFILE '/Oracle/oradata/db/TEMP_DATA.dbf' SIZE 50M
四、改变表空间状态
1.使表空间脱机
ALTER TABLESPACE game OFFLINE;
如果是意外删除了数据文件,则必须带有RECOVER选项
ALTER TABLESPACE game OFFLINE FOR RECOVER;
2.使表空间联机
ALTER TABLESPACE game ONLINE;
3.使数据文件脱机
ALTER DATABASE DATAFILE 3 OFFLINE;
4.使数据文件联机
ALTER DATABASE DATAFILE 3 ONLINE;
5.使表空间只读
ALTER TABLESPACE game READ ONLY;
6.使表空间可读写
ALTER TABLESPACE game READ WRITE;
五、删除表空间
DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;
六、扩展表空间
首先查看表空间的名字和所属文件
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;
1.增加数据文件
ALTER TABLESPACE game
ADD DATAFILE '/Oracle/oradata/db/GAME02.dbf' SIZE 1000M;
2.手动增加数据文件尺寸
ALTER DATABASE DATAFILE '/Oracle/oradata/db/GAME.dbf'
RESIZE 4000M;
3.设定数据文件自动扩展
ALTER DATABASE DATAFILE '/Oracle/oradata/db/GAME.dbf
AUTOEXTEND ON NEXT 100M
MAXSIZE 10000M;
设定后查看表空间信息
SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME
三、临时文件及表空间的介绍
临时文件(Tempfiles)
临时文件并不是指临时存在的文件,而是临时表空间对应的数据文件,从实际表现上来看,该文件也不是说用完就会被删除,而是指该文件中存储的对象属于临时存在对象。
这是一类特殊的数据文件,该文件也是用来保存数据。通常,Oracle在执行大规模排序操作时,如果PGA内存区中无法存放,就会将数据存储到临时表空间中作为中转,但是这部分空间并不是永久占用,当操作执行完毕后,这部分空间又可以随时再被其他操作临时使用,因此你可以把这类文件理解成需要时临时拿来用用的文件。不过,临时表空间的数据文件确实与普通表空间数据文件有不同之外:
因为临时表空间必须处于Nologging模式,因此临时表空间数据文件也处于Nologging模式。临时表空间数据文件不能被置为只读。
不能通过ALTER DATABASE语句创建临时表空间数据文件。
通过RMAN备份时不会备份临时表空间数据文件,恢复时自然也不会对其进行恢复。不过10g 环境下,通过RMAN执行数据库恢复后,当打开数据库时,会自动创建临时表空间数据文件。临时表空间数据文件在分配存储空间时也与普通表空间数据文件不同。普通表空间数据文件
分配空间时,需要等待操作系统格式化相应空间。临时表空间数据文件是一种稀疏(Sparse)类型的文件,初始创建时只是看起来占用了指定大小的空间,但实际上此时空间并未真正占用,这类文件的空间将会在实际使用时进行分配。
可以通过DBA_TEMP_FILES或V$TEMPFILE视图查询当前数据库中临时表空间的数据文件信息,例如:
SQL> SELECT NAME FROM V$TEMPFILE;
临时表空间主要用途是在数据库进行排序运算[如创建索引、order by及group by、distinct、union/intersect/minus/、sort-merge及join、analyze命令]、管理索引[如创建索引、IMP进行数据导入]、访问视图等操作时提供临时的运算空间,当运算完成之后系统会自动清理。
当临时表空间不足时,表现为运算速度异常的慢,并且临时表空间迅速增长到最大空间(扩展的极限),并且一般不会自动清理了。
如果临时表空间没有设置为自动扩展,则临时表空间不够时事务执行将会报ora-01652无法扩展临时段的错误,当然解决方法也很简单:1、设置临时数据文件自动扩展,或者2、增大临时表空间。
临时表空间的相关操作:
查询默认临时表空间:
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
查询临时表空间状态:
select tablESPace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
扩展临时表空间:
方法一、增大临时文件大小:
alter database tempfile ‘/u01/app/Oracle/oradata/orcl/temp01.dbf’ resize 100m;
方法二、将临时数据文件设为自动扩展:
alter database tempfile ‘/u01/app/Oracle/oradata/orcl/temp01.dbf’ autoextend on next 5m maxsize unlimited;
方法三、向临时表空间中添加数据文件:
alter tablESPace temp add tempfile ‘/u01/app/Oracle/oradata/orcl/temp02.dbf’ size 100m;
删除临时表空间的一个数据文件:
alter database tempfile '/u01/app/Oracle/oradata/orcl/temp02.dbf' drop;
删除临时表空间(彻底删除):
drop tablESPace temp1 including contents and datafiles cascade constraints;
创建临时表空间:
create temporary tablESPace temp1 tempfile
'/u01/app/Oracle/oradata/orcl/temp11.dbf' size 10M;
更改系统的默认临时表空间:
alter database default temporary tablespace temp1;
所有用户的默认临时表空间都将切换为新的临时表空间:
select username,temporary_tablespace,default_ from dba_users;
更改某一用户的临时表空间:
alter user scott temporary tablespace temp;
select username,temporary_tablespace from dba_users;
创建新的临时表空间:
create temporary tablespace temp02
tempfile '/oracle/oms/oradata/undo/temp02.dbf' size 512M;
把新建的临时表空间却换成数据库的默认临时表空间
alter database default temporary tablespace temp02;
确认目前数据库的默认临时表空间
select * from database_properties
where property_name='DEFAULT_TEMP_TABLESPACE';
在删除temp临时表空间之前,先把运行在temp临时表空间的sql语句kill掉,这样的sql语句多为排序的语句
SQL>Select
se.username,se.sid,se.serial#,su.extents,su.blocks*to_number(rtrim(p.value))as Space,
tablespace,segtype,sql_text
from v$sort_usage su,v$parameter p,v$session se,v$sql s
where p.name='db_block_size' and su.session_addr=se.saddr and
s.hashvalue=su.sqlhash
and s.address=su.sqladdr
order by se.username,se.sid;
查询出来之后,kill掉这些sql语句:
alter system kill session '524,778'; (假如某一条运行的sql语句的SID为524,serial#为778)
删除temp临时表空间
drop tablESPace temp including contents and datafiles;
现在temp02临时表空间占据了别人的磁盘空间,需要重新把临时表空间建立在原来的位置,重新建立temp临时表空间
create temporary tablESPace temp
tempfile '/Oracle/oms/oradata/temp/temp01.dbf'size 512M autoextend on maxsize 15G; 新建一个512M的自动扩展临时表空间,最大的扩展为15G。
查看新建的temp临时表空间是否正确:
select
file_name,tablespace_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_temp_files;
把新建的temp临时表空间却换成数据库的默认临时表空间
alter database default temporary tablespace temp;
确认目前数据库的默认临时表空间
select * from database_properties
where property_name='DEFAULT_TEMP_TABLESPACE';
目前把原来的temp临时表空间变成了512M,把剩余的磁盘空间空了出来,temp02临时表空间就没有用了,删除temp02临时表空间
drop tablespace temp02 including contents and datafiles;
四、索引的介绍
数据库索引好比是一本
书
关于书的成语关于读书的排比句社区图书漂流公约怎么写关于读书的小报汉书pdf
前面的目录,能加快数据库的查询速度。例如这样一个查询: select * from table1 where id=44。如果没有索引,必须遍历整个表,直到ID等于44的这一行被找到为止;有了索引之后(必须是在ID这一列上建立的索引),直接在索引里面
找44(也就是在ID这一列找),就可以得知这一行的位置,也就是找到了这一行。可见,索引是用来定位的。索引分为聚簇索引和非聚簇索引两种,聚簇索引是按照数据存放的物理位置为顺序的,而非聚簇索引就不一样了;聚簇索引能提高多行检索的速度,而非聚簇索引对于单行的检索很快。
创建索引可以大大提高系统的性能。第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。第二,可以大大加快数据的检索速度,这也是创建索引的最主要的原因。第三,可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。第四,在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。第五,通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。
也许会有人要问:增加索引有如此多的优点,为什么不对表中的每一个列创建一个索引呢?因为,增加索引也有许多不利的方面。第一,创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。第二,索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。第三,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。
索引是建立在数据库表中的某些列的上面。在创建索引的时候,应该考虑在哪些列上可以创建索引,在哪些列上不能创建索引。一般来说,应该在这些列上创建索引:在经常需要搜索的列上,可以加快搜索的速度;
在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;
在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;
在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;
在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。
同样,对于有些列不应该创建索引。一般来说,不应该创建索引的的这些列具有下列特点:
第一,对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。
第二,对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值
很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。
第三,对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少,不利于使用索引。
第四,当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改操作远远多于检索操作时,不应该创建索引。表记录太少
第五,如果一个表只有5条记录,采用索引去访问记录的话,那首先需访问索引表,再通过索引表访问数据表,一般索引表与数据表不在同一个数据块,这种情况下Oracle至少要往返读取数据块两次。而不用索引的情况下Oracle会将所有的数据一次读出,处理速度显然会比用索引快。
第六,经常插入、删除、修改的表。对一些经常处理的业务表应在查询允许的情况下尽量减少索引。数据重复且分布平均的表字段
根据数据库的功能,可以在数据库设计器中创建三种索引:唯一索引、主键索引和聚集索引。有关数据库所支持的索引功能的详细信息,请参见数据库文档。
提示:尽管唯一索引有助于定位信息,但为获得最佳性能结果,建议改用主键或唯一约束。
唯一索引唯一索引是不允许其中任何两行具有相同索引值的索引。
当现有数据中存在重复的键值时,大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如,如果在employee表中职员的姓(lname)上创建了唯一索引,则任何两个员工都不能同姓。
主键索引数据库表经常有一列或列组合,其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引,主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时,它还允许对数据的快速访问。
聚集索引在聚集索引中,表中行的物理顺序与键值的逻辑(索引)顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引,则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比,聚集索引通常提供更快的数据访问速度。
要注意的是,建立太多的索引将会影响更新和插入的速度,因为它需要同样更新每个索
引文件。对于一个经常需要更新和插入的表格,就没有必要为一个很少使用的where字句单独建立索引了,对于比较小的表,排序的开销不会很大,也没有必要建立另外的索引。
查看系统表中的用户索引
在Oracle中,SYSTEM表是安装数据库时自动建立的,它包含数据库的全部数据字典,存储过程、包、函数和触发器的定义以及系统回滚段。
一般来说,应该尽量避免在SYSTEM表中存储非SYSTEM用户的对象。因为这样会带来数据库维护和管理的很多问题。一旦SYSTEM表损坏了,只能重新生成数据库。我们可以用下面的语句来检查在SYSTEM表内有没有其他用户的索引存在。
select* from dba_indexes where tablESPace_name = 'SYSTEM'and owner not in ('SYS','SYSTEM')
索引按功能分类
唯一索引
唯一索引有两个作用,一个是数据约束,一个是数据索引,其中数据约束主要用来保证数据的完整性,唯一索引产生的索引记录中每一条记录都对应一个唯一的ROWID。
主关键字索引
主关键字索引产生的索引同唯一索引,只不过它是在数据库建立主关键字时系统自动建立的。一般索引
一般索引不产生数据约束作用,其功能主要是对字段建立索引表,以提高数据查询速度。
索引按索引对象分类
单列索引(表单个字段的索引)
多列索引(表多个字段的索引)
函数索引(对字段进行函数运算的索引)
如何快速新建大数据量表的索引
如果一个表的记录达到100万以上的话,要对其中一个字段建索引可能要花很长的时间,甚至导致服务器数据库死机,因为在建索引的时候Oracle要将索引字段所有的内容取出并进行全面排序,数据量大的话可能导致服务器排序内存不足而引用磁盘交换空间进行,这将严重影响服务器数据库的工作。解决方法是增大数据库启动初始化中的排序内存参数,如果要进行大量的索引修改可以设置10M以上的排序内存(ORACLE缺省大小为64K),在索引建立完成后应将参数修改回来,因为在实际OLTP数据库应用中一般不会用到这么大的排序内存。用索引提高效率
索引是表的一个概念部分,用来提高检索数据的效率. 实际上,Oracle使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.
除了那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率.
虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来
存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.
定期的重构索引是有必要的.
ALTER INDEX
REBUILD