6 Replies Latest reply: Nov 29, 2012 1:20 AM by 882532 RSS

    Oracle Text, multi_column_datastore performance

    882532
      Hi,

      DB 11.2.0.3

      I have started to wonder what index are faster - single, or multi_column - ctxsys.context ?

      I have a table which has 782046 rows (for now) and I would like to search for about 7 columns.
        • 1. Re: Oracle Text, multi_column_datastore performance
          Roger Ford-Oracle
          If you want to search one column at a time, then default datastore (single column) will be marginally faster, since each individual index will be smaller.

          However, if you ever want to search over more than one column (eg "searchterm WITHIN title OR searchterm WITHIN author") then a MULTI_COLUMN_DATASTORE index will be very much faster than using two CONTAINS clauses on two separate indexes.
          • 2. Re: Oracle Text, multi_column_datastore performance
            882532
            I want search only at all columns without "WITHIN" clause.

            The problem is I have a specific data, and user can write any text.

            So I see 2 solution.

            FIRST:

            Create code like :
                     SELECT   item_barcode
                       INTO   v_item_barcode
                       FROM   a_item
                      WHERE   contains (item_barcode, v_text) > 0 AND ROWNUM = 1;
            for all columns. Then when v_item_? is not null return all table (yes, I need it because I show it to user on a web page):
            SELECT * FROM table_name WHERE contains (column_name, v_text) > 0;
            SECOND:

            multi_column index which search at all columns

            Edited by: Ndejo on 2012-11-28 09:14
            • 3. Re: Oracle Text, multi_column_datastore performance
              Roger Ford-Oracle
              If you want to search over multiple columns, with or without the WITHIN clause, then you are much better off using a single index than multiple indexes.
              • 4. Re: Oracle Text, multi_column_datastore performance
                882532
                I updated mu last post.

                Please look on first solution and tell me that can be more efficient ?

                You searching for just one word (the first encounter), and if you find then you search the entire table.

                I assume that the user searches for a word that is in one column.

                If an additional bet that the word can not be found in other columns and blocks the possibility of searching for other columns (when finding a result for that column), the solution of the index on a single column could be better ?

                Something like that :
                SELECT   item_barcode
                  INTO   v_item_barcode
                  FROM   a_item
                 WHERE   contains (item_barcode, v_text) > 0 AND ROWNUM = 1;
                
                IF v_item_barcode IS NOT NULL THEN
                         SELECT   *
                           FROM   a_item
                          WHERE   contains (item_barcode, v_text) > 0;
                END IF;
                
                SELECT   item_title
                  INTO   v_item_title
                  FROM   a_item
                 WHERE   contains (item_title, v_text) > 0 AND ROWNUM = 1;
                
                IF v_item_title IS NOT NULL AND v_item_barcode IS NULL THEN
                         SELECT   *
                           FROM   a_item
                          WHERE   contains (item_title, v_text) > 0;
                END IF;
                
                SELECT   item_subtitle
                  INTO   v_item_subtitle
                  FROM   a_item
                 WHERE   contains (item_subtitle, v_text) > 0 AND ROWNUM = 1;
                
                IF v_item_subtitle IS NOT NULL AND v_item_barcode IS NULL AND v_item_subtitle IS NULL THEN
                         SELECT   *
                           FROM   a_item
                          WHERE   contains (item_subtitle, v_text) > 0;
                END IF;
                
                ...
                • 5. Re: Oracle Text, multi_column_datastore performance
                  Roger Ford-Oracle
                  A multi_column_datastore index, with a single CONTAINS clause, will be much faster than running several queries each with their own CONTAINS clauses.
                  • 6. Re: Oracle Text, multi_column_datastore performance
                    882532
                    Thank you for response.