10 Replies Latest reply: Jul 20, 2013 5:04 PM by hospel RSS

    oracle text indexes

    hospel

      Can anybody know is it possible to create two oracle text indexes on one column, for example, CTXCAT index and CTXRULE index and what will be during the querying of that column? is it a good practise?

       

      Thanks in advance.

        • 1. Re: oracle text indexes
          Barbara Boehmer

          When in doubt, test and see.  Yes, you can create two different types of Oracle Text indexes on the same column.  If you create a CTXCAT index and a CTXRULE index, then queries using CATSEARCH will use the CTXCAT index and queries using MATCHES will use the CTXRULE index.  When querying with CATSEARCH, it will find all rows where the terms searched for are found within the column value.  When querying with CTXRULE, it does the opposite, and finds all rows where the column values are found within the terms searched for.  Please see the demonstration below.  As to whether it is a good practice, it depends on what you need.  If you need both types of searches, then yes.  If not, then no, it would be unnecessary overhead.

           

           

          SCOTT@orcl_11gR2> create table test_tab (test_col  varchar2(60))

            2  /

           

          Table created.

           

          SCOTT@orcl_11gR2> insert all

            2  into test_tab values ('test')

            3  into test_tab values ('data')

            4  into test_tab values ('test data')

            5  into test_tab values ('other stuff')

            6  select * from dual

            7  /

           

          4 rows created.

           

          SCOTT@orcl_11gR2> create index ctxcat_idx on test_tab (test_col)

            2  indextype is ctxsys.ctxcat

            3  /

           

          Index created.

           

          SCOTT@orcl_11gR2> create index ctxrule_idx on test_tab (test_col)

            2  indextype is ctxsys.ctxrule

            3  /

           

          Index created.

           

          SCOTT@orcl_11gR2> set autotrace on explain

          SCOTT@orcl_11gR2> select * from test_tab

            2  where  catsearch (test_col, 'test data', null) > 0

            3  /

           

          TEST_COL

          ------------------------------------------------------------

          test data

           

          1 row selected.

           

           

          Execution Plan

          ----------------------------------------------------------

          Plan hash value: 399706479

           

          ------------------------------------------------------------------------------------------

          | Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

          ------------------------------------------------------------------------------------------

          |   0 | SELECT STATEMENT            |            |     1 |    44 |     3   (0)| 00:00:01 |

          |   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TAB   |     1 |    44 |     3   (0)| 00:00:01 |

          |*  2 |   DOMAIN INDEX              | CTXCAT_IDX |       |       |            |          |

          ------------------------------------------------------------------------------------------

           

          Predicate Information (identified by operation id):

          ---------------------------------------------------

           

             2 - access("CTXSYS"."CATSEARCH"("TEST_COL",'test data',NULL)>0)

           

          Note

          -----

             - dynamic sampling used for this statement (level=2)

           

          SCOTT@orcl_11gR2> select * from test_tab

            2  where  matches (test_col, 'test data') > 0

            3  /

           

          TEST_COL

          ------------------------------------------------------------

          test

          data

          test data

           

          3 rows selected.

           

           

          Execution Plan

          ----------------------------------------------------------

          Plan hash value: 1476734355

           

          -------------------------------------------------------------------------------------------

          | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

          -------------------------------------------------------------------------------------------

          |   0 | SELECT STATEMENT            |             |     1 |    44 |     1   (0)| 00:00:01 |

          |   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TAB    |     1 |    44 |     1   (0)| 00:00:01 |

          |*  2 |   DOMAIN INDEX              | CTXRULE_IDX |       |       |     0   (0)| 00:00:01 |

          -------------------------------------------------------------------------------------------

           

          Predicate Information (identified by operation id):

          ---------------------------------------------------

           

             2 - access("CTXSYS"."MATCHES"("TEST_COL",'test data')>0)

           

          Note

          -----

             - dynamic sampling used for this statement (level=2)

           

          SCOTT@orcl_11gR2>

           

          Message was edited by: BarbaraBoehmer

          • 2. Re: oracle text indexes
            hospel

            Thank you for a quick answer, it will help me a lot.

            • 3. Re: oracle text indexes
              hospel

              Do you know what schema permisssion I have to grant to my user to successfully create ctxcat and ctxrule indexes? The problem is that when I try to create it I got a couple of errors:

              Error starting at line 1 in command:

              create index ctxcat_idx on content (content) indextype is ctxsys.ctxcat

              Error at Command Line:1 Column:14

              Error report:

              SQL Error: ORA-29855: wystąpił błąd przy wykonywaniu podprogramu ODCIINDEXCREATE

              ORA-20000: Oracle Text - błąd:

              DRG-10509: niepoprawna kolumna tekstowa: CONTENT

              ORA-06512: przy "CTXSYS.DRUE", linia 160

              ORA-06512: przy "CTXSYS.CATINDEXMETHODS", linia 97

              29855. 00000 -  "error occurred in the execution of ODCIINDEXCREATE routine"

              *Cause:    Failed to successfully execute the ODCIIndexCreate routine.

              *Action:   Check to see if the routine has been coded correctly.

               

              It seems there is lack of permission somewhere...can you help me?

              • 4. Re: oracle text indexes
                Barbara Boehmer

                The following demonstrates some minimal privileges.  I think the one people tend to miss is that CREATE TRIGGER is necessary when creating Text indexes.

                 

                SCOTT@orcl_11gR2> create user test identified by test

                  2  /

                 

                User created.

                 

                SCOTT@orcl_11gR2> alter user test quota 10m on users

                  2  /

                 

                User altered.

                 

                SCOTT@orcl_11gR2> grant create session to test

                  2  /

                 

                Grant succeeded.

                 

                SCOTT@orcl_11gR2> grant create table to test

                  2  /

                 

                Grant succeeded.

                 

                SCOTT@orcl_11gR2> grant create trigger to test

                  2  /

                 

                Grant succeeded.

                 

                SCOTT@orcl_11gR2> connect test/test

                Connected.

                TEST@orcl_11gR2> create table content (content    varchar2(60))

                  2  /

                 

                Table created.

                 

                TEST@orcl_11gR2> insert into content values ('test data')

                  2  /

                 

                1 row created.

                 

                TEST@orcl_11gR2> create index ctxcat_idx on content (content) indextype is ctxsys.ctxcat

                  2  /

                 

                Index created.

                 

                TEST@orcl_11gR2> create index ctxrule_idx on content (content) indextype is ctxsys.ctxrule

                  2  /

                 

                Index created.

                • 5. Re: oracle text indexes
                  Barbara Boehmer

                  Upon closer examination, your problem does not appear to be due to permissions.  I believe you are trying to create a CTXCAT index on a column that is of a datatype that CTXCAT does not support.  For example, the only modification to the following code was to change the datatype from varchar2 to nvarchar2.  It helps if you post a complete problem that includes table structure.

                   

                   

                  SCOTT@orcl_11gR2> create user test identified by test

                    2  /

                   

                  User created.

                   

                  SCOTT@orcl_11gR2> alter user test quota 10m on users

                    2  /

                   

                  User altered.

                   

                  SCOTT@orcl_11gR2> grant create session to test

                    2  /

                   

                  Grant succeeded.

                   

                  SCOTT@orcl_11gR2> grant create table to test

                    2  /

                   

                  Grant succeeded.

                   

                  SCOTT@orcl_11gR2> grant create trigger to test

                    2  /

                   

                  Grant succeeded.

                   

                  SCOTT@orcl_11gR2> connect test/test

                  Connected.

                  TEST@orcl_11gR2> create table content (content    nvarchar2(60))

                    2  /

                   

                  Table created.

                   

                  TEST@orcl_11gR2> insert into content values ('test data')

                    2  /

                   

                  1 row created.

                   

                  TEST@orcl_11gR2> create index ctxcat_idx on content (content) indextype is ctxsys.ctxcat

                    2  /

                  create index ctxcat_idx on content (content) indextype is ctxsys.ctxcat

                  *

                  ERROR at line 1:

                  ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine

                  ORA-20000: Oracle Text error:

                  DRG-10509: invalid text column: CONTENT

                  ORA-06512: at "CTXSYS.DRUE", line 160

                  ORA-06512: at "CTXSYS.CATINDEXMETHODS", line 97

                   

                   

                  TEST@orcl_11gR2> create index ctxrule_idx on content (content) indextype is ctxsys.ctxrule

                    2  /

                  create index ctxrule_idx on content (content) indextype is ctxsys.ctxrule

                  *

                  ERROR at line 1:

                  ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine

                  ORA-20000: Oracle Text error:

                  DRG-10509: invalid text column: CONTENT

                  ORA-06512: at "CTXSYS.DRUE", line 160

                  ORA-06512: at "CTXSYS.RULEINDEXMETHODS", line 167

                   

                   

                  TEST@orcl_11gR2>

                  • 6. Re: oracle text indexes
                    hospel

                    Thank you again for the quick response, here is my table structure:

                     

                    Nazwa                                     Wartość NULL? Typ

                    ----------------------------------------- -------- ----------------------------

                    ID_CONTENT                                NOT NULL NUMBER(38)

                    CONTENT                                   NOT NULL CLOB

                    DESCRIPTION                                        VARCHAR2(300 CHAR)

                     

                     

                     

                    I think that I fixed the problem...I think it's because I tried to create ctxcat index for clob column, right? But what about ctxrule index? Is it also only for varchar2 datatype, not clob datatype?

                    • 7. Re: oracle text indexes
                      Barbara Boehmer

                      A ctxcat index cannot be created on a clob datatype.  A ctxrule index can be created on a clob datatype.

                      • 8. Re: oracle text indexes
                        hospel

                        I am quite new in oracle text so I just didn't know that ctxcat can only be used for varchar2 datatype.

                        • 9. Re: oracle text indexes
                          Barbara Boehmer

                          The following link contains a section on Syntax for CTXCAT Index Type that says, "The column that you specify when you create a CTXCAT index must be of type CHAR or VARCHAR2. No other types are supported for CTXCAT."

                           

                          Oracle Text SQL Statements and Operators

                          • 10. Re: oracle text indexes
                            hospel

                            Ok, I have connected the errors and now everithing is working right. thanks.