This discussion is archived
1 2 3 Previous Next 38 Replies Latest reply: Dec 25, 2012 11:08 PM by 949210 Go to original post RSS
  • 15. Re: searching  in a materialized view
    949210 Newbie
    Currently Being Moderated
    yes
    but
    The CONTAINS clause with it's progressive relaxation will need to be constructed for each query, you can't replace just the "1234" in my example with a bind variable.
    i have no idea what among the 67000 rows would the end user search

    Edited by: 946207 on Dec 6, 2012 8:51 PM
  • 16. Re: searching  in a materialized view
    Roger Ford Expert
    Currently Being Moderated
    What I meant was, if you want to search for "678", for example, then you will have to construct a string
    <query>
      <textquery>
        <progression>
          <seq> 678% WITHIN S </seq>
          <seq> 678% WITHIN K </seq>
          <seq> 678% WITHIN C </seq>
          <seq> %678% WITHIN K </seq>
          <seq> %678% WITHIN S </seq>
          <seq> %678% WITHIN C </seq>
        </progression>
      </textquery>
    </query>
    and use that in your CONTAINS clause (either as a bind variable or a literal).

    So there's a small amount of coding to be done to contruct this search string, but it's not hard to do.
  • 17. Re: searching  in a materialized view
    949210 Newbie
    Currently Being Moderated
    i did not understand you. sorry
    select * from
      ( select prod_details, sign from prod_search_det2 
        where contains( prod_details, '
    <query>
      <textquery>
        <progression>
          <seq> <prefix_text> WITHIN S </seq>
          <seq> <prefix_text> WITHIN K </seq>
          <seq> <prefix_text> WITHIN C </seq>
          <seq> <prefix_text> WITHIN K </seq>
          <seq> <prefix_text> WITHIN S </seq>
          <seq> <prefix_text> WITHIN C </seq>
        </progression>
      </textquery>
    </query>
    ', 1) > 0
        order by score(1) desc
      )
    where rownum < 15
    / 
    will be how it is written on .NET
    what i dont understand is
    there's a small amount of coding to be done to contruct this search string, but it's not hard to do.
    whats the coding and on which construct?

    Edited by: 946207 on Dec 6, 2012 9:20 PM
  • 18. Re: searching  in a materialized view
    Roger Ford Expert
    Currently Being Moderated
    The second argument of the contains clause is a text search string. This can either be a simple search such as 'cat AND dog' or it can be a query template as shown in my example, beginning with <query> and ending with </query>. The query template contains the actual searchterms, so you must create a new query template each time you search for a new searchterm.

    I've extended by example with a PL/SQL function which creates the query template for the contains clause:
    set echo on
    set timing on
    
    drop table prod_search_det2
    /
    create table prod_search_det2 
      (prod_details varchar2(1000)
      ,sign varchar2(42)
      )
    /
    
    declare
      sign varchar2(1);
    begin
      for k in 1..67000 loop
        case mod(k, 3)
          when 0 then sign := 'K';
          when 1 then sign := 'S';
          else sign := 'C';
        end case;
        insert into prod_search_det2 values ( to_char(k), sign );
      end loop;
    end;
    /
    
    select count(*) from prod_search_det2
    /
    
    exec ctx_ddl.drop_section_group   ( 'my_secgroup' )
    exec ctx_ddl.create_section_group ( 'my_secgroup', 'BASIC_SECTION_GROUP' )
    exec ctx_ddl.add_field_section    ( 'my_secgroup', 'K', 'K' )
    exec ctx_ddl.add_field_section    ( 'my_secgroup', 'S', 'S' )
    exec ctx_ddl.add_field_section    ( 'my_secgroup', 'C', 'C' )
    
    exec ctx_ddl.drop_preference  ( 'my_datastore' )
    exec ctx_ddl.create_preference( 'my_datastore', 'MULTI_COLUMN_DATASTORE')
    exec ctx_ddl.set_attribute    ( 'my_datastore', 'COLUMNS', '''<''|| sign || ''>'' || prod_details || ''</''|| sign ||''>''' )
    
    exec ctx_ddl.drop_preference  ( 'my_wl' )
    exec ctx_ddl.create_preference( 'my_wl', 'BASIC_WORDLIST' )
    exec ctx_ddl.set_attribute    ( 'my_wl', 'SUBSTRING_INDEX', 'true' )
    
    create index prod_search_idx on prod_search_det2( prod_details )
    indextype is ctxsys.context
    parameters( 'section group my_secgroup datastore my_datastore wordlist my_wl')
    /
    
    -- simple query:
    select * from
      (select prod_details from prod_search_det2 
       where contains( prod_details, '%234% within K' ) > 0
      )
    where rownum < 15
    /
    
    column prod_details format a40
    column sign format a10
    
    -- create a progressive relaxation query
    
    create or replace function create_my_contains_clause( searchterm varchar2 ) 
    return varchar2 is 
    begin
      return '
    <query>
      <textquery>
        <progression>
          <seq> ' || searchterm || '% WITHIN S </seq>
          <seq> ' || searchterm || '% WITHIN K </seq>
          <seq> ' || searchterm || '% WITHIN C </seq>
          <seq> %' || searchterm || '% WITHIN K </seq>
          <seq> %' || searchterm || '% WITHIN S </seq>
          <seq> %' || searchterm || '% WITHIN C </seq>
        </progression>
      </textquery>
    </query>';
    end;
    /
    show err
    
    select * from
      ( select prod_details, sign from prod_search_det2 
        where contains( prod_details, create_my_contains_clause( '1234' ), 1) > 0
        order by score(1) desc
      )
    where rownum < 15
    /
    
    select * from
      ( select prod_details, sign from prod_search_det2 
        where contains( prod_details, create_my_contains_clause( '12345' ), 1) > 0
        order by score(1) desc
      )
    where rownum < 15
    /
  • 19. Re: searching  in a materialized view
    949210 Newbie
    Currently Being Moderated
    the query returns
    rows which have the word entered by end user anywhere in the row
    it returns all 's' rows first irrespective of the position of the search string
    it returns all 'k' rows second irrespective of the position of the search string
    it returns all 'c' rows third irrespective of the position of the search string
    :followed by that
    it does return 's'
    it does return 'k'
    it does return 'c'
    (i saw this by removing the '+where rownum<15+' clause, but based on what did it return the very first row of the output when it clearly did not start with the search string entered by the end user)
    :
    the query works fine for numbers

    ---one more observation------
    this query returns first three rows with the sign 's'
    1269th row with the sign 's'
    there is a pattern here
    the first three rows, although they dont start with the search string entered by the end user, the words(in this case one of the words) within the rows start with search string entered by the end user
    whereas
    the 1269th row has this (search string entered by the end user) at the end of the first word(that is any word in the 1269th row does not start with search string entered by the end user)
    please let me know if this does not make sense to you

    Edited by: 946207 on Dec 8, 2012 5:51 PM
  • 20. Re: searching  in a materialized view
    Roger Ford Expert
    Currently Being Moderated
    Sorry, I don't understand you. Please give examples of the results you're getting and the results you hope to get.

    I think you might be saying that it's a word-based search rather than a string-based search. That's correct, and that's how Oracle Text works. We can make it do string searches, even though that's not what it's designed for, so long as the strings are not longer than 64 characters.

    I'm not sure what you're saying about the 1269th row.
  • 21. Re: searching  in a materialized view
    949210 Newbie
    Currently Being Moderated
    what i get with the present query *(just assume that these numbers in the 2nd column are alphabets)*
    if i have searched for 1234, i get
    1      987435 123498343      S
    2      6898 64654 123478     S
    3      12346546 654654        S
    :
    :
    : in this portion there are 'k's and 'c's
    :
    :
    1269      781234 98565        S
    what i want
    1      12346546 654654           S
    2      12349687 645789           K
    3      123456789 05464           c           first set ends
    4      781234 98565                S
    5      851234674576               K
    6      9871234                        C           second set ends
    :
    of course in the firstset (s,k,c) and the secondset (s,k,c) there could be multiple s's, k's and c's
    in the first set of skc rows have to start(start of the first word) with what the end user has entered
    in the second set of skc rows can have what the end user has entered anywhere except start (start of the first word)


    Edited by: 946207 on Dec 8, 2012 6:51 PM

    <font color="red">assume that these numbers in the 2nd column are alphabets </font>

    Edited by: 946207 on Dec 10, 2012 3:45 PM

    Edited by: 946207 on Dec 10, 2012 3:52 PM

    Edited by: 946207 on Dec 10, 2012 3:54 PM
  • 22. Re: searching  in a materialized view
    949210 Newbie
    Currently Being Moderated
    I think you might be saying that it's a word-based search rather than a string-based search. That's correct.....
    Yes.
    Also
    SELECT MAX(LENGTH(PROD_DETAILS)) FROM MV_PROD_SEARCH_DET2;
    107
    select avg(LENGTH(PROD_DETAILS)) FROM MV_PROD_SEARCH_DET2;
    33.28
    select count(*) from mv_prod_search_det2 where length (prod_details)>64
    375
    I cannot avoid these 375.
    We can make it do string searches, even though that's not what it's designed for, so long as the strings are not >than 64 characters.
    select replace (prod_details,' ','') from prod_search_det2
    if we do this will there be a need to do a string based search
    Please post the subheading of the documentation which documents string based search using contains





    Edited by: 946207 on Dec 10, 2012 3:02 PM

    Edited by: 946207 on Dec 10, 2012 7:47 PM
  • 23. Re: searching  in a materialized view
    Roger Ford Expert
    Currently Being Moderated
    OK, try this. I've attached the special string XXX to the front of the first word in each document, so we can search for them separately and have them sorted to the top of the list.
    I've also changed the number-strings to random character strings - each row has four words of five random characters.

    This now returns:
    S, K and C rows, in that order, where the whole string starts with the search term
    S, K and C rows, in that order, where any word starts with the search term
    S, K and C rows, in that order, where any word contains the search term

    If you don't need the second of those then just delete the middle three sequences from the progressive relaxation.
    set echo on
    set timing on
    
    drop table prod_search_det2
    /
    create table prod_search_det2 
      (prod_details varchar2(1000)
      ,sign varchar2(42)
      )
    /
    
    declare
      sign varchar2(1);
    begin
      for k in 1..67000 loop
        case mod(k, 3)
          when 0 then sign := 'K';
          when 1 then sign := 'S';
          else sign := 'C';
        end case;
        insert into prod_search_det2 values ( 
            dbms_random.string('u', 5) || ' '
          ||dbms_random.string('u', 5) || ' '
          ||dbms_random.string('u', 5) || ' '
          ||dbms_random.string('u', 5)
          , sign );
      end loop;
    end;
    /
    
    select count(*) from prod_search_det2
    /
    
    exec ctx_ddl.drop_section_group   ( 'my_secgroup' )
    exec ctx_ddl.create_section_group ( 'my_secgroup', 'BASIC_SECTION_GROUP' )
    exec ctx_ddl.add_field_section    ( 'my_secgroup', 'K', 'K' )
    exec ctx_ddl.add_field_section    ( 'my_secgroup', 'S', 'S' )
    exec ctx_ddl.add_field_section    ( 'my_secgroup', 'C', 'C' )
    
    exec ctx_ddl.drop_preference  ( 'my_datastore' )
    exec ctx_ddl.create_preference( 'my_datastore', 'MULTI_COLUMN_DATASTORE')
    exec ctx_ddl.set_attribute    ( 'my_datastore', 'COLUMNS', '''<''|| sign || ''>XXX'' || prod_details || ''</''|| sign ||''>''' )
    
    exec ctx_ddl.drop_preference  ( 'my_wl' )
    exec ctx_ddl.create_preference( 'my_wl', 'BASIC_WORDLIST' )
    exec ctx_ddl.set_attribute    ( 'my_wl', 'SUBSTRING_INDEX', 'true' )
    
    create index prod_search_idx on prod_search_det2( prod_details )
    indextype is ctxsys.context
    parameters( 'section group my_secgroup datastore my_datastore wordlist my_wl')
    /
    
    -- simple query:
    select * from
      (select prod_details from prod_search_det2 
       where contains( prod_details, '%234% within K' ) > 0
      )
    where rownum < 15
    /
    
    column prod_details format a40
    column sign format a10
    
    -- create a progressive relaxation query
    
    create or replace function create_my_contains_clause( searchterm varchar2 ) 
    return varchar2 is 
    begin
      return '
    <query>
      <textquery>
        <progression>
          <seq> XXX' || searchterm || '% WITHIN S </seq>
          <seq> XXX' || searchterm || '% WITHIN K </seq>
          <seq> XXX' || searchterm || '% WITHIN C </seq>
          <seq> ' || searchterm || '% WITHIN S </seq>
          <seq> ' || searchterm || '% WITHIN K </seq>
          <seq> ' || searchterm || '% WITHIN C </seq>
          <seq> %' || searchterm || '% WITHIN K </seq>
          <seq> %' || searchterm || '% WITHIN S </seq>
          <seq> %' || searchterm || '% WITHIN C </seq>
        </progression>
      </textquery>
    </query>';
    end;
    /
    show err
    
    select * from
      ( select prod_details, sign from prod_search_det2 
        where contains( prod_details, create_my_contains_clause( 'bcd' ), 1) > 0
        order by score(1) desc
      )
    where rownum < 31
    /
    The output of that, with the search for "bcd" is:
    PROD_DETAILS                     SIGN
    ---------------------------------------- ----------
    BCDWY JWXUT XEAIW LHVSP            S
    BCDOM NLXUA FEQNK UOEMY            K
    BCDJD ZRATL KXHFE CPLDH            C
    BCDCN GYVKB OXZLP WGFIN            C
    TJZZQ KOMDQ BCDZG WPKPL            S
    KMJRN XSFEU UVMOR BCDDC            S
    MLSMP BCDLK WCWTO SPZDL            S
    JOSXG BCDLN LCXJO RTGQD            K
    AWETV BCDEI SYDIO GDMJI            K
    EEIKL OMXSV XDYKA BCDCZ            C
    HHMPO CKMCO VQNWX BCDLT            C
    MEFSA BCDSG VCGKT SJOEF            C
    XMYXY VHSGW BCDFP FADZN            C
    UDNWT DDWFN DOBCD HZKGK            K
    KBCDR EBEGX NXZWU VTOWR            K
    DKRJV EIBCD GLUHR TOBTI            K
    URJKX BBBCD SFQIR NORNY            K
    EBCDC YNYYG AFMMS CQFEQ            K
    YBCDW GQSKI HIKKU MNAAQ            K
    ISHQT VRSWR HBCDP WQKHX            K
    UZQPX BBCDQ FXMNN GJHCA            K
    UBCDP OFJDN NAHUJ DORXV            K
    VTIYY SBCDD IFROC CAQFN            K
    FOZGK NLNVI SLPOZ VBCDD            K
    GZOEM RRGQK VNUGU JFBCD            K
    DDNXZ XVFYJ SGBCD UXNRE            K
    CUPJH EKWDP IBCDH TZMIB            K
    ALBCD NXKXN DRZIZ OREFQ            K
    CHEIB YGAJU GBBCD JICDU            K
    FOBFR XLCQY KWJCY GIBCD            S
    Does that fit your requirements?

    Edited by: Roger Ford on Dec 10, 2012 7:56 AM (changed rownum < 15 to rownum < 31 to reflect the output I'm showing).
  • 24. Re: searching  in a materialized view
    949210 Newbie
    Currently Being Moderated
    I was considering following all the spaces by a special character(or something like XXX),
    this would prevent the second set(the set that shows rows in which search string is what the word/s start with, other than first word)
    To have a separate column where this would be implemented and perform contains operation on that column
    and exclude that column from the select list
    something like
    select prod_details,sign from prod_search_det2 where contains(prod_details2,'bcd',1)>0
    prod_details2 would be the column where i implement adding the special character
    thanks for your responses

    Edited by: 946207 on Dec 11, 2012 7:35 PM
  • 25. Re: searching  in a materialized view
    949210 Newbie
    Currently Being Moderated
    there is a teeny tiny problem with progressive relaxation
    example1
    search term=Java
    suppose there are rows in the database for s,k and c as follows
    Java struts         S
    Java jsp             K
    Java servlet        C 
    Java applet         C 
    the progressive relaxation query will show all four
    example2
    search term=Java
    suppose there is only one row in the database for c and no rows for s and k as follows
    Java applet         C 
    the progressive relaxation query does not seem to return this
    this situation was known later considering there are enough rows in s and k, and a few these type of cases
    this is what is evident right now (i hope i am noticing the problem rightly)

    Edited by: 946207 on Dec 12, 2012 10:37 AM

    Edited by: 946207 on Dec 12, 2012 12:34 PM
  • 26. Re: searching  in a materialized view
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    If I recall correctly, there was a bug in 10g, such that if no records matched the first criteria in the progressive relaxation then no rows were returned. It sounds like this is what you are describing. I believe upgrading to 11g solves that problem. I don't know if there is a patch available.
  • 27. Re: searching  in a materialized view
    949210 Newbie
    Currently Being Moderated
    what workarounds do i have
    bug using dbms_redefinition on table with altered text index?
    seems to have a workaround for a bug(although not the same) there has to be a way to circumvent this right?
    thanks
    Edited by: 946207 on Dec 12, 2012 3:42 PM

    Edited by: 946207 on Dec 12, 2012 7:03 PM
  • 28. Re: searching  in a materialized view
    ebalthes Pro
    Currently Being Moderated
    Last referenced forum thread is NOT related to the issue in this thread.
    The DBMS_REDEFINITION problem is fixed in 11.2.0.2 and 11.2.0.3 by the fix for 13635842.

    You're using a very, very old version 10.2.0.1 (base release of 10gR2) and instead we strongly recommend to use a supported version, ie 11.2.0.2 or higher
  • 29. Re: searching  in a materialized view
    949210 Newbie
    Currently Being Moderated
    Last referenced forum thread is NOT related to the issue in this thread.
    i mentioned ''although not the same"
    also that being 11.2.0.1.0 you said
    No real workaround exists as DBMS_......
    is this description actually a bug, are you sure??
    thanks

    Edited by: 946207 on Dec 17, 2012 12:55 PM

Legend

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