1 2 Previous Next 26 Replies Latest reply on Jan 3, 2013 9:37 PM by mackrispi Go to original post
      • 15. Re: CONTEXT index and search getting slower
        One quick question ....

        Is it possible to limit the MATCHES so that it searches only on some criteria ... what I mean is:

        - we have 550 customers and each and every customer has some search keywords with combination of other criteria like MEDIA,TYPE,CATEGORY etc..

        when i create a table for queries like in your example ... can somehow limit the MATCHES so that it searches only the specific keyword queries that belong to this specific customer ?

        Cause otherwise there are 35.000 queries and this specific customer maybe has only 30-50 keywords to be searched for ... cause I don;t think that I can just run one query and get results back and then filter it, because some keyword queries are in context with some other criteria ,,, example with CONTAINS:
        Select .... FROM .. WHERE  (MEDIA="A" AND CONTAINS(xxxx)>0) OR (MEDIA="B" and CONTAINS(yyyy) AND TYPE=1) OR (CONTAINS(zzzz))  
        So if i create query data for MATCHES in my example for different keywords xxxx,yyyy,zzzz ... I still have to create individual select statements , for individual customer

        From your example I would say that select is done on query's table with only using MATCHES once on the full text .... so in our scenario on 35.000 query keywords ..

        Can I somehow use MATCHES with this mix criteria ? So something like :
        Select .... FROM .. WHERE  (MEDIA="A" AND MATCHES(xxxx)>0) OR (MEDIA="B" and MATCHES(yyyy) AND TYPE=1) OR (MATCHES(zzzz))  
        So statement is TRUE if this article is from MEDIA="A" and has matching keywords XXXX OR is from MEDIA="B" and has other keywords YYYY or there are just keywords ZZZZ

        I see the point if I would have a question :"give me all the customers that have this keywords" .... but what in my example ?

        Thank you .
        • 16. Re: CONTEXT index and search getting slower
          Roger Ford-Oracle
          You can use standard SQL queries on other columns in the searches table, for example:
          select search_area, owner_name 
          from searches 
          where matches(search_terms, 'Barack Obama yesterday announced that he flying to Iraq to discuss the financial status of US interests' ) > 0
          and owner_name = 'Mike'
          Your example doesn't really make sense - MATCHES(xxxx), MATCHES(yyyy) and MATCHES(zzzz) should be run as three separate queries.
          • 17. Re: CONTEXT index and search getting slower
            yes I know :) ... it was just an example of old logic :)

            So the only solution is to split into multiple queries ... which only makes sense, when MATCHES always works on all rows of the index column...

            Thank you to clarify it.
            • 18. Re: CONTEXT index and search getting slower
              Barbara Boehmer
              mackrispi wrote:
              For CONTEXT index the same query:
              select * from s_article where id=4572 
              and fk_media_code='sa' and fk_type=1 
              and contains(oratext,'(dobro or jutro)  not (otroke and imeti2) and near((zakaj,otroke),1)>0 and df and 6df and 5df and 43df and df5 and df45 and df345 and d4f  ')>0
              Plan hash value: 646002122
              | Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
              |   0 | SELECT STATEMENT            |               |     1 |   558 |     2   (0)| 00:00:01 |
              |*  1 |  TABLE ACCESS BY INDEX ROWID| S_ARTICLE     |     1 |   558 |     2   (0)| 00:00:01 |
              |*  2 |   INDEX UNIQUE SCAN         | S_ARTICLE_IDX |     1 |       |     1   (0)| 00:00:01 |
              Predicate Information (identified by operation id):
                 1 - filter("FK_MEDIA_CODE"='sa' AND TO_NUMBER("FK_TYPE")=1 AND 
                            "CTXSYS"."CONTAINS"("ORATEXT",'(dobro or jutro)  not (otroke and imeti2) and 
                            near((zakaj,otroke),1)>0 and df and 6df and 5df and 43df and df5 and df45 and df345 
                            and d4f  ')>0)
                 2 - access("ID"=4572)
              Your query is not using the context index at all. It is using another regular index on the id column. This is why I said that you should use SDATA in your contains clause, so that it uses just the context domain index to access both the text data and the id column, without any separate filtering. I just wanted to clarify this. However, the ctxrule index is more appropriate for your situation.
              • 19. Re: CONTEXT index and search getting slower

                Thank you for pointing this out ..... I have put sdata inside Contains and now I get cost 40 ... before with "wrong" index it was 2 .... is this relevant at all ?

                • 20. Re: CONTEXT index and search getting slower
                  Barbara Boehmer
                  Cost is irrelevant. What counts is how long it takes. When looking at the execution plan, you can usually tell if it is going to be efficient by whether or not it uses the domain index and whether it accesses everything or filters some things. You want it to use the domain index and access the text and id. You would expect it to still filter on anything outside of the contains clause. However, once again, the ctxrule index appears to be more appropriate for your situation.
                  • 21. Re: CONTEXT index and search getting slower
                    if I put " sdate (id=''xxx'') " ... and now I see the index being used, but have to make it as a bound variable ... cause writing it like this " sdate (id='':p1'') " returns error ..

                    I have really considered using CTXRULE , but if I need to split my one select into 3-5 or even more to get the correct result ... will this be efficient at all ?

                    What is funny is that if i use INSTR or REGEXP_INSTR , i get results around 14sec ..... but miss wildcards for single character and Near and other stuff ...

                    Thank you for taking your time for my problem and I can tell you that I'm not just sitting and waiting for answers cause I have already tried 3 different ways to make this work, but really want to make this the right way...

                    Thank you I really appreciate it.
                    • 22. Re: CONTEXT index and search getting slower
                      Barbara Boehmer
                      It is sdata, not sdate, and it would be something like below, using a bind variable.
                      SCOTT@orcl_11gR2> variable p1 varchar2(4)
                      SCOTT@orcl_11gR2> exec :p1 := 'xxxx'
                      PL/SQL procedure successfully completed.
                      SCOTT@orcl_11gR2> select * from s_article
                        2  where  contains
                        3             (oratext,
                        4              '((a or b) not c) and
                        5            sdata (id = ''' || :p1 || ''')') > 0
                        6  and    type = 'xx'
                        7  and    media = 'yy'
                        8  /
                      ---- ---- ---- ----- -------- ------ ------- -------- -
                      xxxx xx   yy   a     b        d      e       f
                      1 row selected.
                      Execution Plan
                      Plan hash value: 1507989626
                      | Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
                      |   0 | SELECT STATEMENT            |                 |     1 |    52 |     4   (0)| 00:00:01 |
                      |*  1 |  TABLE ACCESS BY INDEX ROWID| S_ARTICLE       |     1 |    52 |     4   (0)| 00:00:01 |
                      |*  2 |   DOMAIN INDEX              | ORATEXT_ART_IDX |       |       |     4   (0)| 00:00:01 |
                      Predicate Information (identified by operation id):
                         1 - filter("TYPE"='xx' AND "MEDIA"='yy')
                         2 - access("CTXSYS"."CONTAINS"("ORATEXT",'((a or b) not c) and            sdata (id
                                    = '''||:P1||''')')>0)
                         - dynamic sampling used for this statement (level=2)
                      • 23. Re: CONTEXT index and search getting slower
                        Barbara Boehmer
                        Upon reviewing this thread, I found that you said, "SQLArea shows that the most running sql is the oracle generated query for Dynamic sampling of the context index." However, you also said that you are using, " exec DBMS_stats.gather_table_stats('TEST','S_ARTICLE',cascade=>TRUE);", so there should not be any dynamic sampling, so something is wrong.
                        • 24. Re: CONTEXT index and search getting slower
                          I came up with an idea ... ok maybe it's not a good one but ....

                          IF I use CONTEXT index ... but on another table which has in perfect conditions zero rows ....

                          What I mean is ...... We have a "transitional" table or a queue containing all the new articles that will be imported into db ... after article is imported, the row for this article is deleted from this queue ......

                          And because this table has all the data we want to do the search for, we could set index on this table ... cause this table will have at most 6000 records (if the app that will take care of this records is not running, but it will always run) and when everything is done.... there will be zero rows ....

                          Would this table be more suitable for having such an index on it .....

                          Would inserting,deleting rows somehow harm the index or even db ? If I run optimize every night in Rebuild mode or any other .....

                          Btw.. I just saw your mail ..sdata ... yes I had it like this :) my typo . ..... you said that it's something wrong ... so I took this too queries and here they are ... if this helps at all and AWR screenshot : [http://screencast.com/t/XlOZPHp6]
                          FROM "PRESS"."DR$ORATEXT_ART_IDX$I" i
                          Executions          1,880,260             1              0.30
                          Elapsed Time (sec)          28.42     <0.01     <0.01
                          CPU Time (sec) 28.56     <0.01     <0.01
                          Buffer Gets         5,680,427             3.02        0.89
                          Disk Reads          0              0.00        0.00
                          Direct Writes     0              0.00        0.00
                          Rows     6,372,872             3.39        1
                          Fetches                1,889,988             1.01        0.30
                          FROM "PRESS"."DR$ORATEXT_ART_IDX$I" T
                          WHERE TOKEN_TEXT LIKE :lkexpr ESCAPE '\' and (TOKEN_TYPE = 0 OR TOKEN_TYPE = 4 OR TOKEN_TYPE BETWEEN 16 AND 74)
                          Executions          527,853 1              0.01
                          Elapsed Time (sec)          1,162.73               <0.01     <0.01
                          CPU Time (sec) 1,048.05               <0.01     <0.01
                          Buffer Gets         19,929,084           37.75     0.55
                          Disk Reads          1,776     <0.01     <0.01
                          Direct Writes     0              0.00        0.00
                          Rows     36,225,171           68.63     1
                          Fetches                550,410 1.04        0.02
                          Thank you.
                          • 25. Re: CONTEXT index and search getting slower
                            Barbara Boehmer
                            Your whole process is a bit unclear. However, if you already have this transactional table, then creating the index on that table and searching on that alone should drastically reduce the rows and run faster. The more inserts, deletes, and synchronization that you do, the more fragmented the index will become, and the slower the queries will run, until you optimize. With fewer rows, the effect should not be as bad and optimization or rebuilding should not take as long, so you could perhaps do so more often. It's an interesting idea. Test it and let us know how it works out.
                            • 26. Re: CONTEXT index and search getting slower
                              Great ... I was afraid that so many inserts,deletes would somehow kill the CPU or db itself .... I will do that cause I'm really curious how the system will react ... and let you know ...

                              There is also another solution that we are leaning to , and that is REGEX_INSTR ..... cause using pattern search I can do my

                              wildcard single character replacement search and it's performing, really well.... I just hope I didn't miss something :) cause I have

                              on our i7 machine times from 5-9sec .... Our best time when using CONTEXT was 9-11sec when database was almost empty :) and our worst 120sec ...

                              CPU is now bellow 20% and ram around 4Gb... before that our CPU was at 100% all the time and RAM on 8Gb ,,, :/ ..

                              I have really high hopes now and I'm really glad I have at least 2 possibilities to solve our problem.

                              And of course a lot of gratitude goes to you and Roger and other who are willing to help someone like me or should I say "non experts in need".

                              Thank you for helping us out.
                              1 2 Previous Next