ORACLE WITH子句与未公开HINT MATERIALIZE

博客介绍了使用WITH与hint materialize结合的SQL查询方式,可在一定情况下取代全局临时表和物化视图以提高查询效率。适用于当次查询结果记录数不多、查询值和过滤条件含重复表达式、相同子查询被多次使用的场景。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

l id="commonList">
        WITH D AS(
            SELECT /*+materialize*/ * FROM (
                SELECT INNER_TABLE.*, ROWNUM OUTER_TABLE_ROWNUM FROM (
                    SELECT D.* FROM SP_SO_OUTAGE_PLAN D WHERE 1 = 1
                        <include refid="flowStateCondition" />
                        <include refid="queryCondition" />
                        <if test="sortFieldName !='' and sortFieldName !=null and sortType !='' and sortType !=null">
                            ORDER BY ${sortFieldName} ${sortType} NULLS LAST,D.ID
                        </if>
                   ) INNER_TABLE WHERE ROWNUM &lt;= #{END_NUM}
            ) OUTER_TABLE WHERE OUTER_TABLE_ROWNUM > #{START_NUM}
         )

结论:
    在一定的情况下,我们可以用with与hint materialize结合的方式取代全局临时表和物化视图来提高查询效率,使用情况如下。


1.      当次查询的结果记录数不是太多的情况下,如果结果记录集比较庞大,还是推荐使用GTT,因为那里面可以指定索引等。
2.      查询值和查询过滤条件中如果含有重复执行的表达式,可以用此方式减少一次计算。
3.      相同子查询被多次使用。

explain plan for SELECT t2.IDENTIFIERSDID identifiersd, t2.REASON_NAME title, t2.REASON content, t2.CREATETIME createTime, t1.CREATE_TIME_ arrivalTime, t1.PROC_INST_ID_ instanceId, t1.TASK_DEF_KEY_ taskNodeKey, t1.id_ taskId, t1.NAME_ taskName FROM ACT_RU_TASK t1 JOIN T_C_DY_PROVINCE_BXYGD t2 ON t1.PROC_INST_ID_ = t2.INSTANCE_ID WHERE t2.CREATETIME > to_date('2025-06-17 15:00:00', 'yyyy-MM-dd HH24:mi:ss') AND t1.ASSIGNEE_ = 'SYSTEM' AND t1.proc_def_id_ LIKE concat('bxyggd', ':%') AND t1.TASK_DEF_KEY_ = 'bc-bmscl' AND t1.PRE_GROUP_ID IN ('SDCMDYtest1') UNION ALL SELECT t2.IDENTIFIERSDID identifiersd, t2.REASON_NAME title, t2.REASON content, t2.CREATETIME createTime, t1.CREATE_TIME_ arrivalTime, t1.PROC_INST_ID_ instanceId, t1.TASK_DEF_KEY_ taskNodeKey, t1.id_ taskId, t1.NAME_ taskName FROM ACT_RU_TASK t1 JOIN T_C_DY_PROVINCE_BXYGD t2 ON t1.PROC_INST_ID_ = t2.INSTANCE_ID WHERE t2.CREATETIME > to_date('2025-06-17 15:00:00', 'yyyy-MM-dd HH24:mi:ss') AND t1.proc_def_id_ LIKE concat('bxyggd', ':%') AND t1.TASK_DEF_KEY_ = 'bc-bmscl' AND (t1.ASSIGNEE_ = '000000ct') UNION ALL SELECT t2.IDENTIFIERSDID identifiersd, t2.REASON_NAME title, t2.REASON content, t2.CREATETIME createTime, t1.CREATE_TIME_ arrivalTime, t1.PROC_INST_ID_ instanceId, t1.TASK_DEF_KEY_ taskNodeKey, t1.id_ taskId, t1.NAME_ taskName FROM ACT_RU_TASK t1 JOIN T_C_DY_PROVINCE_BXYGD t2 ON t1.PROC_INST_ID_ = t2.INSTANCE_ID WHERE t2.CREATETIME > to_date('2025-06-17 15:00:00', 'yyyy-MM-dd HH24:mi:ss') AND t1.proc_def_id_ LIKE concat('bxyggd', ':%') AND t1.TASK_DEF_KEY_ = 'bc-bmscl' AND (t1.ASSIGNEE_ = 'SDCMDY5b242609'); 为啥下边两union all ,T_C_DY_PROVINCE_BXYGD这个表可以走上索引,上边三个union all就全表扫描了 explain plan for SELECT t2.IDENTIFIERSDID identifiersd, t2.REASON_NAME title, t2.REASON content, t2.CREATETIME createTime, t1.CREATE_TIME_ arrivalTime, t1.PROC_INST_ID_ instanceId, t1.TASK_DEF_KEY_ taskNodeKey, t1.id_ taskId, t1.NAME_ taskName FROM ACT_RU_TASK t1 JOIN T_C_DY_PROVINCE_BXYGD t2 ON t1.PROC_INST_ID_ = t2.INSTANCE_ID WHERE t2.CREATETIME > to_date('2025-06-17 15:00:00', 'yyyy-MM-dd HH24:mi:ss') AND t1.ASSIGNEE_ = 'SYSTEM' AND t1.proc_def_id_ LIKE concat('bxyggd', ':%') AND t1.TASK_DEF_KEY_ = 'bc-bmscl' AND t1.PRE_GROUP_ID IN ('SDCMDYtest1') UNION ALL SELECT t2.IDENTIFIERSDID identifiersd, t2.REASON_NAME title, t2.REASON content, t2.CREATETIME createTime, t1.CREATE_TIME_ arrivalTime, t1.PROC_INST_ID_ instanceId, t1.TASK_DEF_KEY_ taskNodeKey, t1.id_ taskId, t1.NAME_ taskName FROM ACT_RU_TASK t1 JOIN T_C_DY_PROVINCE_BXYGD t2 ON t1.PROC_INST_ID_ = t2.INSTANCE_ID WHERE t2.CREATETIME > to_date('2025-06-17 15:00:00', 'yyyy-MM-dd HH24:mi:ss') AND t1.proc_def_id_ LIKE concat('bxyggd', ':%') AND t1.TASK_DEF_KEY_ = 'bc-bmscl' AND (t1.ASSIGNEE_ = '000000ct');
最新发布
06-27
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值