視圖、序列、索引、同義字

標籤：des   style   blog   io   ar   color   os   使用   sp   

一、視圖
文法：
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY];
1、簡單視圖：

建立視圖：SQL> create view test_view as select * from emp where deptno=10;查詢檢視：SQL> select * from test_view;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ----------7782 CLARK MANAGER 7839 09-6月 -81 2450 107839 KING PRESIDENT 17-11月-81 5000 107934 MILLER CLERK 7782 23-1月 -82 1300 10查看視圖結構：

SQL> DESC TEST_VIEW;

2、建立複雜視圖

SQL> CREATE VIEW AVG_SAL_COMM AS SELECT DNAME 部門名稱,D.DEPTNO 部門編號,COUNT(ENAME) 部門總人數,ROUND(AVG(NVL(SAL,0)),2) 部門平均工資,ROUND(AVG(NVL(COMM,0)),1) 部門平均資金 FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO=D.DEPTNO GROUP BY DNAME,D.DEPTNOORDER BY D.DEPTNO; SQL> SELECT * FROM AVG_SAL_COMM;部門名稱 部門編號 部門總人數 部門平均工資 部門平均資金-------------- ---------- ---------- ------------ ------------ACCOUNTING 10 3 2916.67 0RESEARCH 20 5 2175 0SALES 30 6 1566.67 366.7OPERATIONS 40 0 0 0testSEQ 94 0 0 0

3、在視圖定義中，可以使用WITH READ ONLY選項來保證該視圖上不能進行DML操作.

4、刪除視圖
DROP VIEW VIEW_NAME;

二、序列
1、建立序列
--建立一個名稱為 DEPT_DEPTNO的序列值，以用於DEPT表.不要設定 CYCLE 選項.

CREATE SEQUENCE DEPT_DEPTNOINCREMENT BY 1START WITH 91MAXVALUE 100NOCACHE --CACHE(緩衝)定義存放序列的記憶體塊的大小，預設為20。NOCACHE表示不對序列進行記憶體緩衝。對序列進行記憶體緩衝，可以改善序列的效能。NOCYCLE;/*NEXTVAL 返回下一個可用的序列值,每訪問一次，將產生一個新的值。. CURRVAL 返回當前的序列值.只有當NEXTVAL被訪問之後，CURRVAL偽列才能包含一個值.所以剛建立好的序列，第一次訪問CURRVAL時報錯。必須先訪問NEXTVAL再訪問CURRVAL。*/

2、序列的使用及查詢

SQL> SELECT DEPT_DEPTNO.CURRVAL FROM DUAL;SELECT DEPT_DEPTNO.CURRVAL FROM DUAL*第 1 行出現錯誤:ORA-08002: 序列 DEPT_DEPTNO.CURRVAL 尚未在此會話中定義SQL> SELECT DEPT_DEPTNO.NEXTVAL FROM DUAL;NEXTVAL----------91SQL> SELECT DEPT_DEPTNO.CURRVAL FROM DUAL;CURRVAL----------91SQL>--使用序列向表DEPT中插入資料SQL> insert into dept values(dept_deptno.nextval,‘testSEQ‘,‘testLOC‘);已建立 1 行。--如果一個序列是以 NOCACHE選項建立的, 那麼可以通過查詢USER_SEQUENCES 表來查看下一個可用的序列值，而不會使序列的當前值增加.SQL> SELECT * FROM USER_SEQUENCES;SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER------------------------------ ---------- ---------- ------------ - - ---------- -----------DEPT_DEPTNO 1 100 1 N N 0 95SQL>=========SELECT * FROM ALL_SEQUENCES;SELECT * FROM DBA_SEQUENCES;

3、修改序列
可以更改序列的增量值、最大值、最小值、迴圈或者緩衝選項。不能修改序列的初始值，否則會報錯：ORA-02283: 無法變更啟動序號
ALTER SEQUENCE DEPT_DEPTNO INCREMENT BY 2;
ALTER SEQUENCE DEPT_DEPTNO MAXVALUE 200;

4、刪除序列
DROP SEQUENCE SEQUENCE_NAME;
三、索引
1、建立索引
自動建立:當在建立表時，如果指定了 PRIMARY KEY或者 UNIQUE約束，那麼將自動建立索引.
手動建立:使用者可以在某個列上建立非唯一的索引，以加快基於該行的查詢.
CREATE INDEX index_name
ON table (column[, column]...);
--建立索引，以提高對錶EMP的ENAME列的訪問速度.
CREATE INDEX EMP2_ENAME_IDX ON EMP2(ENAME);
--什麼時候建立索引
欲建立索引的列在 WHERE子句或者串連條件中頻繁使用.
該列所包含的不同值很多.
該列包含大量的空值.
表中的資料行數非常大，而且只有 2–4% 資料行被查詢出來.
--什麼時候沒必要建立索引
表是空的.
列在查詢條件中不經常使用.
大多數基於該表的查詢，所查詢出的資料量遠多於2–4% 行.
表被頻繁修改.

2、查看索引
USER_INDEXES 資料字典視圖包含使用者建立的索引的名字和它唯一性.
USER_IND_COLUMNS 視圖包含索引的名字、表名、列名.

SELECTic.index_name, ic.column_name,
ic.column_position col_pos,ix.uniqueness
FROMuser_indexes ix, user_ind_columns ic
WHEREic.index_name = ix.index_name
ANDic.table_name = ‘EMP2‘;

3、基於函數的索引
基於函數的索引也就是基於運算式的索引.
索引運算式由表的列、常量、 SQL函數或者使用者自訂函數組成.
SQL> CREATE TABLE test (col1 NUMBER);
SQL> CREATE INDEX test_index on test(col1,col1+10);
SQL> SELECT col1+10 FROM test;

4、刪除索引
要刪除一個索引，必須是索引的擁有者，或者具有 DROP ANY INDEX的許可權.
從資料字典中刪除 EMP_ENAME_IDX 索引.
SQL> DROP INDEX EMP2_ENAME_IDX;
索引已刪除。

四、同義字
通過建立一個同義字 (對象的另一個名字)來簡化對資料庫中對象的存取. 縮短了對象的名字長度.
CREATE [PUBLIC] SYNONYM synonym
FOR object;
例：為視圖QUERY_TABSPACE建立一個簡短的名字Q_SPACE;

建立視圖：CREATE VIEW QUERY_TABSPACE AS (SELECT /*+NO_MERGE(A) NO_MERGE(B)*/B.TABLESPACE_NAME 資料表空間名稱, ROUND((B.BYTES/1024)/1024,2) 總空間大小MB,NVL2(A.BYTES,ROUND((B.BYTES-NVL(A.BYTES,0))/1024/1024,2),B.BYTES) 已使用大小MB,NVL2(A.BYTES,ROUND(NVL(A.BYTES,0)/1024/1024,2),0) 未使用大小MB,NVL2(A.BYTES,TO_CHAR(ROUND(((B.BYTES-NVL(A.BYTES,0))/B.BYTES)*100,2),‘990.0‘),‘100‘)||‘%‘ 已使用率FROM (SELECT TABLESPACE_NAME,SUM(BYTES) BYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME)A,(SELECT TABLESPACE_NAME,SUM(BYTES) BYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) BWHERE B.TABLESPACE_NAME=A.TABLESPACE_NAME(+))查詢檢視：SELECT * FROM QUERY_TABSPACE;建立同義字：CREATE SYNONYM Q_SPACE FOR QUERY_TABSPACE;查詢同義字：SELECT * FROM Q_SPACE;刪除同義字DROP SYNONYM Q_SPACE;

 

視圖、序列、索引、同義字