RE: Oracle text lookup...

From: Ray Feighery <ray.feighery_at_seertechsolutions.com>
Date: Wed, 8 Nov 2006 11:24:52 +1100
Message-ID: <20061108112452790.00000003856@Ray>

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:24:52 CST