Kaley's (mostly) Oracle Database Blog
Hi! I'm Kaley Crum, Oracle DBA.
Welcome to my (mostly) Oracle database blog.

Archive: Jun 2016

Grabbing the “Most Recent Record”

Suppose you have a table full of tweets.

You would like a view that will show you the most recent tweet of every username, and you want it done fast and efficiently!  (Suppose that the table has millions of rows)

What’s the best way to get this information?  I don’t know.  But I think I’ve got a way that’s pretty fast.

Continue Reading

Detecting Primes with REGEXP_LIKE()

I saw this trick where a regular expression could be used to see if a number is prime or not.  Which, once I saw it, I had to try it out in Oracle.  It works, but it’s not efficient by any stretch of the imagination.

It works by using ‘1’ as a dummy character (any character could’ve been used, ‘1’ was arbitrarily chosen).  For whatever number that we’re trying to determine is prime or not (13 in the above example) we generate a string of that many ‘1’s.  So since 13 was selected in the above example, RPAD creates  a string that is 13 ‘1’s, so it looks like this:  1111111111111

Next, we try the first half of the regular expression:

Meaning, if the generated string is equal to ” or ‘1’ (indicating that the numbers 0 or 1 were chosen) then we immediately return true (meaning prime).  Otherwise, we try this expression:

Which means “search” for a group of two or more ‘1’s in a non-greedy search, repeated one or more times.

So search goes like this

Is is a multiple of two?
11 (no match, let’s try repeating that group)
1111 (still no match, add another repeated group)
111111 (no match, continue adding 11 to the end…)
11111111 (no match)
1111111111 (no match)
111111111111 (no match)
11111111111111 (too long! backtrack)

Is it a multiple of three?
111 (no match, repeat the group of three ‘1’s)
111111 (no match, add another group of three)
111111111 (no match, continue adding groups of three and checking for matches)
111111111111 (no match)
111111111111111 (too long! backtrack)

Is it a multiple of four?
1111 (no match)
11111111 (no match)
111111111111 (no match)
1111111111111111 (too long! backtrack)

Is it a multiple of five?…

etc…until the string that it’s looking for is longer than the string that’s being searched on.  If no matches have been had, the REGEXP_LIKE function returns false, indicating prime.

See Privileges Granted Through Roles

Here’s a couple of queries that can be used to see privileges granted through roles.

System Privileges:

Object Privileges: