Search

Top 60 Oracle Blogs

Recent comments

December 2009

Global Temporary Tables Share Statistics Across Sessions

In another blog posting, I asserted that statistics collected by one session on a Global Temporary table (GTT) would be used by other sessions that reference that table, even though each session has their own physical instance of the table. I thought I should demonstrate that behaviour, so here is a simple test.

We will need two database sessions. I will create a test Global Temporary table with a unique index.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
DROP TABLE t PURGE;
TRUNCATE TABLE t;

CREATE GLOBAL TEMPORARY TABLE t
(a NUMBER,b VARCHAR2(1000))
ON COMMIT PRESERVE ROWS;

CREATE UNIQUE INDEX t ON t(a);

Partition Maintenance with Global Indexes

(updated 3.12.2009 to deal with UPDATE GLOBAL INDEXES option)
When I decide to partition a table, I also need to consider whether to partition the indexes, and if so how. The easiest option is to locally partition the indexes.  I try to avoid globally partitioned indexes because they can become invalid when you do partition maintenance. However, where an index leads on a column other than the partitioning key, then you might have to scan all partitions of a locally partitioned index if you do not query by the partitioning column.  Sometimes, it is necessary to partition an index differently to the table, or not at all. However, I have found that there are some partition management operations that do not invalidate global indexes.

The Thing (I Don't Like) About Video

Video is awesome. I like high-bandwidth communication. Even on the cheapest, most un-produced videos, I can see facial expressions and body language that I'd never be able to pick up from text. I can see candidness that's not going to come through in a document, even a blog that's written pretty much off the cuff. And videos with high production value, ...well of course it's awesome to watch a great short movie right at the tips of your fingers.

But...

But when you send me a 7:48 video, I have to budget 7:48 to watch it. (Well, more actually, because of the latency required to buffer it up.) When you send me a 13-page document, I can "read" it in 10 seconds if I want to. I can skim the first and final paragraphs really quickly and look for pictures or sidebars or quotes, and it takes practically no time for me to do it.

With a video, it's just more difficult to do that. I can watch the first 10 seconds and usually know whether I want to watch the remainder. But skimming through the whole video—like skipping to the end—is more difficult, because I have to sit there un-utilized while the whole video buffers up. Then I have to sit there while words come at me aurally, which is annoyingly sequential compared to reading buckets of text in one eyeful.

So, the bottom line is that the first 10 seconds of your video need to convince me to watch the remainder.

Or I won't.

Is it just me?

What's out there to make video browsing a better, more time-efficient, and more fulfilling experience?

NEXTGRES Gateway SQL*Plus Teaser…

Do you love SQL*Plus but hate that you can’t use it with other, non-Oracle databases?  Do you wish you could more easily migrate some of your MySQL or Postgres applications to Oracle?  If so, stay tuned, because my next few blog entries are going to show you how to do just that.  But first, I […]

Table Expressions, Cardinality, SYS_OP_ATG and KOKBF$

Recently I was involved in discussion on OTN form (see http://forums.oracle.com/forums/thread.jspa?threadID=986657)
and I found an interesting information inside the posted execution plan which was in "Predicate information" section and was

filter(SYS_OP_ATG(VALUE(KOKBF$),2,3,2)='HQPRM003')

Googling showed some interesting information in Vlad Sadilovski's blog that KOKBF$ is a generic alias given the table expressions. What was more interesting was undocumented SYS_OP_ATG function which attracted my interest. So I decided to create a simple case to observe what is going on.

SQL> CREATE TYPE demo_object AS OBJECT
2 ( ID NUMBER(6)
3 , first_name VARCHAR2(30)
4 , last_name VARCHAR2(20));
5 /

Type created.

SQL> create type demo_object_t as AS TABLE OF demo_object;
2 /

Type created.

My interview at Miracle Open World 2008 in Lalandia, Denmark

Just for fun I post here two videos related to my presentation at Miracle Open World 2008 in Lalandia, Denmark. I had a presentation titled "Cost Based Optimizer Falacies Caused By The Application Design".
This is a short video where I announce my presentation at Miracle Open World 2008. I took this video while I was baking pizzas for my grandchildren. The oven is homemade and if you are interested you can see more details how I have built it.