1、Oracle SQL 培训讲义,Michale Zeng,SQL中常见的误区和隐患单条SQL实现常见的特殊需求SQL实现OLAP的报表功能,谁动了我的INDEX,B-Tree索引的特点 所有叶子接点都有相同的层高 自动维护平衡性 精确匹配和范围匹配 大表小表都适合,为什么有索引但是没起作用？ 代码疏忽，例如包含格式的隐式转换 表大小低于“小表”的阀值（内部定义，默认大小是Buffer的20%） 索引具有较低的可选性,特殊的索引 FBI,Function Based IndexSQL desc t_fbi Name Type Nullable Default Comments - - - - -
2、 RN NUMBER Y NAME VARCHAR2(4000) Y DT DATE Y CREATE INDEX idx_nonfbi ON t_fbi(dt);为什么下面的SQL不能使用索引idx_nonfbi? SELECT * FROM t_fbi WHERE trunc(dt) = to_date(2006-09-21,yyyy-mm-dd) ; SELECT * FROM t_fbi WHERE to_char(dt, yyyy-mm-dd) = 2006-09-21;CREATE INDEX idx_fbi_1 ON t_fbi(trunc(dt);,LOOP过程中的COMMIT
3、,Oracle中，commit引起Redo Log的物理I/O 为提高效率，尽可能的批量提交- 逐行提交 DECLARE BEGIN FOR cur IN (SELECT * FROM user_objects) LOOP INSERT INTO t_loop VALUES cur; COMMIT; END LOOP; END; - 模拟批量提交 DECLARE v_count NUMBER; BEGIN FOR cur IN (SELECT * FROM user_objects) LOOP INSERT INTO t_loop VALUES cur; v_count := v_count
4、+ 1; IF v_count = 100 THEN COMMIT; END IF; END LOOP; COMMIT; END;,LOOP过程中的COMMIT,- 真正的批量提交，用到嵌套表(数组)的特性 DECLARE CURSOR cur IS SELECT * FROM user_objects; TYPE rec IS TABLE OF user_objects%ROWTYPE; recs rec; BEGIN OPEN cur; WHILE (TRUE) LOOP FETCH cur BULK COLLECT INTO recs LIMIT 100; - forall 实现批量 F
5、ORALL i IN 1 recs.COUNT INSERT INTO t_loop VALUES recs (i); COMMIT; EXIT WHEN cur%NOTFOUND; END LOOP; CLOSE cur; END;,悲观锁定/乐观锁定,业务需求：在插入纪录的同时，生成连续的流水号- 常见的实现逻辑，隐含bug DECLARE v_cnt NUMBER; BEGIN - 这里会有什么样的BUG? SELECT MAX(ID) INTO v_cnt FROM t_lock; - here for other operation v_cnt := v_cnt + 1; INSER
6、T INTO t_lock (ID) VALUES (v_cnt); COMMIT; END;,悲观锁定/乐观锁定,- 高并发环境下，安全的实现逻辑 DECLARE v_cnt NUMBER; BEGIN - 对指定的行取得lock SELECT ID INTO v_cnt FROM t_lock WHERE ID=1 FOR UPDATE; - 在有lock的情况下继续下面的操作 SELECT MAX(ID) INTO v_cnt FROM t_lock; - here for other operation v_cnt := v_cnt + 1; INSERT INTO t_lock (I
7、D) VALUES (v_cnt); COMMIT; -提交并且释放lock END;,悲观锁定/乐观锁定,业务需求：WEB环境下，查询并显示一条纪录。修改部分值并提交隐含的逻辑Bug：UserA查询出一条纪录，然后修改。当UserA修改并提交的过程中，UserB已经完成提交。结果是 Lost Update分析： 如何保证UserA提交的时候，被修改纪录仍然是刚才的那一条纪录 合理的解决方案是UserA查询的时候，对纪录进行Lock WEB环境下，公用数据库Session，所以不能采用for update 正确的方案是UserA查询的时候，纪录纪录当前状态，提交时进行比较解决方案 增加一个时间
8、字段update_date MD5或CRC算法对字段进行换算比较 Oracle 10g下可以使用ora_rowscn伪列,硬解析/软解析,硬解析造成的效率下降非常可观 使用绑定变量，消除硬解析DECLARE sql_1 VARCHAR2(200); BEGIN - hard parse - java中的同等语句是 Statement.execute() FOR i IN 1 1000 LOOP sql_1 := insert into t_hard(id) values( | i | ); EXECUTE IMMEDIATE sql_1; END LOOP; COMMIT; - soft pa
9、rse -java中的同等语句是 PreparedStatement.execute() sql_1 := insert into t_hard(id) values(:id); FOR i IN 1 1000 LOOP EXECUTE IMMEDIATE sql_1 USING i; END LOOP; COMMIT; END;,IN 和 EXISTS 之争,SQL desc t_employees Name Type Nullable Default Comments - - - - - EMPLOYEE_ID NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_
10、NAME VARCHAR2(25) Y EMAIL VARCHAR2(25) Y PHONE_NUMBER VARCHAR2(20) Y HIRE_DATE DATE Y JOB_ID VARCHAR2(10) Y SALARY NUMBER(8,2) Y COMMISSION_PCT NUMBER(2,2) Y MANAGER_ID NUMBER(6) Y DEPARTMENT_ID NUMBER(4),SQL desc t_orders Name Type Nullable Default Comments - - - - - ORDER_ID NUMBER(12) Y ORDER_DAT
11、E TIMESTAMP(6) Y ORDER_MODE VARCHAR2(8) Y CUSTOMER_ID NUMBER(6) ORDER_STATUS NUMBER(2) Y ORDER_TOTAL NUMBER(8,2) Y SALES_REP_ID NUMBER(6) PROMOTION_ID NUMBER(6) Y,IN 和 EXISTS 之争,查找处理过指定客户订单的那些职员 订单表的客户号有高可选性的索引- t_orders.customer_id有索引 SELECT a.* FROM t_employees a WHERE a.employee_id IN (SELECT b.s
12、ales_rep_id FROM t_orders b WHERE b.customer_id = 12);SELECT a.* FROM t_employees a WHERE EXISTS (SELECT 1 FROM t_orders b WHERE b.customer_id = 12 AND a.employee_id = b.sales_rep_id);思考：语句1和语句2那个效率高？,IN 和 EXISTS 之争,查找处理过客户订单，并且在指定部门下的员工 员工表的部门号有高可选性的索引- t_employees.department_id有索引 SELECT a.* FROM
13、t_employees a WHERE a.department_id = 10 AND EXISTS (SELECT 1 FROM t_orders b WHERE a.employee_id = b.sales_rep_id);SELECT a.* FROM t_employees a WHERE a.department_id = 10 AND a.employee_id IN (SELECT b.sales_rep_id FROM t_orders b); 思考：语句1和语句2那个效率高？,SQL中常见的误区和隐患单条SQL实现常见的特殊需求SQL实现OLAP的报表功能,单条SQL插入
14、多个表,根据不同的条件，用一条SQL实现把组合的数据插入到多个表。如果不提供WHEN条件，就插入所有的表INSERT ALL WHEN (c1 IN (a1,a3) THEN INTO t_ia_dest_1(flag,c) VALUES(flag1,c2) WHEN (c1 IN (a2,a4) THEN INTO t_ia_dest_2(flag,c) VALUES(flag2,c2) ELSE INTO t_ia_dest_3(flag,c) VALUES(flag1|flag2,c1|c2) SELECT c1,c2, f1 flag1, f2 flag2 FROM t_ia_src;
15、适用案例：父子表同时插入数据；操作成功的同时写LOG,单条SQL实现插入或者更新,判断数据是否存在，如果存在就更新，否则插入新数据行MERGE INTO t_mg a USING (SELECT the code code, the name NAME FROM dual) b ON (a.code = b.code) WHEN MATCHED THEN UPDATE SET a.NAME = b.NAME WHEN NOT MATCHED THEN INSERT (code, NAME) VALUES (b.code, b.NAME); 适用案例：对维度表进行数据装载；对参数表进行修改 限制
16、条件：仅能使用select子句，不能直接用变量和常量。但是可以构造成select的字段进行操作,查找删除重复的数据,数据行有重复字段code，无法通过UNION去重的情况SELECT * FROM t_dup a WHERE a.ROWID (SELECT MIN(b.ROWID) FROM t_dup b WHERE a.code=b.code);SELECT b.code, b.NAME FROM (SELECT a.code, a.NAME, row_number() over(PARTITION BY a.code ORDER BY a.ROWID) rn FROM t_dup a)
17、b WHERE b.rn 1; 适用案例：语句1采用2次Full Scan，语句2采用1次Full Scan和分析函数。 把Select替换成Delete即可直接删除重复数据,分页算法,正确的分页算法，使用Rownum和嵌套子查询SELECT * FROM (SELECT a.*, ROWNUM rn FROM (SELECT * FROM t_employees ORDER BY first_name) a WHERE ROWNUM 480 ;最内层要包括order by保证排序条件一致 中间层取得分页的最大条数 最外层取得分页的最小条数,分页算法,下面是分页算法常见的错误（红色部分）- 分
18、页算法(why not this one) SELECT a.*, ROWNUM rn FROM (SELECT * FROM t_employees ORDER BY first_name) a WHERE ROWNUM 480;- 分页算法(why not this one) SELECT b.* FROM (SELECT a.*, ROWNUM rn FROM t_employees a WHERE ROWNUM 480;,行列转换,数据格式一 CARD_CODE Q BAL - - - 001 1 27 001 2 10 001 3 36 001 4 97 002 1 96 002 2
19、 12 002 3 15 002 4 32数据格式二 CARD_CODE Q1 Q2 Q3 Q4 - - - - - 001 27 10 36 97 002 96 12 15 32,行列转换,从格式一到格式二SELECT a.card_code, SUM(decode(a.q, 1, a.bal, 0) q1, SUM(decode(a.q, 2, a.bal, 0) q2, SUM(decode(a.q, 3, a.bal, 0) q3, SUM(decode(a.q, 4, a.bal, 0) q4 FROM t_change_lc a GROUP BY a.card_code ORDER
20、 BY 1;,行列转换,从格式二到格式一SELECT t.card_code, t.rn q, decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4) bal FROM (SELECT a.*, b.rn FROM t_change_cl a, (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM = 4) b) t ORDER BY 1, 2; 技巧： 构造虚拟表把源数据的行数增加。 根据行号选择显示不同的字段,行列转换 进阶技巧,数据格式一 CARD_CODE Q - - 001 quarter_1 001 q
21、uarter_2 001 quarter_3 001 quarter_4 002 quarter_1 002 quarter_2 002 quarter_3 002 quarter_4数据格式二 CARD_CODE Q - - 002 quarter_1;quarter_2;quarter_3;quarter_4 001 quarter_1;quarter_2;quarter_3;quarter_4,行列转换 进阶技巧,从格式一到格式二SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ;), 2) q FROM (SELECT
22、a.card_code, a.q, row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn FROM t_change_lc_comma a) t1 START WITH t1.rn = 1 CONNECT BY t1.card_code = PRIOR t1.card_code AND t1.rn - 1 = PRIOR t1.rn GROUP BY t1.card_code; 技巧： 根据分组后的行号，构造一棵树（或者多棵）。 把从根到叶子的值串接起来。,行列转换 进阶技巧,从格式二到格式一SELECT t.card_code
23、, substr(t.q, instr(; | t.q, ;, 1, rn), instr(t.q | ;, ;, 1, rn) - instr(; | t.q, ;, 1, rn) q FROM (SELECT a.card_code, a.q, b.rn FROM t_change_cl_comma a, (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM 0) t ORDER BY 1, 2; 技巧： 构造虚拟表把源数据的行数增加。 根据行号选择显示字段的不同部分,SQL中常见的误区和隐患单条SQL实现常见的特殊需求SQL实现OLAP的报表功能,样
24、本数据,SQL desc scott.emp Name Type Nullable - - - - - EMPNO NUMBER(4) ENAME VARCHAR2(10) Y JOB VARCHAR2(9) Y MGR NUMBER(4) Y HIREDATE DATE Y SAL NUMBER(7,2) Y COMM NUMBER(7,2) Y DEPTNO NUMBER(2) Y,DEPTNO EMPNO ENAME SAL - - - -20 7369 SMITH 800.0030 7499 ALLEN 1600.0030 7521 WARD 1250.0020 7566 JONES
25、2975.0030 7654 MARTIN 1250.0030 7698 BLAKE 2850.0010 7782 CLARK 2450.0020 7788 SCOTT 3000.0010 7839 KING 5000.0030 7844 TURNER 1500.0020 7876 ADAMS 1100.0030 7900 JAMES 950.0020 7902 FORD 3000.0010 7934 MILLER 1300.00,根据部门分组小计合计薪水,DEPTNO EMPNO ENAME TOTAL_SAL - - - - 10 7782 CLARK 2450 10 7839 KING
26、5000 小计 7450 20 7566 JONES 2975 20 7788 SCOTT 3000 20 7902 FORD 3000 小计 8975 30 7698 BLAKE 2850 小计 2850 合计 19275,- 小计合计 SELECT CASE WHEN a.deptno IS NULL THEN 合计 WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN 小计 ELSE | a.deptno END deptno, a.empno, a.ename, SUM(a.sal) total_sal FROM scott.emp a
27、WHERE a.sal 2000 GROUP BY GROUPING SETS(a.deptno),(a.deptno, a.empno, a.ename),();,员工薪水在部门内外部的排名,DEPTNO EMPNO ENAME SAL R1 R2 R3 - - - - - - -10 7839 KING 5000.00 1 1 110 7782 CLARK 2450.00 2 2 620 7902 FORD 3000.00 1 1 220 7788 SCOTT 3000.00 1 1 220 7566 JONES 2975.00 3 2 430 7698 BLAKE 2850.00 1 1
28、 5- 分组排序 SELECT a.deptno, a.empno, a.ename, a.sal, - 可跳跃的rank rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) r1, - 密集型rank dense_rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) r2, - 不分组排序 rank() over(ORDER BY sal DESC) r3 FROM scott.emp a ORDER BY a.deptno,a.sal DESC;,当前行数据和前/后n行的数据比
29、较,EMPNO ENAME SAL LAG_1 LEAD_3 - - - - -7839 KING 5000.00 29757902 FORD 3000.00 5000 28507788 SCOTT 3000.00 3000 24507566 JONES 2975.00 3000 7698 BLAKE 2850.00 2975 7782 CLARK 2450.00 2850 - 当前行数据和前/后n行的数据比较 SELECT a.empno, a.ename, a.sal, - 上面一行 lag(a.sal) over(ORDER BY a.sal DESC) lag_1, - 下面三行 lead(a.sal, 3) over(ORDER BY a.sal DESC) lead_3 FROM scott.emp a WHERE a.sal 2000 ORDER BY a.sal DESC;,
