Re: Too bad performance of nested table insert operation

Min-Koo Seo wrote:
> Hello.
>
> I am running oracle 8i (8.1.7) in linux kernel version 2.4.25
> (redhat).
>
> What I want to do is migrate existing tables into nested table form to
> save storage. Example of existing table is as follows:
>
> Table K0
> --------
> ID VARCHAR2(11)
> LOC NUMBER
> TYPESTR CHAR(2)
> TYPELEN NUMBER
> LOOKAHEAD CHAR(7)
>

[large snip]

If your goal is to save space (and I'm not sure what you meant by an inverted index), why don't you just create the table K0 as IOT and compress ?

create table K0 (
typestr char(2),
typelen number,
lookahead char(7),
id char(2),
loc number,
primary key (typestr, typelen, lookahead, id)) organization index
compress 3;

You'll save tons of space. However, if this is appropriate for your application is everyones guess.

And if you get rid of that procedure and do your migration in one step, you might be better off.

Try this (not tested):

INSERT INTO K0_2 VALUES(R.TYPESTR, R.TYPELEN, R.LOOKAHEAD, R.ID_LOC) SELECT

         T1.TYPESTR,
         T1.TYPELEN,
         T1.LOOKAHEAD,
         CAST(
             MULTISET(
                 SELECT ID, LOC FROM K0 T2
                 WHERE T1.TYPESTR = T2.TYPESTR
                 AND T1.TYPELEN = T2.TYPELEN
                 AND T1.LOOKAHEAD = T2.LOOKAHEAD)
             AS ID_LOC_LIST) ID_LOC
     FROM
     (
         SELECT TYPESTR, TYPELEN, LOOKAHEAD
         FROM K0
         GROUP BY TYPESTR, TYPELEN, LOOKAHEAD
     ) T1;

It is Tom Kyte's mantra: If you can do it in SQL, then do it in SQL.

And don't commit in a loop, that calls for an ora-1555.

HTH
Holger Received on Thu May 27 2004 - 11:25:21 CDT