Re: execution plan different inside stored procedure?

I am not sure but I think that in a stored procedure the optimizer tries for all rows instead of first rows.
Jim
"Todd" <thperry_at_duke-energy.com> wrote in message news:5f8b006d.0208271354.1c01aa06_at_posting.google.com...
> 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)
> 172 TABLE ACCESS BY INDEX ROWID TRAN_SCHEDULE_DETAILS
> 215 INDEX RANGE SCAN (object id 128776)
>
>
> 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 Wed Aug 28 2002 - 06:14:26 CEST