Oracle - what is this explained plan expression doing?-CodePudding

I have this query

select distinct
   case when (ifp.delivery_id > 0)
        then ifp.delivery_id
        else csh.delivery_id
   end delivery_id,
   ate.event_num
from
   ab_tran_event ate
join ab_tran ab on ab.ins_num = ate.ins_num and toolset = 36
    and (ate.event_source in (2, 25, 33, 18))
join
   physcash pc on (
                     (ate.event_source in (2, 25, 33, 18)
                      and ate.ins_num = pc.ins_num
                      and ate.ins_para_seq_num = pc.param_seq_num
                      and ate.ins_seq_num = pc.cflow_seq_num)
                      )
join
   ins_price ip on  pc.ins_num = ip.ins_num 
   and pc.param_seq_num = ip.param_seq_num 
   and pc.pricing_group_num = ip.pricing_group_num 
   and  ip.pricing_source = 1
join
   ins_fee_param ifp on ip.ins_num = ifp.ins_num 
   and ip.param_seq_num = ifp.param_seq_num 
   and ip.pricing_source_id = ifp.fee_seq_num
   and ((ifp.delivery_id > 0) or  (ifp.parcel_id > 0 and ifp.delivery_id = 0))
left join
   comm_schedule_header csh on (ifp.parcel_id > 0 and ifp.delivery_id = 0) and  ifp.parcel_id = csh.parcel_id and ifp.ins_num = csh.ins_num
where       
    ate.event_num in( 3475267761 )-- event_type 2)
    and ate.event_source in (2, 25, 33, 18)

That is the full SQL but the most relevant for this question is the left join comm_schedule_header csh on (ifp.parcel_id > 0 and ifp.delivery_id = 0) and ifp.parcel_id = csh.parcel_id and ifp.ins_num = csh.ins_num statement toward the end

When I run explain plan, everything about the plan makes sense to me until I get to this:

Why does the optimizer (show it will) filter by this relatively complex case statement instead of simply filtering by IFP.delivery_id = 0, ifp.parcel_id > 0. Those case statement will always evaluate to 0, unless I'm missing something.

The reason this is more than some pedantic exercise is because how the optimizer rewrites ansi joins as non-ansi joins matters when in the context of materialized view rewrite enabled.

CodePudding user response：

Many years ago Oracle didn't have JOIN clause (it was introduced in 9i release 1). To provide such join functionality it was using join operator: a plus sign in brackets ( ) after the columns of the outer joined table in the where clause.

Then explicit join syntax was added (with join keyword), but internally it still uses those old-style predicates for join conditions in the query plan.

Old-style join has a lot of restrictions listed in Outer Joins section of the documentation, one of which is a join predicate involving constants:

The ( ) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the ( ) operator.

If A and B are joined by multiple join conditions, then you must use the ( ) operator in all of these conditions. ...

So to make, for example, ifp.parcel_id > 0 an outer join condition, it has to involve ( ) sign for the outer joined table and some column from this table. This may be done by using any expression involving any column of the outer joined table that always evaluates to this constant: that case expression fulfills this requirement, as you've noticed.