Optimizer Problem

We have a query that used to work fine under Online 4.1 but under 5.0 the optimizer has decided to do a sequential search on one of the tables (About 9000 rows): Below is the output from 'set explain on' - sorry about the code but weren't me :) Does anyone have any ideas why it is doing the sequential scan in 5.0 but not in 4.1 ??? (There is an index on the cust_enq_stat.trace_ref_no field, and update statistics has been run recently). Thanks and regards, Richard Ridley ===================================================================== QUERY: ------ select unique (values) FROM customer_enquiry,cust_enq_stat,trace_case, trace_hist_summ WHERE customer_enquiry.hawb_no=347102232 AND customer_enquiry.trace_ref_no = trace_hist_summ.trace_ref_no AND customer_enquiry.trace_ref_no[1,7] = trace_case.trace_ref_prefix AND trace_hist_summ.enq_seq_no = customer_enquiry.sequence_no AND trace_hist_summ.event_no = (SELECT max(event_no) FROM trace_hist_summ where trace_hist_summ.trace_ref_no = cust_enq_stat.trace_ref_no and trace_hist_summ.trace_log_code in ('OP','TR','EU','RR')) and trace_hist_summ.trace_ref_no = cust_enq_stat.trace_ref_no ORDER BY customer_enquiry.trace_ref_no Estimated Cost: 19894 Estimated # of Rows Returned: 132 Temporary Files Required For: Order By 1) stmadm.cust_enq_stat: SEQUENTIAL SCAN@@ AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 2) stmadm.trace_hist_summ: INDEX PATH (1) Index Keys: trace_ref_no event_no Lower Index Filter: (stmadm.trace_hist_summ.trace_ref_no = stmadm.cust_enq_stat.trace_ref_no AND stmadm.trace_hist_summ.event_no = <subquery> ) 3) stmadm.customer_enquiry: INDEX PATH Filters: stmadm.customer_enquiry.hawb_no = 347102232 (1) Index Keys: trace_ref_no sequence_no Lower Index Filter: (stmadm.customer_enquiry.trace_ref_no = stmadm.trace_hist_summ.trace_ref_no AND stmadm.customer_enquiry.sequence_no = stmadm.trace_hist_summ.enq_seq_no ) 4) stmadm.trace_case: INDEX PATH (1) Index Keys: trace_ref_prefix Lower Index Filter: stmadm.trace_case.trace_ref_prefix = stmadm.customer_enquiry.trace_ref_no[1,7] Subquery: --------- Estimated Cost: 8 Estimated # of Rows Returned: 1 1) stmadm.trace_hist_summ: INDEX PATH Filters: stmadm.trace_hist_summ.trace_log_code IN ('OP' , 'TR' , 'EU' , 'RR' ) (1) Index Keys: trace_ref_no event_no Lower Index Filter: stmadm.trace_hist_summ.trace_ref_no = stmadm.cust_enq_stat.trace_ref_no