Left joing is taking too much time

[mahesh badgujar]

Hi ,

I have a following query that uses a left join.

select bs.dealerid,bs.dealername, bs.productid,bs.productname,bs.date,plm.date, plm.planned as planned, plm.actual as actual
from
(
select dm.dealerid, dm.dealername, pm.productid, pm.productname, dt.date from icmis.dealer_master dm,
icmis.product_master pm, icmis.datetable dt where dm.dealerid in(select eat.dealerid from icmis.employee_allocation_t eat

where eat.employeeid=1) and (date(dt.date) >= '2007-04-01' and date(dt.date) <= '2008-03-01')
) bs

left join

(
select mstr.dealerid, mstr.productid, mstr.date, sum(mstr.planned) planned,sum(mstr.actual) actual
from
(
select plm.dealerid, plm.productid, plm.date, plm.value planned, 0 as actual from icmis.planning_master plm
where plm.dealerid in(select eat.dealerid from icmis.employee_allocation_t eat where eat.employeeid=1)
union
select ae.dealerid, ae.productid, ae.date, 0 as planned, ae.value as actual from icmis.actual_entry ae where ae.dealerid
in (select eat.dealerid from icmis.employee_allocation_t eat where eat.employeeid=1)
) mstr group by mstr.dealerid,mstr.productid, mstr.date

) plm on (bs.date = plm.date and bs.dealerid = plm.dealerid and bs.productid = plm.productid) order by 1,5


This query returns me 22000 records in result. And it takes near about 7 sec.
I want option or modification in this query that gives me same result with
improvement in performance... Pls help me asap..
As per my knowledge left join is creating a problem here..