Fetch cursor very slow

I use a Oracle 9.2.0.1.0 server

I have a view which is a join of few table : CREATE VIEW SE_SEARCH AS
SELECT ... FROM ITEMS

 INNER JOIN ...
 INNER JOIN ...
 INNER JOIN ...
 INNER JOIN ...
 INNER JOIN ...
 INNER JOIN ...

One of this table has a column which is a full text index, and i perform som search on this

SELECT ITEM_SE_KEY FROM DEV_GMI2.SE_SEARCH WHERE ACCOUNT_FK = 'cfrt' and contains(FIELD_VALUE,'{church}') >0;

I received 108000 answers for this request. But the performance is very bad

When i execute this on Sql*plus
Exec plan

Plan d'exécution

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=318 Card=55284 Bytes


          =11001516)


   1    0   NESTED LOOPS (Cost=318 Card=55284 Bytes=11001516)
   2    1     HASH JOIN (Cost=318 Card=55284 Bytes=10835664)
   3    2       TABLE ACCESS (FULL) OF 'CAT_PROJECT_RIGHT' (Cost=2 Car
          d=514 Bytes=10794)

   4    2       HASH JOIN (Cost=315 Card=7043 Bytes=1232525)
   5    4         TABLE ACCESS (FULL) OF 'CAT_ITEM_TREE' (Cost=13 Card


          =8905 Bytes=480870)


   6    4         NESTED LOOPS (Cost=288 Card=7043 Bytes=852203)
   7    6           HASH JOIN (Cost=288 Card=7043 Bytes=654999)
   8    7             TABLE ACCESS (FULL) OF 'SE_ITEM_REF' (Cost=8 Car
          d=8874 Bytes=337212)

   9    7             HASH JOIN (Cost=272 Card=7043 Bytes=387365)
  10    9               TABLE ACCESS (BY INDEX ROWID) OF 'SE_VALUES' (
          Cost=243 Card=1605 Bytes=73830)

  11   10                 DOMAIN INDEX OF 'SE_VAL_FTIDX' (Cost=29)
  12    9               TABLE ACCESS (FULL) OF 'SE_ITEM_VALUE_LINKS' (
          Cost=22 Card=193820 Bytes=1744380)

  13    6           INDEX (UNIQUE SCAN) OF 'SE_ITEMS_PK' (UNIQUE)
  14    1     INDEX (UNIQUE SCAN) OF 'COM_GRP_PK' (UNIQUE)

108480 ligne(s) sélectionnée(s).

Elapsed : 00 :24 :11.00

24 minutes !!!

SE_VAL_FTIDX is my full text index .

I have not much experience of oracle, and i don't know why performance is so bad, Received on Tue Aug 30 2005 - 06:35:41 CDT