Oracle Scratchpad

July 2, 2014

Comparisons

Filed under: Uncategorized — Jonathan Lewis @ 5:09 pm BST Jul 2,2014

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).

6 Comments »

  1. Interesting…

    What about using col1||col2 < cons1||cons2. Of course using FBI for these columns if needed.

    Comment by Oscar — July 2, 2014 @ 5:30 pm BST Jul 2,2014 | Reply

    • 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.)

      Comment by Jonathan Lewis — July 3, 2014 @ 9:26 am BST Jul 3,2014 | Reply

  2. “…that equality was the only option that Oracle allowed for multi-column operators …”

    that’s probably true for built-in operators, but Oracle allows us to create our own operators if we need them

    http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6004.htm#SQLRF01304.

    Comment by Matthias Rogel — July 2, 2014 @ 5:41 pm BST Jul 2,2014 | Reply

    • Matthias,

      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.)

      Comment by Jonathan Lewis — July 3, 2014 @ 9:31 am BST Jul 3,2014 | Reply

  3. Even with equality the syntax is not

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

    It is

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

    There is an “expression” on the left and an “expression list” on the right, hence the extra parentheses.

    Comment by stewashton — July 2, 2014 @ 10:03 pm BST Jul 2,2014 | Reply

    • Stew,

      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.

      Comment by Jonathan Lewis — July 3, 2014 @ 9:37 am BST Jul 3,2014 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,990 other followers