This discussion is archived
1 Reply Latest reply: May 23, 2012 2:14 AM by Herald ten Dam RSS

CONTAINS function problem

ElMazzaX Newbie
Currently Being Moderated
Hi all!!

i got a problem with contains function. i'm running oracle 11g.

if i execute this query:


SELECT x.lid as id,  x.sztitular as titular, e.szname as proName, x.szresumen  as resumen, b.sztitle  as catName, 
f.szname  as secName, d.szname  as cliName, x.datecreation as datecreation 

FROM CPR_PRACTICAL_CASE x,ctg_category b, CPR_CLIENT d, pro_product e, CAT_SECTOR f, CPR_PCASE_PRODUCT g, CPR_PCASE_SECTOR h, 

PRO_PRODUCTCATEGORY i WHERE  x.lid = g.lpcaseid and e.lid = g.lproductid and x.lid = h.lpcaseid 
and f.lid = h.lsectorid and x.lclientid = d.lid and i.lproductid = e.lid and  b.lid = i.lcategoryid 
AND x.szlocale = 'es-ES' AND  x.bavailable = '1'
i get 1 row as result with the column sztitular = "rodillos de medidas"

if i only add one more sentece to that query:
AND CONTAINS( x.sztitular, 'rodillos',1) >0
the query returns an empty set.. i really don't understand why because the term "rodillos" is present in the row's column called "sztitular"...

i've put an index on that column:
create index ITXT_TITULAR on CPR_PRACTICAL_CASE(sztitular) INDEXTYPE IS CTXSYS.CONTEXT;
any help?

many thanks!!!!
  • 1. Re: CONTAINS function problem
    Herald ten Dam Expert
    Currently Being Moderated
    Hi,

    did the row existed before creating the index or did you add the row after creating? You have created the index without a sync option, so a new row will not be reflected in index, only after a sync.
    A quick sync goes with
    begin
      ctx_ddl.sync_index('ITXT_TITULAR' ); 
    end;
    Furthermore you could add a sync to the index creation, after that every commit will also sync the index:
       create index ITXT_TITULAR on CPR_PRACTICAL_CASE(sztitular) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('SYNC ( ON COMMIT)') ;
    Herald ten Dam
    http://htendam.wordpress.com

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points