RE: Oracle text lookup...

From: Ray Feighery <ray.feighery_at_seertechsolutions.com>
Date: Wed, 8 Nov 2006 13:16:35 +1100
Message-ID: <20061108131635867.00000002216@Ray>

This test is run on Oracle 10.2 on Linux.

The reason I wasn't getting any rows earlier is because "my" and "your" are stopwords on my 10.2 database.

select * from ORG_NAME where contains(NAME, '% ward')> 0 translates to:
Give me every row from ORG_NAME where the NAME column has the word "ward" preceded by any other word (as long as it is not a stopword).

But before it runs the query, it tries to resolve "%" into "every indexed word". For a small set of distinctive words, this is ok. But when the set of words is large, the error occurs. This is governed by the wildcard_maxterms parameter. I don't know what the default value is, but the maximum value you can set it to is 15,000.

In this example, I recreate the error but then avoid or rather, delay it by explicitly setting wildcard_maxterms to 15,000. I still hit the error if I add 22,000 rows to the table, but setting it explicitly seems to give me some more headroom. Incidentally, this has been flagged as Bug 4742903.

SQL> create table ORG_NAME (NAME varchar2(30));

Table created.

SQL>
SQL> insert into ORG_NAME select object_name from all_objects   2 where rownum < 21000;

20999 rows created.

SQL> create index ORG_NAME_NAME on ORG_NAME(NAME) INDEXTYPE IS ctxsys.context;

Index created.

SQL>

SQL> select      count(NAME)
  2  from        ORG_NAME
  3  where       contains(NAME, '% ward') > 0;

select count(NAME)
*
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

SQL>
SQL> drop index ORG_NAME_NAME;

Index dropped.

SQL> drop table ORG_NAME;

Table dropped.

SQL> begin

  2      ctxsys.Ctx_Ddl.drop_Preference('wildcard_pref');
  3      ctxsys.Ctx_Ddl.Create_Preference('wildcard_pref', 'BASIC_WORDLIST');
  4      ctxsys.ctx_ddl.set_attribute('wildcard_pref', 'wildcard_maxterms', 15000) ;

  5 end;
  6 /

PL/SQL procedure successfully completed.

SQL> create table ORG_NAME (NAME varchar2(30));

Table created.

SQL>
SQL> insert into ORG_NAME select object_name from all_objects   2 where rownum < 21000;

20999 rows created.

SQL> create index ORG_NAME_NAME on ORG_NAME(NAME) INDEXTYPE IS ctxsys.context parameters('Wordlist wildcard_pref');

Index created.

SQL>

SQL> select      count(NAME)
  2  from        ORG_NAME
  3  where       contains(NAME, '% ward') > 0;

COUNT(NAME)

---

          0

Ray

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Robert Freeman Sent: Wednesday, November 08, 2006 11:49 AM To: Ray Feighery; dcosta_at_lnec.pt
Cc: oracle-l
Subject: RE: Oracle text lookup...

This is on 10.2. I'll have to get an explain plan tomorrow. There are hits on Metalink that indicate that the % by itself is expanding out to everything and that this is causing the problem. I suspect the examples that you and others have given me are not hitting this failure because of a limited set of rows in your table. The table in questions has some 30k rows in it, can you retry your example on a larger table and see if you get the same results?

RF

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 07 2006 - 20:16:35 CST