Help Center/ Data Warehouse Service/ Standard Data Warehouse (9.1.0.x)/ DWS Performance Tuning/ SQL Tuning Examples/ Cases: Modifying Statements That Cannot Be Pushed Down/ Case: The Type of Record in DUAL That Is Not a Real Table Can Not Be Shipped
Updated on 2026-05-29 GMT+08:00
View PDF

Case: The Type of Record in DUAL That Is Not a Real Table Can Not Be Shipped

Possible Cause

The optimizer finds the record column, but the main table for this column is not a real table. The most common cause is that the USING part of the MERGE INTO statement uses the FROM DUAL clause.

Solution: Rewrite the SQL statement to eliminate the DUAL clause.

Case 1: Using the Subquery f (SELECT 0 AS inv_id,0 AS ovs_pack_num,0 AS ovs_pack_id FROM DUAL)

Original statement

 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;

Rewritten statement

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;

Modification comparison

Parent Topic: Cases: Modifying Statements That Cannot Be Pushed Down