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

On 30 May 2006 03:02:01 -0700, "Jaap W. van Dijk" <j.w.vandijk_at_hetnet.nl> wrote:

>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.
>

On second thought and some checking: I think the way V$SQL_PLAN shows the plan has got nothing to do with not using direct load, and the slow inserts are due to the slow way in which the SELECT produces the rows, so better ignore this thread.

Jaap. Received on Tue May 30 2006 - 13:08:15 CDT