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