oracle培训记录
1、
[html]
select * from t where x <=9
minus
selet * from t where x<5
只须排序5个数据 很大节省了PGA
2、
[html]
rowid >= and rowid <= xxx
3、删除之前先备份
[sql]
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、组合索引,只要带上第一个列就会走索引,也有跳跃式索引 穷举第一列,如下:
[sql]
select * from t where b= 5;
select * from t where a=1 and b=5
union all
all select * from t where a=2 and b=5
union all
select * from t where a=3 and b=5;
6、B*tree