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