Oracle序列使用：建立、删除

2321ewq

1. 背景介绍
什么是semi-join？
所谓的semi-join是指semi-join子查询。 当一张表在另一张表找到匹配的记录之后，半连接（semi-jion）返回第一张表中的记录。与条件连接相反，即使在右节点中找到几条匹配的记录，左节点 的表也只会返回一条记录。另外，右节点的表一条记录也不会返回。半连接通常使用IN  或 EXISTS 作为连接条件。 该子查询具有如下结构：

SELECT ... FROM outer_tables WHERE expr IN (SELECT ... FROM inner_tables ...) AND ...

即在where条件的“IN”中的那个子查询。
这种查询的特点是我们只关心outer_table中与semi-join相匹配的记录。
换句话说，最后的结果集是在outer_tables中的，而semi-join的作用只是对outer_tables中的记录进行筛选。这也是我们进行 semi-join优化的基础，即我们只需要从semi-join中获取到最少量的足以对outer_tables记录进行筛选的信息就足够了。
所谓的最少量，体现到优化策略上就是如何去重。
以如下语句为例：

select * from Country
where
  Country.Code in
(select City.country
                   from City
                   where City.Population>1*1000*1000);

当中的semi-join： “

select City.country
                   from City
                   where City.Population>1*1000*1000

” 可能返回的结果集如下： China(Beijin), China(Shanghai), France(Paris)...
我们可以看到这里有2个China，分别来至2条城市记录Beijin和Shanghai, 但实际上我们只需要1个China就足够对outer_table
Country进行筛选了。所以我们需要去重。

2. Mysql支持的Semi-join策略
Mysql支持的semi-join策略主要有5个，它们分别为：
1. DuplicateWeedout: 使用临时表对semi-join产生的结果集去重。
Duplicate Weedout: Run the semi-join as if it was a join and remove duplicate records using a temporary table.
mysql中semi-join的优化策略介绍
对应的匹配条件为：
mysql中semi-join的优化策略介绍

2. FirstMatch: 只选用内部表的第1条与外表匹配的记录。
FirstMatch: When scanning the inner tables for row combinations and there are multiple instances of a given value group, choose one rather than returning them all. This "shortcuts" scanning and eliminates production of unnecessary rows.
mysql中semi-join的优化策略介绍
对应的匹配条件为：
mysql中semi-join的优化策略介绍


3. LooseScan: 把inner-table数据基于索引进行分组，取每组第一条数据进行匹配。
LooseScan: Scan a subquery table using an index that enables a single value to be chosen from each subquery's value group.
mysql中semi-join的优化策略介绍
对应的匹配条件为：
mysql中semi-join的优化策略介绍



4. Materializelookup: 将inner-table去重固化成临时表，遍历outer-table，然后在固化表上去寻找匹配。
Materialize the subquery into a temporary table with an index and use the temporary table to perform a join. The index is used to remove duplicates. The index might also be used later for lookups when joining the temporary table with the outer tables; if not, the table is scanned.
对应的匹配条件：
mysql中semi-join的优化策略介绍


5. MaterializeScan: 将inner-table去重固化成临时表，遍历固化表，然后在outer-table上寻找匹配。
mysql中semi-join的优化策略介绍
对应的条件：
mysql中semi-join的优化策略介绍


Each of these strategies except Duplicate Weedout can be enabled or disabled using the optimizer_switch system variable. The semijoin flag controls whether semi-joins are used. If it is set to on, the firstmatch, loosescan, and materialization flags enable finer control over the permitted semi-join strategies. These flags are on by default.

The use of semi-join strategies is indicated in EXPLAIN output as follows:

    Semi-joined tables show up in the outer select. EXPLAIN EXTENDED plus SHOW WARNINGS shows the rewritten query, which displays the semi-join structure. From this you can get an idea about which tables were pulled out of the semi-join. If a subquery was converted to a semi-join, you will see that the subquery predicate is gone and its tables and WHERE clause were merged into the outer query join list and WHERE clause.

    Temporary table use for Duplicate Weedout is indicated by Start temporary and End temporary in the Extra column. Tables that were not pulled out and are in the range of EXPLAIN output rows covered by Start temporary and End temporary will have their rowid in the temporary table.

    FirstMatch(tbl_name) in the Extra column indicates join shortcutting.

    LooseScan(m..n) in the Extra column indicates use of the LooseScan strategy. m and n are key part numbers.

    As of MySQL 5.6.7, temporary table use for materialization is indicated by rows with a select_type value of MATERIALIZED and rows with a table value of <subqueryN>.

    Before MySQL 5.6.7, temporary table use for materialization is indicated in the Extra column by Materialize if a single table is used, or by Start materialize and End materialize if multiple tables are used. If Scan is present, no temporary table index is used for table reads. Otherwise, an index lookup is used.



mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
                    index_merge_sort_union=on,
                    index_merge_intersection=on,
                    engine_condition_pushdown=on,
                    index_condition_pushdown=on,
                    mrr=on,mrr_cost_based=on,
                    block_nested_loop=on,batched_key_access=off,
                    materialization=on,semijoin=on,loosescan=on,
                    firstmatch=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on




在开始讲解Oracle序列使用方法之前，先加一点关于Oracle client sqlplus的使用，就是如果执行多行语句的话一定要加“/”才能表示结束，并执行！本篇文章的主题是通过创建Oracle序列和触发器实现表的主键自增。

1.首先创建序列，Oracle序列的语法格式为：
CREATE SEQUENCE 序列名
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE/ MINVALUE n|NOMAXVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE n|NOCACHE}];

1)INCREMENT BY用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表Oracle序列的值是按照此步长递减的。

2)START WITH 定义序列的初始值(即产生的第一个值)，默认为1。

3)MAXVALUE 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义，这时对于递增Oracle序列，系统能够产生的最大值是10的27次方;对于递减序列，最大值是-1。

4)MINVALUE定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项，代表没有最小值定义，这时对于递减序列，系统能够产生的最小值是?10的26次方;对于递增序列，最小值是1。

5)CYCLE 和NOCYCLE 表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值;对于递 减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。

6)CACHE(缓冲)定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。

2.删除Oracle序列的语法是DROP SEQUENCE 序列名;

假设有表TEST，其主键为TEST_ID
1)建立递增序列SEQ_TEST:
create sequence SEQ_TEST
increment by 1
start with 1
minvalue 1 nomaxvalue
nocylce

2)建立触发器，当有数据插入表TEST时，使用Oracle序列为其去的递增的主键值
create trigger TRG_TEST before insert on TEST
for each row
begin
select SEQ_TEST.nextval into :new.TEST_ID from dual;
end;

至此，创建完成！

当然也可以不使用触发器，而是在插入时在sql语句中调用序列，例如
insert into TEST values(SEQ_TEST.nextval, ……)





3、修改序列语法：

ALTER SEQUENCE 序列名
　　[INCREMENT BY n]
　　[{MAXVALUE/ MINVALUE n|NOMAXVALUE}]
　　[{CYCLE|NOCYCLE}]
　　[{CACHE n|NOCACHE}];

*不能修改序列的初始值

但是要注意的是：
- 第一次NEXTVAL返回的是初始值；随后的NEXTVAL会自动增加你定义的INCREMENT BY值，
然后返回增加后的值。CURRVAL 总是返回当前sequence的值，但是在第一次NEXTVAL
初始化之后才能使用CURRVAL，否则会出错。一次NEXTVAL会增加一次sequence的值，
所以如果你在同一个语句里面使用多个NEXTVAL，其值就是不一样的。明白？
- 如果指定CACHE值，oracle就可以预先在内存里面放置一些sequence，这样存取的快
些。cache里面的取完后，oracle自动再取一组到cache。 使用cache或许会跳号， 比如
数据库突然不正常down掉（shutdown abort),cache中的sequence就会丢失. 所以可
以在create sequence的时候用nocache防止这种情况。



模版>>

Oracle创建序列：

create sequence seq_a
minvalue 1000
maxvalue 99999999
start with 1000
increment by 1
nocache;  

查询序列:

select seq_a.nextval from dual;  

为每张表生成对应的序列:

--创建存储过程:


    create or replace procedure p_createseq(tablename in varchar2)   
    is   
    strsql varchar2(500);   
    begin   
    strsql:='create sequence seq_'||tablename||' minvalue 1000 maxvalue 99999999 start with 1000 increment by 1 nocache';   
    execute immediate strsql;   
    end p_createseq;   
    /  



--Oracle创建序列:


exec p_createseq('t_power');   
exec p_createseq('t_roler');   
exec p_createseq('t_roler_power');   
exec p_createseq('t_department');   
exec p_createseq('t_quarters');   
exec p_createseq('t_quarters_roler');   
exec p_createseq('t_emp');   
exec p_createseq('t_require_plan');   
exec p_createseq('t_require_minutia');   
exec p_createseq('t_require_audit');   
exec p_createseq('t_engage');   
exec p_createseq('t_home');   
exec p_createseq('t_education');
... ...