Oracle事務和對象上集（視圖、索引）

標籤：Oracle   事務   索引   視圖   張晨   

一、Oracle事務
·事務的含義：事務是業務上的一個邏輯單元，為了保證資料的所有操作要麼全部完成，要麼全部失敗。
1、事務的開始是從一條SQL語句開始，結束於下面的幾種情況：
1）顯示提交：輸入commit指令，事務完成提交
2）顯示復原：輸入rollback指令，未提交的事務丟掉，復原到事務開始時的狀態。
3）DDL語句：即create、drop等語句，這些語句會使事務自動隱式提交
4）結束程式：輸入exit退出資料庫，則自動認可事務；或者意外終止、出現程式崩潰，則事務自動復原。
2、事務的特點-ACID特性
1）原則性：要麼同時成功，要麼同時失敗的原則
2）一致性：如，a轉賬給b，最總結果a+b的金錢總數是不變的
3）隔離性：當出現多個事務出現，它們之間是互相隔離、互不影響的
4）持久性：事務一旦提交，則資料永久修改。
3、關於事務的三個命令
commit ：立即提交事務
rollback ：復原事務
set autocommit on/off ：設定/關閉自動認可
二、索引
·索引是Oracle的一個對象，是與表關聯的可選結構，用於加快查詢速度，提高檢索效能。
1、特點
1）適當使用索引可以提高查詢速度、建立索引的數量無限制
2）可以對錶的一列或者多列建立索引
3）索引是需要磁碟空間，可以指定資料表空間儲存索引。
4）是否使用索引有Oracle決定
2、索引的分類
B樹索引：從頂部為根，逐漸向下一級展開
唯一索引：定義索引的列沒有任何重複
非唯一索引：與唯一索引相反
反向鍵索引：對與數字列作用較大，會將1234產生4321進行查詢的索引
位元影像索引：應用於資料倉儲和決策支援系統中。優點是相對於b樹索引，可以減少回應時間；相對於其他索引，其空間佔用少。
函數索引：使用函數涉及正在建立索引的列的索引
3、建立索引
操作時我們可以使用Oracle的scott使用者進行測試，首先解鎖，在改一個密碼，登陸進去就可以操作了

create [unique] index i_name on t_name(c_list) [tablespace ts_name];
注釋： create ... index ... on 建立索引
unique 唯一索引
i_name 建立的索引名稱
t_name 在哪個表建立
(c_list) 列名
ts_name 資料表空間名
舉例：
create index emp_ename_idx on emp(ename);
：b樹索引
create unique index emp_ename_unique_idx on emp(ename);
：唯一索引
create index emp_ename_reverse_idx on emp(ename) reverse;
：反向索引
create index emp_ename_upper_idx on emp(upper(ename));
：函數索引
4、建立索引的原則
1）頻繁檢索的列
2）經常排序、分組的列
3）主鍵/外鍵
4）大型索引使用nologing子句建立
5）需定期進行組織索引、磁碟重組
5、維護索引的命令
1）重建索引
alter index i_table rebuild [tablespace ts_name];
2）合并索引片段
alter index i_name coalesce;
3）刪除索引
drop index i_name;
4）查看索引（使用user_indexes、user_ind_columns字典）
select index_name,index_tyep,table_name,column_name from user_indexes;或者
select index_name,table_name,_column_name from user_ind_columns where
index_name like ‘emp%’;
三、視圖
·視圖是一個虛表，不佔用物理空間，視圖本身的定義語句存放於字典裡，可以由一個或者多個表中獲得資料。目的是為了方便查詢，而不需要在敲繁瑣的查詢語句。
1、視圖的作用
1）通過限制對錶中預定的一組行和列進行查看，可以防止使用者看到無許可權資料，提供了安全性
2）簡化了使用者的命令、隱藏了資料的複雜性，方便操作
3）視圖可以對列進行重新命名，提升了資料庫的靈活性和人性化
4）視圖將應用程式與基表定義的修改進行了隔離，表結構的修改不會影響視圖結構。
2、建立視圖
create [or replace] [force] view vname [(alias[,alias]...)] as SQL
[with check option] [with read only];
注釋： or replace ：覆蓋
force ：強制的；視圖中的基表是否存在，都會建立
vname ：建立的視圖名稱
[(alias[,alias]...)] ：可選項，視圖別名，可以由多個
SQL ：SQL查詢語句
with check option ：用於更改視圖時的約束
with read only ：唯讀
舉例：
create view vname as select from tname where ‘a=b’;
簡單的視圖
create view vname as select from tname where ‘a=b’ with read only;
唯讀視圖
create force view vname as select from tname where ‘a=b’;
強制建立視圖，假如‘tname’表不存在，也強制建立，也就是錯誤視圖。
create table tname (a (char));
alter view vname compile;
查看錯誤視圖（使用字典）
show errors view;
show
建立tname表；然後編譯錯誤視圖，使其生效
3、在視圖中使用DML語句（insert、update、delete）的限制
1）DML語句只能修改視圖中的一個基表
2）如果修改違反了check option約束，則無法修改
3）如果視圖包括串連運算子、DISTINCT運算子、集合運算子、彙總函式和group by子句，則無法更新視圖
4）如果包含偽列、運算式，也無法更新視圖
4、維護視圖
1）刪除視圖
drop view vname;
2）查詢已有視圖(user_views字典)
select viewname from user_views;
5、物化視圖
·顧名思義，物化視圖就是將視圖物理化存到磁碟，它與基表即時同步。物化視圖可以避免order by子句或者多表串連查詢帶來的效率降低和時耗，但是會佔用用磁碟空間。
1）物化視圖同步的重新整理方式可分為：
on commit：即提交事務以後
on demand：簡單講就是使用者手動重新整理
2）物化視圖同步的重新整理類型
complete：完全重新整理
fast：追求速度，增量重新整理
force：Oracle自動判斷使用complete還是fast類型重新整理
never：從不重新整理
·關於以上四個類型，Oracle預設選擇force重新整理。工作中無特殊需求，無需更改
6、建立物化視圖
1）首先用sys登陸，為scott使用者授權

2）建立物化視圖日誌

3）正式建立

注釋： build immediate ：立即建立
refresh fast ：重新整理類型為fast
enable query rewrite ：啟用查詢重寫
4）刪除物化視圖
**drop materialized view materview;***

Oracle事務和對象上集（視圖、索引）