Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Oracle Text Design Question

Oracle Text Design Question

From: Kevin Crosbie <caoimhinocrosbai_at_yahoo.com>
Date: Wed, 13 Apr 2005 12:42:34 +0200
Message-ID: <1113388960.142d6d314d5ee8b61e2c6b3c8e3e2a3f@teranews>


Hi all,

I posted this question yesterday to com.databases.oracle.server but got no answers. This is not a cross-post, rather a recategorization. I hope I'm posting this to the right group now, apologies if I'm not.

I have some existing data that I want to put an Oracle Text index on to add some search functionality.
I am using Oracle 9iR2 Enterprise Edition.

My tables look like the following:

items (

    item_id number primary key,
    date_added timestamp(3),
    title varchar2(200));

item_text (

   item_id        number references items(item_id),
   content        clob);

unique index on item_text (item_id);
unique index on items(date_added, item_id);

They have about 6M rows, and as you can see there is a zero to one relationship between items and item_text, although in most cases it is a one to one relationship.

The data inserted into the content column in item_text is HTML and this is the column I want to index.

Creating the index is no problem, although it seems I am restricted to a CONTEXT rather than a CTXCAT index due to my data being stored in a CLOB.

My question is what is the best way to set up the index such that I can do the following query in the most efficient way: select i.title, i.date_added, it.content  from items i, item_text it
where i.item_id = it.item_id
  and i.date_added between :start_time and :end_time   and contains(it.content, 'something') > 0;

I have set up an index using a straight forward: create index content_ix on item_text(content) indextype is ctxsys.context;

but this seems to give me the following explain plan DML SELECT

 0  SELECT STATEMENT      CHOOSE  20635995  3236344  1588
  1   SORT   GROUP BY         20635995   3236344   1588
   2    HASH JOIN                15489    46501959024    22817448
    3     INDEX     RANGE SCAN     ITEMS_DATE_ADDED_ITEM_ID     ANALYZED 22
25408     1588
    4     TABLE ACCESS     BY INDEX ROWID     ITEM_TEXT          13319
2905345074     1436867
     5      DOMAIN INDEX            CONTENT_IX            12107            1

This query normally takes around 5 to 15 minutes to run on an alpha server so it's pretty slow.

Can anyone suggest any way to speed this up?

Thanks,

Kevin Received on Wed Apr 13 2005 - 05:42:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US