This discussion is archived
1 2 Previous Next 25 Replies Latest reply: Jan 14, 2013 11:16 AM by kevinUCB Go to original post RSS
  • 15. Re: Like (%) search performance issues.
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    It is not clear what you did. You mentioned "index sync". It would probably be more efficient to drop and recreate the index than try to synchronize it. It would also help if you posted a copy and paste of what code you are running and the results.
  • 16. Re: Like (%) search performance issues.
    Rohit Jadhav Newbie
    Currently Being Moderated
    i followed the steps mentioned by you earlier and then ran

    BEGIN
    ctx_ddl.sync_index(in_ci_custmast_ctx);
    END;
  • 17. Re: Like (%) search performance issues.
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    If you did things in the order that I did, once you created the index, there should have been nothing new to synchronize. You must be doing something different than what you are describing. As previously stated, you need to post a copy and paste of what you do, as I have. Otherwise, we cannot guess what you are actually doing and cannot help you and will cease responding.
  • 18. Re: Like (%) search performance issues.
    Rohit Jadhav Newbie
    Currently Being Moderated
    Apologies,

    I am again doing the steps you mentioned earlier from start. I will come back to you on same. Index creation step mentioned by you is running from 2hrs now for 8.6million records. fyi.
    i wont run sync index now. if i have i to? then will seek your views on same and proceed on it.

    Edited by: Rohit Jadhav on Dec 30, 2012 7:32 PM

    Edited by: Rohit Jadhav on Dec 30, 2012 7:32 PM
  • 19. Re: Like (%) search performance issues.
    Rohit Jadhav Newbie
    Currently Being Moderated
    Referring your post on Nov 7, 2012 10:55 PM , i again did a index creation activity from scratch. took around 5hrs for index creation.
    post which i tried search for records based on chinese character.

    Observations :

    1) Search is working fine and faster when i run

    SELECT * FROM xface_ic_no_xref_rj WHERE CONTAINS (nam_cust_full, '?%') > 0;

    2) Only doubt i have is below

    Returns no difference :

    SELECT * FROM xface_ic_no_xref_rj WHERE nam_cust_full LIKE '刘%'
    MINUS
    SELECT * FROM xface_ic_no_xref_rj WHERE CONTAINS (nam_cust_full, 'start '||'刘'||%') > 0;

    Returns difference :

    SELECT * FROM xface_ic_no_xref_rj WHERE CONTAINS (nam_cust_full, 'start '||'刘'||%') > 0
    MINUS
    SELECT * FROM xface_ic_no_xref_rj WHERE nam_cust_full LIKE '刘%';

    Observed that if text at first position is white spaces or special characters to which my chinese character is immediate following character i.e. on second position, Even such records are getting picked up in in query result, i.e. extra search records are getting fetched,

    sample production and UAT data in which query is returning additional search character and is on second postion.

    1) " 刘亚霞" - White Space
    2) ".刘亚芹" - Some dot
    3) "`刘明" - Some inverter quote in chinese keyboard.
    4) "[刘秋红" - Closing bracket.



    Any suggestion.?                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
  • 20. Re: Like (%) search performance issues.
    kevinUCB Explorer
    Currently Being Moderated
    Are you sure you don't want the 'extra' results? Most people think that the system should return the extra rows, that they represent data entry issues and it's better to include them in the resultset.
    If the results of the LIKE clause are what you need, then you should use the LIKE clause with a function-based index, as was mentioned before, and use the text search when you're looking for more characters.
    declare 
        querystr := '刘';
    begin
        if (length(querystr = 1)) then
            SELECT * FROM xface_ic_no_xref_rj WHERE nam_cust_full LIKE querystr ||'%';
        else
            SELECT * FROM xface_ic_no_xref_rj WHERE CONTAINS (nam_cust_full, querystr) > 0;
        end if;
    end;
  • 21. Re: Like (%) search performance issues.
    Rohit Jadhav Newbie
    Currently Being Moderated
    Thanks at ton to Kevin/Barbara/Roger,

    I am almost on the verge to complete this stuff and put the same in Pre Production for testing purpose.

    Responding to you question Kevin, It is OK to have extra results in result set.

    Further Query I have which will help me to conclude this thread :

    1) How will i keep doing incremental sync of index for new data that will be inserted or would it automatic sync up the index with latest data ?
    2) Do i need to put any cron job in production to sync indexes...? If yes, would it affect the performance, i mean if cron is required would it impact anyways the performance when there is sync index is running.

    Asking doubt since this table stores customer data and is used almost in entire business flow of my system and i will be able to represent this stuff in concrete way to my managers and peers.

    Request Response.
  • 22. Re: Like (%) search performance issues.
    kevinUCB Explorer
    Currently Being Moderated
    There are multiple ways to do it, and you should consult the docs to be clear on what you're choosing, but the easiest way is to declare when you want to sync as part of the CREATE INDEX command:
    CREATE INDEX tdrmauto02x ON tdrmauto02(text)
       INDEXTYPE IS CTXSYS.CONTEXT local
       (PARTITION tdrm02x_i1 PARAMETERS('
       MEMORY 20m SYNC(ON COMMIT)'),
       PARTITION tdrm02x_i2,
       PARTITION tdrm02x_i3)  PARAMETERS('
       SYNC (EVERY "NEXT_DAY(TRUNC(SYSDATE), ''MONDAY'') + 15/24")
      ');
    This is from the docs, and shows a partitioned index, but more importantly it shows two ways of performing the sync:
    sync(ON COMMIT) means that the text index will get updated as result of the completion of the transaction that affects the text column.
    sync(EVERY "NEXT_DAY(TRUNC(SYSDATE), ''MONDAY'') + 15/24") means sync every Monday at 3pm.
    The more often you sync, the worse fragmentation will get, which affects performance and you will want to plan to run OPTIMIZE_INDEX more frequently.

    If you pursue the strategy of mixing LIKE and CONTAINS queries, you will need to be extra careful of this; the LIKE query will go against the committed column value, while the CONTAINS goes against the current text index, which means that you could get different answers depending on how (and when) you ask the question.
  • 23. Re: Like (%) search performance issues.
    499773 Newbie
    Currently Being Moderated
    Isn't this just the problem of having a to short LIKE pattern prefíx ?

    nam_cust_full like '[1CHINESECHAR]%'

    will probably result into a FULL TABLE|INDEX SCAN

    but nam_cust_full like '[CHINESECHAR(S)]%'

    already into an INDEX RANGE SCAN. Using bind variables here even worse the behavior.

    Can a context index query in this case really perform better than an INDEX RANGE Scan ?
  • 24. Re: Like (%) search performance issues.
    Rohit Jadhav Newbie
    Currently Being Moderated
    Rainer Stenzel,

    Issue i was or I am facing is with Single character search on 40million data. sync index is a helping feature and is alternative to fasten the search, Also it gave me good gain in response of my query.
    earlier 0.16 million data use to take 3mins to get inserted into intermediate temp table and now it happens in seconds.

    But again referring post which is just above your post, There is a catch, if sync index, since data will be read only from context index for newly added data i to have put a job to have the latest data synced with index else
    response results may vary. on top of it there is also a performance impact. so consolidating views from every one. I am still in process to identify best solution, off course CTX stuff is perfect.
    But i have to consider hardware and infrastructure limitations which my client has set :)

    I wonder sometimes why Google search is so fast.....? i know its flat file DB, but still there must be something in oracle as well.

    Answering your Quest :

    Can a context index query in this case really perform better than an INDEX RANGE Scan ?

    I am not a db guy, but i will try to comment as what is my understanding about database and my learning at this site.

    Index range scan :
    ------------------------
    Considering size of table and block size allocated to it. It is highly prone to query responding very slow due to physical reads happening i couldn't see binding variable concept helping on it as well at least
    when i analyzed!.
    Context index is much better but only doubt of sync index.
  • 25. Re: Like (%) search performance issues.
    kevinUCB Explorer
    Currently Being Moderated
    One more example of a potential solution:
    CREATE INDEX tdrmauto02x ON tdrmauto02(text)
       INDEXTYPE IS CTXSYS.CONTEXT local
       (PARTITION tdrm02x_i1 PARAMETERS('
       MEMORY 20m SYNC(ON COMMIT)'),
       PARTITION tdrm02x_i2,
       PARTITION tdrm02x_i3)  PARAMETERS('
       SYNC (EVERY "NEXT_DAY(TRUNC(SYSDATE), ''MONDAY'') + 15/24")
       TRANSACTIONAL
      ');
    Adding the TRANSACTIONAL parameter forces the engine to query both the 'full index' and the rows that haven't been added yet (stored in a table called DR$PENDING). This obviously has some performance concerns -- and benefits. You would probably have to test whether TRANSACTIONAL or SYNC(ON COMMIT) made more sense for you (depends on how many new rows are added per second/minute/day, as well as the frequency of updates and deletions).
1 2 Previous Next

Legend

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