RE: Oracle text lookup...
- From: "Ray Feighery" <ray.feighery@xxxxxxxxxxxxxxxxxxxxx>
- To: "dcosta@xxxxxxx" <dcosta@xxxxxxx> , "robertgfreeman@xxxxxxxxx" <robertgfreeman@xxxxxxxxx>
- Date: Wed, 8 Nov 2006 11:24:52 +1100
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
