Oracle——索引，序列，觸發器

1.索引

　　1）注意

　　　　oracle建立主鍵時會自動在該列上建立索引

　　2）索引原理

　　　　A.  若沒有索引，搜尋某個記錄時（例如查找name='wish'）需要搜尋所有的記錄，因為不能保證隻有一個wish，必須全部搜尋一遍

　　　　B. 若在name上建立索引，oracle會對全表進行一次搜尋，将每條記錄的name值哪找升序排列，然後建構索引條目（name和rowid），存儲到索引段中，查詢name為wish時即可直接查找對應地方

　　　　C.建立了索引并不一定就會使用，oracle自動統計表的資訊後，決定是否使用索引，表中資料很少時使用全表掃描速度已經很快，沒有必要使用索引

　　3）索引使用()

　　　　A.建立索引的文法

CREATE [UNIQUE] | [BITMAP] INDEX index_name  --unique表示唯一索引
ON table_name([column1 [ASC|DESC],column2    --bitmap，建立位圖索引
[ASC|DESC],…] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1]                                 --指定索引在資料塊中空閑空間
[STORAGE (INITIAL n2)]
[NOLOGGING]                                  --表示建立和重建索引時允許對表做DML操作，預設情況下不應該使用
[NOLINE]
[NOSORT];                                    --表示建立索引時不進行排序，預設不适用，如果資料已經是按照該索引順序排列的可以使用      

 　　　　B.修改索引

　　　　　　重命名索引：alter index index_sno rename to bitmap_sno

　　　　　　合并索引：表使用一段時間會産生資料碎片，此時重建索引或合并索引會很好：alter index index_sno coalesce;

　　　　　　重建索引：alter index index_sno rebuild;

　　　　 C.删除索引

　　　　　　drop index index_sno

　　　　 D.檢視索引 

　　　　　　create index index_sno on student('name');

　　　　　　select * from all_indexes where table_name='student';      

　　4）索引分類

　　　　A：B樹索引

　　　　　　就是我們不同的索引：create index index_sno on table('sno')

　　　　　　應用場景：列基數大（列不重複數）時

　　　　B：位圖索引

　　　　　　建立位圖索引時，oracle會掃描整張表，并為索引列的每個取值建立一個位圖（位圖中，對表中每一行使用一位（bit，0或者1）來辨別該行是否包含該位圖的索引列的取值，如果為1，表示對應的rowid所在的記錄包含該位圖索引列值），最後通過位圖索引中的映射函數完成位到行的ROWID的轉換

　　　　　　create bitmap index index_sno on table(sno);

　　　　　　應用場景：基數小的列

　　　　C：單列索引複合索引

　　　　D：函數索引

　　　　　　create index fbi on student (upper(name));

　　　　　　select * from student where upper(name) ='WISH';
　　
      

　　5）原則

　　1. 如果有兩個或者以上的索引，其中有一個唯一性索引，而其他是非唯一，這種情況下oracle将使用唯一性索引而完全忽略非唯一性索引

　　2. 至少要包含組合索引的第一列（即如果索引建立在多個列上，隻有它的第一個列被where子句引用時，優化器才會使用該索引）

　　3. 小表不要履歷索引

　　4. 對于基數大的列适合建立B樹索引，對于基數小的列适合履歷位圖索引

　　5. 列中有很多空值，但經常查詢該列上非空記錄時應該建立索引

　　6. 經常進行連接配接查詢的列應該建立索引

　　7. 使用create index時要将最常查詢的列放在最前面

　　8. LONG（可變長字元串資料，最長2G）和LONG RAW（可變長二進制資料，最長2G）列不能建立索引

　　9.限制表中索引的數量（建立索引耗費時間，并且随資料量的增大而增大；索引會占用實體空間；當對表中的資料進行增加、删除和修改的時候，索引也要動态的維護，降低了資料的維護速度）

　　6）注意事項

　　　　

1. 通配符在搜尋詞首出現時，oracle不能使用索引，eg：

--我們在name上建立索引；

create index index_name on student('name');

--下面的方式oracle不适用name索引

select * from student where name like '%wish%';

--如果通配符出現在字元串的其他位置時，優化器能夠利用索引；如下：

select * from student where name like 'wish%';      

 2. 不要在索引列上使用not，可以采用其他方式代替如下：（oracle碰到not會停止使用索引，而采用全表掃描）

select * from student where not (score=100);

select * from student where score <> 100;

--替換為

select * from student where score>100 or score <100      

3. 索引上使用空值比較将停止使用索引， eg：

select * from student where score is not null;      

2.序列

　　1）建立序列

　　2）通過序列插入表資料自增長

　　　　insert into textseq values(seq_textseq.nextval,'zs')

3.同義詞

　　Create synonym dept for scott.dept;建立私有同義詞

　　drop synonym dept;删除私有同義詞

　　Create public synonym dept for scott.dept;建立共有同義詞

　　drop public sysnonym dept;删除共有同義詞

　　

 3.觸發器

　　http://blog.csdn.net/indexman/article/details/8023740/

　　1）建立觸發器的一般文法

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.]table_name | [schema.]view_name 
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
PL/SQL_BLOCK | CALL procedure_name;      

BEFORE 和AFTER指出觸發器的觸發時序分别為前觸發和後觸發方式，前觸發是在執行觸發事件之前觸發目前所建立的觸發器，後觸發是在執行觸發事件之後觸發目前所建立的觸發器。

       FOR EACH ROW選項說明觸發器為行觸發器。行觸發器和語句觸發器的差別表現在：行觸發器要求當一個DML語句操作影響資料庫中的多行資料時，對于其中的每個資料行，隻要它們符合觸發限制條件，均激活一次觸發器；而語句觸發器将整個語句操作作為觸發事件，當它符合限制條件時，激活一次觸發器。當省略FOR EACH ROW 選項時，BEFORE 和AFTER 觸發器為語句觸發器，而INSTEAD OF 觸發器則隻能為行觸發器。

           REFERENCING 子句說明相關名稱，在行觸發器的PL/SQL塊和WHEN 子句中可以使用相關名稱參照目前的新、舊列值，預設的相關名稱分别為OLD和NEW。觸發器的PL/SQL塊中應用相關名稱時，必須在它們之前加冒号(:)，但在WHEN子句中則不能加冒号。

WHEN 子句說明觸發限制條件。Condition 為一個邏輯表達時，其中必須包含相關名稱，而不能包含查詢語句，也不能調用PL/SQL 函數。WHEN 子句指定的觸發限制條件隻能用在BEFORE 和AFTER 行觸發器中，不能用在INSTEAD OF 行觸發器和其它類型的觸發器中。

    當一個基表被修改( INSERT, UPDATE, DELETE)時要執行的存儲過程，執行時根據其所依附的基表改動而自動觸發，是以與應用程式無關，用資料庫觸發器可以保證資料的一緻性和完整性。

　　2）例子：建立一個觸發器, 當職工表 emp 表被删除一條記錄時，把被删除記錄寫到職工表删除日志表中去。

CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2; 
CREATE OR REPLACE TRIGGER tr_del_emp 
   BEFORE DELETE --指定觸發時機為删除操作前觸發
   ON scott.emp 
   FOR EACH ROW   --說明建立的是行級觸發器 
BEGIN
   --将修改前資料插入到日志記錄表 del_emp ,以供監督使用。
   INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )
       VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );
END;
DELETE emp WHERE empno=7788;
DROP TABLE emp_his;
DROP TRIGGER del_emp;      

　　限制對Departments表修改（包括INSERT,DELETE,UPDATE）的時間範圍，即不允許在非工作時間修改departments表

CREATE OR REPLACE TRIGGER tr_dept_time
BEFORE INSERT OR DELETE OR UPDATE 
ON departments
BEGIN
 IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00') THEN
     RAISE_APPLICATION_ERROR(-20001, '不是上班時間，不能修改departments表');
 END IF;
END;      

不虛度光陰，享受生命裡的每一天！