How can I get this faster??

From: Kev.- <java2e_at_yahoo.com>
Date: Tue, 17 Apr 2001 01:51:32 GMT
Message-ID: <3adb9ffc.50612747@ns1.nothingbutnet.net>

Here is the query. I have tried everything I know and even have had help with it but I cannot for the life of get the performance I need. Please someone anyone help!

Also, when I deleted the statistics it helped speed things up but it is still extremely slow in some cases.

Here is the execution plan and statistics.

SQL> select count(*) from (select ARTICLEHEADERID from articleheaders where contains(article, 'backup') > 0 and categoryid = 3 );

  COUNT(*)

---

      5390

Elapsed: 00:00:07.51

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2022)    1 0 SORT (AGGREGATE)

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'ARTICLEHEADERS' (Cost=
          1 Card=1 Bytes=2022)

   3    2       DOMAIN INDEX OF 'CTX_ARTHEAD_ART' (Cost=1 Card=1)

Statistics

---

        877  recursive calls
         12  db block gets
      11236  consistent gets
       2241  physical reads
          0  redo size
    3170262  bytes sent via SQL*Net to client
     114032  bytes received via SQL*Net from client
       1579  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
________________________________________________________________________

  1 select count(*) from (select ARTICLEHEADERID from articleheaders where contains(article, 'backup') > 0

  2                         intersect
  3*                  select ARTICLEHEADERID from articleheaders where

categoryid = 3 )
SQL> /   COUNT(*)

---

      5390

Elapsed: 00:00:01.22

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1141 Card=1)    1 0 SORT (AGGREGATE)

   2    1     VIEW (Cost=1141 Card=55873)
   3    2       INTERSECTION
   4    3         SORT (UNIQUE) (Cost=3 Card=1 Bytes=2022)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'ARTICLEHEADERS'
          (Cost=1 Card=1 Bytes=2022)

   6    5             DOMAIN INDEX OF 'CTX_ARTHEAD_ART' (Cost=1)
   7    3         SORT (UNIQUE) (Cost=1138 Card=55872 Bytes=1452672)
   8    7           INDEX (RANGE SCAN) OF 'ARTHEAD_IDX_AID_CAT' (UNIQU
          E) (Cost=2 Card=55872 Bytes=1452672)

Statistics

---

        874  recursive calls
         19  db block gets
      11119  consistent gets
        163  physical reads
          0  redo size
    3163283  bytes sent via SQL*Net to client
     111909  bytes received via SQL*Net from client
       1564  SQL*Net roundtrips to/from client
          3  sorts (memory)
          2  sorts (disk)
          1  rows processed

SQL> Received on Mon Apr 16 2001 - 20:51:32 CDT