How not to write subqueries:
AND sal.ticket_airline || sal.ticket_number NOT IN ( SELECT sub.dsd_airline || sub.dsd_ticket_number FROM ... )
If there had been any opportunity for the optimizer to do something clever with the NOT IN, you’ve just made sure it can’t happen. On top of that you may find that you don’t get the right results – consider the following cut-n-paste:
SQL> select user from dual where 1 || 23 = 12 || 3; USER ------------------------------ TEST_USER 1 row selected.
Sometimes people simply forget that you can have multiple columns in subqueries (or in “IN Lists”) – so it’s perfectly valid to write the subquery as:
AND (sal.ticket_airline, sal.ticket_number) NOT IN ( SELECT sub.dsd_airline, sub.dsd_ticket_number FROM ... )
It’s quite likely that Oracle will actually turn this into a NOT EXISTS, or ANTI-JOIN, of course. But if it doesn’t do something nice you could try doing a manual rewrite – provided it is actually logically equivalent:
AND not exists ( select null from .... where sub.dsd_airline = sal.ticket_airline and sub.dsd_ticket_number = sal.ticket_number )
Remember: NOT IN may not translate to NOT EXISTS – see also this.