Too bad performance of nested table insert operation

From: Min-Koo Seo <pool0078_at_hanmail.net>
Date: 26 May 2004 09:51:13 -0700
Message-ID: <4c458db8.0405260851.2b8bc3fd@posting.google.com>

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) In this table, I've made B+ tree multicolumn index on TYPESTR+TYPELEN+LOOKAHEAD column. However, there are too many duplicates in TYPESTR+TYPELEN+LOOKAHEAD, so I want to make those columns as parent table, and let other columns exist in nested table. (Actually, I want this migration as an alternative implementation of inverted index.)

So I proceeded as follows:

CREATE TYPE ID_LOC AS OBJECT
(ID VARCHAR2(11),
 LOC NUMBER)
/

CREATE TYPE ID_LOC_LIST AS TABLE OF ID_LOC
/

CREATE TYPE K0_TYPE AS OBJECT
(TYPESTR CHAR(2),
 TYPELEN NUMBER,
 LOOKAHEAD CHAR(7),
 IDLOC ID_LOC_LIST)
/

CREATE TABLE K0_2 OF K0_TYPE
NESTED TABLE IDLOC STORE AS K0_2_IDLOC
STORAGE(MAXEXTENTS UNLIMITED)
PCTFREE 0
NOLOGGING
/

I've made following script to move existing data of K0 into K0_2.

CREATE OR REPLACE PROCEDURE POPULATE_K0_2 AS     CURSOR C IS
    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;
    R C%ROWTYPE;
    INSERT_CNT PLS_INTEGER := 0;
BEGIN
    OPEN C;
    DBMS_OUTPUT.PUT_LINE('CURSOR OPENED.');     LOOP

        INSERT_CNT := INSERT_CNT + 1;
        DBMS_OUTPUT.PUT_LINE(INSERT_CNT);
        
        FETCH C INTO R;
        
        
        IF C%NOTFOUND THEN
            EXIT;
        END IF;
        
        INSERT INTO K0_2 VALUES(R.TYPESTR, R.TYPELEN, R.LOOKAHEAD,
R.ID_LOC);
        
        IF INSERT_CNT MOD 100 = 0 THEN
            COMMIT;
        END IF;

    END LOOP;          COMMIT;          CLOSE C;
END;
/
    

Statistical data of K0 is as follows:
SQL> select count(1) from k0;

  COUNT(1)

---

  11744975

SQL> select count(distinct typestr||typelen||lookahead) from k0;

COUNT(DISTINCTTYPESTR||TYPELEN||LOOKAHEAD)

---

                                     25921

SQL> During monitoring the progress using v$session_longops and 'SELECT COUNT(1) FROM K0_2' command, I've found that actual full table scan and sort&merge of K0 tooks about 2~3 min while insert operations seem to be take forever.

It took about 1 day to insert 1000 rows. However, there are 25,921 rows to be inserted as you can see statistical analysis described above.

To enhance performance I've done the following: - Made the table nologging.
- Commit operation was fired every 100 row to reduce the nubmer of rollback extents allocations.

I can not find another way to improve perfomance of my migration process. v$session_wait and v$session_event does not show any distinguished indicators regarding performance to me. Plus, there's no concurrent transactions on this machine besides the migration.

Is this Oracle database system's innate performance limitation concerning Object features?

TIA.
Minkoo Seo. Received on Wed May 26 2004 - 11:51:13 CDT