why so slowly?

[rabbit]

Hi all,
i want to creat a context index for clob column in oracle text. 2 MB
files(pdf, doc, xls, ppt, htm) are stored in this column. But it is
very slowly. I must c.a. 7 hours wait until index is created. I think,
it is not not correct. I set the Preferenz in such a way with java.

//lexer
cstmt = conn.prepareCall("{call ctx_ddl.create_preference(?, ?)}");
cstmt.setString(1, "oracle_lexer_extern");
cstmt.setString(2, "BASIC_LEXER");
cstmt.execute();
cstmt = conn.prepareCall("{call ctx_ddl.set_attribute(?, ?, ?)}");
cstmt.setString(1, "oracle_lexer_extern");
cstmt.setString(2, "base_letter");
cstmt.setString(3, "yes");
cstmt.execute();
cstmt = conn.prepareCall("{call ctx_ddl.set_attribute(?, ?, ?)}");
cstmt.setString(1, "oracle_lexer_extern");
cstmt.setString(2, "index_stems");
cstmt.setString(3, "german");
cstmt.execute();
cstmt = conn.prepareCall("{call ctx_ddl.set_attribute(?, ?, ?)}");
cstmt.setString(1, "oracle_lexer_extern");
cstmt.setString(2, "printjoins");
cstmt.setString(3, "_-");
cstmt.execute();

//wordlist
cstmt = conn.prepareCall("{call ctx_ddl.create_preference(?, ?)}");
cstmt.setString(1, "oracle_wordlist_extern");
cstmt.setString(2, "BASIC_WORDLIST");
cstmt.execute();
cstmt = conn.prepareCall("{call ctx_ddl.set_attribute(?, ?, ?)}");
cstmt.setString(1, "oracle_wordlist_extern");
cstmt.setString(2, "stemmer");
cstmt.setString(3, "German");
cstmt.execute();
cstmt = conn.prepareCall("{call ctx_ddl.set_attribute(?, ?, ?)}");
cstmt.setString(1, "oracle_wordlist_extern");
cstmt.setString(2, "fuzzy_match");
cstmt.setString(3, "German");
cstmt.execute();
cstmt = conn.prepareCall("{call ctx_ddl.set_attribute(?, ?, ?)}");
cstmt.setString(1, "oracle_wordlist_extern");
cstmt.setString(2, "substring_index");
cstmt.setString(3, "true");
cstmt.execute();
cstmt = conn.prepareCall("{call ctx_ddl.set_attribute(?, ?, ?)}");
cstmt.setString(1, "oracle_wordlist_extern");
cstmt.setString(2, "prefix_index");
cstmt.setString(3, "true");
cstmt.execute();

String index_str = "CREATE INDEX oracle_index_extern ON
texttable_intern(content) INDEXTYPE IS ctxsys.context PARAMETERS('
DATASTORE CTXSYS.DIRECT_DATASTORE
FILTER CTXSYS.INSO_FILTER format column text_typ
LEXER oracle_lexer_extern
WORDLIST oracle_wordlist_extern')";

stmt.execute(index_str);

Is it correct? If ja, what's wrong with the creating index? Can you
tell me? (I use Oracle 9i under windows server 2003)

Thank you very much and greeting aus germany!


rabbit

[Niall Litchfield]

7 hours seems an excessively long time for index creation I agree.On the
other hand trying to do this through Java rather than as part of schema
creation using SQL seems remarkably daft as well. What happens if you use
good old sqlplus?



--
Niall Litchfield
Oracle DBA
Audit Commission Uk
"rabbit" <zhoudiwen@hotmail.com> wrote in message
news:6a950fe.0310281422.12b8091e@posting.google.com... Hi all, i want to creat a context index for clob column in oracle text. 2 MB files(pdf, doc, xls, ppt, htm) are stored in this column. But it is very slowly. I must c.a. 7 hours wait until index is created. I think, it is not not correct. I set the Preferenz in such a way with java. //lexer cstmt = conn.prepareCall("{call ctx_ddl.create_preference(?, ?)}"); cstmt.setString(1, "oracle_lexer_extern"); cstmt.setString(2, "BASIC_LEXER"); cstmt.execute(); cstmt = conn.prepareCall("{call ctx_ddl.set_attribute(?, ?, ?)}"); cstmt.setString(1, "oracle_lexer_extern"); cstmt.setString(2, "base_letter"); cstmt.setString(3, "yes"); cstmt.execute(); cstmt = conn.prepareCall("{call ctx_ddl.set_attribute(?, ?, ?)}"); cstmt.setString(1, "oracle_lexer_extern"); cstmt.setString(2, "index_stems"); cstmt.setString(3, "german"); cstmt.execute(); cstmt = conn.prepareCall("{call ctx_ddl.set_attribute(?, ?, ?)}"); cstmt.setString(1, "oracle_lexer_extern"); cstmt.setString(2, "printjoins"); cstmt.setString(3, "_-"); cstmt.execute(); //wordlist cstmt = conn.prepareCall("{call ctx_ddl.create_preference(?, ?)}"); cstmt.setString(1, "oracle_wordlist_extern"); cstmt.setString(2, "BASIC_WORDLIST"); cstmt.execute(); cstmt = conn.prepareCall("{call ctx_ddl.set_attribute(?, ?, ?)}"); cstmt.setString(1, "oracle_wordlist_extern"); cstmt.setString(2, "stemmer"); cstmt.setString(3, "German"); cstmt.execute(); cstmt = conn.prepareCall("{call ctx_ddl.set_attribute(?, ?, ?)}"); cstmt.setString(1, "oracle_wordlist_extern"); cstmt.setString(2, "fuzzy_match"); cstmt.setString(3, "German"); cstmt.execute(); cstmt = conn.prepareCall("{call ctx_ddl.set_attribute(?, ?, ?)}"); cstmt.setString(1, "oracle_wordlist_extern"); cstmt.setString(2, "substring_index"); cstmt.setString(3, "true"); cstmt.execute(); cstmt = conn.prepareCall("{call ctx_ddl.set_attribute(?, ?, ?)}"); cstmt.setString(1, "oracle_wordlist_extern"); cstmt.setString(2, "prefix_index"); cstmt.setString(3, "true"); cstmt.execute(); String index_str = "CREATE INDEX oracle_index_extern ON texttable_intern(content) INDEXTYPE IS ctxsys.context PARAMETERS(' DATASTORE CTXSYS.DIRECT_DATASTORE FILTER CTXSYS.INSO_FILTER format column text_typ LEXER oracle_lexer_extern WORDLIST oracle_wordlist_extern')"; stmt.execute(index_str); Is it correct? If ja, what's wrong with the creating index? Can you tell me? (I use Oracle 9i under windows server 2003) Thank you very much and greeting aus germany! rabbit

[Daniel Morgan]

Niall Litchfield wrote:
7 hours seems an excessively long time for index creation I agree.On theother hand trying to do this through Java rather than as part of schemacreation using SQL seems remarkably daft as well. What happens if you usegood old sqlplus?
Well if you're getting paid by the hour the job is worth less for one
thing. ;-)

--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)