Hi Tom

I am not an expert in oracle so thought I will use your help here. I have an application which does a full text search but it is very very slow. Not sure if I build the index correctly. Below are the details:

BELOW IS USED TO CREATE INDEX:

CREATE INDEX "KMCS"."ARTIFACTS_TEXT_INDEX" ON "KMCS"."ARTIFACT_FILES" ("ORIGINAL_PATH")
INDEXTYPE IS "CTXSYS"."CONTEXT" PARAMETERS ('DATASTORE ctxsys.file_datastore
FILTER artifacts_filter
LEXER ARTIFACT_TEXT_INDEX_LEX
STOPLIST KM_STOPLIST
WORDLIST ARTIFACT_TEXT_INDEX_WDL
STORAGE ARTIFACTS_TEXT_INDEX_STO
MEMORY 250M')
PARALLEL 4 ;

ARTIFACT_FILES TABLE CONTAINS 4M ROWS. RUNNING A FULL TEXT SEARCH TAKES A LONG TIME TO RETURN RESULT. BELOW IS THE CAPTURE FROM OEM OF THE QUERY EXECUTED:

select *
from (
select /*+ FIRST_ROWS(1000) */ a.*, rownum rnum
from (
select aem.artifact_id id
from artifact_email_metadata aem , kmcs_objects ko , artifacts art , container_artifacts ca , context_folders cf , artifact_files af
where ko.deleted = '0' and ko.id = art.artifact_id and art.artifact_id = aem.artifact_id and art.enabled = '1' and art.artifact_id = ca.artifact_id and ca.container_id = cf.container_id and ca.is_in_recycle_bin = '0' AND aem.artifact_id = af.artifact_id and CONTAINS(af.original_path, :p_1)>0 order by aem.email_date desc ) a
where rownum <= :sizelimit )
where rnum >= 0

I HAVE THE QUERY PLAN FROM OEM BUT CANNOT PASTE HERE AS IT IS NOT ALIGNED.

PLEASE HELP ON THIS MATTER.

THANK YOU AND MUCH APPRECIATED.