RE: Oracle text lookup...

From: Robert Freeman <robertgfreeman_at_yahoo.com>
Date: Tue, 7 Nov 2006 17:48:40 -0700
Message-ID: <KEEDIPJOJLCHPPAIDPDOGECGCMAA.robertgfreeman@yahoo.com>

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

-----Original Message-----
From: Ray Feighery [mailto:ray.feighery_at_seertechsolutions.com] Sent: Tuesday, November 07, 2006 5:25 PM To: dcosta_at_lnec.pt; robertgfreeman_at_yahoo.com Cc: oracle-l
Subject: RE: Oracle text lookup...

Robert

Following on from Dias's example, the behaviour differs between Oracle 9.2 and 10.2. On 9.2 I get the same results as Dias; on 10.2 I get no rows.

Either way, you can get an explain plan of the search using this: create table xres (

      explain_id      varchar2(30),
      id              number,
      parent_id       number,
      operation       varchar2(30),
      options         varchar2(30),
      object_name     varchar2(64),
      position        number

    )
/

exec ctx_query.explain('ORG_NAME_NAME', '% ward' , 'xres');

select lpad(' ',2*(level-1))||level||'.'||position||' '||

            operation||' '||
            decode(options, null, null, options || ' ') ||
            object_name plan
       from xres
      start with id = 1

    connect by prior id = parent_id;

drop table xres;

On 9.2 this gives me:

PLAN

---

---

1.1 PHRASE
  2.1 EQUIVALENCE %

    3.1 WORD BACKWARD
    3.2 WORD FORWARD
    3.3 WORD FRONTWARD
    3.4 WORD MY
    3.5 WORD WARD
    3.6 WORD YOUR

  2.2 WORD WARD So '% ward' is resolving to a two word phrase with the first word as anything in the table and the second word as ward. This sounds like what you want.

What is your version and platform, and what does your explain plan return?

Ray
> Fellow Oracle types...
>
> So, I have a query like this:
>
> select * from ORG_NAME where contains(NAME, '% ward')
>> 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
>
>
> What I want is to be able to bring back records like:
>
> My ward
> your ward
>
> and not bring back records like
>
> backward
> frontward

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

Received on Tue Nov 07 2006 - 18:48:40 CST