This discussion is archived
6 Replies Latest reply: Nov 28, 2012 11:20 PM by 882532 RSS

Oracle Text, multi_column_datastore performance

882532 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank you for response.

Legend

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