June 2016 – KC Database

Examples/Deep Dives/Reference

Grabbing the “Most Recent Record”

Suppose you have a table full of tweets.

create table tweets

(

username varchar2(128) not null

, tweet_time timestamp default systimestamp not null

, tweet varchar2(140) not null

);

insert into tweets (username, tweet_time, tweet)

values ('John', timestamp '2016-06-14 14:00:00', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.');

insert into tweets (username, tweet_time, tweet)

values ('John', timestamp '2016-06-17 09:54:00', 'Suspendisse scelerisque nec felis feugiat euismod. Integer vel iaculis neque. ');

insert into tweets (username, tweet_time, tweet)

values ('John', timestamp '2016-06-24 14:00:00', 'Curabitur congue mollis sem, et iaculis nunc cursus non.');

insert into tweets (username, tweet_time, tweet)

values ('Paul', timestamp '2016-06-27 12:38:00', 'Maecenas blandit pellentesque turpis, id fringilla elit lacinia ac. Morbi scelerisque orci sit amet.');

insert into tweets (username, tweet_time, tweet)

values ('George', timestamp '2016-06-18 13:21:00', 'In quis tortor leo. Maecenas aliquam eu urna non laoreet.');

insert into tweets (username, tweet_time, tweet)

values ('George', timestamp '2016-06-22 12:11:00', 'Pellentesque quis lectus urna. Aenean est lectus, semper at turpis at, feugiat finibus tortor. Fusce ec condimentum quam.');

insert into tweets (username, tweet_time, tweet)

values ('George', timestamp '2016-06-25 15:55:00', 'Duis vitae erat vel est posuere fermentum malesuada ac erat. Integer malesuada non magna vitae gravida.');

insert into tweets (username, tweet_time, tweet)

values ('George', timestamp '2016-06-28 10:03:00', 'Nunc ligula ex, gravida quis turpis elementum, posuere tincidunt nunc.');

insert into tweets (username, tweet_time, tweet)

values ('Ringo', timestamp '2016-06-26 17:46:00', 'Morbi tortor urna, convallis congue dapibus laoreet, egestas id est.');

insert into tweets (username, tweet_time, tweet)

values ('Ringo', timestamp '2016-06-26 19:22:00', 'Sed id semper est. ');

commit;

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.

Examples/Deep Dives/Reference

The Append Hint

An /*+ append */ hint, when added to an insertion, tells Oracle you’d like to do a direct path insert instead of a conventional path insert. Most people use the append hint to speed up an insert; however, direct path inserts aren’t always faster than conventional inserts.

Useless Queries

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.

with cte (x)

(

select 13

from dual

)

select case when not regexp_like(rpad('1', x, '1'), '^1?$|^(11+?)\1+$') then 'Prime' else 'Not Prime' end prime_or_composite

from cte

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:

^1?$

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:

1	^(11+?)\1+$

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.

Useful Queries and Code

See Privileges Granted Through Roles

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

System Privileges:

with cte (x)

(

select 'JOHNSMITH' x

from dual

)

select distinct privilege

from dba_sys_privs

where grantee in (select granted_role

from dba_role_privs

start with grantee = (select x from cte)

connect by grantee = prior granted_role

---------

union all

---------

select x

from cte);

Object Privileges:

with cte (x)

(

select 'JOHNSMITH' x

from dual

)

select distinct privilege, owner, table_name

from dba_tab_privs

where grantee in (select granted_role

from dba_role_privs

start with grantee = (select x from cte)

connect by grantee = prior granted_role

---------

union all

---------

select x

from cte);

Archive: Jun 2016

Grabbing the “Most Recent Record”

The Append Hint

Detecting Primes with REGEXP_LIKE()

See Privileges Granted Through Roles