Re: Urgent Tuning Question....

Miggins wrote:
> Each,
>
> I have the query shown below which is running slowly.
>
> journal_subscribers table has around 400,000 rows and a non-unique
> index on jsu_journal_code
>
> rate_types table has 40 rows and a unique index on rty_code
>
> payment_methods has 7 rows and a unique index on payment_method
>
> select js.jsu_type,
> js.jsu_code,
> js.jsu_charge_type,
> rt.organisation,
> js.jsu_copies
> from journal_subscribers js,
> rate_types rt,
> payment_methods pm
> where js.jsu_journal_code = v_journal_code
> and js.jsu_start_date < p_start
> and rt.jsu_rate = rt.rty_code(+)
> and jsu_payment_code = pm.payment_code
> and pm.real_payment_code = '1';
>
> -----------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes |
> Cost |
> -----------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 8 | 352 |
> 582 |
> | 1 | HASH JOIN OUTER | | 8 | 352 |
> 582 |
> | 2 | HASH JOIN | | 8 | 312 |
> 579 |
> | 3 | TABLE ACCESS FULL | PAYMENT_METHODS | 2 | 6 |
> 2 |
> | 4 | TABLE ACCESS FULL | JOURNAL_SUBSCRIBERS | 28 | 1008 |
> 576 |
> | 5 | TABLE ACCESS FULL | RATE_TYPES | 38 | 190 |
> 2 |
> -----------------------------------------------------------------------------
>
> It seems the optimizer is choosing to use full table scans and a hash
> join rather than using the indexes. Any ideas why it would do this and
> not use the indexes and a nested loop or how i can improve the speed of
> the query?
>
> Am using version 9.2.0.4
>
> Thanks

Stats up to date?

        robert Received on Wed May 10 2006 - 09:55:06 CDT