July 2, 2014

Comparisons

Catching up (still) from the Trivadis CBO days, here’s a little detail which had never crossed my mind before.

where (col1, col2) < (const1, const2)

This isn’t a legal construct in Oracle SQL, even though it’s legal in other dialects of SQL. The logic is simple (allowing for the usual anomaly with NULL): the predicate should evaluate to true if (col1 < const1), or if (col1 = const1 and col2 < const2). The thought that popped into my mind when Markus Winand showed a slide with this predicate on it – and then pointed out that equality was the only option that Oracle allowed for multi-column operators – was that, despite not enabling the syntax, Oracle does implement the mechanism.

If you’re struggling to think where, it’s in multi-column range partitioning: (value1, value2) belongs in the partition with high value (k1, k2) if (value1 < k1) or if (value1 = k1 and value2 < k2).

Related

Oscar,
It’s possible to work around the limitation, of course – though you have to be very careful about how you do it. Simple concatenation will not be sufficient.
(Actually, you’re not doing a multi-column comparison at the end of it anyway – which was the point of the post.)

Okay, so someone had to get too smart and bloody-minded.
How about assuming that my 60-second note read “built-in operators”, or “in-fix operators” ? (Can user-defined operators be in-fix – I don’t think so, but it’s a long time since I looked at them.)

Thanks for the note. It’s an interesting syntactical anomaly – it shouldn’t fail, of course, since an expression list can contain a single expression, but it shouldn’t be necessary.

Generically (viz. when the length of the list is greater than 1) “expression = literal-expression-list” SHOULD fail with Oracle error: “ORA-01797: this operator must be followed by ANY or ALL”, or “ORA-00936: missing expression” when the list is empty.