oracle序列、触发器及索引示例

程序员文章站 2022-07-13 14:11:51

...

oracle在创建表时和其他的数据库有点不一样，如SQL SERVER可以在int类型的字段后加上identity（1,1），该字段就会从1开始，按照+1的方式自增，将这个字段设置为主键，有利于我们进行数据的插入操作。MySql中可以使用“auto_increment”即可。但是oracle有点麻烦，需要使用序列和触发器。

 首先创建一个测试表
 create table test(
   Id number(10),
   name varchar2(32),
   age number(10)
    );

 二.创建测试表自动增长序列
 create sequence test_queue
 minvalue 1
 maxvalue 9999999999999999999999999999
 start with 1
 increment by 1
 cache 20;

 第三，创建触发器将序列中的值赋给插入test表的行
 create or replace trigger insert_test_queue
 before insert  on test
 for each row
      begin
           select test_queue.nextval into :new.Id from dual;
      end;

 最后测试一下
 insert into test(name,age)
 values('testname',11);


 当然也可以不使用触发器，而是在插入时在sql语句中调用序列，例如：
 insert into test values(test_queue.nextval,'testname',11);

INCREMENT BY 用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表序列的值是按照此步长递减的。
START WITH 定义序列的初始值（即产生的第一个值），默认为1。
MAXVALUE 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义，这时对于递增序列，系统能够产生的最大值是10的27次方；对于递减序列，最大值是-1。
MINVALUE定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项，代表没有最小值定义，这时对于递减序列，系统能够产生的最小值是?10的26次方；对于递增序列，最小值是1。
CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值；对于递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。
CACHE(缓冲)定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。

索引：
首先我们要知道，并不是创建了索引就一定会加快查询速度，多数索引使用了B树（图1）或B+树（图2）的数据结构。
oracle序列、触发器及索引示例

对于频繁进行增删改操作的表，建立索引会增加维护索引的操作，会导致增删改速度变慢。
最适合索引的列是出现在where语句中的列,或者连接字句中指定的列,而不是出现在select关键字后的选择列表中的列。
一些字段需要频繁用作查询条件，并且表数据较多的时候，创建索引会明显提高查询速度，因为可由全表扫描改成索引扫描。

简单索引: 
create index 索引名 on 表名(列名) 

唯一索引: 
create unique index 索引名 on 表名(列名) 

一张表创建多个索引
create index 索引名 on 表名(列名1,列名2) 
(索引建立在多个列上，只有它的第一个列(列名1)被where子句引用时，优化器才会使用该索引)

当要求索引查出时排序 
create index 索引名 on 表名(列名 desc或asc)

我们也需要注意一些情况下索引会失效：
where语句后使用！= ，or，对字段进行表达式或函数操作，都会使索引失效
where后判断字段为空时，=null会使索引失效，应该用is null
如果列类型是字符串，需要在条件中将数据使用引号引用起来，否则不使用索引
我们可以使用explain字段查看索引情况：
oracle序列、触发器及索引示例

like的模糊查询以 % 开头，索引失效