GTT(Global Temporary Table) and Hint

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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: