2 Replies Latest reply: Nov 19, 2012 7:37 AM by Emily Robertson RSS

    Using Context fuzzy search in multiple params

    Emily Robertson
      Hi there, my previous post is regarding catserch and context search, i had advice from Roger and Barbara (both are extremely helpful ~thanks), am now focusing in using context search, but i have running to a fuzzy search problem. i did some research, didnlt get really far, i think this is a question for the expertise again, so sorry for the trouble, and sincerly thank you for your time.

      ------------------------------
      Test Data
      ----------------------------
      CREATE TABLE cust_catalog
      (id NUMBER (16),
      forename VARCHAR2 (80),
      surname VARCHAR2 (80),
      birthdate Date,
      gender VARCHAR2(10)
      )

      INSERT ALL
      INTO cust_catalog VALUES (1, 'John', 'Smith', to_date('10/03/1971','DD/MM/YYYY'), 'Male')
      INTO cust_catalog VALUES (2, 'Emaily', 'Johnson', to_date('05/07/1974','DD/MM/YYYY'), 'Female')
      INTO cust_catalog VALUES (3, 'David', 'Miles', to_date('16/10/1978','DD/MM/YYYY'), 'Male')
      INTO cust_catalog VALUES (4, 'Chris', 'Johnny', to_date('25/02/1976','DD/MM/YYYY'), 'Male')
      INTO cust_catalog VALUES (5, 'Jenny', 'Smithy', to_date('28/11/1977','DD/MM/YYYY'), 'Female')
      INTO cust_catalog VALUES (6, 'Andy', 'Mil', to_date('16/08/1975','DD/MM/YYYY'), 'Male')
      INTO cust_catalog VALUES (7, 'Andrew', 'Smithe', to_date('15/12/1974','DD/MM/YYYY'), 'Male')
      INTO cust_catalog VALUES (8, 'John', 'Smith', to_date('07/11/1972','DD/MM/YYYY'), 'Male')
      INTO cust_catalog VALUES (9, 'Willam John', 'Henson', to_date('04/01/1971','DD/MM/YYYY'), 'Male')
      INTO cust_catalog VALUES (10, 'Emma John', 'Mil', to_date('06/04/1979','DD/MM/YYYY'), 'Male')
      INTO cust_catalog VALUES (11, 'Jon', 'Smith', to_date('19/09/1977','DD/MM/YYYY'), 'Male')
      INTO cust_catalog VALUES (12, 'Jen', 'Smith', to_date('17/06/1978','DD/MM/YYYY'), 'Female')
      INTO cust_catalog VALUES (13, 'Chrissie','Smith', to_date('21/05/1975','DD/MM/YYYY'), 'Male')
      INTO cust_catalog VALUES (14, 'Chrisy', 'Smith', to_date('21/05/1975','DD/MM/YYYY'), 'Male')
      INTO cust_catalog VALUES (15, 'Chrisi', 'Smith', to_date('21/05/1975','DD/MM/YYYY'), 'Male')
      INTO cust_catalog VALUES (16, 'Johnny', 'Clark', to_date('21/05/1975','DD/MM/YYYY'), 'Male')
      INTO cust_catalog VALUES (17, 'Bobbie', 'Clarkson', to_date('21/05/1975','DD/MM/YYYY'), 'Male')
      INTO cust_catalog VALUES (18, 'Bob', 'Clark', to_date('21/05/1975','DD/MM/YYYY'), 'Male')
      INTO cust_catalog VALUES (19, 'Jone', 'Smith', to_date('21/05/1975','DD/MM/YYYY'), 'Male')
      INTO cust_catalog VALUES (20, 'Johan', 'Smith', to_date('21/05/1975','DD/MM/YYYY'), 'Male')
      INTO cust_catalog VALUES (21, 'John', 'Smithie', to_date('21/05/1975','DD/MM/YYYY'), 'Male')
      INTO cust_catalog VALUES (22, 'Chris', 'Smithey', to_date('21/05/1975','DD/MM/YYYY'), 'Male')
      INTO cust_catalog VALUES (23, 'John', 'Smithy', to_date('21/05/1975','DD/MM/YYYY'), 'Male')
      INTO cust_catalog VALUES (24, 'Chris', 'Smithie', to_date('21/05/1975','DD/MM/YYYY'), 'Male')
      INTO cust_catalog VALUES (25, 'John', 'Smithke', to_date('21/05/1975','DD/MM/YYYY'), 'Male')
      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 ('forename_datastore', 'MULTI_COLUMN_DATASTORE');
      EXEC CTX_DDL.SET_ATTRIBUTE ('forename_datastore', 'COLUMNS', 'forename');

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

      EXEC CTX_DDL.CREATE_SECTION_GROUP ('your_sec', 'BASIC_SECTION_GROUP');
      EXEC CTX_DDL.ADD_FIELD_SECTION ('your_sec', 'forename', 'forename', TRUE);
      EXEC CTX_DDL.ADD_FIELD_SECTION ('your_sec', 'surname', 'surname', TRUE);
      EXEC CTX_DDL.ADD_FIELD_SECTION ('your_sec', 'birthdate', 'birthdate', TRUE);

      CREATE INDEX forename_context_idx ON cust_catalog (forename) INDEXTYPE IS CTXSYS.CONTEXT
      FILTER BY Gender
      PARAMETERS
      ('DATASTORE forename_datastore
      SECTION GROUP your_sec
      LEXER cust_lexer
      WORDLIST cust_wildcard_pref')

      CREATE INDEX surname_context_idx ON cust_catalog (surname) INDEXTYPE IS CTXSYS.CONTEXT
      FILTER BY Gender
      PARAMETERS
      ('DATASTORE surname_datastore
      SECTION GROUP your_sec
      LEXER cust_lexer
      WORDLIST cust_wildcard_pref')


      -- this sql work ok if no fuzzy
      SELECT * FROM cust_catalog
      WHERE CONTAINS (forename,' john|chris ') > 0 AND CONTAINS (surname,'smith|Miles|Mil ') > 0

      -- cleaning
      DROP TABLE cust_catalog;
      EXEC CTX_DDL.DROP_PREFERENCE ('cust_lexer');
      EXEC CTX_DDL.DROP_PREFERENCE ('cust_wildcard_pref');
      EXEC CTX_DDL.DROP_PREFERENCE ('forename_datastore');
      EXEC CTX_DDL.DROP_PREFERENCE ('surname_datastore');
      EXEC CTX_DDL.DROP_SECTION_GROUP ('your_sec');
      DROP INDEX forename_context_idx;
      DROP INDEX surname_context_idx ;
      -------------------
      Questions Here
      -------------------
      1. i have problem when i try to implement fuzzy search

      SELECT * FROM cust_catalog
      WHERE CONTAINS (forename,' fuzzy({john|chris} , 1, 100, weight)') > 0
      AND CONTAINS(surname,'smith|Miles|Mil')>0

      2. Am also like to add on the birthdate range in search, am not sure what is the best way to do this, please commen on below

      SELECT * FROM cust_catalog
      WHERE CONTAINS (forename,' fuzzy({john|chris} , 1, 100, weight)') > 0
      AND CONTAINS (surname,'smith|Miles|Mil') > 0
      AND birthdate BETWEEN TO_DATE('01/02/1970','DD/MM/YYYY') AND TO_DATE('11/07/1980','DD/MM/YYYY')

      3. There is an overnight job insert new row to this table, Roger mention that i need to create a trigger to update the index, does it mean to DROP and CREATE indexes for both forename_context_idx and surname_context_idx everytime when the night job is running?

      Edited by: Emily Robertson on Nov 14, 2012 1:02 PM
        • 1. Re: Using Context fuzzy search in multiple params
          Barbara Boehmer
          For maximum efficiency, you need to use one index and one contains clause. You should put all of your text columns, like forename and surname and gender in your multi_column_datastore and section group. You can use filter by and sdata to add the date column to the index. When querying a date using sdata, the date must be in the format yyyy/mm/dd.

          You can create the index on any text column. Whatever column that you create the index on must be the column that you search on and must be the column that is updated in order to cause Oracle text to recognize that there has been an update.

          Fuzzy can only be applied to one term (word), so you need to use some concatenation and replace to change something like "fuzzy(word1|word2)" to "fuzzy(word1) | fuzzy(word2)".

          In the following example, I have used the table, lexer, and wordlist that you provided. I have used one multi_column_datastore with all three text columns (forename, surname, and gender) and put all three of those columns in the section group. I added a dummy column called any_column, and created the index on that column, filtering by birthdate, using the datastore, section group, lexer, and wordlist, and adding sync(on commit), which will cause the index to be synchronized any time that any row is inserted or deleted or the any_column that the index is created on is updated. I then inserted the data to show that synchronization takes place. I then demonstrated a query using all of the features that you requested and the explained plan shows that it uses one index hit to access everything, so it is a very efficient query.

          You will still need to optimize or rebuild or drop and recreate periodically to reduce the index fragmentation caused by frequent synchronization.

          -- script:
          -- table, lexer, and wordlist you provided:
          CREATE TABLE cust_catalog
            (id                NUMBER   (16),
             forename          VARCHAR2 (80),
             surname           VARCHAR2 (80),
             birthdate         DATE,
             gender            VARCHAR2 (10))
          /
          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');
          
          -- revised datastore, section group, added column, and index:
          EXEC CTX_DDL.CREATE_PREFERENCE ('names_and_gender_datastore', 'MULTI_COLUMN_DATASTORE');
          EXEC CTX_DDL.SET_ATTRIBUTE ('names_and_gender_datastore', 'COLUMNS', 'forename, surname, gender');
          
          EXEC CTX_DDL.CREATE_SECTION_GROUP ('your_sec', 'BASIC_SECTION_GROUP');
          EXEC CTX_DDL.ADD_FIELD_SECTION ('your_sec', 'forename', 'forename', TRUE);
          EXEC CTX_DDL.ADD_FIELD_SECTION ('your_sec', 'surname', 'surname', TRUE);
          EXEC CTX_DDL.ADD_FIELD_SECTION ('your_sec', 'gender', 'gender', TRUE);
          
          ALTER TABLE cust_catalog ADD (any_column  VARCHAR2(1))
          /
          CREATE INDEX all_columns_context_idx 
          ON cust_catalog (any_column) 
          INDEXTYPE IS CTXSYS.CONTEXT
          FILTER BY birthdate
          PARAMETERS
            ('DATASTORE      names_and_gender_datastore
              SECTION GROUP  your_sec
              LEXER          cust_lexer
              WORDLIST       cust_wildcard_pref
              SYNC           (ON COMMIT)')
          /
          -- data you provided:
          INSERT ALL
          INTO cust_catalog VALUES (1, 'John', 'Smith', to_date('10/03/1971','DD/MM/YYYY'), 'Male', null)
          INTO cust_catalog VALUES (2, 'Emaily', 'Johnson', to_date('05/07/1974','DD/MM/YYYY'), 'Female', null)
          INTO cust_catalog VALUES (3, 'David', 'Miles', to_date('16/10/1978','DD/MM/YYYY'), 'Male', null)
          INTO cust_catalog VALUES (4, 'Chris', 'Johnny', to_date('25/02/1976','DD/MM/YYYY'), 'Male', null)
          INTO cust_catalog VALUES (5, 'Jenny', 'Smithy', to_date('28/11/1977','DD/MM/YYYY'), 'Female', null)
          INTO cust_catalog VALUES (6, 'Andy', 'Mil', to_date('16/08/1975','DD/MM/YYYY'), 'Male', null)
          INTO cust_catalog VALUES (7, 'Andrew', 'Smithe', to_date('15/12/1974','DD/MM/YYYY'), 'Male', null)
          INTO cust_catalog VALUES (8, 'John', 'Smith', to_date('07/11/1972','DD/MM/YYYY'), 'Male', null)
          INTO cust_catalog VALUES (9, 'Willam John', 'Henson', to_date('04/01/1971','DD/MM/YYYY'), 'Male', null)
          INTO cust_catalog VALUES (10, 'Emma John', 'Mil', to_date('06/04/1979','DD/MM/YYYY'), 'Male', null)
          INTO cust_catalog VALUES (11, 'Jon', 'Smith', to_date('19/09/1977','DD/MM/YYYY'), 'Male', null)
          INTO cust_catalog VALUES (12, 'Jen', 'Smith', to_date('17/06/1978','DD/MM/YYYY'), 'Female', null)
          INTO cust_catalog VALUES (13, 'Chrissie','Smith', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
          INTO cust_catalog VALUES (14, 'Chrisy', 'Smith', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
          INTO cust_catalog VALUES (15, 'Chrisi', 'Smith', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
          INTO cust_catalog VALUES (16, 'Johnny', 'Clark', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
          INTO cust_catalog VALUES (17, 'Bobbie', 'Clarkson', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
          INTO cust_catalog VALUES (18, 'Bob', 'Clark', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
          INTO cust_catalog VALUES (19, 'Jone', 'Smith', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
          INTO cust_catalog VALUES (20, 'Johan', 'Smith', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
          INTO cust_catalog VALUES (21, 'John', 'Smithie', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
          INTO cust_catalog VALUES (22, 'Chris', 'Smithey', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
          INTO cust_catalog VALUES (23, 'John', 'Smithy', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
          INTO cust_catalog VALUES (24, 'Chris', 'Smithie', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
          INTO cust_catalog VALUES (25, 'John', 'Smithke', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
          SELECT * FROM DUAL
          /
          COMMIT
          /
          -- query:
          COLUMN forename FORMAT A10
          COLUMN surname  FORMAT A10
          SET AUTOTRACE ON EXPLAIN
          SELECT * FROM cust_catalog
          WHERE CONTAINS 
                  (any_column,
                   '(FUZZY (' || REPLACE ('john|chris', '|', ', 1, 100, WEIGHT) | FUZZY (') 
                    || ', 1, 100, WEIGHT) WITHIN forename) AND
                    (FUZZY (' || REPLACE ('smith|Miles|Mil', '|', ', 1, 100, WEIGHT) | FUZZY (') 
                    || ', 1, 100, WEIGHT) WITHIN surname) AND
                    (SDATA (birthdate BETWEEN ''1970/01/02'' AND ''1980/11/07''))') > 0
          /
          SET AUTOTRACE OFF
          -- cleaning
          DROP TABLE cust_catalog;
          EXEC CTX_DDL.DROP_PREFERENCE ('cust_lexer');
          EXEC CTX_DDL.DROP_PREFERENCE ('cust_wildcard_pref');
          EXEC CTX_DDL.DROP_PREFERENCE ('names_and_gender_datastore');
          EXEC CTX_DDL.DROP_SECTION_GROUP ('your_sec');
          -- execution:
          SCOTT@orcl_11gR2> -- table, lexer, and wordlist you provided:
          SCOTT@orcl_11gR2> CREATE TABLE cust_catalog
            2    (id            NUMBER   (16),
            3       forename       VARCHAR2 (80),
            4       surname        VARCHAR2 (80),
            5       birthdate       DATE,
            6       gender            VARCHAR2 (10))
            7  /
          
          Table 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> 
          SCOTT@orcl_11gR2> -- revised datastore, section group, added column, and index:
          SCOTT@orcl_11gR2> EXEC CTX_DDL.CREATE_PREFERENCE ('names_and_gender_datastore', 'MULTI_COLUMN_DATASTORE');
          
          PL/SQL procedure successfully completed.
          
          SCOTT@orcl_11gR2> EXEC CTX_DDL.SET_ATTRIBUTE ('names_and_gender_datastore', 'COLUMNS', 'forename, surname, gender');
          
          PL/SQL procedure successfully completed.
          
          SCOTT@orcl_11gR2> 
          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', 'forename', 'forename', 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', 'gender', 'gender', TRUE);
          
          PL/SQL procedure successfully completed.
          
          SCOTT@orcl_11gR2> 
          SCOTT@orcl_11gR2> ALTER TABLE cust_catalog ADD (any_column  VARCHAR2(1))
            2  /
          
          Table altered.
          
          SCOTT@orcl_11gR2> CREATE INDEX all_columns_context_idx
            2  ON cust_catalog (any_column)
            3  INDEXTYPE IS CTXSYS.CONTEXT
            4  FILTER BY birthdate
            5  PARAMETERS
            6    ('DATASTORE     names_and_gender_datastore
            7        SECTION GROUP     your_sec
            8        LEXER          cust_lexer
            9        WORDLIST     cust_wildcard_pref
           10        SYNC          (ON COMMIT)')
           11  /
          
          Index created.
          
          SCOTT@orcl_11gR2> -- data you provided:
          SCOTT@orcl_11gR2> INSERT ALL
            2  INTO cust_catalog VALUES (1, 'John', 'Smith', to_date('10/03/1971','DD/MM/YYYY'), 'Male', null)
            3  INTO cust_catalog VALUES (2, 'Emaily', 'Johnson', to_date('05/07/1974','DD/MM/YYYY'), 'Female', null)
            4  INTO cust_catalog VALUES (3, 'David', 'Miles', to_date('16/10/1978','DD/MM/YYYY'), 'Male', null)
            5  INTO cust_catalog VALUES (4, 'Chris', 'Johnny', to_date('25/02/1976','DD/MM/YYYY'), 'Male', null)
            6  INTO cust_catalog VALUES (5, 'Jenny', 'Smithy', to_date('28/11/1977','DD/MM/YYYY'), 'Female', null)
            7  INTO cust_catalog VALUES (6, 'Andy', 'Mil', to_date('16/08/1975','DD/MM/YYYY'), 'Male', null)
            8  INTO cust_catalog VALUES (7, 'Andrew', 'Smithe', to_date('15/12/1974','DD/MM/YYYY'), 'Male', null)
            9  INTO cust_catalog VALUES (8, 'John', 'Smith', to_date('07/11/1972','DD/MM/YYYY'), 'Male', null)
           10  INTO cust_catalog VALUES (9, 'Willam John', 'Henson', to_date('04/01/1971','DD/MM/YYYY'), 'Male', null)
           11  INTO cust_catalog VALUES (10, 'Emma John', 'Mil', to_date('06/04/1979','DD/MM/YYYY'), 'Male', null)
           12  INTO cust_catalog VALUES (11, 'Jon', 'Smith', to_date('19/09/1977','DD/MM/YYYY'), 'Male', null)
           13  INTO cust_catalog VALUES (12, 'Jen', 'Smith', to_date('17/06/1978','DD/MM/YYYY'), 'Female', null)
           14  INTO cust_catalog VALUES (13, 'Chrissie','Smith', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
           15  INTO cust_catalog VALUES (14, 'Chrisy', 'Smith', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
           16  INTO cust_catalog VALUES (15, 'Chrisi', 'Smith', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
           17  INTO cust_catalog VALUES (16, 'Johnny', 'Clark', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
           18  INTO cust_catalog VALUES (17, 'Bobbie', 'Clarkson', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
           19  INTO cust_catalog VALUES (18, 'Bob', 'Clark', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
           20  INTO cust_catalog VALUES (19, 'Jone', 'Smith', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
           21  INTO cust_catalog VALUES (20, 'Johan', 'Smith', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
           22  INTO cust_catalog VALUES (21, 'John', 'Smithie', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
           23  INTO cust_catalog VALUES (22, 'Chris', 'Smithey', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
           24  INTO cust_catalog VALUES (23, 'John', 'Smithy', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
           25  INTO cust_catalog VALUES (24, 'Chris', 'Smithie', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
           26  INTO cust_catalog VALUES (25, 'John', 'Smithke', to_date('21/05/1975','DD/MM/YYYY'), 'Male', null)
           27  SELECT * FROM DUAL
           28  /
          
          25 rows created.
          
          SCOTT@orcl_11gR2> COMMIT
            2  /
          
          Commit complete.
          
          SCOTT@orcl_11gR2> -- query:
          SCOTT@orcl_11gR2> COLUMN forename FORMAT A10
          SCOTT@orcl_11gR2> COLUMN surname  FORMAT A10
          SCOTT@orcl_11gR2> SET AUTOTRACE ON EXPLAIN
          SCOTT@orcl_11gR2> SELECT * FROM cust_catalog
            2  WHERE CONTAINS
            3            (any_column,
            4             '(FUZZY (' || REPLACE ('john|chris', '|', ', 1, 100, WEIGHT) | FUZZY (')
            5              || ', 1, 100, WEIGHT) WITHIN forename) AND
            6              (FUZZY (' || REPLACE ('smith|Miles|Mil', '|', ', 1, 100, WEIGHT) | FUZZY (')
            7              || ', 1, 100, WEIGHT) WITHIN surname) AND
            8              (SDATA (birthdate BETWEEN ''1970/01/02'' AND ''1980/11/07''))') > 0
            9  /
          
                  ID FORENAME   SURNAME    BIRTHDATE GENDER     A
          ---------- ---------- ---------- --------- ---------- -
                   1 John       Smith      10-MAR-71 Male
                   8 John       Smith      07-NOV-72 Male
                  10 Emma John  Mil        06-APR-79 Male
                  11 Jon        Smith      19-SEP-77 Male
                  13 Chrissie   Smith      21-MAY-75 Male
                  14 Chrisy     Smith      21-MAY-75 Male
                  15 Chrisi     Smith      21-MAY-75 Male
                  19 Jone       Smith      21-MAY-75 Male
                  20 Johan      Smith      21-MAY-75 Male
                  21 John       Smithie    21-MAY-75 Male
                  22 Chris      Smithey    21-MAY-75 Male
                  23 John       Smithy     21-MAY-75 Male
                  24 Chris      Smithie    21-MAY-75 Male
                  25 John       Smithke    21-MAY-75 Male
          
          14 rows selected.
          
          
          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 1894062579
          
          -------------------------------------------------------------------------------------------------------
          | Id  | Operation                   | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
          -------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT            |                         |     1 |   127 |     4   (0)| 00:00:01 |
          |   1 |  TABLE ACCESS BY INDEX ROWID| CUST_CATALOG            |     1 |   127 |     4   (0)| 00:00:01 |
          |*  2 |   DOMAIN INDEX              | ALL_COLUMNS_CONTEXT_IDX |       |       |     4   (0)| 00:00:01 |
          -------------------------------------------------------------------------------------------------------
          
          Predicate Information (identified by operation id):
          ---------------------------------------------------
          
             2 - access("CTXSYS"."CONTAINS"("ANY_COLUMN",'(FUZZY (john, 1, 100, WEIGHT) | FUZZY (chris,
                        1, 100, WEIGHT) WITHIN forename) AND           (FUZZY (smith, 1, 100, WEIGHT) | FUZZY (Miles,
                        1, 100, WEIGHT) | FUZZY (Mil, 1, 100, WEIGHT) WITHIN surname) AND           (SDATA (birthdate
                        BETWEEN ''1970/01/02'' AND ''1980/11/07''))')>0)
          
          Note
          -----
             - dynamic sampling used for this statement (level=2)
          
          SCOTT@orcl_11gR2> SET AUTOTRACE OFF
          SCOTT@orcl_11gR2> -- cleaning
          SCOTT@orcl_11gR2> DROP TABLE cust_catalog;
          
          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 ('names_and_gender_datastore');
          
          PL/SQL procedure successfully completed.
          
          SCOTT@orcl_11gR2> EXEC CTX_DDL.DROP_SECTION_GROUP ('your_sec');
          
          PL/SQL procedure successfully completed.
          • 2. Re: Using Context fuzzy search in multiple params
            Emily Robertson
            Barbara, thanks so much, you are a star !!!!
            much appreciated !!!!