Re: Performance problem with partitioned indexes & tables

Michael,

Sorry about the delay in the follow-up -

As I suggested in my last post about this issue, it is extremely likely that your performance change is NOT due to local indexes per se, but to the use of parallel execution.

Your run time changes from one or two hundredths of a second to around 2 seconds when you switch to parallel execution. As a simple rule of thumb - if a query runs in a few hundredths of a second serially, then it will probably be slower running in parallel - that's always been true of parallel execution.

What is the time like if you re-run the desired query serially - perhaps using the NOPARALLEL and NOPARALLEL_INDEX hints ?

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
 UK  Sept, Nov
 USA x 2  November

http://www.jlcomp.demon.co.uk/seminar.html





Michael Burden wrote in message
<8ea7fbb6.0208121354.d57e5af_at_posting.google.com>...



>Execution Plan

>----------------------------------------------------------

>   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=15)

>   1    0   SORT (AGGREGATE)

>   2    1     SORT* (AGGREGATE)

:Q10744000


>   3    2       PARTITION RANGE* (ALL)

:Q10744000


>   4    3         TABLE ACCESS* (BY LOCAL INDEX ROWID) OF 'TRANSTEST'

:Q10744000


>          (Cost=4 Card=270 Bytes=4050)

>   5    4           INDEX* (RANGE SCAN) OF 'TRANSTEST_IDX3' (NON-UNIQUE)

>>:Q10744000

>          (Cost=4 Card=270)