1 2 3 Previous Next 38 Replies Latest reply: Dec 26, 2012 1:08 AM by 949210 RSS

    progressive relaxation, sectiongroup & multicolumn datastore simple example

    949210
      need help on this one
      Please refer
      tuning regexp_like
      Re: partitioning a table
      Re: partitioning(range) a table values less than 'A'
      the requirement is to return rows as fast as possible
      the query we are using right now is
      SELECT PROD_DETAILS,SIGN,ROWNUM FROM( SELECT PROD_DETAILS,SIGN,
       (CASE WHEN REGEXP_LIKE(PROD_DETAILS,'^ups','i') THEN '1' ELSE '2' END) AS 
       SIGN2 FROM (SELECT PROD_DETAILS,SIGN FROM  
       MV_PROD_SEARCH_DET2 WHERE REGEXP_LIKE(PROD_DETAILS,'ups','i')) 
       order by SIGN2,SIGN desc) where rownum<15
      this query takes atleast 2.5 to 3 seconds
      we need something less than 1/4 second
      i am looking for some suggestions to implement this with text index search
      the above query will be used for auto complete (auto suggest) from a front end
      (we were using catsearch earlier with results slower than the above query)
      thank you

      Edited by: 946207 on Dec 3, 2012 6:17 PM

      Edited by: 946207 on Dec 4, 2012 12:35 PM

      Edited by: 946207 on Dec 28, 2012 11:51 AM

      Edited by: 946207 on Dec 28, 2012 11:53 AM
        • 1. Re: searching  in a materialized view
          Roger Ford-Oracle
          How much data are you searching?

          This might be possible with a CONTEXT index, but you'll probably need to have the index, and the data in the select list, cached in memory to get 1/4 second response times.
          What are "sign2" and "sign" here? How do they define the ordering? Are they strictly necessary, and could they be combined into a single field in a meaningful way? (The reason I ask is that we may be able to force the sorting into the text index if they can be).
          • 2. Re: searching  in a materialized view
            949210
            the materialized view has 67000 rows at present(almost 5 mb)
            there are 5000 inserts per day to the table that this materialized view uses to refresh
            desc mv_prod_search_det2
            Name         Null Type           
            ------------ ---- -------------- 
            PROD_DETAILS      VARCHAR2(1000) 
            SIGN              VARCHAR2(42)   
            :
            select * from mv_prod_search_det2 where rownum<4;
            something     K-14483
            anything     S-99
            everything     C-12065
            K in the second column means that the value in the first column (of the same row) is a keyword
            S in the second column means that the value in the first column (of the same row) is a segment
            C in the second column means that the value in the first column (of the same row) is a company

            the requirement is to have segments followed by keywords followed by company
            if the first column starts with what the user has entered then sign2 is 1
            else sign2 is 2(in this case the column 1 has what the user has entered somewhere within but not at the start )
            the output first shows all the values in column 1 that have started with what the end user has entered and then others
            like this
            s  1
            s  1
            s  1
            k  1
            k  1
            k  1
            k  1
            k  1
            c  1
            c  1
            c  1
            s  2
            s  2
            s  2
            k  2
            k  2
            c  2
            c  2
            c  2
            c  2
            c  2
            Edited by: 946207 on Dec 4, 2012 12:46 PM
            • 3. Re: searching  in a materialized view
              Roger Ford-Oracle
              OK, here we go. There's a few concepts you might not be familiar with here:

              The MULTI_COLUMN_DATASTORE creates an indexable string from a "select list". In this case I'm wrapping the SIGN column as XML-like tags around the PROD_DETAILS column, so if SIGN=K and PROD_DETAILS=12345 then I'm going to index <K>12345</K>. If your materialized view is only used for this search, it might be easier to do that concatenation in the materialized view rather than in the datastore.

              That allows me to search within sections - I can look for "%234% WITHIN K"

              The query uses "progressive relaxation" to effectively force the ordering to what we want - we have six sequences which will be executed in order, to fetch "begin matches" in the three sections, then "anywhere matches" in the sections after.

              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.

              For simplicity of the example, I've just inserted numbers from 1 to 67000 in the PROD_DETAILS column. But this will work equally well with short text strings. However be aware that "begin matches" refers to words - if you search for "US" then "Oracle USA" will be just as high as "USA Electric". If that's not what you want, you'll have to do some extra work.

              This runs very fast - too fast to measure - on my system.
              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
              
              -- progressive relaxation query
              select * from
                ( select prod_details, sign from prod_search_det2 
                  where contains( prod_details, '
              <query>
                <textquery>
                  <progression>
                    <seq> 1234% WITHIN S </seq>
                    <seq> 1234% WITHIN K </seq>
                    <seq> 1234% WITHIN C </seq>
                    <seq> %1234% WITHIN K </seq>
                    <seq> %1234% WITHIN S </seq>
                    <seq> %1234% WITHIN C </seq>
                  </progression>
                </textquery>
              </query>
              ', 1) > 0
                  order by score(1) desc
                )
              where rownum < 15
              /
              Edited by: Roger Ford on Dec 4, 2012 1:47 AM
              Noticed I wasn't closing the tag properly in the COLUMNS attribute : I had "<" but should have had "</". It worked anyway, but watch this if you're cutting and pasting from the notification email rather than what's now listed above.
              • 4. Re: searching  in a materialized view
                949210
                1)please tell me the important portions of documentation for just this (and a liitle more) to be implemented
                2)will the above suggestion work on
                Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
                PL/SQL Release 10.2.0.1.0 - Production
                "CORE     10.2.0.1.0     Production"
                TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
                NLSRTL Version 10.2.0.1.0 - Production
                3)I read
                http://www.oracle.com/technetwork/database/enterprise-edition/mem-load-082296.html
                I thought you would suggest this again.Your comments

                Edited by: 946207 on Dec 5, 2012 7:15 PM
                • 5. Re: searching  in a materialized view
                  Roger Ford-Oracle
                  Yes, it will work with that version. Documentation:

                  Oracle Text Reference manual
                  http://docs.oracle.com/cd/B19306_01/text.102/b14218/toc.htm
                  Specifically:
                  Multi Column Datastore:
                  http://docs.oracle.com/cd/B19306_01/text.102/b14218/cdatadic.htm#sthref320
                  Section Groups:
                  http://docs.oracle.com/cd/B19306_01/text.102/b14218/cdatadic.htm#sthref837

                  Also read my note about progressive relaxation:
                  http://www.oracle.com/technetwork/database/enterprise-edition/prog-relax-099280.html

                  Preloading the indexes is certainly recommended for best performance. The index here will be relatively small, so you might be lucky that it remains in the SGA after creating the index and never ages out. However, you may then see slow performance after a database restart until the index is fully cached again.
                  • 6. Re: searching  in a materialized view
                    949210
                    corrigendum
                    exec ctx_ddl.set_attribute    ( 'my_datastore', 'COLUMNS', '''<''|| sign || ''>'' || prod_details || ''</''|| sign ||''>'' ')
                    Edited by: 946207 on Dec 6, 2012 1:20 PM
                    • 7. Re: searching  in a materialized view
                      949210
                      what
                      ALTER TABLE <tablename> STORAGE (BUFFER POOL KEEP);
                      ALTER INDEX <indexname> STORAGE (BUFFER_POOL KEEP);
                      does is that it keeps this table on the most recently used list in the SGA.
                      is there a need to remove it from buffer pool(or will it come to least recently used automatically if frequent full table scans do not happen)
                      if there is a need to remove how to do that(i did some googling in vain)
                      NAME                                               TYPE        VALUE                                                                                                
                      -------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------- 
                      db_cache_advice                                    string      ON                                                                                                   
                      db_cache_size                                      big integer 8M                           
                      anything else to be aware of regarding this please let me know
                      please let me know if you need other parameters
                      sga_max_size                                       big integer 3G                                                                                                   
                      sga_target                                         big integer 2504M                        
                      NAME                                               TYPE        VALUE                                                                                                
                      -------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------- 
                      db_keep_cache_size                                 big integer 0                                                                                                    
                      perhaps
                      alter table table_name storage(buffer_pool default)

                      Edited by: 946207 on Dec 6, 2012 7:41 PM
                      • 8. Re: searching  in a materialized view
                        Roger Ford-Oracle
                        My understanding (and I can't promise this is correct):

                        Since db_keep_cache_size is 0 you don't have a buffer cache called KEEP.

                        The KEEP buffer cache is just like any other buffer cache in terms of how stuff is aged in and out of it. However, since you are manually assigning objects to it ( via the STORAGE clauses you mentioned) and you are manually setting a size for it using the db_keep_cache_size, you can ensure that the cache is at least as large as all the objects assigned to it, and therefore there will be no need for anything to get aged out from it.

                        Tom Kyte recommends not using the KEEP pool:
                        http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1590999000346302363

                        I'm sure he's right in most cases. However, if you want to guarantee that some data is never aged out of the main buffer cache, even by "more popular" data, then the KEEP cache seems to make sense to me.
                        • 9. Re: searching  in a materialized view
                          949210
                          the within query you sent searches only in one alphabet
                          that would have me do union all
                          resulting in time delay again
                          • 10. Re: searching  in a materialized view
                            Roger Ford-Oracle
                            Sorry, I don't understand you. What do you mean by "searches only in one alphabet"?

                            Do you mean only one column of a table, perhaps? Or is it something to do with character sets?
                            • 11. Re: searching  in a materialized view
                              949210
                              the sign,
                              it searches only in K or C or S
                              not all
                              • 12. Re: searching  in a materialized view
                                Roger Ford-Oracle
                                Oh I see. Yes, I based that on you saying:

                                -----------------------------------
                                K in the second column means that the value in the first column (of the same row) is a keyword
                                S in the second column means that the value in the first column (of the same row) is a segment
                                C in the second column means that the value in the first column (of the same row) is a company

                                the requirement is to have segments followed by keywords followed by company
                                -----------------------------------

                                So are you saying that there are many other possible characters here? Is there an ordering specified for them?
                                • 13. Re: searching  in a materialized view
                                  949210
                                  >
                                  So are you saying that there are many other possible characters here? Is there an ordering specified for them?
                                  there are only three possibilities K,C or S
                                  select * from
                                    (select prod_details from prod_search_det2 
                                     where contains( prod_details, '%234% within K' ) > 0
                                    )
                                  where rownum < 15
                                  there is a possibility that a certain word be in the rows where value in the second column is not just K
                                  that is 234 in this case could be present in the rows where value in the second column is C or S also
                                  to do that i would have to
                                  select * from
                                    (select prod_details from prod_search_det2 
                                     where contains( prod_details, '%234% within S' ) > 0
                                    )
                                  union all
                                  select * from
                                    (select prod_details from prod_search_det2 
                                     where contains( prod_details, '%234% within K' ) > 0
                                    )
                                  union all
                                  select * from
                                    (select prod_details from prod_search_det2 
                                     where contains( prod_details, '%234% within C' ) > 0
                                    )
                                  Edited the order (it was not S-K-C in the union all query which is my requirement)
                                  • 14. Re: searching  in a materialized view
                                    Roger Ford-Oracle
                                    Your query could be much more simply expressed as
                                    select * from
                                      (select prod_details from prod_search_det2 
                                       where contains( prod_details, '%234% within K OR %234% within C OR %234%  within S' ) > 0
                                      )
                                    but that doesn't take the ordering into account. My query using progressive relaxation finds all those possibilities and ranks them in the order you specified.
                                    1 2 3 Previous Next