This discussion is archived
10 Replies Latest reply: Jan 28, 2013 11:56 AM by Barbara Boehmer RSS

using 2 catsearch in where clause

766669 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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
    766669 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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
    766669 Newbie
    Currently Being Moderated
    Thank you both Roger and Barbara, much much appreciated. both answer were really helpful :)
  • 6. Re: using 2 catsearch in where clause
    987553 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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;
    / 

Legend

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