This discussion is archived
1 2 3 Previous Next 38 Replies Latest reply: Dec 25, 2012 11:08 PM by 949210 RSS

progressive relaxation, sectiongroup & multicolumn datastore simple example

949210 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    the sign,
    it searches only in K or C or S
    not all
  • 12. Re: searching  in a materialized view
    Roger Ford Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    >
    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 Expert
    Currently Being Moderated
    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

Legend

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