Difference in efficiency between NVL and IS NULL

Question

I have the below query (showing the last part of the query). In the first example I get a result (which is not exactly as desired) but the results arrive within 10min. The second version results in an error: snapshot too old: rollback segment number.

Basically I'm joining in a second table with one key, and add a second key if available, and a third key if available. If key #2 and #3 are null then the join should only happen on the first key.

My research has shown that the snapshot too old error message originates from the fact that the query takes too much resources. That's why I would like to understand in how far NVL is more efficient than IS NULL and what I can do to adjust my query.

quick but result is not as desired:

..…….
   left outer join tlea
          on (    dset_cob_dt = tlea_cob_dt
              and meas_data.tlsn_trd_id = tlea.tlsn_trd_id
              and nvl (meas_data.tlsn_vrsn_num, -1) =
                     nvl (tlea.tlsn_vrsn_num, -1)
              and meas_data.tlsn_leg_id = tlea.tlsn_leg_id);

works correctly but getting errors:

    ………
             LEFT OUTER JOIN tlea
       ON (dset_cob_dt = tlea_cob_dt
        AND  meas_data.tlsn_trd_id = tlea.tlsn_trd_id
        AND (meas_data.tlsn_leg_id = tlea.tlsn_leg_id OR tlea.tlsn_leg_id IS NULL)
        AND (meas_data.tlsn_vrsn_num = tlea.tlsn_vrsn_num OR tlea.tlsn_vrsn_num IS NULL)
         );

ORA-12801: error signaled in parallel query server P034
ORA-01555: snapshot too old: rollback segment number 240 with name "_SYSSMU240_2257379025$" too small

How can I make the second quefry more efficient so I can avoid the error? Is there a way to achieve the same with NVL?

UPDATE: The query should always join on tlsn_trd_id and if available (not null) additionally on tlsn_leg_id and if available (not null) additional on tlsn_vrsn_num as well

Answer 1

To me, the logic is that the NVL value gives a specific answer to the query handler. Whereas, the IS NULL value causes an ambiguous join, thus resulting in a Cartesian product.

That is, every row on the left is trying to join with every single null on the right in the IS NULL. Conversely, in the NVL only those rows which are null on both sides join.