execution plan different inside stored procedure?

The following query generates the following execution plan in Toad/SQLPlus/etc..

SELECT /*+ LEADING(query_result) */TRAN_SCHEDULE_DETAILS.* FROM
PROFILE,
TRAN_SCHEDULE_DETAILS,
AB_TRAN,
QUERY_RESULT
WHERE
PROFILE.start_date < '01-SEP-2002' AND
PROFILE.end_date > '01-JUL-2002' AND
QUERY_RESULT.unique_id = 3526317 AND

AB_TRAN.toolset = 33 AND 
PROFILE.param_seq_num = 0 AND
AB_TRAN.ins_num  = PROFILE.ins_num AND
AB_TRAN.tran_num = QUERY_RESULT.QUERY_RESULT AND
AB_TRAN.tran_num = TRAN_SCHEDULE_DETAILS.tran_num AND
PROFILE.profile_seq_num = TRAN_SCHEDULE_DETAILS.profile_seq_num	

execution plan per TKPROF is 
Rows     Row Source Operation


-------  ---------------------------------------------------

    172  NESTED LOOPS 
     44   NESTED LOOPS 
      2    NESTED LOOPS 
      2     INDEX RANGE SCAN (object id 162054)
      2     TABLE ACCESS BY INDEX ROWID AB_TRAN 
      2      INDEX UNIQUE SCAN (object id 127624)
     44    TABLE ACCESS BY INDEX ROWID PROFILE 
     45     INDEX RANGE SCAN (object id 127528)

the same statement when included in a stored procedure but as a returned cursor generates this plan
Rows Row Source Operation
------- ---------------------------------------------------

    172 NESTED LOOPS
     44 NESTED LOOPS
 228590 MERGE JOIN CARTESIAN

      2     INDEX RANGE SCAN (object id 162054)
 228590     SORT JOIN 
 228589      TABLE ACCESS BY INDEX ROWID PROFILE 
 231271       INDEX RANGE SCAN (object id 127529)
 228632    TABLE ACCESS BY INDEX ROWID AB_TRAN 
 457178     INDEX UNIQUE SCAN (object id 127624)

    172 TABLE ACCESS BY INDEX ROWID TRAN_SCHEDULE_DETAILS     215 INDEX RANGE SCAN (object id 128776)

Why is this?

Thanks,
-TP Received on Tue Aug 27 2002 - 23:54:16 CEST