By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,058 Members | 1,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,058 IT Pros & Developers. It's quick & easy.

sql*loader - partitions - domain index

P: n/a
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
Jul 19 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.