Forum Stats

  • 3,815,707 Users
  • 2,259,070 Discussions
  • 7,893,212 Comments

Discussions

The Creation of Oracle TEXT index on huge table never finish

User_RLK2I
User_RLK2I Member Posts: 7 Blue Ribbon
edited Oct 30, 2019 10:34AM in Text

We want to move the huge table with one BLOB filed and Oracle TEXT index from Small file tablespace to bigfile tablespace . therefore we have created new table in bogfile tablespace and we inserted data from old tables to this new table .

But creating Oracle TEXT index with the following syntax takes so long time and it dose not finish even after 4 days of ruuning .

the small file tablespace is TBSA and the new bigfile tablespace is TBSB . the main purpose is to migrate to new bogfile tablespace.

the table creation script is as following:

create table NEW

(

a VARCHAR2(30) not null,

b BLOB,

c VARCHAR2(60) not null,

d VARCHAR2(60) not null

) TBSB

I have populated data in this new table in new table tablespace with the following command:

insert / * + APPEND NOLOGGING PARALLEL * / into NEW (select / * + PARALLEL * / * from OLD;

----------------------

I wanted to create Oracle Text index on this new table but it was extremly slow :

-- Create/Recreate indexes

create index IDX_NEW on NEW (b) indextype is CTXSYS.CONTEXT;

---------------------------------------------

Could you plesae tell us how to create new oracle TEXT in this new table ? and generaly if it is possible could you tell us another solution to migrate such a huge table from one tablespace to another tablespace with data and indexes including text index?

I have done the following steps also :

Hi,

the number of rows in source table with Text Index is 83102926. as I mentioned before it has BLOB filed .the table structure is :
SAP NEW
(
dok_key VARCHAR2(30) not null,
befund BLOB,
db_modtstamp VARCHAR2(60) not null,
db_stotstamp VARCHAR2(60) not null
)

the BLOB segment size is 9TB .
I executed the index creation with PARALLEL but it was extremly slow.
I extracted the index creation script from dbms_metadata from existing table .
CREATE INDEX IDX_NEW  ON NEW (BEFUND)
INDEXTYPE IS "CTXSYS"."CONTEXT" PARAMETERS ('filter ctxsys.auto_filter
LEXER CTXSYS.KISDC_BEFUND_LEXER
DATASTORE CTXSYS.DEFAULT_DATASTORE'
-----------------------------
I also run the create index to see the output logs but there was no log generated :

EXEC CTX_OUTPUT.START_LOG('ctx_log');

EXEC CTX_OUTPUT.ADD_EVENT(CTX_OUTPUT.EVENT_INDEX_PRINT_ROWID);

CREATE INDEX  IDX_REOG2_LF ON NEW (BEFUND)

INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('filter ctxsys.auto_filter

LEXER CTXSYS.KISDC_BEFUND_LEXER

DATASTORE CTXSYS.DEFAULT_DATASTORE');

mNem

Answers

  • Timo Hahn
    Timo Hahn Senior Principal Technical Consultant - Oracle ACE Director Member, Moderator Posts: 38,280 Red Diamond
    edited Oct 30, 2019 8:22AM

    User, there is a specialized space for Oracle Text question in the MOS Community . If you have a support contract with Oracle I strongly recommend to ask this question over there.

    Timo

  • mNem
    mNem Member Posts: 1,380 Gold Trophy
    edited Oct 30, 2019 9:01AM

    Alternatively, you may also use move this question to

  • Timo Hahn
    Timo Hahn Senior Principal Technical Consultant - Oracle ACE Director Member, Moderator Posts: 38,280 Red Diamond
    edited Oct 30, 2019 9:19AM

    ***Moderator action (Timo): moved over to Text.***

    mNem
  • Bud Light
    Bud Light Member Posts: 70 Blue Ribbon
    edited Oct 30, 2019 9:25AM

    I probably can't help much with speeding it up.  Indexing 9TB will take a while.

    I just wanted to let you know you can see how far along it is with:

    select idx_docid_count from ctx_user_indexes where idx_name='IDX_NEW';

    Might help Roger when he shows up, to go ahead and post your Oracle version (all 4 numbers).

  • Roger Ford-Oracle
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Oct 30, 2019 10:34AM

    As Bud says, indexing 83 million binary documents will take a long time - 4 days isn't at all unusual.

    Where did you look for the indexing log file? In 12.1 and before it will be in $ORACLE_HOME/ctx/log

    In 12.2 and later it will write into RDBMS log files, see this blog post: https://blogs.oracle.com/searchtech/oracle-text-indexes-new-logging-and-tracing-methods-in-12cr2

    What kind of content is in the table? As it's a BLOB column it will normally be filtered by the Oracle Text AUTO_FILTER which identifies the file format such as MSWord, PDF, etc, and filters those into plain text for indexing. This is the slowest part of the index creation. If, for some reason, you have plain text in the BLOB column then you can explicitly tell it to use NULL_FILTER instead.

    Other things you can do to improve indexing speed is to increase the degree of parallelism - I would suggest setting it to between half and one times the number of CPU cores you have available - and increasing the MEMORY parameter for the indexing. Beware that the index memory value is applied to each process, so if you use PARALLEL 8 and MEMORY 1G then it will use up to 8GB of PGA memory.  Make sure that your system has enough memory and that PGA_AGGREGATE_LIMIT in the database is set to a significantly higher value. You don't want to get close to the end and then have it fail because it's hit the PGA_AGGREGATE_LIMIT.