| Help with a CONTEXT index [message #403099] |
Wed, 13 May 2009 14:24  |
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 (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 #403108 is a reply to message #403103] |
Wed, 13 May 2009 15:19  |
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 #403120 is a reply to message #403108] |
Wed, 13 May 2009 16:10 |
 |
Barbara Boehmer
Messages: 9100
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>
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
