Home » Server Options » Text & interMedia » Error during Context Index Creation for a Table of 235 Million Records (2 threads merged by bb) (Oracle Text,10.2.0.4.0, Win XP)
Error during Context Index Creation for a Table of 235 Million Records (2 threads merged by bb) [message #446515] Tue, 09 March 2010 00:11 Go to next message
aditya532
Messages: 3
Registered: March 2010
Junior Member
Hi,

I am a beginner in Oracle Text, and have been extensively going through the available official documentation.
I have been trying to create a context index on a table of 235 Million Records using User_Datastore and MDATA section groups.
However, the index creation runs for 30-35 Hours and fails with the below mentioned error (happened all 2-3 times that i tried):-

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Mar 8 12:23:57 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

'Creating the context index xxcss_qot_line_ctx_indx'
CREATE INDEX xxcss_qot_line_ctx_indx
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in dreii0fsh
ORA-01400: cannot insert NULL into
("APPS"."DR$XXCSS_QOT_LINE_CTX_INDX$I"."TOKEN_TEXT")
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364


Nothing is available in ctx_user_index_errors or logs.
The index create script and the relevant parameter preferences that i had used are below :-

CREATE INDEX xxcss_qot_line_ctx_indx
ON apps.xxcss_qot_lines_search_bk (dummy)
INDEXTYPE IS ctxsys.CONTEXT
PARAMETERS ('
 datastore xxcss_qot_lines_search_ds 
 section group xxcss_qot_lines_search_sg 
 storage xxcss_qot_lines_search_sf 
 filter ctxsys.null_filter 
 lexer xxcss_qot_lines_search_lx 
 stoplist ctxsys.empty_stoplist 
 memory 50m transactional'
 );


/* Formatted on 2010/03/09 11:16 (Formatter Plus v4.8.8) */
BEGIN
   ctx_ddl.create_preference ('xxcss_qot_lines_search_ds', 'user_datastore');
   ctx_ddl.set_attribute
                      ('xxcss_qot_lines_search_ds',
                       'procedure',
                       'apps.xxcss_qot_search_mdata_b.lines_search_indx_proc'
                      );
END;

BEGIN
   ctx_ddl.create_section_group ('xxcss_qot_lines_search_sg',
                                 'html_section_group'
                                );
   ctx_ddl.add_mdata_section ('xxcss_qot_lines_search_sg',
                              'quotelineid',
                              'quotelineid'
                             );
   ctx_ddl.add_mdata_section ('xxcss_qot_lines_search_sg',
                              'quoteheaderid',
                              'quoteheaderid'
                             );
   ctx_ddl.add_mdata_section ('xxcss_qot_lines_search_sg',
                              'productname',
                              'productname'
                             );
   ctx_ddl.add_mdata_section ('xxcss_qot_lines_search_sg',
                              'installsite',
                              'installsite'
                             );
   ctx_ddl.add_mdata_section ('xxcss_qot_lines_search_sg',
                              'servicelevel',
                              'servicelevel'
                             );
   ctx_ddl.add_mdata_section ('xxcss_qot_lines_search_sg',
                              'serialnumber',
                              'serialnumber'
                             );
   ctx_ddl.add_mdata_section ('xxcss_qot_lines_search_sg',
                              'scontractnum',
                              'scontractnum'
                             );
   ctx_ddl.add_mdata_section ('xxcss_qot_lines_search_sg',
                              'tcontractnum',
                              'tcontractnum'
                             );
   ctx_ddl.add_mdata_section ('xxcss_qot_lines_search_sg',
                              'projectnumber',
                              'projectnumber'
                             );
   ctx_ddl.add_mdata_section ('xxcss_qot_lines_search_sg',
                              'asptqotnumber',
                              'asptqotnumber'
                             );
END;

BEGIN
   ctx_ddl.create_preference ('xxcss_qot_lines_search_lx', 'BASIC_LEXER');
   ctx_ddl.set_attribute ('xxcss_qot_lines_search_lx', 'INDEX_THEMES', 'NO');
END;

BEGIN
   ctx_ddl.create_preference ('xxcss_qot_lines_search_wdl', 'BASIC_WORDLIST');
   ctx_ddl.set_attribute ('xxcss_qot_lines_search_wdl', 'STEMMER', 'ENGLISH');
   ctx_ddl.set_attribute ('xxcss_qot_lines_search_wdl',
                          'FUZZY_MATCH',
                          'GENERIC'
                         );
END;

BEGIN
   ctx_ddl.create_preference ('xxcss_qot_lines_search_sf', 'basic_storage');
END;

BEGIN
   ctx_ddl.set_attribute
             ('xxcss_qot_lines_search_sf',
              'i_table_clause',
              'tablespace xxcssd1 storage (initial 1024M next 20M) nologging'
             );
END;

BEGIN
   ctx_ddl.set_attribute ('xxcss_qot_lines_search_sf',
                          'k_table_clause',
                          'tablespace xxcssd1 nologging'
                         );
END;

BEGIN
   ctx_ddl.set_attribute
      ('xxcss_qot_lines_search_sf',
       'r_table_clause',
       'tablespace xxcssd1 lob(data)store as (disable storage in row nocache nologging) nologging'
      );
END;

BEGIN
   ctx_ddl.set_attribute ('xxcss_qot_lines_search_sf',
                          'n_table_clause',
                          'tablespace xxcssd1 nologging'
                         );
END;

BEGIN
   ctx_ddl.set_attribute
      ('xxcss_qot_lines_search_sf',
       'i_index_clause',
       'tablespace xxcssd1 storage (initial 256M next 20M)compress 2 nologging'
      );
END;


Attached herewith is index description file. Kindly advise as to what is causing the above error.
Please note, that the same index creation works on an identical table, albeit with much lesser records.
Please do let me know if i need to supply any other data and i will do it ASAP.

Thanks,
Aditya
  • Attachment: IND_DESC.txt
    (Size: 3.72KB, Downloaded 195 times)
Re: Error during Context Index Creation for a Table of 235 Million Records (2 threads merged by bb) [message #446643 is a reply to message #446515] Tue, 09 March 2010 15:14 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7860
Registered: November 2002
Location: California, USA
Senior Member
aditya532 wrote on Mon, 08 March 2010 22:11

Please note, that the same index creation works on an identical table, albeit with much lesser records.


That would seem to indicate that the problem is related to size limitations of some kind. Please see the following section of the online documentation for suggestions on parameter settings related to size:

http://download.oracle.com/docs/cd/B28359_01/text.111/b28303/aoptim.htm#i1006756
Re: Error during Context Index Creation for a Table of 235 Million Records (2 threads merged by bb) [message #446733 is a reply to message #446515] Wed, 10 March 2010 03:30 Go to previous messageGo to next message
aditya532
Messages: 3
Registered: March 2010
Junior Member
Thanks Barbara for checking on this. I did go through the doc for improving indexing performance.
As of now, I am considering to further increase the default_index_memory setting and try the 'alter index rebuild resume' on this.

However, considering that this issue could be due to the large data (since index with much lesser data was successful)would it be fine to create the index using 'nopopulate' and then sync/rebuild the index in batches?
just a thought..kindly let me know.
Re: Error during Context Index Creation for a Table of 235 Million Records (2 threads merged by bb) [message #446776 is a reply to message #446733] Wed, 10 March 2010 07:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7860
Registered: November 2002
Location: California, USA
Senior Member
aditya532 wrote on Wed, 10 March 2010 01:30

However, considering that this issue could be due to the large data (since index with much lesser data was successful)would it be fine to create the index using 'nopopulate' and then sync/rebuild the index in batches?


If your problem is insufficient permanent storage space, then that won't solve it. However if your problem is insufficient temporary processing space, then processing it in batches might be a good solution.


Re: Error during Context Index Creation for a Table of 235 Million Records (2 threads merged by bb) [message #459410 is a reply to message #446515] Fri, 04 June 2010 12:45 Go to previous messageGo to next message
aditya532
Messages: 3
Registered: March 2010
Junior Member
Hi Barbara,

Pardon me for bringing this up again suddenly.
I did try creating the index in batches by using nopopulate and pumping-synching data bit by bit into the indexed table.
I also ensured that the tablespaces containing the dr$ have ample storage capacity (permanent). Also my temp tablespaces seem to be large enough to be able to support this.
However, the process fails after 70-75% of the total docids have been indexed and refuses to index any more.
Would be grateful for any lead/suggestion on what i could also try before I reach oracle support for this.

Many thanks,
Aditya
Re: Error during Context Index Creation for a Table of 235 Million Records (2 threads merged by bb) [message #459413 is a reply to message #459410] Fri, 04 June 2010 15:18 Go to previous message
Barbara Boehmer
Messages: 7860
Registered: November 2002
Location: California, USA
Senior Member
You could try the Oracle Text forum on the OTN discussion forums:

http://forums.oracle.com/forums/forum.jspa?forumID=71

Oracle Text product manager Roger Ford and a few other very knowledgeable people respond there frequently and have solved a lot of complex problems. The more detail that you provide the more likely you are to get a quick solution. For example, when you say that it refuses to index any more, they will want to know what sort of error message you get. They will want to know if you have used the Oracle Text utilities for monitoring index creation. You also need to specify whether you have optimized and committed, between batches of pumping and synchronizing.

Previous Topic: Mixed queries with Oracle Text
Next Topic: Oracle Text install
Goto Forum:
  


Current Time: Thu Apr 17 15:00:53 CDT 2014

Total time taken to generate the page: 0.09546 seconds