ORACLE SEQUENCE的簡單介紹

在oracle中sequence就是所謂的序列號，每次取的時候它會自動增加，一般用在需要按序列號排序的地方。  
1、Create Sequence  
你首先要有CREATE SEQUENCE或者CREATE ANY SEQUENCE權限，  
CREATE SEQUENCE emp_sequence  
    INCREMENT BY 1  -- 每次加幾個  
    START WITH 1    -- 從1開始計數  
    NOMAXVALUE      -- 不設置最大值  
    NOCYCLE         -- 一直累加，不循環  
    CACHE 10;  

一旦定義了emp_sequence，你就可以用CURRVAL，NEXTVAL  
 CURRVAL=返回 sequence的當前值  
 NEXTVAL=增加sequence的值，然返回 sequence 值  
比如：  
  emp_sequence.CURRVAL  
  emp_sequence.NEXTVAL  

可以使用sequence的地方：  
- 不包含子查詢、snapshot、VIEW的 SELECT 語句  
- INSERT語句的子查詢中  
- NSERT語句的VALUES中  
- UPDATE 的 SET中    

可以看如下例子：  
INSERT INTO emp VALUES   
(empseq.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20);  

SELECT empseq.currval     FROM DUAL;  

但是要注意的是：  
- 第一次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防止這種情況。  

2、Alter Sequence  
你或者是該sequence的owner，或者有ALTER ANY SEQUENCE 權限才能改動sequence. 可以alter除start至以外的所有sequence參數.如果想要改變start值，必須 drop  sequence 再 re-create .  
Alter sequence 的例子  
ALTER SEQUENCE emp_sequence  
    INCREMENT BY 10  
    MAXVALUE 10000  
    CYCLE    -- 到10000從頭開始  
    NOCACHE ;  


影響Sequence的初始化參數：  
SEQUENCE_CACHE_ENTRIES =設置能同時被cache的sequence數目。   

可以很簡單的Drop Sequence  
DROP SEQUENCE order_seq;  




http://fanqiang.chinaunix.net/a2/b2/20010514/10150052_b.html

Write an SQL Query to Delete Duplicate records

Ref:

http://www.allinterview.com/showanswers/56895.html

RESET SEQUENCE

I'm using the sequence to generate an identifier in the format YYYY<sequence value> and the sequence value has to be reset to 0 every year.

   DROP SEQUENCE MY_SEQ;
    CREATE SEQUENCE MY_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 0;

CREATE OR REPLACE PROCEDURE my_seq_reset AS
BEGIN
    EXECUTE IMMEDIATE 'DROP SEQUENCE my_seq';
    EXECUTE IMMEDIATE
      'CREATE SEQUENCE my_seq' ||
      '  MINVALUE 1 ' ||
      '  MAXVALUE 999999 ' ||
      '  START WITH 1 ' ||
      '  INCREMENT BY 1 ' ||
      '  NOCACHE';
END;

http://stackoverflow.com/questions/942844/how-do-i-automatically-reset-a-sequences-value-to-0-every-year-in-oracle-10g

http://stackoverflow.com/questions/5817545/how-to-change-the-oracle-sequence-using-loop

在java會有TRANSECTION的問題，最後要加PRAGMA AUTONOMOUS_TRANSACTION;

最後的寫法:

JUC6306


PROCEDURE RESETSEQ   -- 每次進來重新RESET SEQUENCE
IS
PRAGMA AUTONOMOUS_TRANSACTION;


    S VARCHAR2(500);
BEGIN    
    BEGIN
        EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ';
    EXCEPTION
      WHEN OTHERS THEN NULL;
    END;


     EXECUTE IMMEDIATE
      'CREATE SEQUENCE SEQ START WITH 1'
         ||' INCREMENT BY 1 NOCACHE';


END RESETSEQ;   

SQL Tuning

Tuing的好文章

3) Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query. 
For Example: Write the query as

SELECT name 
FROM employee 
WHERE (salary, age ) = (SELECT MAX (salary), MAX (age) 
FROM employee_details) 
AND dept = 'Electronics'; 

Instead of:

SELECT name 
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee_details) 
AND age = (SELECT MAX(age) FROM employee_details) 
AND emp_dept = 'Electronics'

http://beginner-sql-tutorial.com/sql-query-tuning.htm