ORACLE的索引和约束详解

ORACLE的索引和约束详解

博客分类：

数据库

Oracle数据结构

Oracle的约束--

primary key, not null, unique,foreign key, check 约束

* 如果某个约束只作用于单独的字段，即可以在字段级定义约束，也可以在表级定义约束，但如果某个约束作用于多个字段，

必须在表级定义约束

* 在定义约束时可以通过CONSTRAINT关键字为约束命名，如果没有指定，ORACLE 将自动为约束建立默认的名称

PRIMARY KEY

定义primary key约束(单个字段）

create table employees (empno number(5) primary key,...)

指定约束名

create table employees (empno number(5) constraint emp_pk primary key,...)

定义primary key约束(多个字段,在表级定义约束）

create table employees

(empno number(5),

deptno number(3) not null,

constraint emp_pk primary key(empno,deptno)

using index tablespace indx

storage (initial 64K

next 64K

)

)

ORACLE自动会为具有PRIMARY KEY约束的字段(主码字段)建立一个唯一索引和

一个NOT NULL约束,定义PRIMARY KEY约束时可以为它的索引指定存储位置和存储参数

alter table employees add primary key (empno)

alter table employees add constraint emp_pk primary key (empno)

alter table employees add constraint emp_pk primary key (empno,deptno)

not null约束(只能在字段级定义NOT NULL约束,在同一个表中可以定义多个NOT NULL约束)

alter table employees modify deptno not null/null

unique约束

create table employees

( empno number(5),

ename varchar2(15),

phone varchar2(15),

email varchar2(30) unique,

deptno number(3) not null,

constraint emp_ename_phone_uk unique (ename,phone)

)

alter table employees

add constraint emp_uk unique(ename,phone)

using index tablespace indx

定义了UNIQUE约束的字段中不能包含重复值，可以为一个或多个字段定义UNIQUE约束,因此，UNIQUE即可以在字段级也可以在表级定义，

在UNIQUED约束的字段上可以包含空值.

foreign key约束

* 定义为FOREIGN KEY约束的字段中只能包含相应的其它表中的引用码字段的值或者NULL值

* 可以为一个或者多个字段的组合定义FOREIGN KEY约束

* 定义了FOREIGN KEY约束的外部码字段和相应的引用码字段可以存在于同一个表中，这种情况称为"自引用"

* 对同一个字段可以同时定义FOREIGN KEY约束和NOT NULL约束

定义了FOREIGN KEY约束的字段称为"外部码字段",被FORGIEN KEY约束引用的字段称为"引用码字段",引用码必须是主码或唯一码,包含外部码的表称为子表，包含引用码的表称为父表.

A:

create table employees

(.....,

deptno number(3) NOT NULL,

constraint emp_deptno_fk foreign key (deptno)

references dept (deptno)

)

如果子表中的外部码与主表中的引用码具有相同的名称，可以写成:

create table employees

(.....,

deptno number(3) NOT NULL

constraint emp_deptno_fk references dept

)

注意：

上面的例子(B)中not null后面没有加逗号,因为这一句的contraint是跟在那一列deptno后面的，属于列定义，所以都无需指明列。而A例中的是表定义，需要指明那一列，所以要加逗号，不能在列后面定义，还可以写成：

create table employees

(empno char(4),

deptno char(2) not null constraint emp_deptno_fk references dept, ename varchar2(10)

)

表定义contraint的只能写在最后，再看两个例子：

create table employees

(empno number(5),

ename varchar2(10),

deptno char(2) not null constraint emp_deptno_fk references dept, constraint emp_pk primary key(empno,ename)

)

create table employees

( empno number(5),

ename varchar2(15),

phone varchar2(15),

email varchar2(30) unique,

deptno number(3) not null,

constraint emp_pk primary key(empno,ename),

constraint emp_phone_uk unique (phone)

)

添加foreign key约束(多字段／表级）

alter table employees

add constraint emp_jobs_fk foreign key (job,deptno)

references jobs (jobid,deptno)

on delete cascade

更改foreign key约束定义的引用行为(delete cascade/delete set

null/delete no action),默认是delete on action

引用行为(当主表中一条记录被删除时，确定如何处理字表中的外部码字段）：delete cascade : 删除子表中所有的相关记录

delete set null : 将所有相关记录的外部码字段值设置为NULL

delete no action: 不做任何操作

先删除原来的外键约束,再添加约束

ALTER TABLE employees DROP CONSTRAINT emp_deptno_fk;

ALTER TABLE employees ADD CONSTRAINT emp_deptno_fk FOREIGN KEY(deptno) REFERENCES dept(deptno) ON DELETE CASCADE;

check约束

* 在CHECK约束的表达式中必须引用到表中的一个或多个字段，并且表达式的计算结果必须是一个布尔值

* 可以在表级或字段级定义

* 对同一个字段可以定义多个CHECK约束，同时也可以定义NOT NULL约束

create table employees

(sal number(7,2)

constraint emp_sal_ck1 check (sal > 0)

)

alter table employees

add constraint emp_sal_ck2 check (sal < 20000)

删除约束

alter table dept drop unique (dname,loc) --指定约束的定义内容

alter table dept drop constraint dept_dname_loc_uk --指定约束名

删除约束时，默认将同时删除约束所对应的索引，如果要保留索引，用KEEP INDEX 关键字

alter table employees drop primary key keep index

如果要删除的约束正在被其它约束引用，通过ALTER TABLE..DROP语句中指定CASCADE关键字能够同时删除引用它的约束

利用下面的语句在删除DEPT表中的PRIMARY KEY约束时，同时将删除其它表中引用这个约束的FOREIGN KEY约束:

alter table dept drop primary key cascade

禁用/激活约束(禁用/激活约束会引起删除和重建索引的操作)

alter table employees disable/enable unique email

alter table employees disable/enable constraint emp_ename_pk

alter tabel employees modify constraint emp_pk disable/enable

alter tabel employees modify constraint emp_ename_phone_uk

disable/enable

如果有FOREIGN KEY约束正在引用UNIQUE或PRIMARY KEY约束，则无法禁用这些UNIQUE或PRIMARY KEY约束，

这时可以先禁用FOREIGN KEY约束，然后再禁用UNIQUE或PRIMARY KEY约束；或者可以在ALTER TABLE...DISABLE

语句中指定CASCADE关键字，这样将在禁用UNIQUE或PRIMARY KEY约束的同时禁用那些引用它们的FOREIGN KEY约束，如：

alter table employees disable primary key cascade

约束数据字典

all_constraints/dba_constraints/user_constraints 约束的基本信息，包括约束的名称，类型，状态

(约束类型：C(CHECK约束),P(主码约束),R(外部码约束),U(唯一码约束))

all_cons_columns/dba/user 约束对应的字段信息

Oracle的索引

索引和对应的表应该位于不同的表空间中,oracle能够并行读取位于不同硬盘上的数据，可以避免产生I/O冲突

B树索引：在B树的叶节点中存储索引字段的值与ROWID。

唯一索引和不唯一索引都只是针对B树索引而言.

Oracle最多允许包含32个字段的复合索引

索引创建策略

1.导入数据后再创建索引

2.不需要为很小的表创建索引

3.对于取值范围很小的字段（比如性别字段）应当建立位图索引

4.限制表中的索引的数目

5.为索引设置合适的PCTFREE值

6.存储索引的表空间最好单独设定

创建不唯一索引

create index emp_ename on employees(ename)

tablespace users

storage(......)

pctfree 0;

创建唯一索引

create unique index emp_email on employees(email)

tablespace users;

创建位图索引

create bitmap index emp_sex on employees(sex)

tablespace users;

创建反序索引

create unique index order_reinx on orders(order_num,order_date) tablespace users

reverse;

创建函数索引(函数索引即可以是普通的B树索引，也可以是位图索引) create index emp_substr_empno

on employees(substr(empno,1,2))

tablespace users;

修改索引存储参数(与表类似，INITIAL和MINEXTENTS参数在索引建立以后不能再改变)

alter index emp_ename storage(pctincrease 50);

由于定义约束时由oracle自动建立的索引通常是不知道名称的，对这类索引的修改经常是利用alter table https://www.sodocs.net/doc/7e17331849.html,ing index语句进行的,而不是alter index 语句

利用下面的语句将employees表中primary key约束对应的索引的PCTFREE参数修改为5

alter table employees enable primary key using index pctfree 5;

清理索引碎片

1.合并索引(只是简单的将B树叶结点中的存储碎片合并在一起，并不会改变索引的物理组织结构）

alter index emp_pk coalesce;

2.重建索引(不仅能够消除存储碎片,还可以改变索引的全部存储参数设置，并且可以将索引移动到其它的表空间中,重建索引

实际上就是再指定的表空间中重新建立一个新的索引,然后删除原来的索引) alter index emp_pk rebuild;

删除索引

drop index emp_ename;

如果索引中包含损坏的数据块，或者包含过多的存储碎片，需要首先删除这个索引，然后再重建它.

如果索引是在创建约束时由oracle自动产生的,可以通过禁用约束或删除约束的方法来删除对应的索引.

在删除一个表时,oracle会自动删除所有与该表相关的索引.

索引数据字典

all_indexes/dba_indexes/user_indexes 索引的基本信息

all_ind_columns/dba_ind_columns/user_ind_columns 索引对应的字段信息

oracle实例内存解析

一、名词解释 (1)SGA：System Global Area是Oracle Instance的基本组成部分，在实例启动时分配;系统全局域SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区。 (2)共享池：Shared Pool用于缓存最近被执行的SQL语句和最近被使用的数据定义，主要包括：Library cache（共享SQL区）和Data dictionary cache（数据字典缓冲区）。共享SQL区是存放用户SQL命令的区域，数据字典缓冲区存放数据库运行的动态信息。 (3)缓冲区高速缓存：Database Buffer Cache用于缓存从数据文件中检索出来的数据块，可以大大提高查询和更新数据的性能。 (4)大型池：Large Pool是SGA中一个可选的内存区域，它只用于shared server环境。 (5)Java池：Java Pool为Java命令的语法分析提供服务。 (6)PGA：Process Global Area是为每个连接到Oracle database的用户进程保留的内存。 二、分析与调整 (1)系统全局域： SGA与操作系统、内存大小、cpu、同时登录的用户数有关。可占OS系统物理内存的1/3到1/2。 a.共享池Shared Pool： 查看共享池大小Sql代码 SQL>show parameter shared_pool_size 查看共享SQL区的使用率： Sql代码 select(sum(pins-reloads))/sum(pins)"Library cache"from v$librarycache; --动态性能表 LIBRARY命中率应该在90％以上，否则需要增加共享池的大小。

Oracle__插入数据

Oracle 插入数据 插入数据就是将数据放置到已经创建的表中，Oracle 数据库是通过INSERT 语句来实现插入数据的。一般情况下，使用INSERT 语句可以一次插入一行数据。 与SELECT 语句相比，INSERT 语句的使用方式要简单的多。在INSERT 语句的使用方式中，最为常用的形式是在INSERT INTO 子句中指定添加数据的列，并在V ALUES 子句中为各个列提供一个值。 下面语句将向SCOTT 模式中的EMP 表添加一条记录： SQL> insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) 2 values(7995,'A TG','CLERK',7782,to_date('2007-9-23','yyyy-mm-dd'),1300,null,10); 已创建 1 行。。 在向表中所有列添加数据时，可以省略INSERT INTO 子句后的列表清单，使用这种方法时，必须根据表中定义的列的顺序，为所有的列提供数据，用户可以使用DESC 命令查看表中定义列的顺序。下面的INSERT 语句在向EMP 表添加记录时省略了列清单： SQL> desc emp SQL> insert into emp 2 values(7996,'LI','CLERK',7782,to_date('2006-5-12','yyyy-mm-dd'),1200,null,10); 已创建 1 行。 在插入操作过程中，用户也可以根据实际情况只为部分列提供数据，而省略某些列的数据。注意这些列必须允许空值、有默认值或系统可以自动成生值等。例如，在EMP 表中，除EMPNO 列不允许空值外，其他列都可以为空值。 如果某个列不允许NULL 值存在，而用户没有为该列提供数据，则会因为违反相应的约束而插入失败。事实上，在定义表的时候为了数据的完整性，经常会为表添加许多约束。例如，在EMP 表中为了保证表中每条记录的惟一性，在表的EMPNO 列上定义了主键约束。如果用户试图为表中的EMPNO 列添加一个重复值，则因为违反主键约束而失败： SQL> insert into emp(empno,ename,job) 2 values(7782,'KING','CLERK'); insert into emp(empno,ename,job) * 第 1 行出现错误: ORA-00001: 违反唯一约束条件 (SCOTT.PK_EMP) 注 意 使用这种方法插入数据时有一个大隐患，如果为表指定的数值位置不对， 并且指定的数据类型之间可以转化，则执行该语句时系统不会返回任何错误信息。但是这会为该表添加一条错误的记录。由于这种错误难以发现，所以在添 加记录时最好在INSERT INTO 子句中指定列清单，以明确接收数据的列。

Oracle索引分析与查询优化

龙源期刊网 https://www.sodocs.net/doc/7e17331849.html, Oracle索引分析与查询优化 作者：崔黎明志远李婧 来源：《数字技术与应用》2016年第07期 摘要：Oracle是目前国内在大型数据存储中用得比较多的一种关系型数据库，磁盘阵列技术（RAID）和集群技术（RAC）的运用，使Oracle在处理数据效率和数据安全上有非常大的提高，在国内交通、电力，通信和金融等重要领域都有广泛的用途。本文基于Oracle 11g版本，对Rowid和索引的原理机制做出分析，并论述利用这些原理对大型数据表查询的优化。 关键词：Oracle Rowid 索引查询 中图分类号：TP311.13 文献标识码：A 文章编号：1007-9416（2016）07-0234-02 在逻辑上，Oracle是由多个表空间构成的，在新建一个表空间的时候，必须指定存储的文件，可以指向多个存储在不同磁盘上的数据。表空间下面分为段、区、块。新建一张表的时候，Oracle数据库就会为它创建一个段。所谓区就是指连续的块（block）构成的空间，一般区包含8个空块，block是Oracle数据库最小的数据空间，一般为8k或16k，当开始往这个表中插入数据的时候，Oralce数据库会自动为这个表分配一个区，并把数据不断往此区进行填充，当数据填满此区后，Oralce数据库会重新为该表分配一个区而不是一个段。实际上，对于我们不同的查询过程中，就是通过一些谓词过滤条件，从对应的数据块中获取正确的一行数据或多行数据。如何快速定位到该数据行，是一个数据库学习者不断探索的方向。 1 Rowid 1.1 简介 从字面上理解是行标识的意思，它是Oracle数据库中数据表的一个伪列，用于存放该表中每一行数据的地址，在8i版本之前，Oracle采用受限制的rowid，它是由数据文件编号，块编号和数据在该块内的偏移量这三个部分构成，长度为6个字节，因此在8i之前的版本中，每 个数据库最多可以包含1022个文件，每个文件最多能有4m个数据块，而每个数据库最多能 存储64k条记录。为了突破长度的限制和解决其他一些缺陷，Oracle数据库引入了这样一个概念：相对文件号。它的主要特点是改变之前rowid中数据文件编号是整个数据库范围组成的表空间，即文件编号为5的文件不再是数据库中编号为5的数据文件，而是表空间中对应编号的数据文件。如下图1所示为Oracle11g中一个普通表的rowid。 从该图1中可以看到rowid共有18位，分为四部分，格式为：AAAAAABBBCCCCCCDDD，其中AAAAAA六位表示dataobjectid，根据这个id可以确定该行数据在哪个段中；BBB三位表示相对文件号，通过这个字段号可以用来确定该行数据的绝 对文件号；CCCCCC六位表示datablocknumber，它是相对于datafile的编号；最后三位DDD

oracle数据库的5种约束类型

oracle 数据库的 5 种约束类型 oracle 数据库数据表的 5 个约束类型： 1.主键约束 2.外键约束 3.唯一约束 4.检查约束 5. 非空约束 F 主键约束：用来唯一标示表中的一个列， 一个表中的主键约束只能有一个，但是可以在一个主键约束 中包含多个列，也称为联合约束。外键约束：用来约束两 个表中列之间的关系。唯一约束：用来唯一标示表中的 列。与主键约束不同的是，在一个数据表中可以有多个唯一约束。检查约束：用来约束表中列的输入值得范围，比如 在输入性别时，要求数据库中只能输入男或者女，就可以使用检查约束来约束该列。非空约束：约束该列一定要输入值。 ----- 创建一个带检查约束的表：使用PL/SQL 语句创建检查约束的语法如下所示：CONSTRAINT constraint_name CHECK(condition) [ 语法说明：] CONSTRAINT: 关键词constraint_name: 约束名称condition: 约束条件列如:创建BOOKINFO 表时，给图书 价格加上一个检查约束，要求图书价格在10 元到100 元之间。CREATE TABLE BOOKINFO ( BOOKID

INT, BOOKNAME CAHR, PUBLISH

char, publish varchar2(20), pubdate varchar2(20), CHECK(PRICE>=10 AND PRICE ----- 创建一个带非空约束的表： 举例 :在创建 BOOKINFO 表时，给图书名称加上一个非空约束。 CREATE TABLE BOOKINFO ( BOOKID INT NOT NULL, BOOKNAME CHAR NOT NULL, PUBLISH PUBDATE VARCHAR2(20), AUTHOR CHAR, STORE ----- 创建一个带唯一约束的表 语法格式： CONSTRAINT constraint_name UNIQUE(column_name) 【语法说明】 UNIQUE: 唯一约束的关键词 column_name: 唯一约束的名称。 举例 :创建 BOOKINFO 表时，为图书名称( BOOKNAME )列添加唯一约束 create table bookinfo ( bookid int, bookname PRICE DECIMAL, AUTHOR CHAR, STORE VARCHAR2(1), READER INT, REMARKS VARCHAR2(50), CONSTRAINT CK_PRICE ); VARCHAR2(20), PRICE DECIMAL, VARCHAR2(1), VARCHAR2(50) READER INT, REMAERKS );

oracle题1

A. DECLARE ? B. SET ? C. UPDATE ? D. SELECT o 2. 能计算出一列中所有数的总和的函数是_____。【2.0分】 ? A. COUNT ? B. SUM ? C. AVG ? D. SQRT o 3. ORACLE中，用来判断列值是否为空的操作符是_____。【2.0分】? A. = NULL ? B. IS NULL ?

C. AS NULL ? D. NULLIS o 4. 分析以下SQL 命令: SELECT price FROM inventory WHERE price BETWEEN 1 AND 50 AND (price IN(55, 30, 95); 命令执行后的最可能的输出结果是_____。【2.0分】 ? A. 55 ? B. 30 ? C. 95 ? D. 51 o 5. Oracle中，当需要使用显式游标更新游标中的行时，声明游标时指定的SELECT语句必须带有_____子句。【2.0分】 ? A. WHERE CURRENT OF ? B. INTO ? C. FOR UPDATE ? D. ORDER BY

o 6. 当执行SELECT INTO语句没有返回行时，会触发_______异常。【2.0分】? A. TOO_MANY_ROWS ? B. NO_DATA_FOUND ? C. VALUE_ERROR ? D. 不会触发任何异常 o7. 下面哪一个like命令会返回名字像HOTKA的行？【2.0分】 ? A. where ename like …_HOT%' ? B. where ename like …H_T%' ? C. where ename like …%TKA_' ? D. where ename like …%TOK%' o8. 以下运算结果不为空值的是______。【2.0分】 ? A. 12+NULL ? B. 60*NULL ?

oracle索引基本分类

oracle索引基本分类法分类 逻辑上： Single column 单行索引 Concatenated 多行索引 Unique 唯一索引 NonUnique 非唯一索引 Function-based函数索引 Domain 域索引 物理上： Partitioned 分区索引 NonPartitioned 非分区索引 B-tree： Normal 正常型B树 Rever Key 反转型B树 Bitmap 位图索引 索引结构： B-tree： 适合与大量的增、删、改（OLTP）； 不能用包含OR操作符的查询； 适合高基数的列（唯一值多） 典型的树状结构； 每个结点都是数据块； 大多都是物理上一层、两层或三层不定，逻辑上三层； 叶子块数据是排序的，从左向右递增； 在分支块和根块中放的是索引的范围； Bitmap: 适合与决策支持系统； 做UPDATE代价非常高； 非常适合OR操作符的查询； 基数比较少的时候才能建位图索引； 树型结构： 索引头 开始ROWID，结束ROWID（先列出索引的最大范围） BITMAP 每一个BIT对应着一个ROWID，它的值是1还是0，如果是1，表示着BIT 对应的ROWID有值； =============================================== ======================

Oracle的索引主要包含两类：BTree和位图索引。默认情况下大多使用Btree 索引，该索引就是通常所见唯一索引、聚簇索引等等，Btree用在OLTP，加快查询速度。位图索引是Oracle的比较引人注目的地方，其主要用在OLAP（联机数据分析）方面，也就是数据仓库方面用到，目的是在加快查询速度是，节省存储空间。通常情况下，索引都要耗费比较大的存储空间，位图采用了压缩技术实现磁盘空间缩减。Btree用在高基数（即列的数据相异度大），位图用在低基数列。位图索引的基本原理是在索引中使用位图而不是列值。通常在事实表和维表的键之间有很低的集的势（cardinality），使用位图索引，存储更为有效，与B*Tree 索引比较起来，只需要更少的存储空间，这样每次读取可以读到更多的记录，而且与B*Tree索引相比,位图索引将比较,连接和聚集都变成了位算术运算,大大减少了运行时间，从而得到性能上的极大的提升。 在Oracle中如何合理的使用位图索引？以下的几个事项应该考虑。 *如果要使用位图索引，初始化参数STAR_TRANSFORMATION_ENABLED 应该设置为TRUE. *优化模式应该是CBO。对于数据仓库的环境中，总是应该考虑使用CBO （COST-BASEDOPTIMIZER）。 *位图索引应该建立在每一个事实表的外键列上。(这只是一个一般的规则.) 此外，对于数据表中的cardinality如何客观的确定也是一个问题，一万条数据中只包含3个值的集和算是低的了，那么一亿条记录中包含3万条记录算不算低的呢？对于这样的情况，建议几行一下数据的模拟测试，一般来说，在数据仓库环境中，位图索引的性能要好于B*Tree索引。还要注意位图索引不是为OLTP 数据库设计的，不应该在OLTP数据库中大量的使用它，尤其是对那些有更新操作的表。 ==================================================================== ========== B*Tree索引 B*Tree索引是最常见的索引结构，默认建立的索引就是这种类型的索引。B*Tree索引在检索高基数数据列（高基数数据列是指该列有很多不同的值）时提供了最好的性能。当取出的行数占总行数比例较小时B-Tree索引比全表检索提供了更有效的方法。但当检查的范围超过表的10%时就不能提高取回数据的性能。B-Tree索引是基于二叉树的，由分支块（branch block）和叶块（leaf block）组成。在树结构中，位于最底层底块被称为叶块，包含每个被索引列的值和行所对应的rowid。在叶节点的上面是分支块，用来导航结构，包含了索引列（关键字）范围和另一索引块的地址，如图26-1所示。

Oracle数据库实例及其相关概念

Oracle数据库实例及其相关概念2010-11-24 00:00 出处：中国IT实验室作者：佚名 完整的Oracle数据库通常由两部分组成：Oracle数据库实例和数据库。　用数据库安全策略防止权限升级攻击　C++虚函数的显式声明 完整的Oracle数据库通常由两部分组成：Oracle数据库实例和数据库。 1)数据库是一系列物理文件的集合(数据文件，控制文件，联机日志，参数文件等); 2)Oracle数据库实例则是一组Oracle后台进程/线程以及在服务器分配的共享内存区。 在启动Oracle数据库服务器时，实际上是在服务器的内存中创建一个Oracle实例(即在服务器内存中分配共享内存并创建相关的后台内存)，然后由这个Oracle数据库实例来访问和控制磁盘中的数据文件。Oracle有一个很大的内存快，成为全局区(SGA)。 一、数据库、表空间、数据文件 1.数据库 数据库是数据集合。Oracle是一种数据库管理系统，是一种关系型的数据库管理系统。 通常情况了我们称的“数据库”，并不仅指物理的数据集合，他包含物理数据、数据库管理系统。也即物理数据、内存、操作系统进程的组合体。 数据库的数据存储在表中。数据的关系由列来定义，即通常我们讲的字段，每个列都有一个列名。数据以行(我们通常称为记录)的方式存储在表中。表之间可以相互关联。以上就是关系模型数据库的一个最简单的描述。

当然，Oracle也是提供对面象对象型的结构数据库的最强大支持，对象既可以与其它对象建立关系，也可以包含其它对象。关于OO型数据库，以后利用专门的篇幅来讨论。一般情况下我们的讨论都基于关系模型。 2.表空间、文件 无论关系结构还是OO结构，Oracle数据库都将其数据存储在文件中。数据库结构提供对数据文件的逻辑映射，允许不同类型的数据分开存储。这些逻辑划分称作表空间。 表空间(tablespace)是数据库的逻辑划分，每个数据库至少有一个表空间(称作SYSTEM表空间)。为了便于管理和提高运行效率，可以使用一些附加表空间来划分用户和应用程序。例如：USER表空间供一般用户使用，RBS表空间供回滚段使用。一个表空间只能属于一个数据库。 每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件只能属于一个表空间。在Oracle7.2以后，数据文件创建可以改变大小。创建新的表空间需要创建新的数据文件。数据文件一旦加入到表空间中，就不能从这个表空间中移走，也不能与其它表空间发生联系。 如果数据库存储在多个表空间中，可以将它们各自的数据文件存放在不同磁盘上来对其进行物理分割。在规划和协调数据库I/O请求的方法中，上述的数据分割是一种很重要的方法。 3.Oracle数据库的存储结构分为逻辑存储结构和物理存储结构： 1)逻辑存储结构：用于描述Oracle内部组织和管理数据的方式; 2)物理存储结构：用于描述Oracle外部即操作系统中组织和管理数据的方式。 二、Oracle数据库实例

oracle ALTER 用法

oracle ALTER 用法 1.如果sql语句中有''（单引号或者双引号）括的字符串，则需要同表中严格一致，不然会显示“找 不到行”这个错误。 alter语句的用法 需要修改结构的，就用到alter语句，方法如下： 1.ALTER TABLE语句用于修改已经存在的表的设计。 2.语法： ALTER TABLE table ADD COLUMN field type[(size)] [NOT NULL] [CONSTRAINT index ] 3.ALTER TABLE table ADD CONSTRAINT multifieldindex 4.ALTER TABLE table DROP COLUMN field 5.ALTER TABLE table DROP CONSTRAINT indexname 6. 7.说明：table参数用于指定要修改的表的名称。 8.ADD COLUMN为SQL的保留字，使用它将向表中添加字段。 9.ADD CONSTRAINT为SQL的保留字，使用它将向表中添加索引。 10.DROP COLUMN为SQL的保留字，使用它将向表中删除字段。 11.DROP CONSTRAINT为SQL的保留字，使用它将向表中删除索引。 12.field指定要添加或删除的字段的名称。 13.type参数指定新建字段的数据类型。 14.size参数用于指定文本或二进制字段的长度。 15.indexname参数指定要删除的多重字段索引的名称。 16. 17.用sql*plus或第三方可以运行sql语句的程序登录数据库： 18. 19.ALTER TABLE (表名) ADD (列名数据类型); 20.ALTER TABLE (表名) MODIFY (列名数据类型); 21.ALTER TABLE (表名) RENAME COLUMN (当前列名) TO (新列名); /////不需要括号 22.ALTER TABLE (表名) DROP COLUMN (列名); 23.ALTER TABLE (当前表名) RENAME TO (新表名); 24. 25.如： 26.Alter Table Employ Add (weight Number(38,0)) ; 27. 28.Alter Table Employ Modify (weight Number(13,2)) ; 29. 30.Alter Table Emp Rename Cloumn weight To weight_new ; 31. ALTER TABLE emp DROP COLUMN weight_new ;

Oracle 建表(一对多)代码及相关约束示例

建表(一对多)代码及相关约束 create table t_class( c_id number(3) primary key, c_name varchar2(20) not null ); create table t_stu( s_id number(5) primary key, s_name varchar2(8) not null, sex char(2) default '男', birthday date, school_age number(2) check(school_age>0), school_score number(5,2), c_id number(3), id_card char(18) unique, foreign key(c_id) references t_class(c_id) ); 附：测试数据： insert into t_class values(1,'订单班'); insert into t_class values(2,'开发班'); insert into t_class values(3,'美工班'); insert into T_STU (S_ID, S_NAME, SEX, BIRTHDAY, SCHOOL_AGE, SCHOOL_SCORE, C_ID, ID_CARD) values (1, 'a', '男', to_date('01-01-1980', 'dd-mm-yyyy'), 24, 90, 1, null); insert into T_STU (S_ID, S_NAME, SEX, BIRTHDAY, SCHOOL_AGE, SCHOOL_SCORE, C_ID, ID_CARD) values (2, 'b', '男', to_date('11-05-1981', 'dd-mm-yyyy'), 23, 80, 1, null); insert into T_STU (S_ID, S_NAME, SEX, BIRTHDAY, SCHOOL_AGE, SCHOOL_SCORE, C_ID, ID_CARD) values (3, 'c', '女', to_date('19-09-1982', 'dd-mm-yyyy'), 22, 50, 1, null); insert into T_STU (S_ID, S_NAME, SEX, BIRTHDAY, SCHOOL_AGE, SCHOOL_SCORE, C_ID, ID_CARD) values (4, 'd', '女', to_date('28-01-1984', 'dd-mm-yyyy'), 21, 80, 2, null); insert into T_STU (S_ID, S_NAME, SEX, BIRTHDAY, SCHOOL_AGE, SCHOOL_SCORE, C_ID, ID_CARD)

Oracle索引原理

Oracle数据库中的索引详解 一、ROWID的概念 存储了row在数据文件中的具体位置：64位编码的数据，A-Z, a-z, 0-9, +, 和/，row在数据块中的存储方式 SELECT ROWID, last_name FROM hr.employees WHERE department_id = 20; 比如：OOOOOOFFFBBBBBBRRR OOOOOO：data object number, 对应dba_objects.data_object_id FFF：file#, 对应v$datafile.file# BBBBBB：block# RRR：row# Dbms_rowid包 SELECT dbms_rowid.rowid_block_number('AAAGFqAABAAAIWEAAA') from dual; 具体到特定的物理文件 二、索引的概念 1、类似书的目录结构 2、Oracle 的“索引”对象，与表关联的可选对象，提高SQL查询语句的速度 3、索引直接指向包含所查询值的行的位置，减少磁盘I/O 4、与所索引的表是相互独立的物理结构 5、Oracle 自动使用并维护索引，插入、删除、更新表后，自动更新索引 6、语法：CREA TE INDEX index ON table (column[, column]...); 7、B-tree结构(非bitmap)： [一]了解索引的工作原理： 表：emp

目标：查询Frank的工资salary 建立索引：create index emp_name_idx on emp(name);

[试验]测试索引的作用： 1. 运行/rdbms/admin/utlxplan 脚本 2. 建立测试表 create table t as select * from dba_objects; insert into t select * from t; create table indextable as select rownum id,owner,object_name,subobject_name, object_id,data_object_id,object_type,created from t; 3. set autotrace trace explain 4. set timing on 5. 分析表，可以得到cost 6. 查询object_name=’DBA_INDEXES’ 7. 在object_name列上建立索引 8. 再查询 [思考]索引的代价： 插入，更新 三、唯一索引 1、何时创建：当某列任意两行的值都不相同 2、当建立Primary Key(主键)或者Unique constraint(唯一约束)时，唯一索引将被自动建立 3、语法：CREA TE UNIQUE INDEX index ON table (column); 4、演示

Oracle内存全面分析

Oracle 内存内存全面全面全面分析分析 作者作者：：fuyuncat 来源来源：：https://www.sodocs.net/doc/7e17331849.html, 作者简介 黄玮，男，99年开始从事DBA 工作，有多年的水利、军工、电信及航 运行业大型数据库Oracle 开发、设计和维护经验。 曾供职于南方某著名电信设备制造商——H 公司。期间，作为DB 组 长，负责设计、开发和维护彩铃业务的数据库系统。目前，H 公司的彩铃系 统是世界上终端用户最多的彩铃系统。最终用户数过亿。 目前供职于某世界著名物流公司，负责公司的电子物流系统的数据库开 发、维护工作。 msn: fuyuncat@https://www.sodocs.net/doc/7e17331849.html, Email ：fuyuncat@https://www.sodocs.net/doc/7e17331849.html, Oracle 的内存配置与oracle 性能息息相关。而且关于内存的错误（如4030、4031错 误）都是十分令人头疼的问题。可以说，关于内存的配置，是最影响Oracle 性能的配 置。内存还直接影响到其他两个重要资源的消耗：CPU 和IO。 首先，看看Oracle 内存存储的主要内容是什么： ? 程序代码（PLSQL、Java）； ? 关于已经连接的会话的信息，包括当前所有活动和非活动会话； ? 程序运行时必须的相关信息，例如查询计划； ? Oracle 进程之间共享的信息和相互交流的信息，例如锁； ? 那些被永久存储在外围存储介质上，被cache 在内存中的数据（如redo log 条 目，数据块）。 此外，需要记住的一点是，Oracle 的内存是与实例对应的。也就是说，一个实例就有 一个独立的内存结构。 先从Oracle 内存的组成架构介绍。 1. Oracle 的内存架构组成 Oracle 的内存，从总体上讲，可以分为两大块：共享部分（主要是SGA）和进程独享 部分（主要是PGA 和UGA）。而这两部分内存里面，根据功能不同，还分为不同内存池 （Pool）和内存区（Area）。下面就是Oracle 内存构成框架图：

oracle数据库索引的理解与总结

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。 例如这样一个查询：SELECT * FROM TABLE1 WHERE ID = 44。如果没有索引，必须遍历整个表，直到ID等于44的这一行被找到为止；有了索引之后(必须是在ID这一列上建立的索引)，直接在索引里面找44（也就是在ID这一列找），就可以得知这一行的位置，也就是找到了这一行。可见，索引是用来定位的。 建立索引的目的是加快对表中记录的查找或排序。为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。 虽然建立索引能加快对表中记录的查询或者排序速度，但是并不是索引建得越多越好，这就需要我们了解使用索引过程中，索引的一些优点以及缺陷： 使用索引的好处： 创建索引可以大大提高系统的性能： 第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。 第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。使用索引的一些不足： 第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。 第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。 索引是建立在数据库表中的某些列的上面。在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引： ?在经常需要搜索的列上，可以加快搜索的速度；在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构； ?在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度； ?在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的； ?在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间； ?在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。 同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点： ?对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。 ?对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了

oracle复习题剖析

一、选择题 1． Oracle数据表中，下面哪个可以作为有效的列名（ A ）（选择一项） A. class B. 123_NUM C. date D. #123_NUM 2． Oracle中的三种系统文件分别是（ ABC ）〔选择三项〕 A. 数据文件 DBF B. 控制文件 CTL C. 日志文件 LOG D. 归档文件 ARC 3．下面哪个命令可以用来查看学生表（表名student）的表结构( C )〔选择一项〕 A. Select student ; B. Display student ; C. Describe student ; D. Show student ;。 4．下面哪个命令不属于数据定义语言（DDL）( D )（选择一项） A. alter table… B、drop index… C、create view… D、update tablename… 5．在设计数据库时，要充分考虑数据的完整性或准确性。下面关于primary key和unique的描述错误的是（）〔选择一项） A. primary key用来在表中设置主键，主键列的值是不能重复的，用来唯一标识表中的每一条记录 B. 设为unique的列的值是不能重复的，用来唯一区别unique列的值 C. primary key列不可以有null值, 而unique列是可以有null的 D. primary key列和unique列都不可以有null值 6．假定有一张表员工表employee，其中一性别字段sex,该列只能存入'男'或'女'。为了维护数据的完整性，在设计数据库时，最好对sex字段添加约束，请问应该添加什么约束（ B ）（选择一项） A. primary key B、check C、default D、unique 7．为表TEST中ID列添加主键约束的语法是（ D ）〔选择一项〕 A. ALTER TABLE TEST CHANGE( ID INT PRIMARY KEY) B. ALTER TABLE TEST ADD( ID INT PRIMARY KEY)

oracle实例内存详解

一、名词解释 (1)SGA：SystemGlobal Area是Oracle Instance的基本组成部分，在实例启动时分配;系统全局域SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区。 (2)共享池：Shared Pool用于缓存最近被执行的SQL语句和最近被使用的数据定义，主要包括：Librarycache（共享SQL区）和Datadictionarycache（数据字典缓冲区）。共享SQL区是存放用户SQL命令的区域，数据字典缓冲区存放数据库运行的动态信息。 (3)缓冲区高速缓存：DatabaseBufferCache用于缓存从数据文件中检索出来的数据块，可以大大提高查询和更新数据的性能。 (4)大型池：Large Pool是SGA中一个可选的内存区域，它只用于shared server环境。 (5)Java池：Java Pool为Java命令的语法分析提供服务。 (6)PGA：Process Global Area是为每个连接到Oracle database的用户进程保留的内存。 二、分析与调整 (1)系统全局域： SGA与操作系统、内存大小、cpu、同时登录的用户数有关。可占OS系统物理内存的1/3到1/2。 a.共享池Shared Pool： 查看共享池大小Sql代码 SQL>show parameter shared_pool_size 查看共享SQL区的使用率： Sql代码 select(sum(pins-reloads))/sum(pins)"Library cache"from v$librarycache; --动态性能表 LIBRARY命中率应该在90％以上，否则需要增加共享池的大小。 查看数据字典缓冲区的使用率：

Oracle 插入数据

Oracle 插入数据 插入数据就是将数据放置到已经创建的表中，Oracle 数据库是通过INSERT 语句来实现插入数据的。一般情况下，使用INSERT 语句可以一次插入一行数据。 与SELECT 语句相比，INSERT 语句的使用方式要简单的多。在INSERT 语句的使用方式中，最为常用的形式是在INSERT INTO 子句中指定添加数据的列，并在V ALUES 子句中为各个列提供一个值。 下面语句将向SCOTT 模式中的EMP 表添加一条记录： SQL> insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) 2 values(7995,'A TG','CLERK',7782,to_date('2007-9-23','yyyy-mm-dd'),1300,null,10); 已创建 1 行。。 在向表中所有列添加数据时，可以省略INSERT INTO 子句后的列表清单，使用这种方法时，必须根据表中定义的列的顺序，为所有的列提供数据，用户可以使用DESC 命令查看表中定义列的顺序。下面的INSERT 语句在向EMP 表添加记录时省略了列清单： SQL> desc emp SQL> insert into emp 2 values(7996,'LI','CLERK',7782,to_date('2006-5-12','yyyy-mm-dd'),1200,null,10); 已创建 1 行。 在插入操作过程中，用户也可以根据实际情况只为部分列提供数据，而省略某些列的数据。注意这些列必须允许空值、有默认值或系统可以自动成生值等。例如，在EMP 表中，除EMPNO 列不允许空值外，其他列都可以为空值。 如果某个列不允许NULL 值存在，而用户没有为该列提供数据，则会因为违反相应的约束而插入失败。事实上，在定义表的时候为了数据的完整性，经常会为表添加许多约束。例如，在EMP 表中为了保证表中每条记录的惟一性，在表的EMPNO 列上定义了主键约束。如果用户试图为表中的EMPNO 列添加一个重复值，则因为违反主键约束而失败： SQL> insert into emp(empno,ename,job) 2 values(7782,'KING','CLERK'); insert into emp(empno,ename,job) * 第 1 行出现错误: ORA-00001: 违反唯一约束条件 (SCOTT.PK_EMP) 关于为表定义完整性约束，将在后面的章节中介绍，这里需要记住的是在向表添加记录时，添加的数据必须符合为表定义的所有完整性约束。 INSERT 语句还有一种用法，可以实现一次向表中添加一组数据。即使用SELECT 语句替换V ALUES 子句，这样由SELECT 语句提供添加的数值。例如，下面的示例从EMP 表提注 意 使用这种方法插入数据时有一个大隐患，如果为表指定的数值位置不对， 并且指定的数据类型之间可以转化，则执行该语句时系统不会返回任何错误信 息。但是这会为该表添加一条错误的记录。由于这种错误难以发现，所以在添加记录时最好在INSERT INTO 子句中指定列清单，以明确接收数据的列。

Oracle基础练习题及答案(表约束)

练习作业 创建表emp1，字段如下 eno char(3), ename char(6) sex char(2) age number(2) dno char(3) 并插入如下数据 1 ，TOM ，男，21 ，’001’ 2 ，JERRY ，男，21 ，’002’ 3 ，KATE ，女，21 ，’003’ 4 ，MARY ，女，21 ，’004’ 5 ，JACK ，男，21 ，’005’ i.在eno字段上创建主键约束 ii.在ename字段上创建非空约束 iii.创建检查约束判断age在18到60岁之间的男性或者age在18到55岁之间的女性iv.在dno字段上创建唯一性约束 create table emp1( eno char(3) primary key, ename char(6) not null, sex char(2), age number(2), dno char(3) unique, constraint sex_age check( ((age between18and60) and sex='男') or ((age between18and55) and sex='女')) ) insert into emp1 values('1','TOM','男',21,'001') insert into emp1 values('2','JERRY','男',21,'002');

insert into emp1 values('3','KATE','女',21,'003'); insert into emp1 values('4','MARY','女',21,'004'); insert into emp1 values('5','JACK','男',21,'005'); v.创建和emp1表字段相同的emp_bak表，将emp_bak表的eno字段与emp表的eno字段创建外键约束 create table emp_bak as select * from emp1 where1=2 alter table emp_bak add constraint v foreign key(eno) references emp1(eno); 1、请创建一个表，表名为phone,表结构如下 电话号码（PHONENUM VARCHAR2（8）） 电话费（PAY number(8,2)） 号码等级（NUMLEVEL VARCHAR2（4）） 费用日期（PAYDATE varchar2(12) create table phone ( phonenum varchar2(8), pay number(8,2), numlevel varchar2(4), paydate varchar2(12) ) 1）插入以下两条数据. 123456， 600， pt04, 20051220 888888, 900, pt05, 20051019. insert into phone values('123456',600,'pt04', '20051220'); insert into phone values('888888',900,'pt05', '20050919');