文档首页/ 数据仓库服务 DWS/ 更多文档/ 开发指南（联盟区域）/ DWS性能调优/ SQL调优案例/ 案例：典型不下推语句整改案例集/ 案例：Type of Record in dual that is not a real table can not be shipped
更新时间：2025-12-12 GMT+08:00
查看PDF
分享

案例：Type of Record in dual that is not a real table can not be shipped

问题根因

优化器识别到的record列，且record列对应的主表不是一个实体表。最常见的情况为MERGE INTO语句的USING部分使用了FROM dual字句导致问题出现。

处理方案：改写SQL，消除语句中的dual子句。

案例1：子查询f为(SELECT 0 AS inv_id,0 AS ovs_pack_num,0 AS ovs_pack_id FROM DUAL)

原始语句

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
MERGE INTO dwiscm.dwi_inv_balance_onhand t USING (
    SELECT
        f.inv_id,
        d.ovs_pack_id
    FROM (SELECT 0 AS inv_id,0 AS ovs_pack_num,0 AS ovs_pack_id FROM DUAL) f,
    dwicommon.dwi_inv_oversea_pack d
    WHERE f.ovs_pack_num = d.ovs_pack_num
) S ON (t.inv_id = s.inv_id)
WHEN MATCHED THEN UPDATE
SET t.ovs_pack_id = s.ovs_pack_id, t.last_upd_cycle_id = 20250301000000, t.dw_last_update_date = sysdate;

改写语句

1
2
3
4
5
6
7
8
9
 
MERGE INTO dwiscm.dwi_inv_balance_onhand t USING (
    SELECT
        0 AS inv_id,
        d.ovs_pack_id
    FROM dwicommon.dwi_inv_oversea_pack d
    WHERE 0 = d.ovs_pack_num
) S ON (t.inv_id = s.inv_id)
WHEN MATCHED THEN UPDATE
SET t.ovs_pack_id = s.ovs_pack_id, t.last_upd_cycle_id = 20250301000000, t.dw_last_update_date = sysdate;

修改点对比