die Seilerwerks

Something new I found out today. When performing an ALTER TABLE statement (in this case to add a column) in another schema for which you have a synonym (public or private), you must refer to it with the schema qualifier, e.g. HR.EMPLOYEES rather than just EMPLOYEES. It seems the DDL statement doesn’t bother itself with synonyms. Probably a good thing, as DDL is nothing to be taken lightly and it’s best to fully spell out what it is you are mucking about with.

In the example, my user has been granted the ALTER ANY TABLE privilege, as well as SELECT on HR.EMPLOYEES. I’ve made a private synonym in my own schema, also named EMPLOYEES. Let’s see what happens:

SQL> alter table employees add foo number;
alter table employees add foo number
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> alter table hr.employees add foo number;

Table altered.

I was doing a bit of studying last night when the material made a passing reference to Oracle’s INSERT ALL and INSERT FIRST, and then had a review question on it. So this morning I set out to do a bit of learning, and was pleased to find that not only had Pythian’s Babette Turner-Underwood recently written on the subject, but that Andrew followed up on her post with another creative use.

Gary “Igore” Myers, upon hearing the glad tidings of Pete Finnigan’s parental status, has produced a handy (if not a bit cheeky) SQL primer for the kids. Maybe I can teach my 21-year-old brother-in-law some SQL now as well.

Another gem found off of the oracle-l list is this page, which offers a nice breakdown of the differences between the various RDBMS implementations (Oracle, Postgres, MySQL, etc.) in terms of their SQL implementations.

Someone one the oracle-l list posted a link to a nice breakdown of everything about Oracle’s “explain plan” output. Very nice read.

Eddie Awad posted (in June) a nice flowchart of what Oracle does when it processes a SQL statement. Of particular import is the note in step 2 (parsing):

“If a similar SQL statement exists in the shared pool, Oracle skips the parsing step. A SQL statement is parsed once no matter how many times the statement is run. As you can see, parsing does many things and consumes time and resources. You should always aim at minimizing parsing when writing SQL.”

How do you minimize parsing? Bind variables! Now go re-write all of your applications to use them.