3 Replies Latest reply: Apr 17, 2013 1:38 PM by Barbara Boehmer RSS

    Creating a single context index on a one-to-many and lookup table

    JamieC
      Hello,

      I've been successfully setting up text indexes on multiple columns on the same table (using MULTI_COLUMN_DATASTORE preferences), but now I have a situation with a one-to-many data collection table (with a FK to a lookup table), and I need to search columns across both of these tables. Sample code below, more of my chattering after the code block:
      CREATE TABLE SUBMISSION
      ( SUBMISSION_ID             NUMBER(10)          NOT NULL,
        SUBMISSION_NAME           VARCHAR2(100)       NOT NULL
      );
       
      CREATE TABLE ADVISOR_TYPE
      ( ADVISOR_TYPE_ID           NUMBER(10)          NOT NULL,
        ADVISOR_TYPE_NAME         VARCHAR2(50)        NOT NULL
      );
        
      CREATE TABLE SUBMISSION_ADVISORS
      ( SUBMISSION_ADVISORS_ID    NUMBER(10)          NOT NULL,
        SUBMISSION_ID             NUMBER(10)          NOT NULL,
        ADVISOR_TYPE_ID           NUMBER(10)          NOT NULL,
        FIRST_NAME                VARCHAR(50)         NULL,
        LAST_NAME                 VARCHAR(50)         NULL,
        SUFFIX                    VARCHAR(20)         NULL
      );
      
      INSERT INTO SUBMISSION (SUBMISSION_ID, SUBMISSION_NAME) VALUES (1, 'Some Research Paper');
      INSERT INTO SUBMISSION (SUBMISSION_ID, SUBMISSION_NAME) VALUES (2, 'Thesis on 17th Century Weather Patterns');
      INSERT INTO SUBMISSION (SUBMISSION_ID, SUBMISSION_NAME) VALUES (3, 'Statistical Analysis on Sunny Days in March');
      
      INSERT INTO ADVISOR_TYPE (ADVISOR_TYPE_ID, ADVISOR_TYPE_NAME) VALUES (1, 'Department Chair');
      INSERT INTO ADVISOR_TYPE (ADVISOR_TYPE_ID, ADVISOR_TYPE_NAME) VALUES (2, 'Department Co-Chair');
      INSERT INTO ADVISOR_TYPE (ADVISOR_TYPE_ID, ADVISOR_TYPE_NAME) VALUES (3, 'Professor');
      INSERT INTO ADVISOR_TYPE (ADVISOR_TYPE_ID, ADVISOR_TYPE_NAME) VALUES (4, 'Associate Professor');
      INSERT INTO ADVISOR_TYPE (ADVISOR_TYPE_ID, ADVISOR_TYPE_NAME) VALUES (5, 'Scientist');
      
      INSERT INTO SUBMISSION_ADVISORS (SUBMISSION_ADVISORS_ID, SUBMISSION_ID, ADVISOR_TYPE_ID, FIRST_NAME, LAST_NAME, SUFFIX) VALUES (1,1,2,'John', 'Doe', 'PhD');
      INSERT INTO SUBMISSION_ADVISORS (SUBMISSION_ADVISORS_ID, SUBMISSION_ID, ADVISOR_TYPE_ID, FIRST_NAME, LAST_NAME, SUFFIX) VALUES (2,1,2,'Jane', 'Doe', 'PhD');
      INSERT INTO SUBMISSION_ADVISORS (SUBMISSION_ADVISORS_ID, SUBMISSION_ID, ADVISOR_TYPE_ID, FIRST_NAME, LAST_NAME, SUFFIX) VALUES (3,2,3,'Johan', 'Smith', NULL);
      INSERT INTO SUBMISSION_ADVISORS (SUBMISSION_ADVISORS_ID, SUBMISSION_ID, ADVISOR_TYPE_ID, FIRST_NAME, LAST_NAME, SUFFIX) VALUES (4,2,4,'Magnus', 'Jackson', 'MS');
      INSERT INTO SUBMISSION_ADVISORS (SUBMISSION_ADVISORS_ID, SUBMISSION_ID, ADVISOR_TYPE_ID, FIRST_NAME, LAST_NAME, SUFFIX) VALUES (5,3,5,'Williard', 'Forsberg', 'AMS');
       
      COMMIT;
      I want to be able to create a text index to lump these fields together:

      SUBMISSION_ADVISORS.FIRST_NAME
      SUBMISSION_ADVISORS.LAST_NAME
      SUBMISSION_ADVISORS.SUFFIX
      ADVISOR_TYPE.ADVISOR_TYPE_NAME

      I've looked at DETAIL_DATASTORE and USER_DATASTORE, but the examples in Oracle Docs for DETAIL_DATASTORE leave me a little bit perplexed. It seems like this should be pretty straightforward.

      Ideally, I'm trying to avoid creating new columns, and keeping the trigger adjustments to a minimum. But I'm open to any and all suggestions. Thanks for for your time and thoughts.

      -Jamie
        • 1. Re: Creating a single context index on a one-to-many and lookup table
          Barbara Boehmer
          I would create a procedure that creates a virtual document with tags, which is what the multi_column_datatstore does behind the scenes. Then I would use that procedure in a user_datastore, so the result is the same for multiple tables as what a multi_column_datastore does for one table. I would also use either auto_section_group or some other type of section group, so that you can search using WITHIN as with the multi_column_datastore. Please see the demonstration below.
          SCOTT@orcl_11gR2> -- tables and data that you provided:
          SCOTT@orcl_11gR2> CREATE TABLE SUBMISSION
            2  ( SUBMISSION_ID           NUMBER(10)          NOT NULL,
            3    SUBMISSION_NAME           VARCHAR2(100)          NOT NULL
            4  )
            5  /
          
          Table created.
          
          SCOTT@orcl_11gR2> CREATE TABLE ADVISOR_TYPE
            2  ( ADVISOR_TYPE_ID           NUMBER(10)          NOT NULL,
            3    ADVISOR_TYPE_NAME      VARCHAR2(50)          NOT NULL
            4  )
            5  /
          
          Table created.
          
          SCOTT@orcl_11gR2> CREATE TABLE SUBMISSION_ADVISORS
            2  ( SUBMISSION_ADVISORS_ID      NUMBER(10)          NOT NULL,
            3    SUBMISSION_ID           NUMBER(10)          NOT NULL,
            4    ADVISOR_TYPE_ID           NUMBER(10)          NOT NULL,
            5    FIRST_NAME           VARCHAR(50)          NULL,
            6    LAST_NAME           VARCHAR(50)          NULL,
            7    SUFFIX                VARCHAR(20)          NULL
            8  )
            9  /
          
          Table created.
          
          SCOTT@orcl_11gR2> INSERT ALL
            2  INTO SUBMISSION (SUBMISSION_ID, SUBMISSION_NAME)
            3    VALUES (1, 'Some Research Paper')
            4  INTO SUBMISSION (SUBMISSION_ID, SUBMISSION_NAME)
            5    VALUES (2, 'Thesis on 17th Century Weather Patterns')
            6  INTO SUBMISSION (SUBMISSION_ID, SUBMISSION_NAME)
            7    VALUES (3, 'Statistical Analysis on Sunny Days in March')
            8  SELECT * FROM DUAL
            9  /
          
          3 rows created.
          
          SCOTT@orcl_11gR2> INSERT ALL
            2  INTO ADVISOR_TYPE (ADVISOR_TYPE_ID, ADVISOR_TYPE_NAME)
            3    VALUES (1, 'Department Chair')
            4  INTO ADVISOR_TYPE (ADVISOR_TYPE_ID, ADVISOR_TYPE_NAME)
            5    VALUES (2, 'Department Co-Chair')
            6  INTO ADVISOR_TYPE (ADVISOR_TYPE_ID, ADVISOR_TYPE_NAME)
            7    VALUES (3, 'Professor')
            8  INTO ADVISOR_TYPE (ADVISOR_TYPE_ID, ADVISOR_TYPE_NAME)
            9    VALUES (4, 'Associate Professor')
           10  INTO ADVISOR_TYPE (ADVISOR_TYPE_ID, ADVISOR_TYPE_NAME)
           11    VALUES (5, 'Scientist')
           12  SELECT * FROM DUAL
           13  /
          
          5 rows created.
          
          SCOTT@orcl_11gR2> INSERT ALL
            2  INTO SUBMISSION_ADVISORS (SUBMISSION_ADVISORS_ID, SUBMISSION_ID, ADVISOR_TYPE_ID, FIRST_NAME, LAST_NAME, SUFFIX)
            3    VALUES (1,1,2,'John', 'Doe', 'PhD')
            4  INTO SUBMISSION_ADVISORS (SUBMISSION_ADVISORS_ID, SUBMISSION_ID, ADVISOR_TYPE_ID, FIRST_NAME, LAST_NAME, SUFFIX)
            5    VALUES (2,1,2,'Jane', 'Doe', 'PhD')
            6  INTO SUBMISSION_ADVISORS (SUBMISSION_ADVISORS_ID, SUBMISSION_ID, ADVISOR_TYPE_ID, FIRST_NAME, LAST_NAME, SUFFIX)
            7    VALUES (3,2,3,'Johan', 'Smith', NULL)
            8  INTO SUBMISSION_ADVISORS (SUBMISSION_ADVISORS_ID, SUBMISSION_ID, ADVISOR_TYPE_ID, FIRST_NAME, LAST_NAME, SUFFIX)
            9    VALUES (4,2,4,'Magnus', 'Jackson', 'MS')
           10  INTO SUBMISSION_ADVISORS (SUBMISSION_ADVISORS_ID, SUBMISSION_ID, ADVISOR_TYPE_ID, FIRST_NAME, LAST_NAME, SUFFIX)
           11    VALUES (5,3,5,'Williard', 'Forsberg', 'AMS')
           12  SELECT * FROM DUAL
           13  /
          
          5 rows created.
          
          SCOTT@orcl_11gR2> -- constraints presumed based on your description:
          SCOTT@orcl_11gR2> ALTER TABLE submission ADD CONSTRAINT submission_id_pk
            2    PRIMARY KEY (submission_id)
            3  /
          
          Table altered.
          
          SCOTT@orcl_11gR2> ALTER TABLE advisor_type ADD CONSTRAINT advisor_type_id_pk
            2    PRIMARY KEY (advisor_type_id)
            3  /
          
          Table altered.
          
          SCOTT@orcl_11gR2> ALTER TABLE submission_advisors ADD CONSTRAINT submission_advisors_id_pk
            2    PRIMARY KEY (submission_advisors_id)
            3  /
          
          Table altered.
          
          SCOTT@orcl_11gR2> ALTER TABLE submission_advisors ADD CONSTRAINT submission_id_fk
            2    FOREIGN KEY (submission_id) REFERENCES submission (submission_id)
            3  /
          
          Table altered.
          
          SCOTT@orcl_11gR2> ALTER TABLE submission_advisors ADD CONSTRAINT advisor_type_id_fk
            2    FOREIGN KEY (advisor_type_id) REFERENCES advisor_type (advisor_type_id)
            3  /
          
          Table altered.
          
          SCOTT@orcl_11gR2> -- resulting data:
          SCOTT@orcl_11gR2> COLUMN submission_name FORMAT A45
          SCOTT@orcl_11gR2> COLUMN advisor      FORMAT A40
          SCOTT@orcl_11gR2> SELECT s.submission_name,
            2           a.advisor_type_name || ' ' ||
            3           sa.first_name || ' ' ||
            4           sa.last_name || ' ' ||
            5           sa.suffix AS advisor
            6  FROM   submission_advisors sa,
            7           submission s,
            8           advisor_type a
            9  WHERE  sa.advisor_type_id = a.advisor_type_id
           10  AND    sa.submission_id = s.submission_id
           11  /
          
          SUBMISSION_NAME                               ADVISOR
          --------------------------------------------- ----------------------------------------
          Some Research Paper                           Department Co-Chair John Doe PhD
          Some Research Paper                           Department Co-Chair Jane Doe PhD
          Thesis on 17th Century Weather Patterns       Professor Johan Smith
          Thesis on 17th Century Weather Patterns       Associate Professor Magnus Jackson MS
          Statistical Analysis on Sunny Days in March   Scientist Williard Forsberg AMS
          
          5 rows selected.
          
          SCOTT@orcl_11gR2> -- procedure to create virtual documents:
          SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE submission_advisors_proc
            2    (p_rowid IN           ROWID,
            3       p_clob     IN OUT NOCOPY CLOB)
            4  AS
            5  BEGIN
            6    FOR r1 IN
            7        (SELECT *
            8         FROM      submission_advisors
            9         WHERE  ROWID = p_rowid)
           10    LOOP
           11        IF r1.first_name IS NOT NULL THEN
           12          DBMS_LOB.WRITEAPPEND (p_clob, 12, '<first_name>');
           13          DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r1.first_name), r1.first_name);
           14          DBMS_LOB.WRITEAPPEND (p_clob, 13, '</first_name>');
           15        END IF;
           16        IF r1.last_name IS NOT NULL THEN
           17          DBMS_LOB.WRITEAPPEND (p_clob, 11, '<last_name>');
           18          DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r1.last_name), r1.last_name);
           19          DBMS_LOB.WRITEAPPEND (p_clob, 12, '</last_name>');
           20        END IF;
           21        IF r1.suffix IS NOT NULL THEN
           22          DBMS_LOB.WRITEAPPEND (p_clob, 8, '<suffix>');
           23          DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r1.suffix), r1.suffix);
           24          DBMS_LOB.WRITEAPPEND (p_clob, 9, '</suffix>');
           25        END IF;
           26        FOR r2 IN
           27          (SELECT *
           28           FROM   submission
           29           WHERE  submission_id = r1.submission_id)
           30        LOOP
           31          DBMS_LOB.WRITEAPPEND (p_clob, 17, '<submission_name>');
           32          DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r2.submission_name), r2.submission_name);
           33          DBMS_LOB.WRITEAPPEND (p_clob, 18, '</submission_name>');
           34        END LOOP;
           35        FOR r3 IN
           36          (SELECT *
           37           FROM   advisor_type
           38           WHERE  advisor_type_id = r1.advisor_type_id)
           39        LOOP
           40          DBMS_LOB.WRITEAPPEND (p_clob, 19, '<advisor_type_name>');
           41          DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r3.advisor_type_name), r3.advisor_type_name);
           42          DBMS_LOB.WRITEAPPEND (p_clob, 20, '</advisor_type_name>');
           43        END LOOP;
           44    END LOOP;
           45  END submission_advisors_proc;
           46  /
          
          Procedure created.
          
          SCOTT@orcl_11gR2> SHOW ERRORS
          No errors.
          SCOTT@orcl_11gR2> -- examples of virtual documents that procedure creates:
          SCOTT@orcl_11gR2> DECLARE
            2    v_clob  CLOB := EMPTY_CLOB();
            3  BEGIN
            4    FOR r IN
            5        (SELECT ROWID rid FROM submission_advisors)
            6    LOOP
            7        DBMS_LOB.CREATETEMPORARY (v_clob, TRUE);
            8        submission_advisors_proc (r.rid, v_clob);
            9        DBMS_OUTPUT.PUT_LINE (v_clob);
           10        DBMS_LOB.FREETEMPORARY (v_clob);
           11    END LOOP;
           12  END;
           13  /
          <first_name>John</first_name><last_name>Doe</last_name><suffix>PhD</suffix><submission_name>Some
          Research Paper</submission_name><advisor_type_name>Department Co-Chair</advisor_type_name>
          <first_name>Jane</first_name><last_name>Doe</last_name><suffix>PhD</suffix><submission_name>Some
          Research Paper</submission_name><advisor_type_name>Department Co-Chair</advisor_type_name>
          <first_name>Johan</first_name><last_name>Smith</last_name><submission_name>Thesis on 17th Century
          Weather Patterns</submission_name><advisor_type_name>Professor</advisor_type_name>
          <first_name>Magnus</first_name><last_name>Jackson</last_name><suffix>MS</suffix><submission_name>The
          sis on 17th Century Weather Patterns</submission_name><advisor_type_name>Associate
          Professor</advisor_type_name>
          <first_name>Williard</first_name><last_name>Forsberg</last_name><suffix>AMS</suffix><submission_name
          
          Statistical Analysis on Sunny Days in
          March</submission_name><advisor_type_name>Scientist</advisor_type_name> PL/SQL procedure successfully completed. SCOTT@orcl_11gR2> -- user_datastore that uses procedure: SCOTT@orcl_11gR2> BEGIN   2    CTX_DDL.CREATE_PREFERENCE ('sa_datastore', 'USER_DATASTORE');   3    CTX_DDL.SET_ATTRIBUTE ('sa_datastore', 'PROCEDURE', 'submission_advisors_proc');   4  END;   5  / PL/SQL procedure successfully completed. SCOTT@orcl_11gR2> -- index (on optional extra column) that uses user_datastore and section group: SCOTT@orcl_11gR2> ALTER TABLE submission_advisors ADD (any_column VARCHAR2(1))   2  / Table altered. SCOTT@orcl_11gR2> CREATE INDEX submission_advisors_idx   2  ON submission_advisors (any_column)   3  INDEXTYPE IS CTXSYS.CONTEXT   4  PARAMETERS   5    ('DATASTORE     sa_datastore   6        SECTION GROUP     CTXSYS.AUTO_SECTION_GROUP')   7  / Index created. SCOTT@orcl_11gR2> -- what is tokenized, indexed, and searchable: SCOTT@orcl_11gR2> SELECT token_text FROM dr$submission_advisors_idx$i   2  / TOKEN_TEXT ---------------------------------------------------------------- 17TH ADVISOR_TYPE_NAME AMS ANALYSIS ASSOCIATE CENTURY CHAIR CO DAYS DEPARTMENT DOE FIRST_NAME FORSBERG JACKSON JANE JOHAN JOHN LAST_NAME MAGNUS MARCH PAPER PATTERNS PHD PROFESSOR RESEARCH SCIENTIST SMITH STATISTICAL SUBMISSION_NAME SUFFIX SUNNY THESIS WEATHER WILLIARD 34 rows selected. SCOTT@orcl_11gR2> -- sample searches across all data: SCOTT@orcl_11gR2> VARIABLE search_string VARCHAR2(100) SCOTT@orcl_11gR2> EXEC :search_string := 'professor' PL/SQL procedure successfully completed. SCOTT@orcl_11gR2> SELECT s.submission_name,   2           a.advisor_type_name || ' ' ||   3           sa.first_name || ' ' ||   4           sa.last_name || ' ' ||   5           sa.suffix AS advisor   6  FROM   submission_advisors sa,   7           submission s,   8           advisor_type a   9  WHERE  CONTAINS (sa.any_column, :search_string) > 0 10  AND    sa.advisor_type_id = a.advisor_type_id 11  AND    sa.submission_id = s.submission_id 12  / SUBMISSION_NAME                               ADVISOR --------------------------------------------- ---------------------------------------- Thesis on 17th Century Weather Patterns       Professor Johan Smith Thesis on 17th Century Weather Patterns       Associate Professor Magnus Jackson MS 2 rows selected. SCOTT@orcl_11gR2> EXEC :search_string := 'doe' PL/SQL procedure successfully completed. SCOTT@orcl_11gR2> / SUBMISSION_NAME                               ADVISOR --------------------------------------------- ---------------------------------------- Some Research Paper                           Department Co-Chair John Doe PhD Some Research Paper                           Department Co-Chair Jane Doe PhD 2 rows selected. SCOTT@orcl_11gR2> EXEC :search_string := 'paper' PL/SQL procedure successfully completed. SCOTT@orcl_11gR2> / SUBMISSION_NAME                               ADVISOR --------------------------------------------- ---------------------------------------- Some Research Paper                           Department Co-Chair John Doe PhD Some Research Paper                           Department Co-Chair Jane Doe PhD 2 rows selected. SCOTT@orcl_11gR2> -- sample searches within specific columns: SCOTT@orcl_11gR2> EXEC :search_string := 'chair' PL/SQL procedure successfully completed. SCOTT@orcl_11gR2> SELECT s.submission_name,   2           a.advisor_type_name || ' ' ||   3           sa.first_name || ' ' ||   4           sa.last_name || ' ' ||   5           sa.suffix AS advisor   6  FROM   submission_advisors sa,   7           submission s,   8           advisor_type a   9  WHERE  CONTAINS (sa.any_column, :search_string || ' WITHIN advisor_type_name') > 0 10  AND    sa.advisor_type_id = a.advisor_type_id 11  AND    sa.submission_id = s.submission_id 12  / SUBMISSION_NAME                               ADVISOR --------------------------------------------- ---------------------------------------- Some Research Paper                           Department Co-Chair John Doe PhD Some Research Paper                           Department Co-Chair Jane Doe PhD 2 rows selected. SCOTT@orcl_11gR2> EXEC :search_string := 'phd' PL/SQL procedure successfully completed. SCOTT@orcl_11gR2> SELECT s.submission_name,   2           a.advisor_type_name || ' ' ||   3           sa.first_name || ' ' ||   4           sa.last_name || ' ' ||   5           sa.suffix AS advisor   6  FROM   submission_advisors sa,   7           submission s,   8           advisor_type a   9  WHERE  CONTAINS (sa.any_column, :search_string || ' WITHIN suffix') > 0 10  AND    sa.advisor_type_id = a.advisor_type_id 11  AND    sa.submission_id = s.submission_id 12  / SUBMISSION_NAME                               ADVISOR --------------------------------------------- ---------------------------------------- Some Research Paper                           Department Co-Chair John Doe PhD Some Research Paper                           Department Co-Chair Jane Doe PhD 2 rows selected. SCOTT@orcl_11gR2> EXEC :search_string := 'weather' PL/SQL procedure successfully completed. SCOTT@orcl_11gR2> SELECT s.submission_name,   2           a.advisor_type_name || ' ' ||   3           sa.first_name || ' ' ||   4           sa.last_name || ' ' ||   5           sa.suffix AS advisor   6  FROM   submission_advisors sa,   7           submission s,   8           advisor_type a   9  WHERE  CONTAINS (sa.any_column, :search_string || ' WITHIN submission_name') > 0 10  AND    sa.advisor_type_id = a.advisor_type_id 11  AND    sa.submission_id = s.submission_id 12  / SUBMISSION_NAME                               ADVISOR --------------------------------------------- ---------------------------------------- Thesis on 17th Century Weather Patterns       Professor Johan Smith Thesis on 17th Century Weather Patterns       Associate Professor Magnus Jackson MS 2 rows selected.
          • 2. Re: Creating a single context index on a one-to-many and lookup table
            JamieC
            Barbara,

            Thank you for the wonderful example. I have plenty of material to model my actual index off of now. I also learned that I don't need to create a dummy section group for ones of the auto variety.

            Just a follow-up question... Is there a best practice or more commonly used practice when it comes to which column to put the text index on. Whether it's better to throw it on a dummy column (like any_column ) in your example, or to just throw it on any column that has a valid datatype for the index? I can see pros and cons of both, but want to try to follow the most commonly used method.

            Thanks again,
            -Jamie
            • 3. Re: Creating a single context index on a one-to-many and lookup table
              Barbara Boehmer
              Is there a best practice or more commonly used practice when it comes to which column to put the text index on. Whether it's better to throw it on a dummy column (like any_column ) in your example, or to just throw it on any column that has a valid datatype for the index? I can see pros and cons of both, but want to try to follow the most commonly used method.
              I don't know what is most commonly used. Any updates will be triggered only when the column that the index is on is updated. I prefer using a separate column, typically with a name that will make sense when used in the query, such as contains(any_column...). Usage of just one existing column tends to give the misimpression that column is the only one being searched. However, many people would argue against adding unnecessary columns, even just one character in length. Also, if you create the index on a column that is updated any time that the others are updated, then there is no need to add an extra process or trigger to update a dummy column.

              Perhaps some others will share their thoughts on this topic.