10 Replies Latest reply: Jan 28, 2013 1:56 PM by Barbara Boehmer RSS

    using 2 catsearch in where clause

    Emily Robertson
      Hi all,
      I have problem using 2 catsearch in where clause, am not sure if there is any solution this. i have created a simple test data as below:-

      -- Test Code
      create table cust_catalog (
      id number(16),
      firstname varchar2(80),
      surname varchar2(80),
      birth varchar2(25),
      age numeric )

      INSERT ALL
      INTO cust_catalog VALUES ('1','John','Smith','Glasgow','52')
      INTO cust_catalog VALUES ('2','Emaily','Johnson','Aberdeen','55')
      INTO cust_catalog VALUES ('3','David','Miles','Leeds','53')
      INTO cust_catalog VALUES ('4','Keive','Johnny','London','45')
      INTO cust_catalog VALUES ('5','Jenny','Smithy','Norwich','35')
      INTO cust_catalog VALUES ('6','Andy','Mil','Aberdeen','63')
      INTO cust_catalog VALUES ('7','Andrew','Smith','London','64')
      INTO cust_catalog VALUES ('8','John','Smith','London','54')
      INTO cust_catalog VALUES ('9','John','Henson','London','56')
      INTO cust_catalog VALUES ('10','John','Mil','London','58')
      INTO cust_catalog VALUES ('11','Jon','Smith','Glasgow','57')
      INTO cust_catalog VALUES ('12','Jen','Smith','Glasgow','60')
      INTO cust_catalog VALUES ('13','Chris','Smith','Glasgow','59')
      SELECT * FROM DUAL

      EXEC CTX_DDL.create_index_set('cust_iset');
      EXEC CTX_DDL.CREATE_PREFERENCE ('cust_lexer', 'BASIC_LEXER');
      EXEC CTX_DDL.SET_ATTRIBUTE('cust_lexer', 'SKIPJOINS' , ',''."+-()/');
      EXEC CTX_DDL.Create_Preference('cust_wildcard_pref', 'BASIC_WORDLIST');
      EXEC CTX_DDL.set_attribute('cust_wildcard_pref', 'prefix_index', 'YES');
      EXEC CTX_DDL.ADD_INDEX('cust_iset','id');
      EXEC CTX_DDL.ADD_INDEX('cust_iset','birth');
      EXEC CTX_DDL.ADD_INDEX('cust_iset','age');

      CREATE INDEX FIRSTNAME_IDX ON cust_catalog(firstname) INDEXTYPE IS CTXSYS.CTXCAT
      PARAMETERS ('index set cust_iset LEXER cust_lexer Wordlist cust_wildcard_pref');

      CREATE INDEX SURNAME_IDX ON cust_catalog(surname) INDEXTYPE IS CTXSYS.CTXCAT
      PARAMETERS ('index set cust_iset LEXER cust_lexer Wordlist cust_wildcard_pref');

      EXEC DBMS_STATS.GATHER_TABLE_STATS('WORKAROUND', 'CUST_CATALOG', cascade=>TRUE);

      -- For removing test data
      drop table cust_catalog;
      EXEC CTX_DDL.DROP_INDEX_SET('cust_iset');
      EXEC CTX_DDL.DROP_PREFERENCE('cust_lexer');
      EXEC CTX_DDL.DROP_PREFERENCE('cust_wildcard_pref');
      DROP INDEX FIRSTNAME_IDX ;
      DROP INDEX SURNAME_IDX ;

      ------- QUESTIONS IN HERE -------------------------------------------------

      SELECT * FROM cust_catalog WHERE ctxsys.catsearch (firstname, 'John','age BETWEEN 40 AND 70 AND birth=''Glasgow''')>0

      I have no problem running above query

      BUT if i add 2 catsearch on both firstname and surname, i have error ~ catsearch does not support function invocation

      SELECT * FROM cust_catalog WHERE ctxsys.catsearch (firstname, 'John','age BETWEEN 40 AND 70 AND birth=''Glasgow''')>0 AND
      ctxsys.catsearch (surname, 'Smith','age BETWEEN 40 AND 70 AND birth=''Glasgow''')>0

      :(
        • 1. Re: using 2 catsearch in where clause
          Barbara Boehmer
          There is a problem with ctxcat indexes and catsearch that the optimizer may choose functional invocation, which catsearch does not support, resulting in an error. This is more likely to happen when the structured portion of the query is very restrictive, but may happen any time. For this reason, I always recommend using a context index with contains instead of a ctxcat index with catsearch. In 11g, you can do just about anything with a context index that you can do with a ctxcat index.
          • 2. Re: using 2 catsearch in where clause
            Emily Robertson
            hi Barbara,

            Thanks for reply, i might need a bit of help on create context index. much much appreciated.

            create table cust_catalog (
            id number(16),
            firstname varchar2(80),
            surname varchar2(80),
            birth varchar2(25),
            age numeric )

            INSERT ALL
            INTO cust_catalog VALUES ('1','John','Smith','Glasgow','52')
            INTO cust_catalog VALUES ('2','Emaily','Johnson','Aberdeen','55')
            INTO cust_catalog VALUES ('3','David','Miles','Leeds','53')
            INTO cust_catalog VALUES ('4','Keive','Johnny','London','45')
            INTO cust_catalog VALUES ('5','Jenny','Smithy','Norwich','35')
            INTO cust_catalog VALUES ('6','Andy','Mil','Aberdeen','63')
            INTO cust_catalog VALUES ('7','Andrew','Smith','London','64')
            INTO cust_catalog VALUES ('8','John','Smith','London','54')
            INTO cust_catalog VALUES ('9','John','Henson','London','56')
            INTO cust_catalog VALUES ('10','John','Mil','London','58')
            INTO cust_catalog VALUES ('11','Jon','Smith','Glasgow','57')
            INTO cust_catalog VALUES ('12','Jen','Smith','Glasgow','60')
            INTO cust_catalog VALUES ('13','Chris','Smith','Glasgow','59')
            SELECT * FROM DUAL

            EXEC CTX_DDL.CREATE_PREFERENCE ('your_datastore', 'MULTI_COLUMN_DATASTORE');
            EXEC CTX_DDL.SET_ATTRIBUTE ('your_datastore', 'COLUMNS', 'firstname, surname');

            EXEC CTX_DDL.CREATE_SECTION_GROUP ('your_sec', 'BASIC_SECTION_GROUP');
            EXEC CTX_DDL.ADD_FIELD_SECTION ('your_sec', 'firstname', 'firstname', TRUE);
            EXEC CTX_DDL.ADD_FIELD_SECTION ('your_sec', 'surname', 'surname', TRUE);
            ----------------------------------------
            Question in here
            ----------------------------------------
            if i have created aboved table, with multi columns with firstname and surname, what shoud my index create based on,
            do i need to create a dummy column varchars for index??

            CREATE INDEX context_idx ON cust_catalog (NOT-SURE-WHAT-INDEX-SHOULD-BASED-ON)
            INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('datastore     your_datastore section group your_sec')
            • 3. Re: using 2 catsearch in where clause
              Roger Ford-Oracle
              You can use any existing column, or add a dummy column.

              You just need to remember
              (1) The column used in the CONTAINS clause must match the column you created the index on, and
              (2) If any columns are updated, other than the one you actually created the index on, then you need to make sure the indexed column gets updated too. This is usually done with a trigger.

              I've expanded your example:
              drop table cust_catalog
              /
              
              create table cust_catalog (
              id number(16),
              firstname varchar2(80),
              surname varchar2(80),
              birth varchar2(25),
              age numeric )
              /
              
              INSERT ALL
              INTO cust_catalog VALUES ('1','John','Smith','Glasgow','52')
              INTO cust_catalog VALUES ('2','Emaily','Johnson','Aberdeen','55')
              INTO cust_catalog VALUES ('3','David','Miles','Leeds','53')
              INTO cust_catalog VALUES ('4','Keive','Johnny','London','45')
              INTO cust_catalog VALUES ('5','Jenny','Smithy','Norwich','35')
              INTO cust_catalog VALUES ('6','Andy','Mil','Aberdeen','63')
              INTO cust_catalog VALUES ('7','Andrew','Smith','London','64')
              INTO cust_catalog VALUES ('8','John','Smith','London','54')
              INTO cust_catalog VALUES ('9','John','Henson','London','56')
              INTO cust_catalog VALUES ('10','John','Mil','London','58')
              INTO cust_catalog VALUES ('11','Jon','Smith','Glasgow','57')
              INTO cust_catalog VALUES ('12','Jen','Smith','Glasgow','60')
              INTO cust_catalog VALUES ('13','Chris','Smith','Glasgow','59')
              SELECT * FROM DUAL
              /
              
              EXEC CTX_DDL.DROP_PREFERENCE   ('your_datastore')
              EXEC CTX_DDL.CREATE_PREFERENCE ('your_datastore', 'MULTI_COLUMN_DATASTORE')
              EXEC CTX_DDL.SET_ATTRIBUTE ('your_datastore', 'COLUMNS', 'firstname, surname')
              
              EXEC CTX_DDL.DROP_SECTION_GROUP   ('your_sec')
              EXEC CTX_DDL.CREATE_SECTION_GROUP ('your_sec', 'BASIC_SECTION_GROUP')
              EXEC CTX_DDL.ADD_FIELD_SECTION ('your_sec', 'firstname', 'firstname', TRUE)
              EXEC CTX_DDL.ADD_FIELD_SECTION ('your_sec', 'surname', 'surname', TRUE)
              
              CREATE INDEX context_idx ON cust_catalog (firstname)
              INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('datastore your_datastore section group your_sec')
              /
              
              CREATE OR REPLACE TRIGGER your_upd_trigger 
              AFTER UPDATE ON cust_catalog
                FOR EACH ROW
              BEGIN
                IF :new.surname != :old.surname THEN
                  -- This is wrong:    UPDATE cust_catalog SET firstname = :new.firstname
                  -- This is wrong:    WHERE id = :new.id;
                  --  Next line replaces those two lines
                  :new.firstname := :new.firstname;
                END IF;
              END;
              /
              show err
              --list
              
              SELECT * FROM cust_catalog WHERE contains (firstname, '(john WITHIN firstname) or (miles WITHIN surname) or (jen)') > 0
              /
              Edited by: Roger Ford on Jan 28, 2013 6:33 AM
              Trigger did not work correctly, so edited to work as it should.
              • 4. Re: using 2 catsearch in where clause
                Barbara Boehmer
                The following expands the example to include the birth (as part of the multi_column_datastore) and age (using filter by during index creation and sdata in the query) and show the execution plan. I have provided the script and execution separately.

                -- script:
                DROP TABLE cust_catalog
                /
                EXEC CTX_DDL.DROP_PREFERENCE ('cust_lexer')
                EXEC CTX_DDL.DROP_PREFERENCE ('cust_wildcard_pref')
                EXEC CTX_DDL.DROP_PREFERENCE ('your_datastore')
                EXEC CTX_DDL.DROP_SECTION_GROUP ('your_sec')
                CREATE TABLE cust_catalog 
                  (id         NUMBER   (16),
                   firstname  VARCHAR2 (80),
                   surname    VARCHAR2 (80),
                   birth      VARCHAR2 (25),
                   age        NUMERIC)
                / 
                INSERT ALL
                  INTO cust_catalog VALUES (1,  'John',   'Smith',   'Glasgow',  52)
                  INTO cust_catalog VALUES (2,  'Emaily', 'Johnson', 'Aberdeen', 55)
                  INTO cust_catalog VALUES (3,  'David',  'Miles',   'Leeds',    53)
                  INTO cust_catalog VALUES (4,  'Keive',  'Johnny',  'London',   45)
                  INTO cust_catalog VALUES (5,  'Jenny',  'Smithy',  'Norwich',  35)
                  INTO cust_catalog VALUES (6,  'Andy',   'Mil',     'Aberdeen', 63)
                  INTO cust_catalog VALUES (7,  'Andrew', 'Smith',   'London',   64)
                  INTO cust_catalog VALUES (8,  'John',   'Smith',   'London',   54)
                  INTO cust_catalog VALUES (9,  'John',   'Henson',  'London',   56)
                  INTO cust_catalog VALUES (10, 'John',   'Mil',     'London',   58)
                  INTO cust_catalog VALUES (11, 'Jon',    'Smith',   'Glasgow',  57)
                  INTO cust_catalog VALUES (12, 'Jen',    'Smith',   'Glasgow',  60)
                  INTO cust_catalog VALUES (13, 'Chris',  'Smith',   'Glasgow',  59)
                SELECT * FROM DUAL
                / 
                EXEC CTX_DDL.CREATE_PREFERENCE ('cust_lexer', 'BASIC_LEXER')
                EXEC CTX_DDL.SET_ATTRIBUTE ('cust_lexer', 'SKIPJOINS' , ',''."+-()/')
                EXEC CTX_DDL.Create_Preference ('cust_wildcard_pref', 'BASIC_WORDLIST')
                EXEC CTX_DDL.set_attribute ('cust_wildcard_pref', 'prefix_index', 'YES')
                EXEC CTX_DDL.CREATE_PREFERENCE ('your_datastore', 'MULTI_COLUMN_DATASTORE')
                EXEC CTX_DDL.SET_ATTRIBUTE ('your_datastore', 'COLUMNS', 'firstname, surname, birth')
                EXEC CTX_DDL.CREATE_SECTION_GROUP ('your_sec', 'BASIC_SECTION_GROUP')
                EXEC CTX_DDL.ADD_FIELD_SECTION ('your_sec', 'firstname', 'firstname', TRUE)
                EXEC CTX_DDL.ADD_FIELD_SECTION ('your_sec', 'surname', 'surname', TRUE)
                EXEC CTX_DDL.ADD_FIELD_SECTION ('your_sec', 'birth', 'birth', TRUE)
                CREATE INDEX context_idx 
                ON cust_catalog (firstname)
                INDEXTYPE IS CTXSYS.CONTEXT 
                FILTER BY age
                PARAMETERS
                  ('DATASTORE      your_datastore 
                    SECTION GROUP  your_sec
                    LEXER          cust_lexer
                    WORDLIST       cust_wildcard_pref')
                / 
                COLUMN firstname FORMAT A10
                COLUMN surname   FORMAT A10
                COLUMN birth     FORMAT A10
                SET AUTOTRACE ON EXPLAIN
                SELECT * FROM cust_catalog 
                WHERE  CONTAINS 
                         (firstname, 
                          '(John WITHIN firstname) 
                            AND (Glasgow WITHIN birth) 
                            AND (SDATA (age BETWEEN 40 AND 70))') > 0
                /
                SET AUTOTRACE OFF
                -- execution:
                SCOTT@orcl_11gR2> DROP TABLE cust_catalog
                  2  /
                
                Table dropped.
                
                SCOTT@orcl_11gR2> EXEC CTX_DDL.DROP_PREFERENCE ('cust_lexer')
                
                PL/SQL procedure successfully completed.
                
                SCOTT@orcl_11gR2> EXEC CTX_DDL.DROP_PREFERENCE ('cust_wildcard_pref')
                
                PL/SQL procedure successfully completed.
                
                SCOTT@orcl_11gR2> EXEC CTX_DDL.DROP_PREFERENCE ('your_datastore')
                
                PL/SQL procedure successfully completed.
                
                SCOTT@orcl_11gR2> EXEC CTX_DDL.DROP_SECTION_GROUP ('your_sec')
                
                PL/SQL procedure successfully completed.
                
                SCOTT@orcl_11gR2> CREATE TABLE cust_catalog
                  2    (id        NUMBER   (16),
                  3       firstname  VARCHAR2 (80),
                  4       surname    VARCHAR2 (80),
                  5       birth        VARCHAR2 (25),
                  6       age        NUMERIC)
                  7  /
                
                Table created.
                
                SCOTT@orcl_11gR2> INSERT ALL
                  2    INTO cust_catalog VALUES (1,  'John',   'Smith',   'Glasgow',  52)
                  3    INTO cust_catalog VALUES (2,  'Emaily', 'Johnson', 'Aberdeen', 55)
                  4    INTO cust_catalog VALUES (3,  'David',  'Miles',   'Leeds',    53)
                  5    INTO cust_catalog VALUES (4,  'Keive',  'Johnny',  'London',   45)
                  6    INTO cust_catalog VALUES (5,  'Jenny',  'Smithy',  'Norwich',  35)
                  7    INTO cust_catalog VALUES (6,  'Andy',   'Mil',       'Aberdeen', 63)
                  8    INTO cust_catalog VALUES (7,  'Andrew', 'Smith',   'London',   64)
                  9    INTO cust_catalog VALUES (8,  'John',   'Smith',   'London',   54)
                 10    INTO cust_catalog VALUES (9,  'John',   'Henson',  'London',   56)
                 11    INTO cust_catalog VALUES (10, 'John',   'Mil',       'London',   58)
                 12    INTO cust_catalog VALUES (11, 'Jon',    'Smith',   'Glasgow',  57)
                 13    INTO cust_catalog VALUES (12, 'Jen',    'Smith',   'Glasgow',  60)
                 14    INTO cust_catalog VALUES (13, 'Chris',  'Smith',   'Glasgow',  59)
                 15  SELECT * FROM DUAL
                 16  /
                
                13 rows created.
                
                SCOTT@orcl_11gR2> EXEC CTX_DDL.CREATE_PREFERENCE ('cust_lexer', 'BASIC_LEXER')
                
                PL/SQL procedure successfully completed.
                
                SCOTT@orcl_11gR2> EXEC CTX_DDL.SET_ATTRIBUTE ('cust_lexer', 'SKIPJOINS' , ',''."+-()/')
                
                PL/SQL procedure successfully completed.
                
                SCOTT@orcl_11gR2> EXEC CTX_DDL.Create_Preference ('cust_wildcard_pref', 'BASIC_WORDLIST')
                
                PL/SQL procedure successfully completed.
                
                SCOTT@orcl_11gR2> EXEC CTX_DDL.set_attribute ('cust_wildcard_pref', 'prefix_index', 'YES')
                
                PL/SQL procedure successfully completed.
                
                SCOTT@orcl_11gR2> EXEC CTX_DDL.CREATE_PREFERENCE ('your_datastore', 'MULTI_COLUMN_DATASTORE')
                
                PL/SQL procedure successfully completed.
                
                SCOTT@orcl_11gR2> EXEC CTX_DDL.SET_ATTRIBUTE ('your_datastore', 'COLUMNS', 'firstname, surname, birth')
                
                PL/SQL procedure successfully completed.
                
                SCOTT@orcl_11gR2> EXEC CTX_DDL.CREATE_SECTION_GROUP ('your_sec', 'BASIC_SECTION_GROUP')
                
                PL/SQL procedure successfully completed.
                
                SCOTT@orcl_11gR2> EXEC CTX_DDL.ADD_FIELD_SECTION ('your_sec', 'firstname', 'firstname', TRUE)
                
                PL/SQL procedure successfully completed.
                
                SCOTT@orcl_11gR2> EXEC CTX_DDL.ADD_FIELD_SECTION ('your_sec', 'surname', 'surname', TRUE)
                
                PL/SQL procedure successfully completed.
                
                SCOTT@orcl_11gR2> EXEC CTX_DDL.ADD_FIELD_SECTION ('your_sec', 'birth', 'birth', TRUE)
                
                PL/SQL procedure successfully completed.
                
                SCOTT@orcl_11gR2> CREATE INDEX context_idx
                  2  ON cust_catalog (firstname)
                  3  INDEXTYPE IS CTXSYS.CONTEXT
                  4  FILTER BY age
                  5  PARAMETERS
                  6    ('DATASTORE     your_datastore
                  7        SECTION GROUP     your_sec
                  8        LEXER          cust_lexer
                  9        WORDLIST     cust_wildcard_pref')
                 10  /
                
                Index created.
                
                SCOTT@orcl_11gR2> COLUMN firstname FORMAT A10
                SCOTT@orcl_11gR2> COLUMN surname   FORMAT A10
                SCOTT@orcl_11gR2> COLUMN birth        FORMAT A10
                SCOTT@orcl_11gR2> SET AUTOTRACE ON EXPLAIN
                SCOTT@orcl_11gR2> SELECT * FROM cust_catalog
                  2  WHERE  CONTAINS
                  3             (firstname,
                  4              '(John WITHIN firstname)
                  5             AND (Glasgow WITHIN birth)
                  6             AND (SDATA (age BETWEEN 40 AND 70))') > 0
                  7  /
                
                        ID FIRSTNAME  SURNAME    BIRTH             AGE
                ---------- ---------- ---------- ---------- ----------
                         1 John       Smith      Glasgow            52
                
                1 row selected.
                
                
                Execution Plan
                ----------------------------------------------------------
                Plan hash value: 495863752
                
                --------------------------------------------------------------------------------------------
                | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
                --------------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT            |              |     1 |   136 |     4   (0)| 00:00:01 |
                |   1 |  TABLE ACCESS BY INDEX ROWID| CUST_CATALOG |     1 |   136 |     4   (0)| 00:00:01 |
                |*  2 |   DOMAIN INDEX              | CONTEXT_IDX  |       |       |     4   (0)| 00:00:01 |
                --------------------------------------------------------------------------------------------
                
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                
                   2 - access("CTXSYS"."CONTAINS"("FIRSTNAME",'(John WITHIN firstname)
                              AND (Glasgow WITHIN birth)             AND (SDATA (age BETWEEN 40 AND 70))')>0)
                
                Note
                -----
                   - dynamic sampling used for this statement (level=2)
                
                SCOTT@orcl_11gR2> SET AUTOTRACE OFF
                • 5. Re: using 2 catsearch in where clause
                  Emily Robertson
                  Thank you both Roger and Barbara, much much appreciated. both answer were really helpful :)
                  • 6. Re: using 2 catsearch in where clause
                    987553
                    Hello,
                    I apologize in advance for my bad english.

                    I tried to use your method for create CONTEXT index on multiple Columns, it's all ok but I have a a problem with TRIGGER.
                    When i try to update a record with trigger On, I get an error:
                    04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
                    *Cause:    A trigger (or a user defined plsql function that is referenced in
                    this statement) attempted to look at (or modify) a table that was
                    in the middle of being modified by the statement which fired it.
                    *Action:   Rewrite the trigger (or function) so it does not read that table.

                    You suggest to trigger the table after update but it seems that it is not possible.
                    Is there another solution?

                    Another question:
                    I must refresh the index (sync) or it will refresh at every insert/update?

                    Thank a lot for the answers!
                    • 7. Re: using 2 catsearch in where clause
                      Roger Ford-Oracle
                      You're right - and I apologise for not testing it properly!

                      You can actually simplify the trigger to remove the "UPDATE" and just set the column data directly as follows:
                      CREATE OR REPLACE TRIGGER your_upd_trigger 
                       AFTER UPDATE ON cust_catalog
                        FOR EACH ROW
                      BEGIN
                        IF :new.surname != :old.surname THEN
                          :new.firstname := :new.firstname;
                        END IF;
                      END;
                      /
                      I'll correct the original post.
                      • 8. Re: using 2 catsearch in where clause
                        987553
                        Hi,
                        I try with this solution but the problem remaining.

                        Report errori:
                        ORA-04084: impossibile cambiare i valori NEW per questo tipo di trigger
                        04084. 00000 - "cannot change NEW values for this trigger type"
                        *Cause:    New trigger variables can only be changed in before row
                        insert or update triggers.
                        *Action:   Change the trigger type or remove the variable reference.

                        I try with after and before, but it seems that is not possible to apply.
                        • 9. Re: using 2 catsearch in where clause
                          Roger Ford-Oracle
                          Definitely works for me. What version are you using?

                          Can you post a complete session log where you get this error?
                          • 10. Re: using 2 catsearch in where clause
                            Barbara Boehmer
                            The following works for me.
                            CREATE OR REPLACE TRIGGER your_upd_trigger 
                              BEFORE UPDATE ON cust_catalog
                              FOR EACH ROW
                            BEGIN
                              :NEW.firstname := :NEW.firstname;
                            END;
                            /