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)

[kebrith]

Hi,

The thing that improve it a bit better is to changing the between clause
to > date 1 and < date 2.

[chrisrlong]

Hi,

The thing that improve it a bit better is to changing the between clause
to > date 1 and < date 2.

Uh, no.

First of all, to be equivalent, it would be >= and <=.

Second of all, there is absolutely no difference between the two to the optimizer.

Now, back to the problem at hand. A few questions:

What is the result supposed to be?

It *looks* like the resultset is supposed to be all the plans and their names that satisfy the given criteria. However, since the GROUP BY and ORDER BY do not match, I'm not positive. I'm not sure why you are replacing the plan_name with NULL in both the SELECT list and the ORDER BY but not the GROUP BY - that is very, very odd. Further, you are excluding records where the PLAN_ID = -1 anyway, so why the CASE statement at all?

So, I will assume first that that is a typo, and the GROUP BY is supposed to match the SELECT list and the ORDER BY.

Given that, the first EXISTS contradicts the outer-join to LINE_CLASS. The outer join says "Don't restrict the resultset even if there is no matching entry in the LINE_CLASS table". Then, the EXISTS says "DO restrict the resultset to only those entries that have a matching entry in the LINE_CLASS table." This obviously makes no sense. In this case, the EXISTS wins, so we can either get rid of the EXISTS and make it a regular join or get rid of the outer-join and leave the EXISTS. The choice depends on the relationship.

This is important to the whole query: We only want a list of plans that match a given criteria. Given the GROUP BY, we can assume that the PLAN table has a M:1 relationship with at least one of the other tables. Therefore, we should use EXISTS on these tables, which will kick out as soon as a matching record is found, as opposed to finding and joining to every record matching record and then having to do a GROUP BY to get rid of the multiples that were just created.

So, back to that first EXISTS - the only purpose there is to say that the LINE_CLASS_ID must be 467, which we can find in the EVENT_COMMERCIAL table, so why even involve the LINE_CLASS table?

Next point - We have a similar issue in the second EXISTS. It contradicts the existing OUTER-JOIN to the same table.

So, if I'm reading everything right, I would try this:

SELECT  
   P.PLAN_ID, 
   P.PLAN_NAME 
FROM 
   PLAN   P
WHERE 
   P.CHANNEL_ID   =   4671                  AND
   P.PLAN_ID      <>   -1                   AND
   EXISTS   
      (
      SELECT   
         1   
      FROM   
         EVENT_COMMERCIAL   EC   , 
      WHERE   
         EC.PLAN_ID         =   P.PLAN_ID        AND
         EC.LINE_CLASS_ID   =   467               
      )                                      AND   
   EXISTS   
      (
      SELECT   
         1   
      FROM   
         PLAN_CELL   PC
      WHERE   
         PC.PLAN_ID      =         P.PLAN_ID                      AND
         PC.START_DATE   BETWEEN   TO_DATE('01082008', 'DDMMYYYY')   
                         AND       TO_DATE('15082008', 'DDMMYYYY')
      )   
ORDER BY   
   PLAN_NAME

[nt002225]

Hi Christ,

you are absolutely right, there is something wrong with the outer join because the original query try to make up null in line_class3.line_class_id (+) and then later in sub (EXISTS) is asking for line_class_id = 467
It is a same for plan_cell4.plan_id (+) . I just read an article about outer join (Tomkyte), so I do agree with you.
moreover, the query ets a millions of rows then group y, order that would take a long time on that.

another thing that I did not see is the CASE and excluding records where the PLAN_ID = -1 .

I appreciate your help , I am gonna rewrite this query (actually, the original query is from a third party software and I cannot tell you about location...)

Thanks a lot