Oracle序列和索引

http://www.cnblogs.com/bzx888/p/4807170.html

一.序列

1.序列的概念：

　　序列（Sequence）是用来生成连续的整数数据的对象。它常常用来作为主键的增长列，可以升序，也可以降序。

2.创建序列：

　　语法：创建序列                                              语法解析：

　　CREATE SEQUENCE sequence_name　　　　　

　　[STRAT WITH num]　　　　　　　　　　　　　START WITH:从某一个整数开始，升序默认为1，降序默认为-1.

　　[INCREMENT BY increment]　　　　　　　　　INCREMENT BY:增长数。如果是正数则升序生成，负数则降序生成。升序默认为1，降序默认为-1。

　　[MAXVALUE num | NOMAXVALUE]　　　　　　MAXVALUE:最大值。　　NOMAXVALUE:最大值的默认选项，升序为,10的27次幂，降序默认值为-1。

　　[MINVALUE  num | NOMINVALUE]　　　　　　MINVALUE:最小值。　　　NOMINVALUE:最小值的默认选项，升序为1，降序为-10的26次幂。

　　[CYCLE | NOCYCLE]　　　　　　　　　　　　　CYCLE:表示升序达到最大值的时候，从最小值开始。如果是降序的话，达到最小值后，从最大值开始。

　　　　　　　　　　　　　　　　　　　　　　　　　NOCYCLE：表示达到最大或最小值时，不重新开始。（会报错）。默认为NOCYCLE。

　　[CACHE num | NOCACHE]　　　　　　　　　  CACHE:使用CACHE选项时，该序列会根据序列规则生成一组序列号。保留在内存中，当使用下一个序列号时，可以更快的响应。

　　　　　　　　　　　　　　　　　　　　　　　　　当内存中的序列号用完时，系统会再次生成一组序列号保存在缓存中，这样可以提高生成序列号的效率。ORACLE会默认生成

　　　　　　　　　　　　　　　　　　　　　　　　　20个序列号。

　　　　　　　　　　　　　　　　　　　　　　　　　NOCACHE:不预先在内存中生成序列号。

　　示例：创建一个从5开始，每次增长3的序列，要求最大值为1000，CYCLE，缓存区有30个预选序列号。

1

2

3

4

5

6

create sequence aa

start with 5

increment by 3

maxvalue 1000

cycle

cache 30;

3.利用序列：

　　序列创建之后，我们可以通过序列对象的CURRVAL和NEXTVAL两个‘伪列’来访问序列的当前值和下一个值。

• 查看下一个值：

1

select aa.nextval from dual;

• 查看当前值：

1

select aa.currval from dual;

　　注意：当你刚创建好序列时，不能直接查看currval当前值，会报错，因为你还没有从序列中取值，所以currval是不被识别的。先取值（nextval，第一次为5），然后就可以查看当前值了。

• 利用序列插入数据。

　　我先创建一个表:

1

2

3

4

create table bb(

  id int primary key

  name varchar2(30);   

);

　　利用上个序列插入给id插入数据：

1

insert into bb values(aa.nextval,'MIKE');

　　插入了id为5，name为MIKE的一条数据。

 4.序列的删除和修改:

• 修改序列：

1

2

alter sequence aa

maxvalue 200;

　　修改aa序列的最大值为200。其他属性照例即可。

• 删除序列：

1

drop sequence aa;

二：索引

1.索引的概念：

　　索引是与表关联的可选结构。可以创建索引加快对表执行SQL语句的速度。就像书的索引可以帮助我们更快的查找信息一样，Oracle中的索引也提供了一种更快的访问数据的途径。

2.什么情况下符合创建索引的条件：

• 在经常需要搜索的列上，可以加快搜索的速度。
• 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构。
• 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度。
• 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是可见的，速度要快。
• 在经常使用where子句的列上面创建索引，加快条件的判断速度。

3.什么情况下不应该创建索引：

• 很少查询的列，只是作为参考的列，不应该创建索引。
• 对于那些数值很少的列。
• 对于那些定义为blob数据类型（大数据）的列。
• 经常增删改的列，创建索引需谨慎。

4.索引的优点。

• 通过创建唯一性索引，可以保证数据库表中没一行数据的唯一性。
• 可以大大的加快数据的检索速度，也是创建索引的最主要的原因。
• 可以加快表和表的连接，特别是在实现数据的参照完整性方面特别有意义。
• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
• 通过使用检索，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

5.索引的缺点。

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
• 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。
• 当对表中的数据进行增删改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

6.创建索引。

• 　　创建B-Tree索引：

　　语法：　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　语法分析：

　　CREATE [UNIQUE] INDEX index_name ON　　　　　　　　　　　　　　UNIQUE:意味着索引列中的值必须是唯一的。

　　table_name(column_name,[column_name...])　　　　　　　　　　　　index_name:索引的名称。

　　TABLESPACE tab_space;　　　　　　　　　　　　　　　　　　　　　　　table_name：需要加索引的表名。　　　

 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　column_name：需要加索引的列（可以加多个列，这样的索引称为符合索引。）

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 tab_space:存贮索引的表空间。如果不指定的话，那么索引将会存贮在用户默认的表空间下。（出于性能原因，可以将索引存贮的表

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　空间和实际表存贮的表空间分开。管理员应该为表和索引创建不同的表空间。）　

 　　示例：为emp员工表的员工号和薪水创建索引。

1

create index empno_sal_index on emp(empno,sal);

　　注意：如果你所要创建的索引的列的值有重复的值，那么就不能创建唯一索引。

• 　　创建函数索引。

　　如果我现在已经创建好了一个ename（员工姓名）列的索引，而现在想实现这样的一条查询语句。

1

select lower(ename) from emp;

　　那么这条语句虽然可以出来结果，但是却没有用到索引。这时我们就需要创建函数索引如下：

1

create index ename_index on emp(lower(ename));

•     创建位图索引。

1

create bitmap index e_i on emp(ename);

 

　　什么情况下创建位图索引：

1. 列包含较大范围的值
2. 列包含大量空值
3. 在where子句或者连接条件中频繁使用一个列或者多个列
4. 表相当的大，但是预计的多数查询检索的行数占总行数的比例较小，如占百分之十以下。

•     四个索引的总结。

　　B树

    　　默认的索引类型，平衡树索引，适用于高基数(不同值的程度高）的列。除非有特殊原因需要使用不同的索引类型或功能，否则用正常的B树索引即可.

　　唯一索引

     　　B树索引的一种形式，用于强制执行列值的唯一性.经常与主键和唯一键约束一起使用，但也可以独立于约束而创建

　　位图索引

     　　对于包含低基数列以及在SQL语句的WHERE子句中使用许多AND或OR运算符的数据仓库环境，非常适合使用这种索引。

　　函数索引

    　　 适用于应用了SQL函数的列。可与B树索引类型或位图索引类型结合使用

 

7.查看索引信息。

1

2

3

SELECT index_name, table_name, uniqueness, status

FROM user_indexes

WHERE table_name IN ('EMP');

　　这个语句实现了查询索引名字，索引的表名，是否是唯一索引，索引的状态（是否可用）。结果截图如下：

　　INVALID的话就是当前索引不可用。

8.修改、删除索引。

• 修改索引：

1

alter index ename_index rename to ei;

　　更改索引名，其他修改格式都是如此。

• 删除索引：

1

drop index ei;