Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Oracle Text Design Question
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