准备：
cmd
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
扫一扫
1066
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
