sql*loader - partitions - domain index

From: stef <stef99R_at_hotmail.com>
Date: 8 Oct 2003 02:23:27 -0700
Message-ID: <5e7a0b07.0310080123.1f08c3b1_at_posting.google.com>

Hi,

I have a range partitioned (one partition = one month) table. On this table I have a local unique index and a local domain index (Oracle Text) on a CLOB column.

I'm running Oracle 9.2.0.1.0

CREATE TABLE PAGES_4M (
   LOADDATE NUMBER(8) NOT NULL,
   APPLID VARCHAR2(20) NOT NULL,
   FILEID NUMBER(5) NOT NULL,
   PAGEID NUMBER(8) NOT NULL,
   PAGE CLOB NOT NULL,
   FORMAT VARCHAR2(10)
   )
   LOB("PAGE") STORE AS (TABLESPACE "DATA")    PARTITION BY RANGE (LOADDATE)
   (

   PARTITION PAGES_4M_P1 VALUES LESS THAN (20030701), 
   PARTITION PAGES_4M_P2 VALUES LESS THAN (20030801), 
   PARTITION PAGES_4M_P3 VALUES LESS THAN (20030901), 
   PARTITION PAGES_4M_P4 VALUES LESS THAN (20031001), 
   PARTITION PAGES_4M_CURRENT VALUES LESS THAN (20031101), 
   PARTITION PAGES_4M_HIGHVAL VALUES LESS THAN (MAXVALUE)

    );

CREATE index fulltext_4M_idx on PAGES_4M(PAGE)

   INDEXTYPE is ctxsys.context
   LOCAL

   PARAMETERS ('FORMAT COLUMN  FORMAT             
                LEXER          PREF_LEXER_EDAS                 
                WORDLIST       PREF_WORDLIST_EDAS
                STORAGE        CTXSYS.DEFAULT_STORAGE 
                STOPLIST       STOPLIST_BASIC_EDAS 
                MEMORY         35M');

CREATE UNIQUE INDEX PAGES_4M_IDX ON PAGES_4M(LOADDATE, APPLID, FILEID, PAGEID) LOCAL COMPRESS; I do have to load a significant amount of data every day into one partition.
(using the partition parameter in sql*loader).

Using the conventional path and updating the domain index whilst loading works but is extremely slow.

I tried to mark the index partition unusable and use the option skip_unusable_indexes=true in sql*loader. The idea was to recreate the index afterwards using the dbms_pclxutil package (true paralellism).

Unfortunatly I receive a ORA-29954 : Domain index is marked unusable.

Is there a way around this ?

Another idea was to speed up the upload using direct path and keeping the domain index usable.

OPTIONS (DIRECT=TRUE)
Load Data
INFILE 'EXTRBPO.1.pages'
BADFILE 'EXTRBPO.1.pages.bad'
DISCARDFILE 'EXTRBPO.1.pages.discard'
APPEND INTO TABLE PAGES_4M
Partition (PAGES_4M_CURRENT)
FIELDS TERMINATED BY ";"
(LOADDATE CONSTANT "20031006",

 APPLID CHAR(20),
 FILEID CHAR(5),
 PAGEID CHAR(8),
 PAGE   CHAR(20000) ENCLOSED BY "<CLOB>",

 FORMAT CONSTANT "IGNORE") This time I received a SQL*Loader-926 : OCI-error as well as a ORA-26090 : row incomplete.

Has anyone a idea how to solve this ?

Thanks a lot Received on Wed Oct 08 2003 - 11:23:27 CEST