ORACLE Advanced SQL
這篇文檔對深入研究Oracle有很大的用處。下面分別從以下幾個方面介紹。
Transaction Management,joins,Subquerys,Optimizer,Indexs, Enhancement to other SQL operations
如果您身邊就有Oracle系統，那么您也可以通過運行本篇文章的例子來做一些試驗。
l 事務處理（Transaction Management）：
事務的四大特性原子性、隔離性、獨立性，持續性。僅僅通過字面理解理解事務處理可能不是太直觀。那么，我們可以借用Jim Gray的描述來理解——“ 事務概念是分布式計算的一個重要抽象。在某一個層次實現了事務，那么所有跟高層次上會有一個簡化的失敗語義（要么全做，要么全不做），并且錯誤處理也會簡單得多。 ”
在Oracle中通過設置ISOLATION LEVEL來避免幾乎所有數據庫都可能遇到的問題——臟讀（一個事務讀取了另一個事務寫的但是尚未提交的數據）、不一致分析（一個事務前后讀取了不同的數據）和幻象數據讀取（例如，我要執行一個事務查詢，該事務查詢的任務是查詢13:00這個時間點的數據，但是這個事務執行時間可能需要一個小時，但是在這期間由于還有正常的數據操作。所以，當我在執行完事務查詢時可能得到的數據就是14: 00的數據了，我們稱這種現象為幻象讀取）。
我們知道標準的SQL99提出的事務隔離級別有四種，分別是READ UNCOMMITED、 READ COMMITED、REPEATABLE READ、SERIALIZABLE。ORACLE分別實現了READ COMMITED和 SERIALIZABLE。從這一點當中我們可以看出，ORACLE默認是避免臟讀的（當然這也不一定如果在執行完DML語句完以后如果沒有做 COMMIT操作可能也會出現臟讀的情況。但是，我還沒有遇到這種情況，讀者可以自己試驗。）例句 SET TRANSACTION ISOLATION LEVEL [READ COMMITED| SERIALIZABLE]。 READ COMMITED可以避免臟讀，這也是ORACLE的默認選項。但是，它不可避免我們提出的不一致分析和幻象讀取的問題。那么我們可以將其隔離級別設置為SERIALIZABLE這樣我們就可以避免不一致分析和幻象讀取的問題。那么，既然SERIALIZABLE級別這么好，那么我們是不是任何事務都要設置呢？答案是否定的，我們知道世界萬物都是有其有利的一面就一定有其不利的一面。那么它的不利一面我們可以通過分析SERIALIZABLE的實現機制來看看。
從圖中可以看出我們要實現SERIALIZABLE需要在系統中建立一個UNDO表空間來存放已經過時的數據。我們可以根據我們最大執行事務的執行時間來估算一個UNDO段的大小。顯然，我們要是實現SERIALIZABLE就必須要另外的空間來建立UNDO表空間。也就是說我們要實現SERIALIZABLE是通過空間來換取避免不一致分析和幻象讀取的。
l Joins操作
在敘述下面的內容之前先介紹一下我的機器環境：
硬件環境：P4 2.8G，MEMORY 526M,80G硬盤。
軟件環境：Windows 2000,ORACLE 9.2.0.1
首先您要建立一個有足夠量數據的表，我建立一個有100萬行以上的數據的表
建表sql語句如下：
DROP SEQUENCE SEQ_EMP
/
CREATE SEQUENCE SEQ_EMP START WITH 1000000
/
DROP TABLE EMPLOYEE
/
CREATE TABLE EMPLOYEE
(EMPNO NUMBER(8),
ENAME VARCHAR2(10),
SAL NUMBER(5),
COMM NUMBER(5),
DEGREE NUMBER(1),
HIREDATE DATE,
DEPTNO NUMBER(2),
ID_NO VARCHAR2(18),
BIRTHDAY DATE,
CONSTRAINT PK_EMPLOYEE PRIMARY KEY(EMPNO)
)
/
CREATE OR REPLACE TRIGGER TRI_INS_EMPLOYEE
BEFORE INSERT ON EMPLOYEE
FOR EACH ROW
DECLARE
V_EN EMPLOYEE.EMPNO%TYPE;
BEGIN
V_EN:=:NEW.EMPNO;
:NEW.COMM:=CEIL(:NEW.EMPNO/500000)*1000;
:NEW.SAL:=(4-MOD(:NEW.EMPNO,4))*1000;
:NEW.DEGREE:=CASE WHEN MOD(:NEW.EMPNO,4)=0 THEN NULL
WHEN MOD(:NEW.EMPNO,10)<7 THEN 1
WHEN MOD(:NEW.EMPNO,10)<9 THEN 2
ELSE 3
END;
:NEW.DEPTNO:=CASE WHEN :NEW.EMPNO<1000000 THEN 10
WHEN :NEW.EMPNO<1500000 THEN 20
WHEN :NEW.EMPNO<2000000 THEN 30
ELSE 40
END;
:NEW.ID_NO:=REPLACE('21010119'||(7+MOD(V_EN,3))||MOD(V_EN,10)||TO_CHAR(1+MOD(V_EN,12),'00')||TO_CHAR(MOD(V_EN,20)+1,'00')||0||TO_CHAR(MOD(V_EN,100),'00')||MOD(V_EN,2),' ',NULL);
:NEW.ID_NO:=REPLACE(:NEW.ID_NO,' ',NULL);
:NEW.BIRTHDAY:=TO_DATE(SUBSTR(:NEW.ID_NO,7,8),'YYYYMMDD');
END;
/
INSERT INTO EMPLOYEE (EMPNO)
VALUES(SEQ_EMP.NEXTVAL)
/
/
INSERT INTO EMPLOYEE (EMPNO) (SELECT SEQ_EMP.NEXTVAL FROM EMPLOYEE)
/
在做好以上準備以后我們就可以進行我們以后的實驗了。
在ORACLE中包括很多中連接方式EQUIJOINS、 SELF JOINS、 CARTESIAN PRODUCTS、 INNER JOINS、 OUTER JOINS、 ANTIJOINS， SEMIJOINS。
由于我的個人水平有限我不能全部介紹，我只能將自己理解的內容介紹個大家。
下面就僅僅介紹有關反連接的問題。
反連接(ANTIJOINS)通過 SET AUTOTRACE TRACE打開跟蹤執行策略我們分別比較以下的語句。
============================================================
SELECT ENAME FROM EMP WHERE DEPTNO NOT IN(
SELECT DEPTNO FROM DEPT WHERE LOC='NEW YORK');
SELECT ENAME FROM EMP WHERE NOT EXISTS(
SELECT 1 FROM DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO AND LOC='NEW YORK');
我們可以發現，運用NOT IN 要比 NOT EXISTS要快一些但是有個比較大的問題，就是在上面的實驗中我們假設EMP表和DEPT表中沒有字段內容為NULL的數據。如果我們要查詢的表中有為NULL的數據的時候，那么我們在運用NOT IN操作會發現查出的數據是不準確的情況。結論：在您為選擇 NOT IN 和 NOT EXISTS時而苦惱時請優先選擇運用NOT EXISTS語句。
接下來是有關JOIN的一些實驗。
下面我們做如下試驗
比較JOIN與IN & 子查詢以及EXISTS & 子查詢 它們三者之間的執行效率
--12.04秒
SELECT * FROM EMPLOYEE A JOIN DEPT B ON A.DEPTNO=B.DEPTNO WHERE B.LOC='NEW YORK';
--4.02秒
SELECT * FROM EMPLOYEE WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC='NEW YORK');
--4.02秒
SELECT * FROM EMPLOYEE WHERE EXISTS (SELECT 1 FROM DEPT WHERE EMPLOYEE.DEPTNO=
DEPT.DEPTNO AND LOC='NEW YORK');
通過上面的實驗我們可以得出運用 IN或者 EXISTS操作的效率要高于運用JOIN操作的語句。我們知道ORACLE在執行用戶的查詢請求是 ORACLE會選擇一些查詢策略來完成操作，但是有時候ORACLE的選擇是很愚蠢的。比如，我要做一個簡單的連接查詢的操作 SELECT * FROM EMPLOYEE A JOIN EMP B ON A.EMPNO=B.EMPNO;通過跟蹤執行策略我們會發現 ORACLE可能選擇一個很慢的策略。那么，遇到這種情況該如何處理呢？我們可以通過兩種方式來處理。第一，我們可以為該查詢添加注釋讓它執行我們要求的策略。比如我們可以這么寫SELECT /*+ use_rule…(EMPLOYEE EMP) */* FROM EMPLOYEE A JOIN EMP B ON A.EMPNO=B.EMPNO;(use_rule您可以選擇您需要的策略。例如HASH JOIN等其他的執行策略)。例如，我們為上面這個查詢語句選擇下面兩種策略：
1. SELECT /*+USE_HASH(EMPLOYEE,DEPT)*/* FROM EMPLOYEE,DEPT WHERE EMPLOYEE.DEPTNO=DEPT.DEPTNO;
2. SELECT /*+USE_MERGE(EMP,DEPT)*/* FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;
第二，就是，為該表添加統計資料分析信息ANALYZE TABLE EMPLOYEE COMPUTE STATISTICS;這樣我們在對該表查詢時ORACLE就可以選擇一個較優的執行策略了。
通過以上的實驗我們可以得出以下結論：ORACLE系統在執行DML語句的時候可能不會選擇其最優的執行策略，這需要我們通過為DML語句添加注釋強迫 ORACLE選擇一個我們人為指定的執行策略或者通過為表、試圖等添加統計資料分析信息來使ORACLE在執行DML時選擇較優的執行策略。
l 子查詢(Subquery)
這部分內容我們將會做一系列的實驗，最后我們從實驗的結果當中得出使用子查詢的情況。我并不是要介紹如何使用子查詢，而是通過使用子查詢來分析ORACLE的執行策略等信息。
實驗1：比較JOIN、IN以及EXISTS三種執行方式的執行時間
--12.04秒
SELECT * FROM EMPLOYEE A JOIN DEPT B ON A.DEPTNO=B.DEPTNO WHERE B.LOC='NEW YORK';
--4.02秒
SELECT * FROM EMPLOYEE WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC='NEW YORK');
--4.02秒
SELECT * FROM EMPLOYEE WHERE EXISTS (SELECT 1 FROM DEPT WHERE EMPLOYEE.DEPTNO=
DEPT.DEPTNO AND LOC='NEW YORK');
通過實驗1我們得出結論：使用EXISTS、IN語句要比JOIN語句執行效率高。
實驗2：表較幾個語句的查詢效率
--27.07秒
SELECT * FROM EMPLOYEE
WHERE SAL>(SELECT AVG(SAL) FROM EMPLOYEE);
--27.03秒
SELECT * FROM EMPLOYEE A
WHERE SAL>
(SELECT AVG(SAL) FROM EMPLOYEE B WHERE A.DEPTNO=B.DEPTNO);
--39秒
SELECT * FROM EMPLOYEE A,
(SELECT DEPTNO,AVG(SAL) AS SAL1
FROM EMPLOYEE GROUP BY DEPTNO) B
WHERE A.DEPTNO=B.DEPTNO AND
A.SAL>B.SAL1 WHERE SAL>(SELECT AVG(SAL)
FROM EMPLOYEE B WHERE A.DEPTNO=B.DEPTNO);
--22.05秒
SELECT YY.EMPNO,YY.ENAME,YY.DEPTNO,YY.SAL,
YY.SAL-XX.SAL1 AS NEWSAL FROM EMPLOYEE YY,
(SELECT DEPTNO,AVG(SAL) AS SAL1 FROM EMPLOYEE
GROUP BY DEPTNO) XX
WHERE YY.DEPTNO=XX.DEPTNO AND YY.SAL>XX.SAL1;
--26.06秒
SELECT YY.EMPNO,YY.ENAME,YY.DEPTNO,YY.SAL,
YY.SAL-XX.SAL1 AS NEWSAL,XX.SAL1,XX.SAL2
FROM EMPLOYEE YY,
(SELECT DEPTNO,AVG(SAL) AS SAL1,MAX(SAL) AS SAL2 FROM EMPLOYEE GROUP BY DEPTNO) XX
WHERE YY.DEPTNO=XX.DEPTNO AND YY.SAL>XX.SAL1;
通過實驗2我們可以得出結論：
1. 在執行子查詢盡可能將語句向連接的方式上靠。
2. 在子查詢中可以將相關子查詢轉為連接的形式。
3. 在做子查詢時select * 要比select 列名 的語句慢得多。
另外，在ORACLE中我們也可以運用WITH語句代替子查詢以提高語句的可讀性。下面是運用with語句的例子。
實驗3：運用WITH語句
WITH
SUB_TABLE AS (
SELECT DEPTNO,AVG(SAL) AS SAL1,MAX(SAL) AS SAL2 FROM EMPLOYEE GROUP BY DEPTNO)
SELECT A.EMPNO,A.ENAME,E.DEPTNO,A.SAL,A.SAL-SUB_TABLE.SAL11,
SUB_TABLE.SAL1,SUB_TABLE.SAL2
FROM EMPLOYEE A,SUB_TABLE WHERE
A.DEPTNO=SUB_TABLE.DEPTNO AND A.SAL>SUB_TABLE.SAL1;
WITH
SUB_TABLE AS (
SELECT DEPTNO,AVG(SAL) AS SAL1,MAX(SAL) AS SAL2
FROM EMPLOYEE GROUP BY DEPTNO)
SELECT *
FROM EMPLOYEE,SUB_TABLE WHERE
EMPLOYEE.DEPTNO=SUB_TABLE.DEPTNO AND EMPLOYEE.SAL>SUB_TABLE.SAL1;
l 索引(Index)
我們知道索引的使用可以提高查詢速度，這是我以前對索引的理解。但是使用索引也有副作用。我們知道在ORACEL中查詢一條記錄時如果沒有索引的情況下，它的執行方式如下所示
如果我要做查詢 select C2 from 表 where C2=6 那么在ORACLE中系統會從最后一條掃描一直將表整個掃描一遍以后，這個查詢動作才算完成。從中我們可以看出沒有索引的表的記錄是無序存放的。相反如果我們對這個表在列C2建立一個索引以后它的查詢執行如下所示：
我們可以看到在查詢是系統如果選用該索引的話那么ORACLE將會查找一些有序的數據，那么我們的查詢速度將會大大地提高。
上面我們描述的是查詢一列數據時的情況，那么如果查詢所有數據呢，請看下圖所示
如果我們帶上索引查詢，ORACLE首先會找到索引然后找到在基表中記錄的位置。顯然這樣比直接在表中查詢要慢。但是這個結論對不對呢？下面將會做一些實驗說明這個問題。
在進行以下的實驗值前首先，您需要作如下準備：
在EMPLOYEE表的SAL列上建立一個索引
CREATE INDEX IND_EMPLOYEE_SAL ON EMPLOYEE(SAL);
在EMPLOYEE表的SAL,DEGREE兩個列上建立一個聯合索引
CREATE INDEX IND_EMPLOYEE_SALandDEGREE
ON EMPLOYEE(SAL,DEGREE);
實驗1：使用索引與不使用索引
SELECT /*+INDEX (EMPLOYEE IND_EMPLOYEE_SAL)*/
* FROM
EMPLOYEE WHERE SAL=1000;
--13.03秒
SELECT /*+NO_INDEX(EMPLOYEE)*/
* FROM
EMPLOYEE WHERE SAL=1000;
利用索引查詢的時間的數據由于我的粗心大意弄丟了，但是我記得運用索引的查詢要比沒有運用索引的查詢要慢一些。
實驗2：單列索引與多列索引之間的區別
//////////////////////////////////////////////////
////單列索引的情況
--13.04秒 USE INDEX IND_EMPLOYEE_SAL
SELECT * FROM EMPLOYEE WHERE SAL=1000;
--13.04秒
SELECT /*+INDEX (EMPLOYEE IND_EMPLOYEE_SAL)*/
* FROM
EMPLOYEE WHERE SAL=1000;
--19秒
SELECT /*+INDEX (EMPLOYEE IND_EMPLOYEE_SALandDEGREE)*/
* FROM
EMPLOYEE WHERE SAL=1000;
//////////////////////////////////////////////////
////單列索引的情況
--22秒 USE TABLE ACCESS
SELECT * FROM EMPLOYEE WHERE DEGREE=1;
--22秒 USE TABLE ACCESS
SELECT /*+INDEX (EMPLOYEE IND_EMPLOYEE_SAL)*/
* FROM
EMPLOYEE WHERE DEGREE=1;
--29秒
SELECT /*+INDEX (EMPLOYEE IND_EMPLOYEE_SALandDEGREE)*/
* FROM
EMPLOYEE WHERE DEGREE=1;
//////////////////////////////////////////////////
////多列索引的情況
--8秒 USE INDEX IND_EMPLOYEE_SALandDEGREE
SELECT * FROM EMPLOYEE WHERE SAL=1000 AND DEGREE=1;
--9秒 USE INDEX IND_EMPLOYEE_SAL
SELECT /*+INDEX (EMPLOYEE IND_EMPLOYEE_SAL)*/
* FROM
EMPLOYEE WHERE SAL=1000 AND DEGREE=1;
--8秒 USE INDEX IND_EMPLOYEE_SALandDEGREE
SELECT /*+INDEX (EMPLOYEE IND_EMPLOYEE_SALandDEGREE)*/
* FROM
EMPLOYEE WHERE SAL=1000 AND DEGREE=1;
//////////////////////////////////////////////////
////多列索引的情況
--8秒 USE INDEX IND_EMPLOYEE_SALandDEGREE
SELECT * FROM EMPLOYEE WHERE DEGREE=1 AND SAL=1000;
--9秒 USE INDEX IND_EMPLOYEE_SAL
SELECT /*+INDEX (EMPLOYEE IND_EMPLOYEE_SAL)*/
* FROM
EMPLOYEE WHERE DEGREE=1 AND SAL=1000;
--8秒 USE INDEX IND_EMPLOYEE_SALandDEGREE
SELECT /*+INDEX (EMPLOYEE IND_EMPLOYEE_SALandDEGREE)*/
* FROM
EMPLOYEE WHERE DEGREE=1 AND SAL=1000;
//////////////////////////////////////////////////
////多列索引的情況
--3.4秒 USE INDEX IND_EMPLOYEE_SAL
SELECT * FROM EMPLOYEE WHERE SAL=1000 AND COMM=2000;
--3.4秒 USE INDEX IND_EMPLOYEE_SAL
SELECT /*+INDEX (EMPLOYEE IND_EMPLOYEE_SAL)*/
* FROM
EMPLOYEE WHERE SAL=1000 AND COMM=2000;
--8秒 USE INDEX IND_EMPLOYEE_SALandDEGREE
SELECT /*+INDEX (EMPLOYEE IND_EMPLOYEE_SALandDEGREE)*/
* FROM
EMPLOYEE WHERE SAL=1000 AND COMM=2000;
實驗3：CLUSTER
CREATE CLUSTER DD_DDMX (ID NUMBER(5))
TABLESPACE users ;
CREATE TABLE DD
(ID NUMBER(5) PRIMARY KEY,
DDTIME DATE)
CLUSTER DD_DDMX(ID);
CREATE TABLE DDMX
(ID NUMBER(5),
PROID VARCHAR2(20),
PRO VARCHAR2(30))
CLUSTER DD_DDMX(ID);
CREATE INDEX DD_DDMX_index
ON CLUSTER DD_DDMX
TABLESPACE indx;
實驗4：BITMAP INDEX
CREATE BITMAP INDEX INDBIT_EMPLOYEE_SAL ON EMPLOYEE(SAL)
TABLESPACE INDX;
--13.04秒
SELECT /*+INDEX (EMPLOYEE IND_EMPLOYEE_SAL)*/
* FROM
EMPLOYEE WHERE SAL=1000;
--12.07秒 USE INDBIT_EMPLOYEE_SAL
SELECT /*+INDEX (EMPLOYEE INDBIT_EMPLOYEE_SAL)*/
* FROM
EMPLOYEE WHERE SAL=1000;
實驗5：分區索引
分區索引的原理如下所示：
我們知道在磁道在磁盤上尋址的I/O操作的開銷是相當大的，如果我們建立了分區索引將其索引放在不同的磁盤上那么可以大大節省I/0開銷提高我們的查詢速度。
ALTER TABLE EMP
ADD CONSTRAINT PK_EMP
PRIMARY KEY(EMPNO) USEING INDEX TABLESPACE INDX;
ALTER TABLE EMPLOYEE
DROP CONSTRAINT PK_EMPLOYEE;
ALTER TABLE EMPLOYEE
ADD CONSTRAINT PK_EMPLOYEE
PRIMARY KEY(EMPNO) USING INDEX TABLESPACE INDX;
CREATE INDEX IND_EMPLOYEE_BIRTHDAY
ON EMPLOYEE(BIRTHDAY)
GLOBAL PARTITION BY RANGE(BIRTHDAY)
(PARTITION P1 VALUES LESS THAN (DATE '1980-01-01') TABLESPACE USERS,
PARTITION P2 VALUES LESS THAN (DATE '1990-01-01') TABLESPACE INDX,
PARTITION P3 VALUES LESS THAN (DATE '2000-01-01') TABLESPACE USERS,
PARTITION P4 VALUES LESS THAN(MAXVALUE) TABLESPACE INDX);
CREATE TABLE wage
( empno NUMBER,
year_month INT NOT NULL,
opdate DATE)
PARTITION BY RANGE (year_month)
( PARTITION wage_q1 VALUES LESS THAN (199701)
TABLESPACE users,
PARTITION wage_q2 VALUES LESS THAN (199702)
TABLESPACE users,
PARTITION wage_q3 VALUES LESS THAN (199703)
TABLESPACE users,
PARTITION sales_q4 VALUES LESS THAN (199704)
TABLESPACE users);
-- Local Partitioned Index
CREATE INDEX IND_WAGE(year_month)
LOCAL
(PARTITION P1,
PARTITION P2,
PARTITION P3,
PARTITION P4);
以上是我們關于索引的一些實驗信息，通過該實驗我們可以得出以下結論：
1. 索引是為了提高查詢速度,排序數據。
2. 索引不見得總能提高速度。
3. 查詢結果占總記錄數越少越可以用索引。
4. 分區索引 將數據分散到多個物理位置上來提高其IO的功能。
5. 全表掃描有時性能也不錯。
6. 在統計資料不完整時未必選擇正確的索引。
7. 有時候添加索引可能會降低速度。
8. 索引適合于結果行占所有行的比很小的時候運用 經驗值為比為5%左右為好，但是還可能還有許多其他情況影響查詢速度。
9. 值是唯一的時候運用索引可以提高查詢速度。
10. 當統計資料不完整的時候,查詢速度會很慢。可以通過對索引進行統計分析來調整。
11. where條件中列的順序不影響其查詢速度。
12. 多列索引中的后面的列最好不要運用索引。
13. 多列索引中其列的順序是不同的。不同的順序可能造成查詢速度不一樣。
14. 多列索引中第一列是有順序的,但是其后面的列是沒有順序。
15. where條件查詢的列中只要有索引,那么oracle會選擇運用索引。
16. 當查詢的結果大于所有行的記錄數的10%時,那么最好不要運用索引。
17. 小表用索引反而會降低速度。
18. 索引需要DBA經常刪除重新建立,因為索引結構變化過多可能造成以后的查詢變慢。
19. DML語句會影響索引結構大的變化,所以經常作DML語句的表可以考慮不用索引。
20. CLUSTER索引僅僅用于只讀的的表多用于cluster表。
21. 維圖索引多用于行多但是值的類型很少的情況。
22. 表和索引要放在兩個不同的表空間上。
l Enhancement to other SQL operations
下面的內容是介紹一些有關ORACLE技術的一些比較雜的內容。
一、 層次查詢
在同一個表中的不同記錄有著直接或者間接的關系。
例如，我們查詢EMP表的信息如下：
從圖中我們可以看到ENAME是’SMITH’的領導是編號為7902的人。而7902的領導是編號為7566的人。7566的領導是編號為7839的人。那么這樣的關系我們就可以通過運用層次查詢就可以查詢出來。
層次查詢實驗
SELECT * FROM EMP
CONNECT BY PRIOR MGR=EMPNO
START WITH ENAME='SMITH';
SELECT * FROM EMP
CONNECT BY PRIOR EMPNO=MGR
START WITH ENAME='SMITH';
SELECT LEVEL,ENAME FROM EMP
CONNECT BY PRIOR EMPNO=MGR
START WITH ENAME='SMITH';
SELECT MID,PARENTID,AMOUNT,SYS_CONNECT_BY_PATH(MID,'/') PATH
FROM MATERIAL
WHERE STOCK=1
CONNECT BY PRIOR MID=PARENTID
START WITH MID='1001';
二、 分組查詢
就是我們平時運用的group語句.
分組查詢實驗
SELECT DEPTNO,JOB,COUNT(*)
FROM EMP
GROUP BY ROLLUP(DEPTNO,JOB);
SELECT DEPTNO,JOB,COUNT(*)
FROM EMP
GROUP BY cube(DEPTNO,JOB);
SELECT DEPTNO,JOB,TO_CHAR(HIREDATE,'YYYY'),COUNT(*)
FROM EMP
GROUP BY GROUPING SETS((DEPTNO,JOB),
(DEPTNO,TO_CHAR(HIREDATE,'YYYY')),());
三、 并行執行的SQL語句
并行執行的SQL語句實驗
--19.09秒
ALTER TABLE EMPLOYEE PARALLEL (DEGREE 1);
SELECT * FROM EMPLOYEE WHERE SAL=1000;
--18秒
ALTER TABLE EMPLOYEE PARALLEL (DEGREE 2);
SELECT * FROM EMPLOYEE WHERE SAL=1000;
--19秒
ALTER TABLE EMPLOYEE PARALLEL (DEGREE 4);
SELECT * FROM EMPLOYEE WHERE SAL=1000;
--20.3秒
ALTER TABLE EMPLOYEE PARALLEL (DEGREE 6);
SELECT * FROM EMPLOYEE WHERE SAL=1000;
--19秒
ALTER TABLE EMPLOYEE PARALLEL (DEGREE 10);
SELECT * FROM EMPLOYEE WHERE SAL=1000;
四、 實體化視圖
在進行以下的實驗之前，我們需要作如下準備：
建立一個普通視圖：
CREATE VIEW V_EMPLOYEE
AS SELECT SUM(SAL) AS C FROM EMPLOYEE;
建立實體化視圖：
CREATE MATERIALIZED VIEW 名字 AS 查詢；
CREATE MATERIALIZED VIEW V_M_EMPLOYEE
AS SELECT SUM(COMM) AS C FROM EMPLOYEE;
實體化視圖與普通視圖的比較
--20.04秒
SELECT * FROM V_EMP;
--0.01秒
SELECT * FROM V_M_EMPLOYEE;
通過以上的實驗，我們可以得出結論：實體化視圖不包含新的數據，查詢速度很快。
如果需要實體化視圖包含新的數據我們可以通過
手工刷新：
EXEC DBMS_MVIEW.REFRESH('V_M_EMPLOYEE','CF'); CF -- 完全快速刷新
自動刷新：
//每個表可以創建一個實體化視圖日志。
CREATE MATERIALIZED VIEW LOG ON 表名
WITH(列名列表)，
ROWID INCLUDING NEW VALUES;
//創建自動刷新的實體化視圖
CREATE MATERIALIZED VIEW 名字
BUILD IMMEDIATE
〔REFRESH FAST||REFRESH COMPLETE〕 ON COMMIT
--REFRESH FAST 僅僅支持Insert語句的刷新
AS ...
運用REFRESH COMPLETE 適合于update,delete操作較少的表。并且試驗發現運用
REFRESH COMPLETE時COMMIT操作會很慢！
五、 查詢重寫技術(QUERY REWRITE)
表面上看是在查詢表，但是oracle實際上是去查詢實體化視圖的技術。
在下面的試驗中我們也可以看出實際上無論是查詢表還是視圖oracle都會轉向查詢實體化視圖 MV_EMP。
對于反復執行的匯總查詢存放起來、節省查詢時間，多用于匯總的計算。
//創建查詢重寫技術
CREATE MATERIALIZED VIEW 名字
BUILD IMMEDIATE
〔REFRESH FAST||REFRESH COMPLETE〕 ON COMMIT
--REFRESH FAST 僅僅支持Insert語句的刷新
ENABLE QUERY REWRITE
AS ...
運用查詢重寫技術的例子
CREATE MATERIALIZED VIEW MV_EMP
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS SELECT SUM(COMM) AS C FROM EMPLOYEE；
ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;
--0.00秒
-- EXPLAN: TABLE ACCESS (FULL) OF 'MV_EMP'
SELECT SUM(COMM) FROM EMPLOYEE;
--0.00秒
--EXPLAN: TABLE ACCESS (FULL) OF 'MV_EMP'
SELECT * FROM V_MEP;
經過手工刷新以后查詢速度會變慢！
六、 分布式技術
分布式技術包括分布式數據庫、DB LINK、分布式查詢以及分布式事務管理。這里我們僅僅介紹有關DB LINK的內容。
DB LINK的實驗
GRANT CREATE DATABALSE LINK TO SCOTT;
CONN SCOTT/TIGER
CREATE DATABASLE LINK OEM_LINK
CONNECT TO SCOTT IDENTIFIED BY TIGER
USING 'AAA';
SELECT ENAME FROM EMP@OEM_LINK
通過以上實驗我們得出結論：如果在執行分布式事務的時候那么網絡一定要保持正常，如果網絡斷開就會發生死鎖的情況。這時要進行在服務端和客戶端分別將鎖刪除即可解決。
七、 DML語句
INSERT INTO ALL
INTO 表1 VALUES(列名列表)
...
INTO 表2 VALUES(列名列表)
子查詢；
DML語句的實驗
CREATE TABLE TEST_DWRS
(DEPTNO NUMBER(2),
RENSHU NUMBER(10)
);
CREATE TABLE TEST_DWGZ
(DEPTNO NUMBER(2),
AVGSAL NUMBER(7,2)
);
INSERT ALL
INTO TEST_DWRS VALUES (DEPTNO,RENSHU)
INTO TEST_DWGZ VALUES (DEPTNO,AVGSAL)
SELECT DEPTNO DEPT,COUNT(*) REN_SHU,AVG(SAL) AVG_SAL
FROM EMP GROUP BY DEPTNO;
INSERT ALL
WHEN 條件1 THEN
INTO 表名1 VALUES(列名列表)
...
WHEN 條件2 THEN
INTO 表名2 VALUES(列名列表)
子查詢;
INSERT ALL
WHEN REN_SHU>1000 THEN
INTO TEST_DWRS VALUES(DEPTNO,RENSHU)
WHEN AVG_SAL>2000 THEN
INTO TEST_DWGZ VALUES(DEPTNO,AVGSAL)
SELECT DEPTNO,COUNT(*) REN_SHU,AVG(SAL) AVG_SAL
FROM EMP GROUP BY DEPTNO;
八、 外部表(EXTERNAL TABLES)
外部表是我們應用中可能會常常碰到的問題之一。例如，我需要將文本文件的數據導入到ORACLE數據庫中我們就可以利用此項技術。
假設外部文件有如下信息：
我們可以將此信息轉為以逗號分割的一些信息存為test.txt文本文件。
然后我們在ORACLE中建立一個訂單表BILL(BILL_ID 訂單編號，BILL_D訂單日期)
以及訂單明細表BILL_MX(BILL_ID 訂單編號，P 產品編號，PAMOUNT 產品數量)。
我們下面的實驗就是通過將外部的文件信息導入到BILL表和BILL_MX表中去。
首先簡單介紹一下創建外部表的方法：
第一步，創建一個DIRECTORY對象
CREATE DIRECTORY 名字 AS '路徑'
CREATE DIRECTORY test_dir AS 'D:\1';
第二步，創建外部表
…
外部表的實驗
//創建外部表
CREATE TABLE BILL_EXTERNAL
(BILL_ID VARCHAR2(8),
BILL_D DATE,
P1 VARCHAR2(10),
P1_AMOUNT VARCHAR2(10),
P2 VARCHAR2(10),
P2_AMOUNT VARCHAR2(10),
P3 VARCHAR2(10),
P3_AMOUNT VARCHAR2(10))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY test_dir
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
(BILL_ID CHAR,
BILL_D CHAR DATE_FORMAT
DATE MASK "YYYYMMDD",
P1 CHAR,
P1_AMOUNT CHAR,
P2 CHAR,
P2_AMOUNT CHAR,
P3 CHAR,
P3_AMOUNT CHAR
LOCATION ('TEST.TXT')
);
//導入數據
INSERT ALL
WHEN BILL_ID<>0 THEN
INTO BILL VALUES(BILL_ID,BILL_D)
WHEN P1<>0 THEN
INTO BILL_MX VALUES(BILL_ID,P1,P1_AMOUNT)
WHEN P2<>0 THEN
INTO BILL_MX VALUES(BILL_ID,P2,P2_AMOUNT)
WHEN P3<>0 THEN
INTO BILL_MX VALUES(BILL_ID,P3,P3_AMOUNT)
SELECT * FROM BILL_EXTERNAL;
九、 日期類型
日期類型的實驗
SELECT SYSDATE + TO_YMINTERVAL('01-02') FROM DUAL;
SELECT SYSTIMESTAMP + TO_DSINTERVAL('01 01:02:01') FROM DUAL;
SELECT SYSTIMESTAMP + TO_YMINTERVAL('10-3') +
TO_DSINTERVAL('05 07:00:00') FROM DUAL;
十、 自定義數據類型
自定義數據類型我們將會簡單地介紹有關記錄和集合的內容。
記錄類型的實驗
ORACLE 對象可以理解為JAVA中的類的概念。
CREATE TYPE T_REC AS OBJECT(
A NUMBER,
B NUMBER
;
CREATE TABLE RTEST(A NUMBER,
B NUMBER,
C T_REC);
INSERT INTO RTEST VALUES(1,2,T_REC(10,20));
CREATE TYPE EMP_INFORMATION AS OBJECT(
ADDR VARCHAR2(50),
EMAIL VARCHAR2(50),
PHONE VARCHAR2(11));
CREATE TABLE EMP1(
EMPID VARCHAR2(10),
EMPNAME VARCHAR2(20),
INFO EMP_INFORMATION);
INSERT INTO EMP1(EMPID,EMPNAME,INFO)
SELECT '0001','SHI',
EMP_INFORMATION('DALIAN','SHIBU@163.COM','240560560')
FROM DUAL;
集合類型的實驗
CREATE TYPE 類型名 AS TABLE OF 元素類型名
CREATE TABLE...
(....
C1 （NESTED TABLE的類型名），
...
CREATE TYPE NTA AS TABLE OF NUMBER;
CREATE TABLE NTEST (A NUMBER,
B NUMBER,
C NTA)
NESTED TABLE C STORE AS NTT;
INSERT INTO NTEST VALUES(1,2,NTA(100,200,300));
實驗：創建一個工資表 職工編號，工資，補貼其中補貼是嵌套表，每個職工的都可以有多種補貼數量不定
CREATE TYPE BT_VALUE AS OBJECT(BT_NAME VARCHAR2(20),
BT_JE NUMBER(4));
CREATE TYPE BT_LIST AS TABLE OF BT_VALUE;
CREATE TABLE GZB(EMPNO NUMBER(4),SAL NUMBER(4), BT BT_LIST)
NESTED TABLE BT STORE AS BT_TAB;
INSERT INTO GZB VALUES(1001,1000,BT_LIST(BT_VALUE('JTFEE',500),
BT_VALUE('TELFEE',200)
UPDATE TABLE(SELECT BT FROM GZB WHERE EMPNO=1001) SET BT_JE=150 WHERE BT_NAME='JTFEE';
SELECT EMPNO,SAL,(SELECT SUM(BT_JE) FROM TABLE(BT)) BT_JE
FROM GZB;
至此，有關的ORACLE Advanced SQL的內容就介紹完了。通過，這篇文檔的總結我也體會到數據庫的學習與應用我們還有很長的路要走。
shibrush 發表于 >2005-10-21 19:52:51  [全文] [評論] [引用] [推薦] [檔案] [推給好友] [收藏到網摘]
|