11 Replies Latest reply: May 21, 2013 3:06 AM by chris227 RSS

    context index not working

    cs01kks
      Hi ,
      I am using context index with preference user_datastore. I attached a procedure to it. when I drop and create the index and check dr$index_Name$i table it shows 140 rows. If I do the same thing tommorow dr$index_name$i table shows 180 rows. total number of rows in the table remained same on both days. Also, i dont get the results consistently, one day I get the results with CONSISTS clause and next day it wonnt show up. Any clues on fixing this behaviour. I would also appreciate on your inputs on how should I sync up this index with out COMMIT mode.


      BEGIN
         ctx_ddl.drop_preference ('ni_emp_datastore');
         ctx_ddl.drop_preference ('ni_eng_case_insensitive');
      END;
      
      BEGIN
         ctx_ddl.create_preference ('ni_emp_datastore', 'user_datastore');
         ctx_ddl.set_attribute ('ni_emp_datastore', 'procedure', 'index_ni_docs');
         ctx_ddl.create_preference ('ni_eng_case_insensitive', 'BASIC_LEXER');
         ctx_ddl.set_attribute ('ni_eng_case_insensitive', 'base_letter', 'YES');
         ctx_ddl.set_attribute ('ni_eng_case_insensitive', 'mixed_case', 'NO');
         ctx_ddl.set_attribute ('ni_eng_case_insensitive', 'printjoins', '-.');
      
         COMMIT;
      END;
      
      DROP INDEX ni_documents_t1;
      
      CREATE INDEX ni_documents_t1
         ON ni_documents (file_name)
         INDEXTYPE IS ctxsys.context
         PARAMETERS ( 'datastore ni_emp_datastore lexer ni_eng_case_insensitive sync (on commit)' );
      
      EXEC DBMS_STATS.GATHER_TABLE_STATS('APPL', 'ni_documents', cascade=>TRUE);
      
      SELECT *
        FROM ni_documents
       WHERE file_name LIKE '%123%';
      This gives the following:
       PR121230.PDF
      PR121235.PDF
      12338.pdf
      
      I don't get any results with CONTAINS clause.
      
      SELECT *
        FROM ni_documents
       WHERE contains (file_name, '%123%') > 0; --- no rows returned
      please advice

      Thanks
      kumar
        • 1. Re: context index not working
          Solomon Yakobson
          Percent sign is a wildcard used by LIKE operator. CONTAINS doesn't have any wildcards. Word contains speaks for itself. The way you wrote it, CONTAINS literally looks for '%123%'. Change query to:
          SELECT *
            FROM ni_documents
           WHERE contains (file_name, '123') > 0;
          SY.
          • 2. Re: context index not working
            cs01kks
            Thanks for getting back on this. I changed the query not to use wild card. It still never output the nedded results. any other suggestions please?
            • 3. Re: context index not working
              chris227
              Solomon Yakobson wrote:
              Percent sign is a wildcard used by LIKE operator. CONTAINS doesn't have any wildcards. Word contains speaks for itself. The way you wrote it, CONTAINS literally looks for '%123%'. Change query to:
              Sorry, but that's not correct as i know and reread from the docs again after you stated this.
              With your query given, only values containing that token will be found.

              Edited by: chris227 on 17.05.2013 08:24
              • 4. Re: context index not working
                chris227
                That's very helpful that you posted the whole thing.
                Nearly the whole thing, where is your insert of the data.
                Did you do it after the index creation, then you might have to refresh the index.
                • 5. Re: context index not working
                  chris227
                  My be there is some issue with the usage of user_datastore.
                  Personally i never used it, so may be you will get better help in the dedicated oracle text forums on this site.
                  However, perhaps you missed to define the output_type attribute of the user_datastore.
                  Furthermore it could be helpful to show the implementation of the procedure 'index_ni_docs'.
                  • 6. Re: context index not working
                    cs01kks
                    Hi Chris,
                    Data is already there before I created this index. I didnot insert the data after index creation. please advice.

                    Thanks
                    kumar
                    • 7. Re: context index not working
                      chris227
                      cs01kks wrote:
                      Data is already there before I created this index. I didnot insert the data after index creation. please advice.
                      I did already.
                      Check the need of the user_datastore attribut outputtype
                      Post the implementation of the procedure used for the user_datastore.
                      Without the datastore this work fine for me
                      create table ni_documents (file_name varchar2(30))
                      
                      insert into ni_documents values('PR121230.PDF')
                      insert into ni_documents values('PR121235.PDF')
                      insert into ni_documents values('12338.pdf')
                       
                      BEGIN
                         ctx_ddl.create_preference ('ni_eng_case_insensitive', 'BASIC_LEXER');
                         ctx_ddl.set_attribute ('ni_eng_case_insensitive', 'base_letter', 'YES');
                         ctx_ddl.set_attribute ('ni_eng_case_insensitive', 'mixed_case', 'NO');
                         ctx_ddl.set_attribute ('ni_eng_case_insensitive', 'printjoins', '-.');
                      END;
                       
                      CREATE INDEX ni_documents_t1
                         ON ni_documents (file_name)
                         INDEXTYPE IS ctxsys.context
                         PARAMETERS ( 'lexer ni_eng_case_insensitive sync (on commit)' );
                       
                      SELECT *
                        FROM ni_documents
                       WHERE contains (file_name, '%123%') > 0
                      
                      FILE_NAME
                      PR121235.PDF
                      PR121230.PDF
                      12338.pdf
                      Edited by: chris227 on 17.05.2013 15:56
                      • 8. Re: context index not working
                        chris227
                        It also works for me with user_datastore AND outputtype attribute
                        create procedure index_ni_docs(rid in rowid, aName in out NOCOPY varchar2)
                        is
                        begin
                            select file_name into aName
                            from ni_documents where rowid = rid;
                        end;
                        
                        BEGIN
                           ctx_ddl.drop_preference ('ni_emp_datastore');
                           ctx_ddl.drop_preference ('ni_eng_case_insensitive');
                        END;
                         
                        BEGIN
                           ctx_ddl.create_preference ('ni_emp_datastore', 'user_datastore');
                           ctx_ddl.set_attribute ('ni_emp_datastore', 'procedure', 'myschema.index_ni_docs');
                           ctx_ddl.set_attribute ('ni_emp_datastore', 'output_type', 'VARCHAR2');
                           ctx_ddl.create_preference ('ni_eng_case_insensitive', 'BASIC_LEXER');
                           ctx_ddl.set_attribute ('ni_eng_case_insensitive', 'base_letter', 'YES');
                           ctx_ddl.set_attribute ('ni_eng_case_insensitive', 'mixed_case', 'NO');
                           ctx_ddl.set_attribute ('ni_eng_case_insensitive', 'printjoins', '-.');
                        END;
                         
                        DROP INDEX ni_documents_t1;
                         
                        CREATE INDEX ni_documents_t1
                           ON ni_documents (file_name)
                           INDEXTYPE IS ctxsys.context
                           PARAMETERS ( 'datastore ni_emp_datastore lexer ni_eng_case_insensitive sync (on commit)' );
                         
                        SELECT *
                          FROM ni_documents
                         WHERE contains (file_name, '%123%') > 0
                        
                        FILE_NAME
                        PR121235.PDF
                        12338.doc
                        PR121230.PDF
                        12338.pdf
                        
                        to see the effect of the user_datastore change the procedure like this for example:
                        
                        create or replace procedure index_ni_docs(rid in rowid, aName in out NOCOPY varchar2)
                        is
                        begin
                            select file_name into aName
                            from ni_documents where rowid = rid
                            and file_name not like '%doc';
                        end;
                        
                        Drop all and recreate it again
                        
                        after all the  doc is missing
                        
                        SELECT *
                          FROM ni_documents
                         WHERE contains (file_name, '%123%') > 0
                        
                        FILE_NAME
                        PR121235.PDF
                        PR121230.PDF
                        12338.pdf
                        Edited by: chris227 on 17.05.2013 16:15
                        • 9. Re: context index not working
                          cs01kks
                          Chris, I will try this and let you know how it went. thanks
                          • 10. Re: context index not working
                            cs01kks
                            chris,
                            Thank you verymuch for the help. I tried doing it with simple procedure that you had posted. It works fine with procedure, even with out seting output type as VARCHAR2. I am able to search file names using wild card as well with CONTAINS clause.
                            I just figured out the issue is a data issue. The procedure behind user_datastore is not getting the rows I am looking for. Thank you for the help.

                            Edited by: cs01kks on May 20, 2013 12:02 PM
                            • 11. Re: context index not working
                              chris227
                              Next step would be to do a similar example with clob, i suggest to use the outputype attribute in this case.
                              If this works, you can start to examine the logic of the procedure itself. May be i doesnt prduces the values you are looking for.
                              Another possibilty would be to examine the index directly, if it does contain the values to search.
                              Didnt do this since longtime, so for this you'll better ask the experts in the text-forums or consult the docs on this yourself.