OracleSQL培训讲义分解.ppt

Oracle SQL 培训讲义 Michale Zeng SQL中常见的误区和隐患 单条SQL实现常见的特殊需求 SQL实现OLAP的报表功能 谁动了我的INDEX 为什么有索引但是没起作用？ 代码疏忽，例如包含格式的隐式转换 表大小低于“小表”的阀值（内部定义，默认大小是Buffer的20%） 索引具有较低的可选性 特殊的索引 FBI Function Based Index SQL desc t_fbi Name Type Nullable Default Comments ---- -------------- -------- ------- -------- 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 Oracle中，commit引起Redo Log的物理I/O 为提高效率，尽可能的批量提交 -- 逐行提交DECLAREBEGIN 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 + 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 实现批量 FORALL i IN 1 .. recs.COUNT INSERT INTO t_loop VALUES recs (i); COMMIT; EXIT WHEN cur%NOTFOUND; END LOOP; CLOSE cur;END; 悲观锁定/乐观锁定 业务需求：在插入纪录的同时，生成连续的流水号 -- 常见的实现逻辑，隐含bugDECLARE v_cnt NUMBER;BEGIN -- 这里会有什么样的BUG? SELECT MAX(ID) INTO v_cnt FROM t_lock; -- here for other operation v_cnt := v_cnt + 1; INSERT 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 +