The Hitchhiker’s Guide to the EXPLAIN PLAN Part 32: Pretzels created by someone experimenting with hallucinogens?

In an interview for the NoCOUG Journal, Steven Feuerstein said: “Some people can perform seeming miracles with straight SQL, but the statements can end up looking like pretzels created by someone who is experimenting with hallucinogens.” As if to prove Steven correct, NoCOUG held three international SQL challenges. The winning entries were mind-boggling indeed.(read more)

MERGE and invisible columns = invisible documentation?

Oracle 12c introduced "invisible columns" to help us add columns to tables without breaking existing applications. The documentation explains how they work with SELECT and INSERT, but not MERGE. Here's what happened when I tried MERGE.

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 31: How to Avoid a Salted Banana

In the February 2015 issue of the NoCOUG Journal, Lothar Flatz introduces the term “salted banana” for a certain kind of problem in SQL tuning.(read more)

A PL/pgSQL Function

Somebody wanted to know how to write a basic PostgreSQL PL/pgSQL function that returned a full name whether or not the middle name was provided. That’s pretty simple. There are principally two ways to write that type of concatenation function. One uses formal parameter names and the other uses positional values in lieu of the formal parameter names.

Since you’ll probably test the two approaches, I’ve also provided a conditional drop statement for the full_name (more...)

Share your interesting SQL or PL/SQL experience on stage, on camera, or in print

If you have had interesting experiences with SQL or PL/SQL in your career, here’s your opportunity to share them on stage, on camera, or in print. Send your stories to contact_nocoug@nocoug.org and we’ll forward them to the SQL evangelists team headed up by Steven Feuerstein. You may be selected to tell your stories on stage at the YesSQL! presentation at the winter conference next Tuesday or your stories may be recorded on camera or (more...)

STEW_COMPARE_SYNC: Introducing the package

After many blog posts about comparing and synchronizing tables, I have united all the techniques I presented in one place. The STEW_COMPARE_SYNC package generates SQL for
  • Comparing tables, views and queries, both local and remote.
  • Synchronizing, or applying changes to target tables from either source tables or "Change Data Capture" input.

PL/SQL Fall Through?

Somebody wants to know how you can write a PL/SQL solution that mimics the fall through of a switch statement because PL/SQL doesn’t support a switch statement. It’s a question that I found interesting because there wasn’t a need for it when I figured out what he wanted to accomplish. Essentially, he wanted to know how to implement a nested loop where the first loop runs in ascending order and the nested loop runs in (more...)

Compare and Sync without Primary Keys

I have written a lot about comparing and synchronizing tables. My examples always had both primary keys and non-key columns, so I could do updates along with inserts and deletes. What about the other tables? Here's a technique that works for them.

Convert to SQL Server?

I’m always amazed at the questions that pop up for me. For example, how do you convert an Oracle script that creates my Video Store model to a Microsoft SQL Server script. It’s not very hard but there’s one big caveat, and that’s the fact that system_user is a reserved word. That means you can’t create the Access Control List (ACL) table with a system_user name. The alternative, would be to convert the system_user table (more...)

How to avoid a salted banana! (SQL tuning)‏

Dear NoCOUG members and friends,

You’re going to enjoy the new issue of the NoCOUG Journal. Click here to download it.

  • Lothar Flatz talks about the biggest issue in SQL tuning: the “salted banana.” We predict that the phrase “salted banana” will become as widely known in the Oracle community as “compulsive tuning disorder.” Click here to go directly to Lothar’s article.
  • Janis Griffin—a.k.a. the Looney Tuner—explains why the Oracle (more...)

MERGE using Change Data Capture (CDC)

Use MERGE to apply "Change Data Capture" input to a target table with one SQL statement.

MySQL Locking

Have you ever wondered if MySQL does Row Level Locking? Or wondered why are you getting the error:

ERROR 1205 (HY000) : Lock wait timeout exceeded; try restarting transaction

You get the error because your allocated time to hold a DML lock in a transaction exceeds the set limit. Usually the default limit to hold a DML row lock, set by innodb_lock_wait_timeout db parameter, is 50 seconds. If your transaction doesn't commit/rollback within 50 seconds (more...)

MERGE magic and madness

Using the MERGE statement, you can insert into, delete from and update the same table all at once: that is the magic. If you don’t pay attention, you can also make the database do a lot of unnecessary work: that is the madness! I’ve blogged a lot about comparing tables, then using MERGE to synchronize them. […]

Popular Programming Languages

First of all, Happy New Years!

IEEE Spectrum published a ranking of the most popular programming languages. Computational journalist Nick Diakopoulos wrote the article. While it may surprise some, I wasn’t surprised to find SQL in the top ten.

07dataflow-1403643424680Nick weighted and combined 12 metrics from 10 sources (including IEEE Xplore, Google, and GitHub) to rank the most popular programming languages.

  • Compiled programming languages (Java [#1], C [#2], C++ [#3], C# [#4], Objective-C [#16])
  • Interpreted (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 30: Throw Away That EXPLAIN PLAN! (Part IV)

We tend to overuse the tool—EXPLAIN PLAN in this case—that we are most familiar with. There is an expression—attributed to behavioral scientist Abraham Maslow—that “if all you have is a hammer, everything looks like a nail.” Maslow also tells the story of a drunk searching under a street lamp for his house key, which he had dropped some distance away. Asked why he didn’t look much where he had dropped it, the drunk replied, (more...)

Oracle 12c WITH inline PL/SQL

I've been having a bit of a play with the Oracle 12c database over the past few days and I thought I'd mention a gotcha I encountered.

Of course, oracle-base is a great place to start for clear & concise information on new features and I was trying out some of the WITH clause enhancements (a.k.a. subquery factoring clause). As a developer I'm pretty excited about these in particular.

Creating inline functions (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 29: Throw Away That EXPLAIN PLAN! (Part III)

We tend to overuse the tool—EXPLAIN PLAN in this case—that we are most familiar with. There is an expression—attributed to behavioral scientist Abraham Maslow—that “if all you have is a hammer, everything looks like a nail.” Maslow also tells the story of a drunk searching under a street lamp for his house key, which he had dropped some distance away. Asked why he didn’t look much where he had dropped it, the drunk replied, (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 28: Throw Away That EXPLAIN PLAN! (Part II)

We tend to overuse the tool—EXPLAIN PLAN in this case—that we are most familiar with. There is an expression—attributed to behavioral scientist Abraham Maslow—that “if all you have is a hammer, everything looks like a nail.” Maslow also tells the story of a drunk searching under a street lamp for his house key, which he had dropped some distance away. Asked why he didn’t look much where he had dropped it, the drunk replied, (more...)

Querying InnoDB Tables

Somebody ran into the following error message trying to query the innodb_sys_foreign and innodb_sys_foreign_cols tables from the information_schema database:

ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

It’s easy to fix the error, except you must grant the process privilege. It’s a global privilege and it should only be granted to super users. You grant the privilege (more...)

MySQL Non-unique Index

Somebody wanted to know how to find any non-unique indexes in MySQL. The query takes a session variable with the table name and returns the non-unique indexes by column names. It uses a correlated subquery to exclude the table constraints.

You set the session variable like this:

SET @sv_table_name := 'member_lab';

You can query the indexes result with the following query:

SELECT   s.table_name
,        s.index_name
,        s.seq_in_index
,        s. (more...)