主页 > 知识库 > 数据库 > Oracle >

Oracle临时表之临时表的应用问题

来源:中国IT实验室 作者:佚名 发表于:2013-05-07 12:57  点击:
网上有人给出了最佳的优化思路是: 1.先将大表中满足条件的记录抽出来生成一张临时表。 2.再将这较小的临时表与另一张较小的表进行关联查询。 先不论思路是否值得商榷,这把临时表当成中转站的做法还是很值得肯定 临时表本质上就是一种cache的表现形式,Oracl
网上有人给出了最佳的优化思路是:
    1.先将大表中满足条件的记录抽出来生成一张临时表。
    2.再将这较小的临时表与另一张较小的表进行关联查询。
    先不论思路是否值得商榷,这把临时表当成中转站的做法还是很值得肯定
    临时表本质上就是一种cache的表现形式,Oracle的临时表都是事先建好的
    一旦用了临时表,存放的就是和本会话相关的数据
    没有人会傻乎乎地用临时表来保存本应该共享的数据
    with子查询实际上也是用了临时表,Oracle会替你创建一张临时表
    因此临时表的开销WITH子查询也会有。只要把AUTOTRACE打开你就会看到REDO的开销
    关于临时表的使用至少会带来两个问题:
    1)主查询的执行计划问题
    2)额外的写redo的问题
    如果,
    临时表作为复杂查询条件的中间结果用于主查询,因为临时表里往往只是个别字段的少量数据,1)的问题比较突出;
    如果,
    临时表作为最终展现前的结果归集,可能临时表会有比较多字段的较多数据,2)的问题比较突出
    ㈠ 主查询的执行计划问题
    9i临时表由于动态采样level 1,还得用hint,10g比较好用
    比较复杂的存储过程(比如数据抽取)可能用到临时表,比实体表优势就是redo少,自动清除
    对于临时表的缺陷--采样问题,执行计划的问题其实主要是临时表的cardinality的问题
    对于临时表方案,建议动态采样。9IR2以后的版本使用DYNAMIC_SAMPLING 参数或hint能基本避免
    如写上 HINT强制它采样 /*+dynamic_sampling(t 0) */
    cardinality hint分段提示是个比较好的最佳实践
    例如:
    临时表里的数据量有大起大落的情形,Oracle只会在硬解析的时候做一次取样
    当临时表数据量变化之后,原来的执行计划可能已经不是最优的
    碰到这种问题建议使用动态SQL
    临时表的数据量在插入结束之后可以通过SQL%ROWCOUNT得知
    然后在动态SQL里面拼入cardinality提示,这个提示没有必要精确,要不然你就会有无数的硬解析了
    建议给它设置的坎是5000, 即1-5000当作5000处理,5001-10000当作10000,
    如此类推,CARDINALITY = CEIL(SQL%ROWCOUNT/5000)*5000,
    你也可以通过测试调整出一个合理的值
    ㈡ 临时表的redo生成
    临时表不会为它们的块生成redo.因此,对临时表的操作不是"可恢复的".
    修改临时表中的一个块时,不会将这个修改记录到重做日志文件中
    不过,临时表确实会生成undo,而且这个undo 会计入日志。因此,临时表也会生成一些redo.
    为什么需要生成undo?这是因为你能回滚到事务中的一个SAVEPOINT
    临时表可以有约束,正常表有的一切临时表都可以有
    可能有一条INSERT 语句要向临时表中插入500 行,但插入到第500 行时失败了,
    这就要求回滚这条语句
    由于临时表一般表现得就像"正常"表一样,所以临时表必须生成undo
    由于undo 数据必须建立日志,因此临时表会为所生成的undo 生成一些重做日志
    redo的出现是为了保护undo
    不过,在临时表上运行的SQL 语句主要是INSERT 和SELECT
    INSERT 只生成极少的undo 另外SELECT 根本不生成undo
    所以,临时表的redo是因为要undo生成,实际上多数使用临时表的情况是用于查询
    因此往往没有undo的需求
    ㈢ 临时表的使用场景
    ① 循环SQL
    最初优化前,系统中有很多类似的sql循环执行,效率很低
    比如通常会有通过接口传入数千的参数,然后根据这些参数循环执行sql
    优化时,可先把这些参数insert到临时表,然后关联该临时表一次执行以达到优化的效果
    ② 多表关联
    利用临时表简化有太多表关联的复杂SQL
    ③ 如果某个数据集会重复多次使用的情况下建议使用临时表
    ④ 临时表在逻辑复杂的大数据量更新的时候很有用,查询部分with就可以了
    ⑤ 临时表作为复杂查询条件的中间结果用于主查询

    有帮助
    (0)
    0%
    没帮助
    (0)
    0%