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

  • in the top query, the join on leg_id is different than yours, can you match and then test? i.e. remove the 'or tlea.tlsn_leg_id is null' part from second query. – Raj Oct 24 '16 at 18:23
  • As Raj said, the queries are not equivalent. What do you want to achieve? – vegatripy Oct 25 '16 at 8:10
  • 1
    There is a random element to ORA-01555 errors. It depends what other queries/updates are running at the same time. The two executions are not comparable, and besides which, the two queries are sufficiently different. – Andrew Brennan Oct 25 '16 at 9:50
  • I think the issue is the second query not equiv to first, it taking long, and while it i still running some other session is coming in and updating data that ia being selected here, thus 1555. So, once OP corrects the second query, technically problem should go away. Additionally, why check for is-null when you already have specified left outer join? Oracle is expecting null values from 'tlea' ... – Raj Oct 25 '16 at 11:49
  • @Raj yes they are not equivalent. The first query is wrong. So you think in the second query I can omit the or is null? – Nickpick Oct 25 '16 at 12:03

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.