Oracle Advanced Sql

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(

我们可以发现，运用NOT IN 要比 NOT EXISTS要快一些但是有个比较大的问题，就是在上面的实验中我们假设EMP表和DEPT表中没有字段内容为NULL的数据。如果我们要查询的表中有为NULL的数据的时候，那么我们在运用NOT IN操作会发现查出的数据是不准确的情况。结论：在您为选择NOT IN 和 NOT EXISTS时而苦恼时请优先选择运用NOT EXISTS语句。

接下来是有关JOIN的一些实验。

下面我们做如下试验

--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的执行策略等信息。

--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语句执行效率高。

调整对话框上控件布局的类CWindowMover：【上一篇】
Oracle初学者笔记(八)--Oracle中的对象(嵌套表与对象表)：【下一篇】