Re: How can I get this faster??

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>