Archive for September, 2006

GTT(Global Temporary Table) and Hint

September 27, 2006

I saw a peculiar usage of GTT recently.
I guess it’s a fairly common that you would create GTT, insert some data and join to another table(most likely a regular one, but could be another GTT). By the way, this is very SQLServer-ish thinking and we should avoid it as much as possible in Oracle. For the case I see, it’s justified to do it this way(I don’t want to get into too much details about the justification, but let’s just say it’s due to the lack of partitioning option). However, the case would insert data into the GTT and use dbms_stats to analyze table. That’s the interesting part. I guess the developer knows that Oracle CBO needs accuate stats but did not realize that the GTT has only one copy of the stats and the same copy will be used by every session. It would be ok if we are guaranteed that the GTT can only be accessed by one session. If two sessions run the same routine with vast different number of rows inserted, they could step on each other.
The solution is to use hint, something to be avoided generally, but justified with GTT. In the case of dynamic SQL and the number rows inserted into GTT is known as it should be, we can use CARDINALITY hint. Notice CARDINALITY hint is undocumented, but blessed by Tom Kyte as a good hint. Otherwise, we can use dynamic_sampling hint and test to make sure we set the correct level.


Use firefox for Report Studio

September 26, 2006

If you are a firefox and Report Studio user, you probably know by now that Report Studio does not support firefox.
But you probably also know by now that the IE tab plug-in does wonders…
BTW, Query Studio drag-and-drop also works with IE tab.

Cognos, a perfect example of a database with the ultimate extensibility

September 22, 2006

Sorry, this is not meant to be compliment.
Let’s start off with Tom Kyte’s post on the DailyWTF.
Unfortunately for the database of ultimate extensibility, usability is nil and performance is down the drain.
Now you may be complaining about Cognos performance, right? Just look at the XML stored as blobs/clobs in content store.
No wonder…

SQL Server pivot query

September 18, 2006

I blogged about Pivot query, sometimes referred to as a cross-tab query, a while ago.
Mike has mentioned pivot syntax in SQL Server here. I checked it out.
I wouldn’t describe it as cool or glamorous, but it is a neat feature that I hope Oracle will adopt. I know, not easy, for some pride issues.

Oracle SQL Parser

September 14, 2006

I have been looking for a decent Oracle SQL parser.
I found JDeveloper eSDK by Oracle, which apparently includes a parser/generator for Oracle SQL.

It is poorly documented, only JavaDoc is available.
The JavaDoc is here, the main packages involved are oracle.javatools.db and oracle.javatools.db.sql.

However, after some days of playing with it, I found serious deficiencies:

  1. Poor documentation, support close to none, only a forum where no one will answer your questions, even bug.
  2. Can’t parse WITH clause. Workaround none.
  3. Eat away keyword distinct in the select list. Workaround, use group by for some cases.
  4. Eat away package name, eg,, parsed as bar. Workaround, have a public synonym for
  5. Can’t parse IN clause with subquery. Workaround, rewrite with equijoin, potential performance impact

I am wondering if you know some decent Oracle SQL parser, perferrably written in Java.

Everyone wants to change my PATH

September 1, 2006

Every little program out there want to modify my PATH environment variable and causes tons of grief.
I had Oracle 9.2 running on my windows box, and then I installed 10g. Suddenly, I can’t connect to my 9.2 database by sqlplus. The culprit is 10g puts its version of sqlplus in the PATH ahead of the 9i version of sqlplus and I haven’t configured network for 10g yet. And then I installed OWB(Oracle Warehouse Builder), and then the same grief again.
Just adding my binary path to the PATH before everybody else is the accepted standard practice in software. Shouldn’t you at least prompt me when there’s a name conflict? Oh, because the novice users will be at a loss as to what that means… But wouldn’t they be at a loss as well when some programs suddenly stop working after installation?