Re: How can I get this faster??

Why not ???:

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

OR, if You wont
     select count(distinct ARTICLEHEADERID)
     from articleheaders
     where contains(article, 'backup') > 0 and categoryid = 3;

"Kev.-" <java2e_at_yahoo.com> wrote in message news:3adb9ffc.50612747_at_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 Tue Apr 17 2001 - 02:35:18 CDT