Oracle VM Consulting
view counter


Oracle PL/SQL (Procedural Language/Structured Query Language) resources, news, and support articles.

SQL Quiz: The optimizer got it wrong. How can we make it do better?

The optimizer does particularly badly with the query below. It estimates the query will return 5,101 rows when in fact nothing matches the predicate:

PostgreSQL Install-Windows

A number of folks have asked me to create a post of a PostgreSQL installation on Windows. So, here is my step-by-step PostgreSQL 9.3 installation.

Download PostgreSQL Database

You can start the download of PostgreSQL from this site. It leads you to the EnterpriseDB site, where you can download the prepackaged software.

Single SQL vs SQL+PL/SQL

Everyone knows Tom Kyte’s mantra:
You should do it in a single SQL statement if at all possible.
But we all know that “Every rule has an exception
There are many different cases when pl/sql with sql can be more efficient than only sql, and i dont want to catalog them. I just want to show a couple examples of such exceptions:

Add User Defined Types

Somebody asked me if there was a cheaper alternative to using the Embarcadero data modeling tools. I said sure, you can use the MySQL Workbench. My friend laughed and said, it’s to model Oracle databases and they use different data types. I broke the news to him that he can create his own user defined types and use MySQL Workbench to model problems for the Oracle Database 11g.

For example, you can launch the MySQL Workbench, and click on the Model menu option, and in the menu window click on the User Defined Types choice, as shown in the following:

Surrogate keys don’t fix normalization problems!

alter /* unnormalized */ table
is_still_unnormalized_after add (
surrogate_key_id integer primary key,
to_it varchar2(1)

Normalization is to do with the dependencies between the (business) attributes. As surrogate keys are additional system generated identifiers, they do nothing to change the dependencies between the original attributes!

Relationship Notations

One of my students asked how to convert MySQL Workbench’s default Crow’s Foot (IE) diagram to one of the other supported formats – Classic, Connect to Columns, UML, and IDEF1X. Crow’s Foot is also known as the Information Engineering Model method (covered in Chapter 3 of my MySQL Workbench: Data Modeling & Development.

Results of First Annual Logic Championship (2013)

You will find below the rankings for the first ever 2013 Logic championship.

Congratulations first and foremost to our top-ranked players:

1st Place: Viacheslav Stepanov of Russia

2nd Place: Jerry Bull of United States

SQL Quiz: I dropped this materialized view, but can still select from it! How?

I dropped a materialized view (without the preserve table option), but the object still exists! Here’s a copy and paste from my session:

MySQL Image Architecture

The MySQL DB Development group posed a questions on how to handle images. Naturally, the argument always goes: Should images be deployed in the database or the file system? I believe they should be stored in the database because the cost and time associated is too high with regard to managing files, a file naming schema, and backing up the file system discretely from the database.

Is Evolution Irrefutable and Compelling?

On my PL/SQL Challenge website, we have a feature called Roundtable, which offers an opportunity to discuss "big picture" questions relevant to Oracle programmers.

The current discussion (well, sharing, really) asks players to share the programming languages with which they work.

In part of my answer, I wrote:

view counter