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_CURREN T VALUES LESS THAN (20031101),
PARTITION PAGES_4M_HIGHVA L 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_E DAS
STORAGE CTXSYS.DEFAULT_ STORAGE
STOPLIST STOPLIST_BASIC_ EDAS
MEMORY 35M');
CREATE UNIQUE INDEX PAGES_4M_IDX ON PAGES_4M(LOADDA TE, 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_i ndexes=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.page s'
BADFILE 'EXTRBPO.1.page s.bad'
DISCARDFILE 'EXTRBPO.1.page s.discard'
APPEND INTO TABLE PAGES_4M
Partition (PAGES_4M_CURRE NT)
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
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_CURREN T VALUES LESS THAN (20031101),
PARTITION PAGES_4M_HIGHVA L 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_E DAS
STORAGE CTXSYS.DEFAULT_ STORAGE
STOPLIST STOPLIST_BASIC_ EDAS
MEMORY 35M');
CREATE UNIQUE INDEX PAGES_4M_IDX ON PAGES_4M(LOADDA TE, 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_i ndexes=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.page s'
BADFILE 'EXTRBPO.1.page s.bad'
DISCARDFILE 'EXTRBPO.1.page s.discard'
APPEND INTO TABLE PAGES_4M
Partition (PAGES_4M_CURRE NT)
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