Re: How can I get this faster??

Hello Jonathon,

Thank you so much for the information. I have tried everything to get this to speed up. I wil rebuild the index with more memory and maybe that will help. But first Iwill test by splitting the query you mentioned to see where exactly the problem is.

Again thanks.

Kev.-

On Tue, 17 Apr 2001 11:56:35 +0100, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>
>I suspect that your biggest problem is going
>to be the context index - your row count for
>the final result is 5,390, so the context index
>is probably quite big.
>
>Start by investigating the two parts separately,
>though, I may be wrong.
>
>Do a select count(*) for the CONTAINS() clause,
>and a separate count() for the category_id, then
>check the logical and physical I/O that each requires.
>(I am not sure however the context option will tell the
>whole truth). It looks as if you have already tried
>including the article_header to the 'ARTHEAD_IDX_AID_CAT'
>index - have you compressed this index on its
>first column, this may help a bit ?
>
>
>In answer to your other post about partitioning
>for this problem - it will probably not help.
>
>Articleheaderid is your primary key on articleheader,
>and context requires the table to have a unique key
>to identify the documents. You cannot create a
>LOCAL index on a primary key unless the paritioning
>column is part of the primary key, so partitioning the
>data on category_id will not allow you to eliminate
>partitions during the context part of the query.
>It will help, however, on the non-context part of
>the query, where you do the INTERSECT - but
>I suspect that this is the cheap part of the query
>anyway.
>
>
>You could try introducing more stop-words
>to reduce the size of the context index -
>that might help. It may also help a bit to rebuild
>the context index using a larger memory area
>to reduce context-index fragmentation. It's worth
>remembering that context indexes can degrade
>quite dramatically if they are updated in 'real'
>time rather than in document batches, and even
>when updated in batches, they can benefit from
>rebuilding rather more often than ordinary indexes.
>
>
>--
>Jonathan Lewis
>Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
>Practical Oracle 8i: Building Efficient Databases
>Publishers: Addison-Wesley
>
>Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
>
>
>
>Kev.- wrote in message <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 - 15:12:06 CDT