Urgent Tuning Question....

From: Miggins <mtproc_at_yahoo.co.uk>
Date: 10 May 2006 04:56:02 -0700
Message-ID: <1147262162.430593.82920@u72g2000cwu.googlegroups.com>

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 Received on Wed May 10 2006 - 06:56:02 CDT