multi-table insert doesn't user direct load with append hint

From: Jaap W. van Dijk <j.w.vandijk_at_hetnet.nl>
Date: 30 May 2006 03:02:01 -0700
Message-ID: <1148983321.675632.198390@j33g2000cwa.googlegroups.com>

Oracle 9.2.0.5 on OPEN-VMS

I'm inserting records with a multi-table INSERT. If I retrieve the statement from V$SQL_TEXT and put it through EXPLAIN PLAN I get the following output:

  ID CARDINALITY ACTION
---- -----------

---

   0 59946180 INSERT STATEMENT

   1              MULTI-TABLE INSERT
   2               DIRECT LOAD INTO ht_jvd_beh_part
   3               DIRECT LOAD INTO ht_jvd_beh_exch
   4               DIRECT LOAD INTO ht_jvd_beh_exch
   5                SEQUENCE ht_mdr_beh_seq
   6    59946180     VIEW
   7                  UNION-ALL
   8                   FILTER
   9                    HASH JOIN OUTER
  10    29985660         TABLE ACCESS FULL d2_jvd_beh
  11    29960520         TABLE ACCESS FULL ht_jvd_beh_part (11:2-2)
  12                   FILTER
  13                    HASH JOIN OUTER
  14    29960520         TABLE ACCESS FULL ht_jvd_beh_part (14:2-2)
  15    29985660         TABLE ACCESS FULL d2_jvd_beh

But if I check v$SQL_PLAN I see the following:

  ID CARDINALITY ACTION
---- -----------

---

   0             INSERT STATEMENT
   1               MULTI-TABLE INSERT
   2                 SEQUENCE
   3    59946180       VIEW
   4                     UNION-ALL
   5                       FILTER
   6                         HASH JOIN OUTER
   7    29985660               TABLE ACCESS FULL d2_jvd_beh
   8    29960520               TABLE ACCESS FULL ht_jvd_beh_part
(8:2-2)
   9                       FILTER
  10                         HASH JOIN OUTER
  11    29960520               TABLE ACCESS FULL ht_jvd_beh_part
(11:2-2)
  12    29985660               TABLE ACCESS FULL d2_jvd_beh

Why the difference and how can I remedy this?

The first part of the statement looks like this (generated from Oracle Warehouse Builder):

INSERT /*+ APPEND noPARALLEL(HT_JVD_BEH_PART) APPEND noPARALLEL(HT_JVD_BEH_EXCH) APPEND noPARALLEL(HT_JVD_BEH_EXCH) */ALL

   WHEN "VERGELIJKING$2" = 'O'
   THEN

      INTO "HT_JVD_BEH_PART"
           ("CJIBNRBEH", "IDSRTBEH", "VLGNRBEH",


            "DTAANVRBEH", "IDGEBR", "DTAKT", "DTUITRES",

            "DTRES", "TOEBEH", "IDROUTE",

            "LAST_INSERT_OR_CHANGE", "DWH_RUNNR_INSERT",

            "DWH_RUNNR_UPDATE", "DWH_BEH_KEY", "DWH_GELDIG_VAN",

            "DWH_GELDIG_TOT"

           )
    VALUES ("CJIBNRBEH_1$9", "IDSRTBEH_1$9", "VLGNRBEH_1$9",


            "DTAANVRBEH_1$9", "IDGEBR_1$9", "DTAKT_1$9",

"DTUITRES_1$9",


            "DTRES_1$9", "TOEBEH_1$9", "IDROUTE_1$9",

            "LAST_INSERT_OR_CHANGE_1$9", "DWH_RUNNR_INSERT$9",

            "DWH_RUNNUMMER", "DWH_BEH_KEY$9", "DWH_GELDIG_VAN$9",

            "DWH_PEILDATUM"

           )

   WHEN "VERGELIJKING$2" IN ('N', 'O')
   THEN

      INTO "HT_JVD_BEH_EXCH"
           ("CJIBNRBEH", "IDSRTBEH", "VLGNRBEH", "DTAANVRBEH",


            "IDGEBR", "DTAKT", "DTUITRES", "DTRES", "TOEBEH",

            "IDROUTE", "LAST_INSERT_OR_CHANGE", "DWH_RUNNR_INSERT",

            "DWH_BEH_KEY", "DWH_GELDIG_VAN", "DWH_GELDIG_TOT"

           )
    VALUES ("CJIBNRBEH$8", "IDSRTBEH$8", "VLGNRBEH$8", "DTAANVRBEH$8",


            "IDGEBR$8", "DTAKT$8", "DTUITRES$8", "DTRES$8", "TOEBEH$8",

            "IDROUTE$8", "LAST_INSERT_OR_CHANGE$8", "DWH_RUNNUMMER",

            "HT_MDR_BEH_SEQ".NEXTVAL, "DWH_PEILDATUM",

"DWH_GELDIG_TOT$8"
           )

   WHEN "VERGELIJKING$2" IN ('G', 'V')
   THEN

      INTO "HT_JVD_BEH_EXCH"
           ("CJIBNRBEH", "IDSRTBEH", "VLGNRBEH",


            "DTAANVRBEH", "IDGEBR", "DTAKT", "DTUITRES",

            "DTRES", "TOEBEH", "IDROUTE",

            "LAST_INSERT_OR_CHANGE", "DWH_RUNNR_INSERT",

            "DWH_BEH_KEY", "DWH_GELDIG_VAN", "DWH_GELDIG_TOT"

           )
    VALUES ("CJIBNRBEH_1$9", "IDSRTBEH_1$9", "VLGNRBEH_1$9",


            "DTAANVRBEH_1$9", "IDGEBR_1$9", "DTAKT_1$9",

"DTUITRES_1$9",


            "DTRES_1$9", "TOEBEH_1$9", "IDROUTE_1$9",

            "LAST_INSERT_OR_CHANGE_1$9", "DWH_RUNNR_INSERT$9",

            "DWH_BEH_KEY$9", "DWH_GELDIG_VAN$9", "DWH_GELDIG_TOT$9"

           )

   (SELECT ( Jaap. Received on Tue May 30 2006 - 05:02:01 CDT