Loading a Nested table

From: April Wells <awells_at_csedge.com>
Date: Thu, 03 Jan 2002 11:29:07 -0800
Message-ID: <F001.003E630A.20020103111847@fatcity.com>

Okay... I have been trying to help the programmer on our data warehouse project to get a nested table loaded... and we are failing miserably...

Oracle 817
AIX unix

We are reading a two tables in one schema, and loading result set into a table and a nested table in another schema. We run for hours and don't get anything in the nested table, be we can load it directly with insert statement...

This is the code that we are running to try to load it...

        INSERT INTO DWH0100.play_sa
                SELECT  a.acct_no,
                                a.claim_no,
                                a.accdnt_dt,
                                a.loca,
                                a.site,
                                cast(multiset(
                                        SELECT  b.spec_anlys_defn,
                                                        b.spec_anlys_cd,
                                                        b.spec_anlys_desc
                                         FROM STG0100.special_anlys
PARTITION (spec_anlys1) b
                                        WHERE a.acct_no = b.acct_no
                                          AND a.claim_no = b.claim_no
                                          AND (b.claim_no like '85933596%'
                                           OR  b.claim_no like '85933597%'))
                                AS typ_spec_anlys_nt)
                FROM ODS0100.claim a;

she did get the cursor version to insert 2 rows yesterday, but it took 16+ seconds for 2 rows in the play_sa table and 186 rows in the nested table.

Suggestions MOST welcome

begin 666 InterScan_Disclaimer.txt

M5&AE(&EN9F]R;6%T:6]N(&-O;G1A:6YE9"!I;B!T:&ES(&4M;6%I;"!I<R!S
M=')I8W1L>2!C;VYF:61E;G1I86P_at_86YD(&9O<B!T:&4@:6YT96YD960@=7-E
M(&]F('1H92!A9&1R97-S964@;VYL>3L@:70@;6%Y(&%L<V\@8F4@;&5G86QL

M>2!P<FEV:6QE9V5D(&%N9"]O<B!P<FEC92!S96YS:71I=F4N("!.;W1I8V4@

M:7,@:&5R96)Y(&=I=F5N('1H870_at_86YY(&1I<V-L;W-U<F4L('5S92!O<B!C
M;W!Y:6YG(&]F('1H92!I;F9O<FUA=&EO;B!B>2!A;GEO;F4@;W1H97(@=&AA
M;B!T:&4@:6YT96YD960@<F5C:7!I96YT(&ES('!R;VAI8FET960_at_86YD(&UA

M>2!B92!I;&QE9V%L+B @268@>6]U(&AA=F4@<F5C96EV960@=&AI<R!M97-S M86=E(&EN(&5R<F]R+"!P;&5A<V4@;F]T:69Y('1H92!S96YD97(@:6UM961I M871E;'D_at_8GD@<F5T=7)N(&4M;6%I;"X*"D-O<G!O<F%T92!3>7-T96US+"!) M;F,N(&AA<R!T86ME;B!E=F5R>2!R96%S;VYA8FQE('!R96-A=71I;VX@=&\@

M96YS=7)E('1H870_at_86YY(&%T=&%C:&UE;G0@=&\@=&AI<R!E+6UA:6P@:&%S
M(&)E96X@<W=E<'0_at_9F]R('9I<G5S97,N("!792!A8V-E<'0@;F\@;&EA8FEL
M:71Y(&9O<B!A;GD_at_9&%M86=E('-U<W1A:6YE9"!A<R!A(')E<W5L="!O9B!S

M;V9T=V%R92!V:7)U<V5S(&%N9"!A9'9I<V4@>6]U(&-A<G)Y(&]U="!Y;W5R M(&]W;B!V:7)U<R!C:&5C:W,@8F5F;W)E(&]P96YI;F<@86YY(&%T=&%C:&UE %;G0N#0H
end

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: April Wells
  INET: awells_at_csedge.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Jan 03 2002 - 13:29:07 CST