Re: How can I get this faster??

Too slow, and too many reads.

The articleheaders are the primary key so it is unique.

Thanks though,

Kev.-

On Tue, 17 Apr 2001 09:35:18 +0200, "leszek" <leszek_at_astech.pl> wrote:

>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 - 05:23:55 CDT