This discussion is archived
11 Replies Latest reply: May 21, 2013 1:06 AM by chris227 RSS

context index not working

cs01kks Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Chris, I will try this and let you know how it went. thanks
  • 10. Re: context index not working
    cs01kks Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

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