专注收集记录技术开发学习笔记、技术难点、解决方案
网站信息搜索 >> 请输入关键词:
您当前的位置: 首页 > SQL

oracle sql 基础(5):数据定义语言(创建和管理序列、索引、同义词)

发布时间:2010-05-20 14:01:29 文章来源:www.iduyao.cn 采编人员:星星草
oracle sql 基础(五):数据定义语言(创建和管理序列、索引、同义词)

       许多应用程序要求使用唯一的数字作为主键的值,你即可以在应用程序中构建代码来处理这种需求,也可以用一个序列来产生唯一的数字。如果你想要增进某些查询的性能,你应该考虑创建一个索引,你也可以用索引在列或列的集合上强制唯一性。你可以用同义词为对象提供可替代的名字。下面我们来介绍序列、索引和同义词三个数据库对象。

      一、创建和管理序列

       序列是用户创建的数据库对象,可以被多个用户共享。 序列的一个典型的用途是创建一个主键的值,它对于每一行必须是唯一的。序列由一个Oracle内部程序产生并增加或减少。 序列是一个节省时间的对象,因为它可以减少应用程序中产生序列程序的代码量。 序列号独立于表被存储和产生,因此,相同的序列可以被多个表使用。

       1.1 定义和查询序列

       用CREATE SEQUENCE语句自动产生序列数。语法:

CREATE SEQUENCE sequence   --sequence 是序列发生器的名字
[INCREMENT BY n]           --序列号之间的间隔,在这儿n是一个整数,
[START WITH n]             --产生的第一个序列数,默认值1。
[{MAXVALUE n| NOMAXVALUE}] --序列能产生的最大值,默认NOMAXVALUE(升序:10^27;降序:-1)
[{MINVALUE n| NOMINVALUE}] --最小序列值,默认NOMINVALUE(升序:1;降序-(10^26))
[{CYCLE | NOCYCLE}]        --指定序列在达到它的最大或最小值之后,是否继续产生,默认NOCYCLE。
[{CACHE n| NOCACHE}];      --指定Oracle服务器预先分配多少值,并且保持在内存中,默认缓冲20个值。

       如果INCREMENT BY值是负数,序列是降序。另外,ORDER | NOORDER选项可用,ORDER选项保证序列值按顺序产生,如果你将序列用于产生主键值它是不重要的,该选项仅与Parallel Server(并行服务)选项有关。如果序列值被高速缓冲,系统故障它们将被丢失。

       一旦创建了序列,它就被文本化在数据字典中。因为序列是一个数据库对象,你可以在USER_OBJECTS数据字典表中识别它。你也可以从USER_SEQUENCES数据字典视图中用选择确认序列的设置。下面是一个定义和查询序列的实例。

--创建序列
CREATE SEQUENCE seq_test
INCREMENT BY 2
START WITH 3
MAXVALUE 1000
NOCYCLE  --若用户产生主键,怎不使用CYCLE选项。
NOCACHE  --不允许高速缓冲;
--查询序列
SELECT object_name,object_id,object_type 
FROM USER_OBJECTS 
WHERE object_name = 'SEQ_TEST';
SELECT * FROM USER_SEQUENCES 
WHERE sequence_name = 'SEQ_TEST';
定义和查询序列实例

       1.2 使用序列

       在创建序列后,它产生连续的数给你在表中使用。用NEXTVAL和CURRVAL伪列引用序列值。NEXTVAL伪列用于从指定的序列中取回连续的序列数的下一个值。你必须用序列名限定NEXTVAL,当你引用sequence.NEXTVAL时,一个新的序列数被产生并且当前的序列数被放入CURRVAL。CURRVAL伪列被用于查阅当前当前用户刚才产生的序列数,NEXTVAL必须被在 CURRVAL可以被引用之前用于在当前用户的会话中产生一个序列数,你必须用序列名限定CURRVAL,当sequence.CURRVAL被引用时,最后返回给用户程序的值被显示。

       如果序列用带 NOCACHE 的选项创建的,就可以在序列值不增加的情况下用查询 USER_SEQUENCES表的方法,查看下一个可用的序列值。经常使用的序列用带缓存创建将增进效率,对于缓存的序列,没有办法找出下一个可用的序列将是什么,该值不实际获得和使用,因此,建议用户不要查找下一个序列值,而相信每次一个序列被用于一个 INSERT 语句时系统会提供一个唯一值。

--在表中插入序列号
INSERT INTO dept (deptno,dname,loc)
VALUES (SEQ_TEST.NEXTVAL,'TEST','TEST');
--查看当前序列值
SELECT SEQ_TEST.CURRVAL FROM dual;
--查看下一个序列值
SELECT SEQ_TEST.NEXTVAL FROM dual;
使用序列实例

       1.3 修改序列

       可以用ALTER SEQUENCE语句修改该序列。语法:

ALTER SEQUENCE sequence
[INCREMENT BY n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];

       用ALTER SEQUENCE语句,只有以后的序列数会受影响;START WITH选项不能被改变。为了以不同的数重新开始一个序列,该序列必须被删除和重新创建;一些验证被执行,例如,一个新MAXVALUE如果小于当前的序列值就不能用。

ALTER SEQUENCE seq_test
INCREMENT BY 10
MAXVALUE 10000;
修改序列实例

       1.4 删除序列

       使用DROP SEQUENCE语句从数据字典中删除一个序列。你必须是被删除序列的所有者或者有DROP ANY SEQUENCE权限来删除它。语法:

DROP SEQUENCE sequence;

       例如:DROP SEQUENCE seq_test;

      二、创建和维护索引

       索引是Oracle服务器的一个方案对象,索引能用指针加速行的取回,索引可以被显式创建,也可以被自动创建,如果你在列上没有索引,那么将发生全表扫描。索引提供对表中行的直接和快速访问,它的目的是用已索引的路径快速定位数据以减少磁盘I/O。索引由Oracle服务器自动使用和维护,一旦一个索引被创建,它就不再需要用户直接管理。

       2.1 创建索引

       可以创建两种类型的索引,一种是唯一性索引:当你在一个表中定义一个列为主键,或者定义一个唯一键约束时Oracle服务器自动创建该索引,索引的名字习惯上是约束的名字。 另一种索引类型是非唯一索引,它可以由用户创建,例如,你可以创建一个FOREIGN KEY列索引用于一个查询中的连接来改进数据取回的速度。创建索引语法:

CREATE INDEX index ON table(column[,column]...);

       例如:CREATE Index dept_dname_idx ON dept(dname);
       在表上建立更多的索引并不意味者更快地查询,在带索引的表上被提交的每个 DML操作意味者索引必须更新;与表联系的索引越多,对Oracle服务器的影响越大。因此,你只需要在以下情况下创建索引:
       1.列包含一个大范围的值、列包含大量的空值、一个或多个列在WHERE子句或连接条件中被频繁使用、表很大并且大多数查询所期望的返回行数少于总行数的2–4%。
       2.如果你想要强制非唯一,你应该在表中定义一个唯一的约束,然后唯一索引被自动创建。
       3.复合索引 (也称为连接索引) 是在一个表中的多个列上创建的索引,在复合索引中的列可以任何顺序出现,并且不需要与在表中的列相一致。

       下面的情况通常不值得创建索引:

       表很小,不经常在查询中作为条件被使用的列,大多数查询期望取回多于表中百分之2到4的行,表经常被更新,被索引的列作为表达式的的一部分被引用。

       2.2 查询索引

       从USER_INDEXES数据字典视图可以确认索引的存在。你也可以查询 USER_IND_COLUMNS视图,检查与索引有关的列。下面的例子查询EMPLOYEES表上所有已创建的索引,包括受影响的列的名字和索引的唯一性。

Select Ic.Index_Name,
       Ic.Column_Name,
       Ic.Column_Position Col_Pos,
       Ix.Uniqueness
From User_Indexes Ix, User_Ind_Columns Ic
Where Ic.Index_Name = Ix.Index_Name
And Ic.Table_Name = 'DEPT';
查询索引实例

       2.3 删除索引

       你不能修改索引,为了改变索引,你必须先删除它,然后重新创建它。用DROP INDEX 语句从数据字典中删除索引,为了删除索引,你必须是索引的所有者,或者有 DROP ANY INDEX权限。如果你删除一个表,相关的索引和约束将被自动删除,但视图和序列将保留。删除索引语法:

DROP INDEX index;

       例如:DROP INDEX dept_dname_idx;

      三、创建和管理同义词

       为了查阅另一个用户所拥有的表,你需要将创建该表的用户名加句点作为前缀加在表名前面。创建一个同义词可以除去对象名必须带的方案限制,并提供给你一个可替换表名、视图名、序列名和过程名或其它对象名。该方法对具有特别长的对象的名字很有用。

       3.1 创建同义词

CREATE [PUBLIC] SYNONYM synonym FOR object;
--PUBLIC 创建一个可以被所有用户访问的同义词
--synonym 是要被创建的同义词的名字
--object 指出要创建同义词的对象

       数据库管理员可以创建一个可为所有用户访问的公共同义词。下面的例子为scott的dept表创建一个公共同义词:

CREATE PUBLIC SYNONYM dept_syn
FOR scott.dept;
创建同义词实例

       3.2 删除同义词

       用DROP SYNONYM语句删除一个同义词,只有数据库管理员可以删除一个公共同义词。

DROP PUBLIC SYNONYM synonym;

       例如:DROP PUBLIC SYNONYM dept_syn;

友情提示:
信息收集于互联网,如果您发现错误或造成侵权,请及时通知本站更正或删除,具体联系方式见页面底部联系我们,谢谢。

其他相似内容:

热门推荐: