oracle培训记录
1、
select * from t where x <=9minusselet * from t where x<5
只须排序5个数据 很大节省了PGA
2、
rowid >= and rowid <= xxx
3、删除之前先备份
declare Type v_rowid is table of varchar2(100) index by binary_integer; --定义rowid类型 var_rowid v_rowid; --定义rowid变量 v_monthno NUMBER; cursor v_cur is select /*+parallel(a,2)*/ROWID from bas01.activeuserslist a where monthno = v_monthno AND mailtype = 'NVL';BEGIN v_monthno := 201104; OPEN v_cur ; loop FETCH v_cur BULK COLLECT INTO var_rowid LIMIT 3000 ; FORALL i IN 1 .. var_rowid.count INSERT INTO activeuserslist_monitor SELECT * FROM bas01.activeuserslist WHERE ROWID =var_rowid(i); FORALL i IN 1 .. var_rowid.count delete from bas01.activeuserslist where ROWID =var_rowid(i); COMMIT;EXIT WHEN v_cur%NOTFOUND OR v_cur%NOTFOUND IS NULL;END LOOP;CLOSE v_cur;end;
4、索引范围扫描的时候他需要判断下一个结果不是我们要的结果才退出扫描
索引范围扫描
索引快速全扫描
5、组合索引,只要带上第一个列就会走索引，也有跳跃式索引 穷举第一列，如下：select * from t where b= 5;select * from t where a=1 and b=5union allall select * from t where a=2 and b=5union all select * from t where a=3 and b=5;
