Execution plan differ from oracle 8.1.7 to 9.2

Hi,

I'm setting up a new oracle server with oracle 9.2 on win2K

here's an exection plan on my old database (8.1.7) :

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

INSERT STATEMENT Optimizer Mode=CHOOSE		11  	 	49
  NESTED LOOPS		11  	341  	49  	 	      	             	 
    TABLE ACCESS BY INDEX ROWID	PROSPECT	11  	143  	5
      INDEX RANGE SCAN	PROSPECT_I_PRO_POSTAL_PRO	11  	 	3
    TABLE ACCESS BY INDEX ROWID	TRANSAC	460  	8 K	4
      INDEX RANGE SCAN	TRANSAC_ID_UEF_NUM_IDX	460  	 	3

here's the execution plan on my new server for the same sql :

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

INSERT STATEMENT Optimizer Mode=CHOOSE		477  	 	1369
  FILTER		  	 	 	 	      	             	 
    HASH JOIN		477  	16 K	1369  	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	TRANSAC	458  	8 K	1357
        INDEX RANGE SCAN	TRANSAC_TYPE_IDX	13  	 	137
      TABLE ACCESS BY INDEX ROWID	PROSPECT	130 K	2 M	10
        INDEX RANGE SCAN	PROSPECT_I_PRO_POSTAL_PRO	23 K	 	2

Maybe someone know why i got a Filter and Hash join on the new one ... i don't have any hint on my query
It kill the performance

Here's the sql command :

INSERT INTO rpt_transac_correspondance

            (id_uef, job_id, t_type, dt_tran)    SELECT id_uef, :v001, t_type, dt_tran      FROM prospect, transac trs
    WHERE pro_no_id_uef = trs.id_uef
      AND TO_DATE (dt_tran, 'yyyymmdd') BETWEEN TO_DATE (:v002,
'yyyy/mm/dd')

                                            AND TO_DATE (:v003,


'yyyy/mm/dd')

      AND (pro_postal LIKE 'H2Y' || '%')
      AND t_type IN (4)

Thanks
Chris Received on Mon Nov 10 2003 - 10:03:20 CST