set oracle_sid=sid_name
sqlplus
SQL>@c:\course.sql

然后輸入用戶名密碼：scott/tiger

SQL>set timing on 記錄SQL的執行時間

SQL>SET LINESIZE 100

SQL>set autotrace on 啟動自動跟蹤功能
啟動自動跟蹤功能的方法，執行以下：
創建：
SQL>@%oracle_home%RDBMS\ADMIN\UTLXPLAN.SQL
步驟一：
SQL>conn / AS SYSDBA
步驟二：
SQL>@%Oracle_home%\SQLPLUS\admin\plustrce.sql
步驟三，授權給scott用戶：
SQL>set echo off
SQL>grant plustrace to scott;
步驟四，進入SCOTT帳戶，執行以下：
SQL>set autotrace on

只看方案和統計數據而不看執行的行的方法，執行以下：
SQL>set autotrace trace

=======================
課程開始
=======================
自然連接:
SQL>select * from employee natural join dept;

優化器提示：
select /*+ USE_HASH(表名1 表名2)*/ .... 散列連接(HASH JOIN)
select /*+ USE_NL(表名1 表名2)*/ .... 嵌套循環連接(NESTED LOOPS)
select /*+ USE_MERGE(表名1 表名2)*/ ....合并連接(MERGE JOIN)

強制使用散列連接，執行以下：
SQL>select  /*+ USE_HASH(emp dept) */ * from emp natural join dept;
USE_NL  嵌套循環連接
USE_MERGE 合并連接

查詢各部門工資最高的人員編號、姓名、工資。
SELECT EMPNO,SAL,DEPTNO
FROM EMPLOYEE A
WHERE SAL=(SELECT MAX(SAL) FROM EMPLOYEE WHERE DEPTNO=A.DEPTNO);

  JOIN

    1) 連接的三種實現方法
    2）如何改變語句的連接方法
      （通過優化器提示 optimizer hints)
    3) 優化器有時候并沒有選擇最快的連接方式
    4）有時也沒有選擇正確的驅動表

  SUBQUERY

     1）子查詢可能被優化器自動改變為連接去執行
     2) 連接比子查詢快
     3) 查詢時間與查詢返回的列的數目成正比
     4）相關子查詢比非相關子查詢慢
     5) 相關子查詢中，
        相關列的值的種類越多，速度越慢

試驗：
查詢高于所在部門平均工資
的職工編號、姓名、工資、以及高出多少錢。

SELECT A.EMPNO,A.ENAME,A.SAL-B.SAL
FROM EMPLOYEE A,(SELECT DEPTNO,AVG(SAL) SAL FROM EMPLOYEE GROUP BY DEPTNO) B
WHERE A.DEPTNO=B.DEPTNO
AND A.SAL > B.SAL;

SELECT /*+ USE_HASH(A B) */ A.EMPNO,A.ENAME,A.SAL-B.SAL
FROM EMPLOYEE A,(SELECT DEPTNO,AVG(SAL) SAL FROM EMPLOYEE GROUP BY DEPTNO) B
WHERE A.DEPTNO=B.DEPTNO
AND A.SAL > B.SAL;

以下因為在SELECT中存在相關子查詢，所以其性能要低于以上的實現方式：
SELECT EMPNO,ENAME,SAL,(SELECT AVG(SAL) FROM EMPLOYEE WHERE DEPTNO=A.DEPTNO)
FROM EMPLOYEE A
WHERE SAL>(SELECT AVG(SAL) FROM EMPLOYEE WHERE DEPTNO=A.DEPTNO);
存在的相關子查詢越多，速度越慢。

試驗：
查詢高于所在部門平均工資的職工編號、
姓名、工資，所在部門的平均工資，以及高出多少錢。

SELECT A.ENAME,A.SAL,B.SAL,A.SAL-B.SAL
FROM EMPLOYEE A,(SELECT DEPTNO,AVG(SAL) SAL FROM EMPLOYEE GROUP BY DEPTNO) B
WHERE A.DEPTNO=B.DEPTNO
AND A.SAL > B.SAL;

IN，EXISTS
NOT IN, NOT EXISTS
SELECT ...
FROM ...
WHERE EXIST(SUB QUERY)
當SUB QUERY有記錄返回，則EXIST(SUB QUERY)返回真，否則返回假

試驗：
分別用IN和EXISTS查詢誰在NEW YORK工作
SELECT *
FROM EMPLOYEE A
WHERE EXISTS (SELECT 1 FROM DEPT WHERE LOC='NEW YORK' AND DEPTNO=A.DEPTNO);

SELECT *
FROM EMPLOYEE
WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC='NEW YORK');

分別用NOT IN和NOT EXISTS查詢誰不在NEW YORK工作

SELECT *
FROM EMPLOYEE A
WHERE NOT EXISTS (SELECT 1 FROM DEPT WHERE LOC='NEW YORK' AND DEPTNO=A.DEPTNO);

SELECT *
FROM EMPLOYEE
WHERE DEPTNO NOT IN (SELECT DEPTNO FROM DEPT WHERE LOC='NEW YORK');

 

連接 > 非相關子查詢 > 相關子查詢
連接 > IN > EXISTS

當主查詢或子查詢的列中中有空值存在的話,
使用NOT IN會返回錯誤的結果，這時應該使用NOT EXISTS，
也就是說NOT EXISTS比NOT IN要安全。

=================================
WITH
=================================
練習：
查詢各部門工資最高的職工的編號，姓名，工資，所在部門最低工資，所在部門的平均工資
效率低：
WITH
A AS (SELECT DEPTNO,AVG(SAL) ASAL FROM EMPLOYEE GROUP BY DEPTNO),
B AS (SELECT DEPTNO,MIN(SAL) BSAL FROM EMPLOYEE GROUP BY DEPTNO),
C AS (SELECT DEPTNO,MAX(SAL) CSAL FROM EMPLOYEE GROUP BY DEPTNO)
SELECT E.EMPNO,E.ENAME,E.SAL,A.ASAL,B.BSAL
FROM EMPLOYEE E,A,B,C
WHERE E.DEPTNO=A.DEPTNO
AND E.DEPTNO=B.DEPTNO
AND E.DEPTNO=C.DEPTNO
AND E.SAL=C.CSAL;

效率高：
WITH B AS (SELECT DEPTNO,MAX(SAL) MSAL,MIN(SAL) NSAL,AVG(SAL)ASAL FROM EMPLOYEE GROUP BY DEPTNO)
SELECT A.EMPNO,A.ENAME,A.SAL,B.NSAL,B.ASAL
FROM EMPLOYEE A,B
WHERE A.DEPTNO=B.DEPTNO
AND A.SAL=B.MSAL;

=================================
INDEX
=================================
試驗：
在employee表的SAL列上創建一個索引，然后查詢工資是1000的職工
SQL>CREATE INDEX IDX_SAL ON EMPLOYEE(SAL);
SQL>
觀察執行方案是否使用了索引，記錄查詢的時間
SQL>SELECT /*+NO_INDEX(EMPLOYEE) */ * FROM EMPLOYEE WHERE SAL=1000;

當（查詢結果行數/表中所有行數）< 10% 時，使用索引很可能會改善查詢速度
當（查詢結果行數/表中所有行數）> 10% 時，使用索引不會改善查詢速度
如果查詢的結果列是索引列的話，那么使用索引一定會提高查詢速度。

 假設索引建立在(A,B,C)列上

 考慮以下語句能夠利用此索引提高查詢速度

  SELECT * FROM T1 WHERE A=? AND B=? AND C=?
  SELECT * FROM T1 WHERE A=? AND B=?
  SELECT * FROM T1 WHERE A=?
  SELECT * FROM T1 WHERE B=? AND C=? AND A=?
  SELECT * FROM T1 WHERE A=? AND C=?
  SELECT * FROM T1 WHERE B=? AND C=? 不能利用索引提高查詢速度
  SELECT * FROM T1 WHERE A=? AND D=?
  SELECT * FROM T1 WHERE C=? 不能利用索引提高查詢速度

  綜上，只有當A類出現的時候才會利用索引提高查詢速度。

試驗：
在SAL列上創建索引IDX_SAL，
在SAL，DERGEE列上創建索引IND_SAL_DEGREE，
然后讓以下語句分別使用上述兩個索引，比較區別。
SELECT * FROM EMPLOYEE WHERE SAL=1000;
SELECT * FROM EMPLOYEE WHERE DERGEE=3;

強制使用索引：
SELECT /*+INDEX(表名 索引名) */ * FROM 表名 WHERE ......

試驗：
在SAL，DEGREE，ID_NO列上創建一個索引，
執行以下查詢，并強制使用這個索引
SELECT *
FROM EMPLOYEE
WHERE SAL=1000
AND DEGREE=1;

SELECT * FROM EMPLOYEE
WHERE SAL=1000
AND ID_NO>210101197200000000
 
SELECT * FROM EMPLOYEE
WHERE DEGREE=1
AND ID_NO>210101197200000000

可以利用索引進行排序已提高排序的速度，當然了這時因空間換效率的方法，
因為索引是需要占用空間的。
當使用ORDER BY進行排序，性能比較低的時候，可以使用將排序列建索引的方法來提高性能，
因為索引是排好序的。

# 索引必須跟標存放在不同的表空間上，是為了防止爭用最大化讀寫硬盤的效率。
創建索引的方法：
CREATE INDEX 索引名 ON 表名(字段名1,字段名2,...);
TABLESPACE 表空間名

移動索引至新的表空間中的方法：
ALTER INDEX 索引名 REBUILD TABLESPACE 表空間名;

ALTER TABLE EMP ADD CONSTRANT PK_EMP PRIMARY KEY(EMPNO);

SQL>EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMPLOYEE',METHOD_OPT=>'FOR COLUMS SIZE 10 SAL');
DBMS_STAS是個包
GATHER_TABLE_STATS其參數為以下：

SQL>DESC DBMS_STATS;
PROCEDURE GATHER_TABLE_STATS
參數名稱                       類型                    輸入/輸出默認值？
------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 TABNAME                        VARCHAR2                IN
 PARTNAME                       VARCHAR2                IN     DEFAULT
 ESTIMATE_PERCENT               NUMBER                  IN     DEFAULT
 BLOCK_SAMPLE                   BOOLEAN                 IN     DEFAULT
 METHOD_OPT                     VARCHAR2                IN     DEFAULT
 DEGREE                         NUMBER                  IN     DEFAULT
 GRANULARITY                    VARCHAR2                IN     DEFAULT
 CASCADE                        BOOLEAN                 IN     DEFAULT
 STATTAB                        VARCHAR2                IN     DEFAULT
 STATID                         VARCHAR2                IN     DEFAULT
 STATOWN                        VARCHAR2                IN     DEFAULT
 NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT

搜集分析資料(改善優化器的數據來源)：
SQL>ANALYZE TABLE EMPLOYEE COMPUTE STATISTICS;

刪除分析資料：
SQL>ANALYZE TABLE EMPLOYEE DELETE STATISTICS;

 索引 
   
   1)索引與全表掃描相比
     當查詢返回的行的數量與表中行的總數相比
     比例較低時（至少低于10%），
     索引方式比全表掃描方式快

   2）有時優化器對索引的選擇是錯誤的
      （全表掃描快時，優化器選擇的是索引方式)

   3) 多列復合索引，
      如果從前向后按順序使用索引中的列
      就有可能利用索引提高速度

    4）如果語句中，只出現和返回被索引列，
       則使用索引的速度一般是最快的

    5）索引和表應該存放在不同的表空間
       這樣就有可能減少競爭，最大化兩者的IO性能

    6) 主鍵約束上自動創建的唯一索引，
       應該放在和表不同的表空間上

=================================
大綱對象  OUTLINE
=================================
如何針對性地進行優化，而在以后再修改優化方案的時候不需要修改源代碼？以下：
方案：大綱對象 OUTLINE

1.準備工作；
SQL>CONN / AS SYSDBA
創建用戶：
SQL>CREATE USER USER_NAME IDENTIFIED BY PASSWD;
修改用戶的默認表空間：
SQL>ALTER USER USER_NAME DEFAULT TABLESPACE TABLESPACE_NAME;
a.準備一個用來存放大綱數據的表空間
SQL>CONN / AS SYSDBA
SQL>SELECT NAME FROM V$DATAFILE;
SQL>CREATE TABLESPACE OL_TS DATAFILE 'D:\ORACLE\ORADATA\ORA\OL_TS01.DBF' SIZE 100M;
SQL>SELECT NAME FROM V$DATAFILE;
b.將OUTLN帳戶的默認表空間設置為這個新建的表空間
SQL>ALTER USER OUTLN DEFAULT TABLESPACE OL_TS;
c.運行腳本dbmsol.sql
d.將OUTLN帳戶解鎖：
SQL>CONN / AS SYSDBA
SQL>ALTER USER OUTLN ACCOUNT UNLOCK;

2.如何創建大綱，來存儲某個語句的當前執行方案
將創建大綱的權限授權給SCOTT：
SQL>GRANT CREATE ANY OUTLINE TO SCOTT

在SCOTT帳戶下，為某個特定的語句創建大綱：
SQL>CREATE OUTLINE OL ON SELECT * FROM EMPLOYEE WHERE SAL=1000;

3.如何使用大綱中的存儲的方案
全局啟用：
SQL>ALTER SYSTEM SET USE_STORED_OUTLINES=TRUE；
當前會話啟用：
SQL>ALTER SESSION SET USE_STORED_OUTLINES=TRUE;

4.如何篡改大綱中的存儲方案
如果創建了兩個大綱：
CREATE OUTLINE OL ON SELECT * FROM EMPLOYEE WHERE SAL=1000;
CREATE OUTLINE OL2 ON SELECT /*+INDEX(EMPLOYEE IND_EMPLOYEE_SAL) */ * FROM EMPLOYEE WHERE SAL=1000;

如何查看目前存在的大綱：
SQL>CONN OUTLN/OUTLN
SQL>SELECT OL_NAME,SQL_TEXT,TEXTLEN,SIGNATURE,HASH_VALUE,HASH_VALUE2,HINTCOUNT FROM OL$;

刪除大綱的方法：
SQL>DROP OUTLINE 大綱名;

==========================
分組
==========================

練習：
使用ROLLUP對EMPLOYEE表的SAL,DEGREE列作多重分組
ROLLUP 結果中含有匯總行
SQL> SELECT SAL,DEGREE, COUNT(*) FROM EMPLOYEE GROUP BY ROLLUP (SAL,DEGREE);
       SAL     DEGREE   COUNT(*)
---------- ---------- ----------
      1000          1     157287
      1000          2      52429
      1000          3      52428
      1000                262144 匯總行
      2000          1     194715
      2000          2      52428
      2000                247143 匯總行
      3000          1     157287
      3000          2      52428
      3000          3      52429
      3000                262144 匯總行
      4000                262144 
      4000                262144 匯總行
      2001          1          1
      2001                     1 匯總行
      2002          1       5000
      2002                  5000 匯總行
      2003          1      10000
      2003                 10000 匯總行
                         1048576 匯總行

使用GRUPING(列名），可以表示出那行記錄是自己加進來的，而不是原有記錄
SQL> SELECT SAL,DEGREE, COUNT(*),GROUPING(DEGREE) FROM EMPLOYEE GROUP BY ROLLUP (SAL,DEGREE);

       SAL     DEGREE   COUNT(*) GROUPING(DEGREE)
---------- ---------- ---------- ----------------
      1000          1     157287                0
      1000          2      52429                0
      1000          3      52428                0
      1000                262144                1 是加進來的匯總記錄
      2000          1     194715                0
      2000          2      52428                0
      2000                247143                1 是加進來的匯總記錄
      3000          1     157287                0
      3000          2      52428                0
      3000          3      52429                0
      3000                262144                1 是加進來的匯總記錄
      4000                262144                0
      4000                262144                1 是加進來的匯總記錄
      2001          1          1                0
      2001                     1                1 是加進來的匯總記錄
      2002          1       5000                0
      2002                  5000                1 是加進來的匯總記錄
      2003          1      10000                0
      2003                 10000                1 是加進來的匯總記錄
                         1048576                1 是加進來的匯總記錄

===========
CUBE
===========
SELECT ... FROM T1 GROUP BY CUBE(A,B,C,D);
相當于：
 ...GROUP BY A,B,C,D
+...GROUP BY A,B,C
+...GROUP BY A,B
+...GROUP BY A
+...GROUP BY B,C,D
+...GROUP BY B,C
+...GROUP BY B
+...GROUP BY C,D
+...GROUP BY C
+...GROUP BY D
+...GROUP BY ()

練習：
使用CUBE對EMPLOYEE表的SAL,DEGREE列作多重分組
SQL> SELECT SAL,DEGREE,GROUPING(DEGREE),COUNT(*),SUM(SAL) FROM EMPLOYEE GROUP BY CUBE(SAL,DEGREE);

======
GROUPING SETS((...),(..),(...),...,())
======
練習：
使用GROUPING SETS在一個語句中同時查詢出：
各種工資的職工工人數和工資總額
各種學位的職工人數和工資總額
每個部門每種學位的職工人數和工資總額
每個部門工資的職工人數和工資總額
全體人數和工資總額
SELECT DEPTNO,SAL,DEGREE,COUNT(*),SUM(SAL)
FROM EMPLOYEE
GROUP BY GROUPING SETS((SAL),(DEGREE),(DEPTNO,DEGREE),(DEPTNO,SAL),());

 

========================================
實體化視圖 MATERALIZED VIEW
========================================
實體化視圖與普通視圖的區別：
普通視圖是個虛表，相當一個子查詢
實體化視圖是將運算結果預先放到實體化視圖表中了

CREATE OR REPLACE VIEW 視圖名 AS 查詢
SQL>CREATE OR REPLACE VIEW V_A AS SELECT SUM(SAL) SS FROM EMPLOYEE;

CREATE MATERIALIZED VIEW 實體化視圖名 AS 查詢
首先要給用戶作CREATE MATERIALIZED VIEW的權限：
SQL>GRANT CREATE MATERIALIZED VIEW TO SCOTT
創建實體化視圖：
SQL>CREATE MATERIALIZED VIEW MV_A SA SELECT SUM(SAL) FROM EMPLOYEE;

練習：
創建一個實體化視圖，查詢所有部門編號，人數和工資總額
然后比較從這個實體化視圖上查詢數據的速度和從表上查詢數據的速度
SQL>CREATE MATERIALIZED VIEW MV_1 AS SELECT DEPTNO,COUNT(*),SUM(SAL) FROM EMPLOYEE GROUP BY DEPTNO;

因為實體化視圖是將運算結果預先放到實體化視圖表中，
所以實體化視圖中的數據在不同的時刻可能會是舊數據
那么就需要對實體化視圖進行刷新
a.手動刷新
SQL>EXEC DBMS_MVIEW.REFRESH('實體化視圖名');
b.自動刷新
首先在表的列上，建立實體化視圖日志對象，用來監視表中列值的變化
CREATE MATERIALIZED VIEW LOG ON 表名
WITH(列名列表),ROWID INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON EMPLOYEE
WITH(SAL), ROWID INCLUDING NEW VALUES;
創建實體化視圖，并且加入自動刷新選項
CREATE MATERIALIZED VIEW 實體化視圖名
REFRESH FAST ON COMMIT
AS 查詢;
#FAST方式不支持UPDATE。
或
CREATE MATERIALIZED VIEW 實體化視圖名
REFRESH COMPLETE ON COMMIT
AS 查詢;

刪實體化視圖的方法:
DROP MATERIALIZED VIEW 實體化視圖名

===================
查詢重寫技術 QUERY REWRITE
===================
作用：
當實體化視圖中的數據不準確時，Oracle會自動從表中進行查詢
當實體化視圖中的數據準確時，Oracle會自動從實體化視圖中進行查詢。
使用查詢重寫技術的步驟：
a.實體化視圖需要支持查詢重寫
CREATE MATERIALIZED VIEW 實體化視圖名
REFRESH COMPLETE ON COMMIT
ENABLE QUERY REWRITE
AS 查詢;
b.啟用查詢重寫功能
ALTER SYSTEM SET QUERY_REWRITE_ENABLED=TRUE;
或
ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;

檢查某查詢語句查詢重寫是否啟用，如果未啟用是什么原因的方法：
a.在當前帳戶內運行以下腳本：
SQL>@%ORACLE_HOME%\RDBMS\ADMIN\UTLXRW.SQL
b.執行過程：
SQL>EXEC DBMS_MVIEW.EXPLAIN_REWRITE('查詢語句');
c.執行查詢：
SQL>SELECT * FROM REWRITE_TABLE; 獲得信息

=======================
分布式查詢
=======================

數據庫=控制文件，充作日志文件，數據文件三種文件邏輯集合

--------------------------
DB LINK
--------------------------
三種方式：
1、Connected user link
CREATE PUBLIC DATABASE LINK 名字
USING ‘網絡服務名’;

SELECT * FROM EMP@db_link_name;

2、Fixed user link
CREATE PUBLIC DATABASE LINK 名字
CONNECT TO username IDENTIFIED BY password 
USING ‘網絡服務名’;

SELECT * FROM EMP@db_link_name;

可以使用以下方式為EMP@DB_LINK_NAME創建別名
CREATE SYNONYM 別名 FRO EMP@DB_LINK_NAME
創建后可以按如下方式使用：
SELECT * FROM 別名;

使用DB-Link的方式如果被連接的數據庫提交失敗(如出現網絡故障)，會導致本地數據庫掛起:
ORA-01591：鎖定以被有問題的分配事務1.37.1794掛起
解決的方式：
不能簡單地使用[ROOLBACK]命令，而需要使用[rollback force '1.37.1794']命令。

 

如何找到會話信息，并殺掉該會話：
找：
SQL>DESC V$TRANSACTION
SQL>SELECT A.SID,A.SERIAL#,A.TERMINAL,A.USERNAME
FROM V$SESSION A, V$TRANSACTION B
WHERE A.SADDR=B.SES_ADDR

殺：
ALTER SYSTEM KILL SESSION 'SID,SERIAL#'
SQL> ALTER SYSTEM KILL SESSION '12,49'

 

=======================
并行執行的SQL
=======================

=======================
DDL&DML
=======================
使用外部表的步驟：
a.創建DIRECTORY對象，該對象指向磁盤上的一個目錄
SQL> GRANT CREATE ANY DIRECTORY TO SCOTT
SQL> CREATE DIRECTORY test_dir AS '磁盤上的目錄'；

b.將格式化的文本文件放在這個目錄中

c.創建外部表，在數據庫內部映射這個文件的數據
請參照PPT的P273