Jake Trent

Not in vs. Outer join Performance

I was running an SQL query today and it was sooooo slow. So slow, in fact, that it never returned. I asked the DBA, Reed, who built the table what might be up, and he informed me that it was not indexed. And proceeded to show me some cool stuff I could do to actually get my query to return. In the end, it was a comparison between the “not in” operator and a “left join”.

Read more

Count number of characters w/ SQL

There’s no pre-defined method to count the number of occurrences of a certain character in a database table column using SQL. There’s a quick and dirty way to get it done however. In my case, I wanted to figure out how many lines existed in a column that held address information, separated by line breaks. Here’s a spot of code that helped from the friendly neighborhood DBA, Reed.

Read more

Show Full SQL Developer Timestamp

In Oracle’s SQL Developer 1.5 tool, there seems to be a crazy bug where one can’t see the full timestamp value (including time) by default. You can change this by running this awesome piece of code…

Read more

These Aren't the Tables You're Looking For

Sometimes you have a large database schema and a lot of tables that you’re not sure what they contain. You do know, however, the nature of the data’ific needle that you’re trying to find in your schema’ific schema. Oracle provides some cool dictionary tables that might help you in your quest.

Read more