Home » Server Options » Text & interMedia » Help with a CONTEXT index (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0)
Help with a CONTEXT index [message #403099] Wed, 13 May 2009 14:24 Go to next message
kcboyes
Messages: 3
Registered: May 2009
Junior Member
I have a table with a column
SERVICE_DESCRIPTION VARCHAR2(100) NOT NULL


I've created a CONTEXT index on it as such:
  CREATE INDEX XIC ON SRV ("SERVICE_DESCRIPTION") 
   INDEXTYPE IS "CTXSYS"."CONTEXT" PARAMETERS ('stoplist ctxsys.empty_stoplist SYNC (ON COMMIT)')


The table has about 468,000 rows (if that matters)

I can run the following query just fine against the table:
select * from SRV where contains(service_description, '2% & quart%') > 0;


but if I replace the search condition with
'1% & quart%'
(replace the 2 with a 1) then the search fails with the following error:

ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-51030: wildcard query expansion resulted in too many terms

My question is why would it fail with the slightly different search condition?
Re: Help with a CONTEXT index [message #403103 is a reply to message #403099] Wed, 13 May 2009 14:49 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Could you post some sample data?
Meanwhile,
this could be a possible reason.
http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/cdatadic.htm#sthref808
Re: Help with a CONTEXT index [message #403108 is a reply to message #403103] Wed, 13 May 2009 15:19 Go to previous messageGo to next message
kcboyes
Messages: 3
Registered: May 2009
Junior Member
I don't think I'm allowed to post the actual data but I can post some statistics about it.

There are 468270 rows in the table with 413859 distinct values in the service_description column. The column describes a service or a product using english words and part numbers and codes.

There are 232697 rows in the table that are like '%1%' in the service_description column and 195442 like '%2%'. I can give additional stats if you tell me what might be helpful.

The query works if I just search for 'quart%'

Thanks for the link. I've looked at discussions about raising the maximum terms but I'd first like to understand the error before making changes like that.

For example, why does '1% quart%' produce too many terms and yet the search for '2% quart%' doesn't. Maybe I don't know what "maximum allowed terms in a wildcard expansion" means. Would anyone be able to explain it?

Re: Help with a CONTEXT index [message #403113 is a reply to message #403108] Wed, 13 May 2009 15:40 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Apart from documentation, this might help.
http://www.orafaq.com/forum/t/100465/0/
And it seems in 10g the default value is way less.

Edit:
Wrong URL

[Updated on: Wed, 13 May 2009 15:42]

Report message to a moderator

Re: Help with a CONTEXT index [message #403120 is a reply to message #403108] Wed, 13 May 2009 16:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
If you have values like 11 and 12 and 13 in your indexed column, then when you search for 1% it is like searching for 11 or 12 or 13. It expands the query by adding an or condition for each term that matches the 1%. It does this for each DISTINCT indexed token that meets the criteria. In the following demonstration, the wildcard_maxterms is set to 8. Although there are 10 rows that match 1% and 10 rows that match 2%, there are only 5 DISTINCT tokens that match 2%. So, the query on 2% does not result in an error, but the query on 1% does produce an error. After that, the wildcard_maxterms is changed to 10, so neither query produces an error. When you do not set the wildcard_maxterms, it is set to the default, which is less than the maximum allowed. When you encounter this error, you can either increase the wildcard_maxterms or make your query more specific. There will always be some queries that are too general and you should handle the exception and return an understandable message to the user, indicating that they need to try a more specific query.

SCOTT@orcl_11g> CREATE TABLE srv
  2    (service_description  VARCHAR2 (100) NOT NULL)
  3  /

Table created.

SCOTT@orcl_11g> INSERT INTO srv SELECT '2' || ROWNUM FROM DUAL CONNECT BY LEVEL <= 5
  2  /

5 rows created.

SCOTT@orcl_11g> INSERT INTO srv SELECT '2' || ROWNUM FROM DUAL CONNECT BY LEVEL <= 5
  2  /

5 rows created.

SCOTT@orcl_11g> INSERT INTO srv SELECT '1' || ROWNUM FROM DUAL CONNECT BY LEVEL <= 10
  2  /

10 rows created.

SCOTT@orcl_11g> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('your_wordlist', 'BASIC_WORDLIST');
  3    CTX_DDL.SET_ATTRIBUTE ('your_wordlist', 'WILDCARD_MAXTERMS', 8);
  4  END;
  5  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE INDEX XIC ON SRV ("SERVICE_DESCRIPTION")
  2  INDEXTYPE IS "CTXSYS"."CONTEXT"
  3  PARAMETERS
  4    ('stoplist  ctxsys.empty_stoplist
  5  	 SYNC	   (ON COMMIT)
  6  	 WORDLIST  your_wordlist')
  7  /

Index created.

SCOTT@orcl_11g> SELECT token_text FROM dr$xic$i ORDER BY token_text
  2  /

TOKEN_TEXT
----------------------------------------------------------------
11
110
12
13
14
15
16
17
18
19
21
22
23
24
25

15 rows selected.

SCOTT@orcl_11g> SELECT * FROM srv WHERE CONTAINS (service_description, '2%') > 0
  2  /

SERVICE_DESCRIPTION
--------------------------------------------------------------------------------
21
22
23
24
25
21
22
23
24
25

10 rows selected.

SCOTT@orcl_11g> SELECT * FROM srv WHERE CONTAINS (service_description, '1%') > 0
  2  /
SELECT * FROM srv WHERE CONTAINS (service_description, '1%') > 0
*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-51030: wildcard query expansion resulted in too many terms


SCOTT@orcl_11g> BEGIN
  2    CTX_DDL.SET_ATTRIBUTE ('your_wordlist', 'WILDCARD_MAXTERMS', 15);
  3  END;
  4  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> DROP INDEX xic
  2  /

Index dropped.

SCOTT@orcl_11g> CREATE INDEX XIC ON SRV ("SERVICE_DESCRIPTION")
  2  INDEXTYPE IS "CTXSYS"."CONTEXT"
  3  PARAMETERS
  4    ('stoplist  ctxsys.empty_stoplist
  5  	 SYNC	   (ON COMMIT)
  6  	 WORDLIST  your_wordlist')
  7  /

Index created.

SCOTT@orcl_11g> SELECT * FROM srv WHERE CONTAINS (service_description, '2%') > 0
  2  /

SERVICE_DESCRIPTION
--------------------------------------------------------------------------------
21
22
23
24
25
21
22
23
24
25

10 rows selected.

SCOTT@orcl_11g> SELECT * FROM srv WHERE CONTAINS (service_description, '1%') > 0
  2  /

SERVICE_DESCRIPTION
--------------------------------------------------------------------------------
11
12
13
14
15
16
17
18
19
110

10 rows selected.

SCOTT@orcl_11g> 


Re: Help with a CONTEXT index [message #403124 is a reply to message #403120] Wed, 13 May 2009 17:17 Go to previous message
kcboyes
Messages: 3
Registered: May 2009
Junior Member
Thank you very much for such a detailed answer.
You've cleared up all of my questions!
Previous Topic: CTX_DOC
Next Topic: Select all strings that appear a substrings of a given string
Goto Forum:
  


Current Time: Thu Mar 28 07:06:40 CDT 2024