Outer join and subquery

[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