Outer join and subquery

[nt002225]

Hello,
I have a query with outer join and 2 subqueries using EXISTS, I am trying to tune this query , if someone can hae any idea I 'd appreciate.
The query is took so long, because 2 subqueries.
:'

SELECT  plan1.plan_id, 
       CASE WHEN plan1.plan_id = -1 THEN NULL 
            ELSE plan1.plan_name END f0 
  FROM PLAN plan1, 
       event_commercial event_commercial2, 
       line_class line_class3, 
       plan_cell plan_cell4 
 WHERE plan1.plan_id = event_commercial2.plan_id (+) 
   AND event_commercial2.line_class_id = line_class3.line_class_id (+) 
   AND plan1.plan_id = plan_cell4.plan_id (+) 
   AND plan1.plan_id <> -1 
   AND plan1.channel_id = 4671 
   AND EXISTS (SELECT 1 
                 FROM line_class line_class1_1 
                WHERE line_class1_1.line_class_id = 467 
                  AND line_class1_1.line_class_id = line_class3.line_class_id) 
   AND EXISTS (SELECT 1 
                 FROM plan_cell plan_cell2_1 
                WHERE (plan_cell2_1.plan_id = plan_cell4.plan_id) 
                  AND (plan_cell2_1.plan_line_id = plan_cell4.plan_line_id) 
                  AND (plan_cell2_1.plan_cell_id = plan_cell4.plan_cell_id) 
                  and (plan_cell2_1.start_date) BETWEEN TO_DATE('01082008', 'ddmmyyyy') AND TO_DATE('15082008', 'ddmmyyyy')) 
 GROUP BY plan1.plan_id, plan1.plan_name 
 ORDER BY NLS_UPPER(CASE WHEN plan1.plan_id = -1 THEN NULL 
                         ELSE plan1.plan_name END)

here is the explain plan:
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=60 Card=1 Bytes=142)
   1    0   SORT (ORDER BY) (Cost=60 Card=1 Bytes=142)
   2    1     SORT (GROUP BY) (Cost=60 Card=1 Bytes=142)
   3    2       FILTER
   4    3         FILTER
   5    4           NESTED LOOPS (OUTER)
   6    5             NESTED LOOPS (OUTER) (Cost=28 Card=1 Bytes=138)
   7    6               NESTED LOOPS (Cost=27 Card=1 Bytes=128)
   8    7                 NESTED LOOPS (OUTER) (Cost=26 Card=1 Bytes=9
          8)

   9    8                   NESTED LOOPS (Cost=25 Card=1 Bytes=76)
  10    9                     INDEX (UNIQUE SCAN) OF 'LINE_CLASS_PK_ID
          X' (UNIQUE)

  11    9                     TABLE ACCESS (BY INDEX ROWID) OF 'PLAN'
          (Cost=24 Card=1 Bytes=72)

  12   11                       INDEX (RANGE SCAN) OF 'PLAN_CHANNEL_FK
          _IDX' (NON-UNIQUE)

  13    8                   TABLE ACCESS (BY INDEX ROWID) OF 'PLAN_CEL
          L' (Cost=1 Card=1 Bytes=22)

  14   13                     INDEX (RANGE SCAN) OF 'PLAN_CELL_PLAN_FK
          _IDX' (NON-UNIQUE)

  15    7                 TABLE ACCESS (BY INDEX ROWID) OF 'PLAN_CELL'
           (Cost=1 Card=1 Bytes=30)

  16   15                   INDEX (UNIQUE SCAN) OF 'PLAN_CELL_UK_IDX'
          (UNIQUE)

  17    6               INDEX (RANGE SCAN) OF 'TEST_EVENT_COMM_PLAN_CL
          ASS_IDX' (NON-UNIQUE) (Cost=1 Card=150 Bytes=1500)

  18    5             INDEX (UNIQUE SCAN) OF 'LINE_CLASS_PK_IDX' (UNIQ
          UE)



Now I 'd change that and get rid of 2 subqueries, to , but I am not sure  logically is correct.

SELECT /*+ ordered(plan1) */ plan1.plan_id,
       CASE WHEN plan1.plan_id = -1 THEN NULL
            ELSE plan1.plan_name END f0
  FROM PLAN plan1,
       event_commercial event_commercial2,
       line_class line_class3,
       plan_cell plan_cell4
 WHERE plan1.plan_id = event_commercial2.plan_id(+)
   AND event_commercial2.line_class_id = line_class3.line_class_id(+)
   AND plan1.plan_id = plan_cell4.plan_id(+)
   AND plan1.plan_id <> -1
   AND plan1.channel_id = 4671
   and line_class3.line_class_id = 467
   and (plan_cell4.start_date) BETWEEN TO_DATE('01012008', 'ddmmyyyy') AND TO_DATE('15082008', 'ddmmyyyy')
 GROUP BY plan1.plan_id, plan1.plan_name
  ORDER BY NLS_UPPER(CASE WHEN plan1.plan_id = -1 THEN NULL
                          ELSE plan1.plan_name END)