3 Replies Latest reply: May 20, 2013 12:15 PM by Barbara Boehmer 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. My main issue is, I do not get intended results for this posted select query.


      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
      CREATE OR REPLACE PROCEDURE index_ni_docs (p_id IN ROWID, p_lob IN OUT CLOB)
      AS
      BEGIN
         FOR x
            IN (SELECT file_name,
                       DOC.status,
                       subs,
                       division,
                       department,
                       attribute_1,
                       attribute_2,
                       attribute_3,
                       attribute_4,
                       attribute_5,
                       (SELECT    dst.document_subtype_code
                               || ' '
                               || dt.document_type_code
                               || ' '
                               || dst.description
                               || ' '
                               || dt.description
                          FROM ni_doc_st dst, ni_doc_typ dt
                         WHERE dst.document_subtype_id = doc.document_subtype_id
                               AND dt.document_type_id = dst.document_type_id)
                          typ_subtyp,
                       TO_CHAR (DOCUMENT_DATE, 'YYYY') DOCUMENT_DATE_YEAR,
                       TO_CHAR (DOC.LAST_UPDATE_DATE, 'MM/DD/YYYY') MODIFIED
                  FROM ni_documents doc
                 WHERE doc.ROWID = p_id)
         LOOP
            BEGIN
               IF x.file_name IS NOT NULL
               THEN
                  DBMS_LOB.writeappend (p_lob,
                                        LENGTH (x.file_name) + 1,
                                        x.file_name || ' ');
               END IF;
               IF x.status IS NOT NULL
               THEN
                  DBMS_LOB.writeappend (p_lob,
                                        LENGTH (x.status) + 1,
                                        x.status || ' ');
               END IF;
               IF x.subs IS NOT NULL
               THEN
                  DBMS_LOB.writeappend (p_lob,
                                        LENGTH (x.subsidiary) + 1,
                                        x.subsidiary || ' ');
               END IF;
               IF x.division IS NOT NULL
               THEN
                  DBMS_LOB.writeappend (p_lob,
                                        LENGTH (x.division) + 1,
                                        x.division || ' ');
               END IF;
      
               IF x.department IS NOT NULL
               THEN
                  DBMS_LOB.writeappend (p_lob,
                                        LENGTH (x.department) + 1,
                                        x.department || ' ');
               END IF;
               IF x.attribute_1 IS NOT NULL
               THEN
                  DBMS_LOB.writeappend (p_lob,
                                        LENGTH (x.attribute_1) + 1,
                                        x.attribute_1 || ' ');
               END IF;
      
               IF x.typ_attrib_2 IS NOT NULL
               THEN
                  DBMS_LOB.writeappend (p_lob,
                                        LENGTH (x.typ_attrib_2) + 1,
                                        x.typ_attrib_2 || ' ');
               END IF;
               IF x.typ_attrib_3 IS NOT NULL
               THEN
                  DBMS_LOB.writeappend (p_lob,
                                        LENGTH (x.typ_attrib_3) + 1,
                                        x.typ_attrib_3 || ' ');
               END IF;
               IF x.typ_attrib_4 IS NOT NULL
               THEN
                  DBMS_LOB.writeappend (p_lob,
                                        LENGTH (x.typ_attrib_4) + 1,
                                        x.typ_attrib_4 || ' ');
               END IF;
      
               IF x.attribute_5 IS NOT NULL
               THEN
                  DBMS_LOB.writeappend (p_lob,
                                        LENGTH (x.empno) + 1,
                                        x.empno || ' ');
               END IF;
      
               IF x.typ_subtyp IS NOT NULL
               THEN
                  DBMS_LOB.writeappend (p_lob,
                                        LENGTH (x.typ_subtyp) + 1,
                                        x.typ_subtyp || ' ');
               END IF;
               ni_api_pkg.msg (0, '[TEXTVAL] ' || SUBSTR (p_lob, 1, 2000));
            EXCEPTION
               WHEN OTHERS
               THEN
                  DBMS_OUTPUT.PUT_LINE ('error');
            END;
         END LOOP;
      END;
      /
      please advice

      Thanks
      kumar

      Edited by: cs01kks on May 20, 2013 11:08 AM