Re: Explain Plan irregularity

From: damorgan <damorgan_at_exesolutions.com>
Date: Fri, 15 Mar 2002 22:02:59 GMT
Message-ID: <3C926F97.32999B69@exesolutions.com>

After someone helps Bill with his question please explain the difference in COST on this one:

HIGH COST

---------------------------------------------:

EXPLAIN PLAN
SET STATEMENT_ID = 'abc'
FOR SELECT a.program_id, c.customer_name, count(*) FROM airplanes a, customer_parts c
WHERE a.customer_id != 'AAL'
AND a.customer_id = c.customer_name
AND ROWNUM < 100
GROUP BY program_id, c.customer_name;

LOW COST

---

EXPLAIN PLAN
SET STATEMENT_ID = 'abc'
FOR SELECT a.program_id, c.customer_name, count(*) FROM airplanes a, customer_parts c
WHERE a.customer_id NOT IN (

   SELECT 'AAL'
   FROM dual)
AND a.customer_id = c.customer_name
AND ROWNUM < 100
GROUP BY a.program_id, c.customer_name;

I guess if everything was easily understood we'd have no need for explain plan. <g>

Daniel Morgan

Bill Sonia wrote:

> While tuning a pl/sql procedure, I've noticed that my execution plan
> will change after I hard code the dates in a between condition for a
> insert into select statement. The between is meant to use passed in
> variables to run for different periods. In fact, if I hard code the
> dates the query will finish in 30 secs while passed in date variables
> will run in about 15 minutes.
>
> i.e.WHERE inv_date between '01-FEB-2002' and '01-MAR-2002';
> if.WHERE inv_date between p_start_date and p_end_date;
>
> Does anyone know why this is? Is there anything I can do about it?
>
> Thanks
>
> --
> Posted via dBforums
> http://dbforums.com
Received on Fri Mar 15 2002 - 16:02:59 CST