ORACLE Advanced SQL
這篇文檔對(duì)深入研究Oracle有很大的用處。下面分別從以下幾個(gè)方面介紹。
Transaction Management,joins,Subquerys,Optimizer,Indexs, Enhancement to other SQL operations
如果您身邊就有Oracle系統(tǒng)，那么您也可以通過(guò)運(yùn)行本篇文章的例子來(lái)做一些試驗(yàn)。
l 事務(wù)處理（Transaction Management）：
事務(wù)的四大特性原子性、隔離性、獨(dú)立性，持續(xù)性。僅僅通過(guò)字面理解理解事務(wù)處理可能不是太直觀。那么，我們可以借用Jim Gray的描述來(lái)理解——“ 事務(wù)概念是分布式計(jì)算的一個(gè)重要抽象。在某一個(gè)層次實(shí)現(xiàn)了事務(wù)，那么所有跟高層次上會(huì)有一個(gè)簡(jiǎn)化的失敗語(yǔ)義（要么全做，要么全不做），并且錯(cuò)誤處理也會(huì)簡(jiǎn)單得多。 ”
在Oracle中通過(guò)設(shè)置ISOLATION LEVEL來(lái)避免幾乎所有數(shù)據(jù)庫(kù)都可能遇到的問(wèn)題——臟讀（一個(gè)事務(wù)讀取了另一個(gè)事務(wù)寫(xiě)的但是尚未提交的數(shù)據(jù)）、不一致分析（一個(gè)事務(wù)前后讀取了不同的數(shù)據(jù)）和幻象數(shù)據(jù)讀取（例如，我要執(zhí)行一個(gè)事務(wù)查詢，該事務(wù)查詢的任務(wù)是查詢13:00這個(gè)時(shí)間點(diǎn)的數(shù)據(jù)，但是這個(gè)事務(wù)執(zhí)行時(shí)間可能需要一個(gè)小時(shí)，但是在這期間由于還有正常的數(shù)據(jù)操作。所以，當(dāng)我在執(zhí)行完事務(wù)查詢時(shí)可能得到的數(shù)據(jù)就是14: 00的數(shù)據(jù)了，我們稱這種現(xiàn)象為幻象讀取）。
我們知道標(biāo)準(zhǔn)的SQL99提出的事務(wù)隔離級(jí)別有四種，分別是READ UNCOMMITED、 READ COMMITED、REPEATABLE READ、SERIALIZABLE。ORACLE分別實(shí)現(xiàn)了READ COMMITED和 SERIALIZABLE。從這一點(diǎn)當(dāng)中我們可以看出，ORACLE默認(rèn)是避免臟讀的（當(dāng)然這也不一定如果在執(zhí)行完DML語(yǔ)句完以后如果沒(méi)有做 COMMIT操作可能也會(huì)出現(xiàn)臟讀的情況。但是，我還沒(méi)有遇到這種情況，讀者可以自己試驗(yàn)。）例句 SET TRANSACTION ISOLATION LEVEL [READ COMMITED| SERIALIZABLE]。 READ COMMITED可以避免臟讀，這也是ORACLE的默認(rèn)選項(xiàng)。但是，它不可避免我們提出的不一致分析和幻象讀取的問(wèn)題。那么我們可以將其隔離級(jí)別設(shè)置為SERIALIZABLE這樣我們就可以避免不一致分析和幻象讀取的問(wèn)題。那么，既然SERIALIZABLE級(jí)別這么好，那么我們是不是任何事務(wù)都要設(shè)置呢？答案是否定的，我們知道世界萬(wàn)物都是有其有利的一面就一定有其不利的一面。那么它的不利一面我們可以通過(guò)分析SERIALIZABLE的實(shí)現(xiàn)機(jī)制來(lái)看看。
從圖中可以看出我們要實(shí)現(xiàn)SERIALIZABLE需要在系統(tǒng)中建立一個(gè)UNDO表空間來(lái)存放已經(jīng)過(guò)時(shí)的數(shù)據(jù)。我們可以根據(jù)我們最大執(zhí)行事務(wù)的執(zhí)行時(shí)間來(lái)估算一個(gè)UNDO段的大小。顯然，我們要是實(shí)現(xiàn)SERIALIZABLE就必須要另外的空間來(lái)建立UNDO表空間。也就是說(shuō)我們要實(shí)現(xiàn)SERIALIZABLE是通過(guò)空間來(lái)?yè)Q取避免不一致分析和幻象讀取的。
l Joins操作
在敘述下面的內(nèi)容之前先介紹一下我的機(jī)器環(huán)境：
硬件環(huán)境：P4 2.8G，MEMORY 526M,80G硬盤(pán)。
軟件環(huán)境：Windows 2000,ORACLE 9.2.0.1
首先您要建立一個(gè)有足夠量數(shù)據(jù)的表，我建立一個(gè)有100萬(wàn)行以上的數(shù)據(jù)的表
建表sql語(yǔ)句如下：
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)
/
在做好以上準(zhǔn)備以后我們就可以進(jìn)行我們以后的實(shí)驗(yàn)了。
在ORACLE中包括很多中連接方式EQUIJOINS、 SELF JOINS、 CARTESIAN PRODUCTS、 INNER JOINS、 OUTER JOINS、 ANTIJOINS， SEMIJOINS。
由于我的個(gè)人水平有限我不能全部介紹，我只能將自己理解的內(nèi)容介紹個(gè)大家。
下面就僅僅介紹有關(guān)反連接的問(wèn)題。
反連接(ANTIJOINS)通過(guò) SET AUTOTRACE TRACE打開(kāi)跟蹤執(zhí)行策略我們分別比較以下的語(yǔ)句。
============================================================
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');
我們可以發(fā)現(xiàn)，運(yùn)用NOT IN 要比 NOT EXISTS要快一些但是有個(gè)比較大的問(wèn)題，就是在上面的實(shí)驗(yàn)中我們假設(shè)EMP表和DEPT表中沒(méi)有字段內(nèi)容為NULL的數(shù)據(jù)。如果我們要查詢的表中有為NULL的數(shù)據(jù)的時(shí)候，那么我們?cè)谶\(yùn)用NOT IN操作會(huì)發(fā)現(xiàn)查出的數(shù)據(jù)是不準(zhǔn)確的情況。結(jié)論：在您為選擇 NOT IN 和 NOT EXISTS時(shí)而苦惱時(shí)請(qǐng)優(yōu)先選擇運(yùn)用NOT EXISTS語(yǔ)句。
接下來(lái)是有關(guān)JOIN的一些實(shí)驗(yàn)。
下面我們做如下試驗(yàn)
比較JOIN與IN & 子查詢以及EXISTS & 子查詢 它們?nèi)咧g的執(zhí)行效率
--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');
通過(guò)上面的實(shí)驗(yàn)我們可以得出運(yùn)用 IN或者 EXISTS操作的效率要高于運(yùn)用JOIN操作的語(yǔ)句。我們知道ORACLE在執(zhí)行用戶的查詢請(qǐng)求是 ORACLE會(huì)選擇一些查詢策略來(lái)完成操作，但是有時(shí)候ORACLE的選擇是很愚蠢的。比如，我要做一個(gè)簡(jiǎn)單的連接查詢的操作 SELECT * FROM EMPLOYEE A JOIN EMP B ON A.EMPNO=B.EMPNO;通過(guò)跟蹤執(zhí)行策略我們會(huì)發(fā)現(xiàn) ORACLE可能選擇一個(gè)很慢的策略。那么，遇到這種情況該如何處理呢？我們可以通過(guò)兩種方式來(lái)處理。第一，我們可以為該查詢添加注釋讓它執(zhí)行我們要求的策略。比如我們可以這么寫(xiě)SELECT /*+ use_rule…(EMPLOYEE EMP) */* FROM EMPLOYEE A JOIN EMP B ON A.EMPNO=B.EMPNO;(use_rule您可以選擇您需要的策略。例如HASH JOIN等其他的執(zhí)行策略)。例如，我們?yōu)樯厦孢@個(gè)查詢語(yǔ)句選擇下面兩種策略：
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;
第二，就是，為該表添加統(tǒng)計(jì)資料分析信息ANALYZE TABLE EMPLOYEE COMPUTE STATISTICS;這樣我們?cè)趯?duì)該表查詢時(shí)ORACLE就可以選擇一個(gè)較優(yōu)的執(zhí)行策略了。
通過(guò)以上的實(shí)驗(yàn)我們可以得出以下結(jié)論：ORACLE系統(tǒng)在執(zhí)行DML語(yǔ)句的時(shí)候可能不會(huì)選擇其最優(yōu)的執(zhí)行策略，這需要我們通過(guò)為DML語(yǔ)句添加注釋強(qiáng)迫 ORACLE選擇一個(gè)我們?nèi)藶橹付ǖ膱?zhí)行策略或者通過(guò)為表、試圖等添加統(tǒng)計(jì)資料分析信息來(lái)使ORACLE在執(zhí)行DML時(shí)選擇較優(yōu)的執(zhí)行策略。
l 子查詢(Subquery)
這部分內(nèi)容我們將會(huì)做一系列的實(shí)驗(yàn)，最后我們從實(shí)驗(yàn)的結(jié)果當(dāng)中得出使用子查詢的情況。我并不是要介紹如何使用子查詢，而是通過(guò)使用子查詢來(lái)分析ORACLE的執(zhí)行策略等信息。
實(shí)驗(yàn)1：比較JOIN、IN以及EXISTS三種執(zhí)行方式的執(zhí)行時(shí)間
--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');
通過(guò)實(shí)驗(yàn)1我們得出結(jié)論：使用EXISTS、IN語(yǔ)句要比JOIN語(yǔ)句執(zhí)行效率高。
實(shí)驗(yàn)2：表較幾個(gè)語(yǔ)句的查詢效率
--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;
通過(guò)實(shí)驗(yàn)2我們可以得出結(jié)論：
1. 在執(zhí)行子查詢盡可能將語(yǔ)句向連接的方式上靠。
2. 在子查詢中可以將相關(guān)子查詢轉(zhuǎn)為連接的形式。
3. 在做子查詢時(shí)select * 要比select 列名 的語(yǔ)句慢得多。
另外，在ORACLE中我們也可以運(yùn)用WITH語(yǔ)句代替子查詢以提高語(yǔ)句的可讀性。下面是運(yùn)用with語(yǔ)句的例子。
實(shí)驗(yàn)3：運(yùn)用WITH語(yǔ)句
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)
我們知道索引的使用可以提高查詢速度，這是我以前對(duì)索引的理解。但是使用索引也有副作用。我們知道在ORACEL中查詢一條記錄時(shí)如果沒(méi)有索引的情況下，它的執(zhí)行方式如下所示
如果我要做查詢 select C2 from 表 where C2=6 那么在ORACLE中系統(tǒng)會(huì)從最后一條掃描一直將表整個(gè)掃描一遍以后，這個(gè)查詢動(dòng)作才算完成。從中我們可以看出沒(méi)有索引的表的記錄是無(wú)序存放的。相反如果我們對(duì)這個(gè)表在列C2建立一個(gè)索引以后它的查詢執(zhí)行如下所示：
我們可以看到在查詢是系統(tǒng)如果選用該索引的話那么ORACLE將會(huì)查找一些有序的數(shù)據(jù)，那么我們的查詢速度將會(huì)大大地提高。
上面我們描述的是查詢一列數(shù)據(jù)時(shí)的情況，那么如果查詢所有數(shù)據(jù)呢，請(qǐng)看下圖所示
如果我們帶上索引查詢，ORACLE首先會(huì)找到索引然后找到在基表中記錄的位置。顯然這樣比直接在表中查詢要慢。但是這個(gè)結(jié)論對(duì)不對(duì)呢？下面將會(huì)做一些實(shí)驗(yàn)說(shuō)明這個(gè)問(wèn)題。
在進(jìn)行以下的實(shí)驗(yàn)值前首先，您需要作如下準(zhǔn)備：
在EMPLOYEE表的SAL列上建立一個(gè)索引
CREATE INDEX IND_EMPLOYEE_SAL ON EMPLOYEE(SAL);
在EMPLOYEE表的SAL,DEGREE兩個(gè)列上建立一個(gè)聯(lián)合索引
CREATE INDEX IND_EMPLOYEE_SALandDEGREE
ON EMPLOYEE(SAL,DEGREE);
實(shí)驗(yàn)1：使用索引與不使用索引
SELECT /*+INDEX (EMPLOYEE IND_EMPLOYEE_SAL)*/
* FROM
EMPLOYEE WHERE SAL=1000;
--13.03秒
SELECT /*+NO_INDEX(EMPLOYEE)*/
* FROM
EMPLOYEE WHERE SAL=1000;
利用索引查詢的時(shí)間的數(shù)據(jù)由于我的粗心大意弄丟了，但是我記得運(yùn)用索引的查詢要比沒(méi)有運(yùn)用索引的查詢要慢一些。
實(shí)驗(yàn)2：?jiǎn)瘟兴饕c多列索引之間的區(qū)別
//////////////////////////////////////////////////
////單列索引的情況
--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;
實(shí)驗(yàn)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;
實(shí)驗(yàn)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;
實(shí)驗(yàn)5：分區(qū)索引
分區(qū)索引的原理如下所示：
我們知道在磁道在磁盤(pán)上尋址的I/O操作的開(kāi)銷是相當(dāng)大的，如果我們建立了分區(qū)索引將其索引放在不同的磁盤(pán)上那么可以大大節(jié)省I/0開(kāi)銷提高我們的查詢速度。
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);
以上是我們關(guān)于索引的一些實(shí)驗(yàn)信息，通過(guò)該實(shí)驗(yàn)我們可以得出以下結(jié)論：
1. 索引是為了提高查詢速度,排序數(shù)據(jù)。
2. 索引不見(jiàn)得總能提高速度。
3. 查詢結(jié)果占總記錄數(shù)越少越可以用索引。
4. 分區(qū)索引 將數(shù)據(jù)分散到多個(gè)物理位置上來(lái)提高其IO的功能。
5. 全表掃描有時(shí)性能也不錯(cuò)。
6. 在統(tǒng)計(jì)資料不完整時(shí)未必選擇正確的索引。
7. 有時(shí)候添加索引可能會(huì)降低速度。
8. 索引適合于結(jié)果行占所有行的比很小的時(shí)候運(yùn)用 經(jīng)驗(yàn)值為比為5%左右為好，但是還可能還有許多其他情況影響查詢速度。
9. 值是唯一的時(shí)候運(yùn)用索引可以提高查詢速度。
10. 當(dāng)統(tǒng)計(jì)資料不完整的時(shí)候,查詢速度會(huì)很慢。可以通過(guò)對(duì)索引進(jìn)行統(tǒng)計(jì)分析來(lái)調(diào)整。
11. where條件中列的順序不影響其查詢速度。
12. 多列索引中的后面的列最好不要運(yùn)用索引。
13. 多列索引中其列的順序是不同的。不同的順序可能造成查詢速度不一樣。
14. 多列索引中第一列是有順序的,但是其后面的列是沒(méi)有順序。
15. where條件查詢的列中只要有索引,那么oracle會(huì)選擇運(yùn)用索引。
16. 當(dāng)查詢的結(jié)果大于所有行的記錄數(shù)的10%時(shí),那么最好不要運(yùn)用索引。
17. 小表用索引反而會(huì)降低速度。
18. 索引需要DBA經(jīng)常刪除重新建立,因?yàn)樗饕Y(jié)構(gòu)變化過(guò)多可能造成以后的查詢變慢。
19. DML語(yǔ)句會(huì)影響索引結(jié)構(gòu)大的變化,所以經(jīng)常作DML語(yǔ)句的表可以考慮不用索引。
20. CLUSTER索引僅僅用于只讀的的表多用于cluster表。
21. 維圖索引多用于行多但是值的類型很少的情況。
22. 表和索引要放在兩個(gè)不同的表空間上。
l Enhancement to other SQL operations
下面的內(nèi)容是介紹一些有關(guān)ORACLE技術(shù)的一些比較雜的內(nèi)容。
一、 層次查詢
在同一個(gè)表中的不同記錄有著直接或者間接的關(guān)系。
例如，我們查詢EMP表的信息如下：
從圖中我們可以看到ENAME是’SMITH’的領(lǐng)導(dǎo)是編號(hào)為7902的人。而7902的領(lǐng)導(dǎo)是編號(hào)為7566的人。7566的領(lǐng)導(dǎo)是編號(hào)為7839的人。那么這樣的關(guān)系我們就可以通過(guò)運(yùn)用層次查詢就可以查詢出來(lái)。
層次查詢實(shí)驗(yàn)
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';
二、 分組查詢
就是我們平時(shí)運(yùn)用的group語(yǔ)句.
分組查詢實(shí)驗(yàn)
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')),());
三、 并行執(zhí)行的SQL語(yǔ)句
并行執(zhí)行的SQL語(yǔ)句實(shí)驗(yàn)
--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;
四、 實(shí)體化視圖
在進(jìn)行以下的實(shí)驗(yàn)之前，我們需要作如下準(zhǔn)備：
建立一個(gè)普通視圖：
CREATE VIEW V_EMPLOYEE
AS SELECT SUM(SAL) AS C FROM EMPLOYEE;
建立實(shí)體化視圖：
CREATE MATERIALIZED VIEW 名字 AS 查詢；
CREATE MATERIALIZED VIEW V_M_EMPLOYEE
AS SELECT SUM(COMM) AS C FROM EMPLOYEE;
實(shí)體化視圖與普通視圖的比較
--20.04秒
SELECT * FROM V_EMP;
--0.01秒
SELECT * FROM V_M_EMPLOYEE;
通過(guò)以上的實(shí)驗(yàn)，我們可以得出結(jié)論：實(shí)體化視圖不包含新的數(shù)據(jù)，查詢速度很快。
如果需要實(shí)體化視圖包含新的數(shù)據(jù)我們可以通過(guò)
手工刷新：
EXEC DBMS_MVIEW.REFRESH('V_M_EMPLOYEE','CF'); CF -- 完全快速刷新
自動(dòng)刷新：
//每個(gè)表可以創(chuàng)建一個(gè)實(shí)體化視圖日志。
CREATE MATERIALIZED VIEW LOG ON 表名
WITH(列名列表)，
ROWID INCLUDING NEW VALUES;
//創(chuàng)建自動(dòng)刷新的實(shí)體化視圖
CREATE MATERIALIZED VIEW 名字
BUILD IMMEDIATE
〔REFRESH FAST||REFRESH COMPLETE〕 ON COMMIT
--REFRESH FAST 僅僅支持Insert語(yǔ)句的刷新
AS ...
運(yùn)用REFRESH COMPLETE 適合于update,delete操作較少的表。并且試驗(yàn)發(fā)現(xiàn)運(yùn)用
REFRESH COMPLETE時(shí)COMMIT操作會(huì)很慢！
五、 查詢重寫(xiě)技術(shù)(QUERY REWRITE)
表面上看是在查詢表，但是oracle實(shí)際上是去查詢實(shí)體化視圖的技術(shù)。
在下面的試驗(yàn)中我們也可以看出實(shí)際上無(wú)論是查詢表還是視圖oracle都會(huì)轉(zhuǎn)向查詢實(shí)體化視圖 MV_EMP。
對(duì)于反復(fù)執(zhí)行的匯總查詢存放起來(lái)、節(jié)省查詢時(shí)間，多用于匯總的計(jì)算。
//創(chuàng)建查詢重寫(xiě)技術(shù)
CREATE MATERIALIZED VIEW 名字
BUILD IMMEDIATE
〔REFRESH FAST||REFRESH COMPLETE〕 ON COMMIT
--REFRESH FAST 僅僅支持Insert語(yǔ)句的刷新
ENABLE QUERY REWRITE
AS ...
運(yùn)用查詢重寫(xiě)技術(shù)的例子
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;
經(jīng)過(guò)手工刷新以后查詢速度會(huì)變慢！
六、 分布式技術(shù)
分布式技術(shù)包括分布式數(shù)據(jù)庫(kù)、DB LINK、分布式查詢以及分布式事務(wù)管理。這里我們僅僅介紹有關(guān)DB LINK的內(nèi)容。
DB LINK的實(shí)驗(yàn)
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
通過(guò)以上實(shí)驗(yàn)我們得出結(jié)論：如果在執(zhí)行分布式事務(wù)的時(shí)候那么網(wǎng)絡(luò)一定要保持正常，如果網(wǎng)絡(luò)斷開(kāi)就會(huì)發(fā)生死鎖的情況。這時(shí)要進(jìn)行在服務(wù)端和客戶端分別將鎖刪除即可解決。
七、 DML語(yǔ)句
INSERT INTO ALL
INTO 表1 VALUES(列名列表)
...
INTO 表2 VALUES(列名列表)
子查詢；
DML語(yǔ)句的實(shí)驗(yàn)
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)
外部表是我們應(yīng)用中可能會(huì)常常碰到的問(wèn)題之一。例如，我需要將文本文件的數(shù)據(jù)導(dǎo)入到ORACLE數(shù)據(jù)庫(kù)中我們就可以利用此項(xiàng)技術(shù)。
假設(shè)外部文件有如下信息：
我們可以將此信息轉(zhuǎn)為以逗號(hào)分割的一些信息存為test.txt文本文件。
然后我們?cè)贠RACLE中建立一個(gè)訂單表BILL(BILL_ID 訂單編號(hào)，BILL_D訂單日期)
以及訂單明細(xì)表BILL_MX(BILL_ID 訂單編號(hào)，P 產(chǎn)品編號(hào)，PAMOUNT 產(chǎn)品數(shù)量)。
我們下面的實(shí)驗(yàn)就是通過(guò)將外部的文件信息導(dǎo)入到BILL表和BILL_MX表中去。
首先簡(jiǎn)單介紹一下創(chuàng)建外部表的方法：
第一步，創(chuàng)建一個(gè)DIRECTORY對(duì)象
CREATE DIRECTORY 名字 AS '路徑'
CREATE DIRECTORY test_dir AS 'D:\1';
第二步，創(chuàng)建外部表
…
外部表的實(shí)驗(yàn)
//創(chuàng)建外部表
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')
);
//導(dǎo)入數(shù)據(jù)
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;
九、 日期類型
日期類型的實(shí)驗(yàn)
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;
十、 自定義數(shù)據(jù)類型
自定義數(shù)據(jù)類型我們將會(huì)簡(jiǎn)單地介紹有關(guān)記錄和集合的內(nèi)容。
記錄類型的實(shí)驗(yàn)
ORACLE 對(duì)象可以理解為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;
集合類型的實(shí)驗(yàn)
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));
實(shí)驗(yàn)：創(chuàng)建一個(gè)工資表 職工編號(hào)，工資，補(bǔ)貼其中補(bǔ)貼是嵌套表，每個(gè)職工的都可以有多種補(bǔ)貼數(shù)量不定
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;
至此，有關(guān)的ORACLE Advanced SQL的內(nèi)容就介紹完了。通過(guò)，這篇文檔的總結(jié)我也體會(huì)到數(shù)據(jù)庫(kù)的學(xué)習(xí)與應(yīng)用我們還有很長(zhǎng)的路要走。
|