1 2 3 Previous Next 38 Replies Latest reply: Dec 26, 2012 1:08 AM by 949210 Go to original post RSS
      • 15. Re: searching  in a materialized view
        949210
        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-Oracle
          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
            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-Oracle
              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
                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-Oracle
                  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
                    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
                      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-Oracle
                        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
                          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
                            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
                              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
                                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-Oracle
                                  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
                                    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