Oracle視圖，索引，序列

標籤：--   scott   sql查詢語句   use   com   策略   art   最佳化   表示   

什麼是視圖【View】
（1）視圖是一種虛表
（2）視圖建立在已有表的基礎上, 視圖賴以建立的這些表稱為基表
（3）向視圖提供資料內容的語句為 SELECT 語句,可以將視圖理解為儲存起來的 SELECT 語句
（4）視圖向使用者提供基表資料的另一種表現形式
（5）視圖沒有儲存真正的資料，真正的資料還是儲存在基表中
（6）程式員雖然操作的是視圖，但最終視圖還會轉成操作基表
（7）一個基表可以有0個或多個視圖

什麼情況下會用到視圖
（1）如果你不想讓使用者看到所有資料（欄位，記錄），只想讓使用者看到某些的資料時，此時可以使用視圖
（2）當你需要減化SQL查詢語句的編寫時，可以使用視圖，但不提高查詢效率

視圖應用領域
（1）銀行，電信，金屬，證券軍事等不便讓使用者知道所有資料的項目中

視圖的作用
（1）限制資料訪問
（2）簡化複雜查詢
（3）提供資料的相互獨立
（4）同樣的資料，可以有不同的顯示方式

基於emp表所有列，建立視圖emp_view_1，create view 視圖名 as select對一張或多張基表的查詢
create view emp_view_1
as
select * from emp;

預設情況下，普通使用者無權建立視圖，得讓sysdba為你分配creare view的許可權

以sysdba身份，授權scott使用者create view許可權
grant create view to scott;

以sysdba身份，撤銷scott使用者create view許可權
revoke create view from scott;

基於emp表指定列，建立視圖emp_view_2，該視圖包含編號/姓名/工資/年薪/年度營收（查詢中使用列別名）
create view emp_view_2
as
select empno "編號",ename "姓名",sal "工資",sal*12 "年薪",sal*12+NVL(comm,0) "年度營收"
from emp;

基於emp表指定列，建立視圖emp_view_3(a,b,c,d,e)，包含編號/姓名/工資/年薪/年度營收（視圖中使用列名）
create view emp_view_3(a,b,c,d,e)
as
select empno "編號",ename "姓名",sal "工資",sal*12 "年薪",sal*12+NVL(comm,0) "年度營收"
from emp;

查詢emp_view_3建立視圖的結構
desc emp_view_3;

修改emp_view_3(id,name,salary,annual,income)視圖，create or replace view 視圖名 as 子查詢
create or replace view emp_view_3(id,name,salary,annual,income)
as
select empno "編號",ename "姓名",sal "工資",sal*12 "年薪",sal*12+NVL(comm,0) "年度營收"
from emp;

查詢emp表，求出各部門的最低工資，最高工資，平均工資
select min(sal),max(sal),round(avg(sal),0),deptno
from emp
group by deptno;

建立視圖emp_view_4，視圖中包含各部門的最低工資，最高工資，平均工資
create or replace view emp_view_4
as
select deptno "部門號",min(sal) "最低工資",max(sal) "最高工資",round(avg(sal),0) "平均工資"
from emp
group by deptno;

建立視圖emp_view_5，視圖中包含員工編號，姓名，工資，部門名，工資等級
create or replace view emp_view_5
as
select e.empno "編號",e.ename "姓名",e.sal "工資",d.dname "部門名",s.grade "工資等級"
from emp e,dept d,salgrade s
where (e.deptno=d.deptno) and (e.sal between s.losal and s.hisal);

刪除視圖emp_view_1中的7788號員工的記錄，使用delete操作，會影響基表嗎
delete from emp_view_1 where empno=7788;寫法正確，會影響基表

修改emp_view_1為唯讀視圖【with read only】，再執行上述delete操作，還行嗎？
create or replace view emp_view_1
as
select * from emp
with read only;
不能進行delete操作了

刪除視圖中的【某條】記錄會影響基表嗎？
會影響基表

將【整個】視圖刪除，會影響表嗎？
不會影響基表

刪除視圖，會進入資源回收筒嗎？
不會進入資源回收筒

刪除基表會影響視圖嗎？
會影響視圖

閃回基表後,視圖有影響嗎？
視圖又可以正常工作了

-------------------------------------------------------------------------------------同義字

什麼是同義字【Synonym】
（1）對一些比較長名字的對象(表，視圖，索引，序列，。。。）做減化，用別名替代

同義字的作用
（1）縮短對象名字的長度
（2）方便訪問其它使用者的對象

建立與salgrade表對應的同義字，create synonym 同義字 for 表名/視圖/其它對象
create synonym e for salgrade;
create synonym ev5 for emp_view_5;

以sys身份授予scott普通使用者create synonym許可權
grant create synonym to scott;

以sys身份從scott普通使用者撤銷create synonym許可權
revoke create synonym from scott;

使用同義字操作salgrade表
select * from s;

刪除同義字
drop synonym ev5;

刪除同義字，會影響基表嗎？
不會影響基表

刪除基表，會影響同義字嗎？
會影響同義字

-------------------------------------------------------------------------------------序列

什麼是序列【Sequence】
（1）類似於MySQL中的auto_increment自動成長機制，但Oracle中無auto_increment機制
（2）是oracle提供的一個產生唯一數值型值的機制
（3）通常用於表的主健值
（4）序列只能保證唯一，不能保證連續
聲明：oracle中，只有rownum永遠保持從1開始，且繼續
（5）序列值，可放於記憶體，取之較快

題問：為什麼oracle不直接用rownum做主健呢？
rownum=1這條記錄不能永遠唯一表示SMITH這個使用者
但主鍵=1確可以永遠唯一表示SMITH這個使用者

 

為什麼要用序列
（1）以前我們為主健設定值，需要人工設定值，容易出錯
（2）以前每張表的主健值，是獨立的，不能共用

為emp表的empno欄位，建立序列emp_empno_seq，create sequence 序列名
create sequence emp_empno_seq;

刪除序列emp_empno_seq，drop sequence 序列名
drop sequence emp_empno_seq;

查詢emp_empno_seq序列的當前值currval和下一個值nextval，第一次使用序列時，必須選用：序列名.nextval
select emp_empno_seq.nextval from dual;
select emp_empno_seq.currval from dual;

使用序列，向emp表插入記錄，empno欄位使用序列值
insert into emp(empno) values(emp_empno_seq.nextval);
insert into emp(empno) values(emp_empno_seq.nextval);
insert into emp(empno) values(emp_empno_seq.nextval);

修改emp_empno_seq序列的increment by屬性為20，預設start with是1，alter sequence 序列名
alter sequence emp_empno_seq
increment by 20;

修改修改emp_empno_seq序列的的increment by屬性為5
alter sequence emp_empno_seq
increment by 5;

修改emp_empno_seq序列的start with屬性，行嗎
alter sequence emp_empno_seq
start with 100;

有了序列後，還能為主健手工設定值嗎？
insert into emp(empno) values(9999);
insert into emp(empno) values(7900);

刪除表，會影響序列嗎？
你無法做insert操作

刪除序列，會影響表嗎？
表真正亡，序列亡

在hibernate中，如果是訪問oracle資料庫伺服器，那麼User.hbm.xml對應檔中關於<id>標籤如何配置呢？
<id name="id" column="id">
<generator class="increment/identity/uuid/【sequence】/【native】"/>
</id>

-------------------------------------------------------------------------------------索引

什麼是索引【Index】
（1）是一種快速查詢表中內容的機制，類似於新華字典的目錄
（2）運用在表中某個/些欄位上，但儲存時，獨立於表之外

為什麼要用索引
（1）通過指標加速Oracle伺服器的查詢速度
（2）通過rowid快速定位元據的方法，減少磁碟I/O
rowid是oracle中唯一確定每張表不同記錄的唯一身份證

rowid的特點
（1）位於每個表中，但表面上看不見，例如：desc emp是看不見的
（2）只有在select中，顯示寫出rowid，方可看見
（3）它與每個表綁定在一起，表亡，該表的rowid亡，二張表rownum可以相同，但rowid必須是唯一的
（4）rowid是18位大小寫加數字混雜體，唯一表代該條記錄在DBF檔案中的位置
（5）rowid可以參與=/like比較時，用‘‘單引號將rowid的值包起來，且區分大小寫
（6）rowid是聯絡表與DBF檔案的橋樑

索引的特點
（1）索引一旦建立, Oracle管理系統會對其進行自動維護, 而且由Oracle管理系統決定何時使用索引
（2）使用者不用在查詢語句中指定使用哪個索引
（3）在定義primary key或unique約束後系統自動在相應的列上建立索引
（4）使用者也能按自己的需求，對指定單個欄位或多個欄位，添加索引

什麼時候【要】建立索引
（1）表經常進行 SELECT 操作
（2）表很大(記錄超多)，記錄內容分布範圍很廣
（3）列名經常在 WHERE 子句或串連條件中出現
注意：符合上述某一條要求，都可建立索引，建立索引是一個最佳化問題，同樣也是一個策略問題

什麼時候【不要】建立索引
（1）表經常進行 INSERT/UPDATE/DELETE 操作
（2）表很小(記錄超少)
（3）列名不經常作為串連條件或出現在 WHERE 子句中
同上注意

為emp表的empno單個欄位，建立索引emp_empno_idx，叫單列索引，create index 索引名 on 表名(欄位,...)
create index emp_empno_idx
on emp(empno);

為emp表的ename,job多個欄位，建立索引emp_ename_job_idx，多列索引/聯合索引
create index emp_ename_job
on emp(ename,job);
如果在where中只出現job不使用索引
如果在where中只出現ename使用索引
我們提倡同時出現ename和job

注意：索引建立後，只有查詢表有關，和其它（insert/update/delete）無關,解決速度問題

刪除emp_empno_idx和emp_ename_job_idx索引，drop index 索引名
drop index emp_empno_idx;
drop index emp_ename_job_idx;

Oracle視圖，索引，序列