Archive for June, 2006

Oracle bug

June 28, 2006

I promise will come back to remote partitioned table later.
I think I hit an Oracle bug.
When I tried to drop tablespace including contents and datafiles, Oracle returned successfully. But throws exception in the alert.log that Oracle couldn’t remove the underlying file on the OS. This happens only on windows and it seems like some other processes have locked the datafiles to be dropped. Oracle should throw an exception with an error code.
But even if Oracle does return an error code instead of silently throwing up in the alert.log, I am still puzzled by couldn’t remove the datafile by OS command. This is random that it’s difficult to reproduce and file a tar.

Remote partitioned table pruning

June 21, 2006

There seems to be little information available regarding remote partitioned table pruning. Oracle doc describes how remote (distributed) SQL is executed, but does not mention anything about remote partitioned table, especially when will Oralce push down partition key to remote database to allow pruning and when will not.

I have seen Oracle does remote partition pruning for very simple predicates on the partition key, say equality/inequality condition with constant, but not in the case when such predicate involves lookup to another table, which is very common in the data warehouse environment. I will try to produce a simple case to illustrate this issue.

Star hint does wonderful things

June 17, 2006

Following my last post about using star optimization, the old-fashioned B-Tree based star query since Oracle 7, I have found that star hint helps the optimizer tremendously. Without the hint, Oracle will not consistently join the dimensions first and/or in correct join order and partition prune the fact table. With hint, the plan is much stable, Oracle chooses the correct join order and then does partition pruning. The issue is the complexity of our dimensions. The time dimension is typical snowflake dimension, but the other dimension is really mini star-schema within itself. And each query generated by Cognos is not only huge and unreadable, but involves more than ten tables at the least.

Star transformation vs Star optimization

June 15, 2006

eHealth 6.0 beta release features ReportCenter, an embedded Cognos solution to enable clients for report customization as well as drag-and-drop querying. Obviously, we use a star schema, a snowflaked one though.

I started working on the performance issues a month ago. The first thing comes to mind is that we have a star schema so we should use star transformation. I created bitmap indexes on fact table, turn on star_transformation_enabled init parameter and stick in STAR_TRANSFORMATION hint. Oracle did the transformation but the execution time is much worse. I was puzzled for a while and looked up the oracle doc. And there's the answer:


Whereas the star optimization works well for schemas with a small number of dimensions and dense fact tables, the star transformation may be considered as an alternative if any of the following holds true:

  • The number of dimensions is large
  • The fact table is sparse
  • There are queries where not all dimension tables have constraining predicates

The star transformation does not rely on computing a Cartesian product of the dimension tables, which makes it better suited for cases where fact table sparsity and/or a large number of dimensions would lead to a large Cartesian product with few rows having actual matches in the fact table. In addition, rather than relying on concatenated indexes, the star transformation is based on combining bitmap indexes on individual fact table columns.


In our case, we have only two dimensions and the fact table is very dense such that we almost have every combination of the two dimensions in the fact table. We also use the two dimensions in the predicate for all of our standard out-of-box reports.

I also googled a powerpoint, which details when it is appropriate to use star transformation and when to use star optimization.

Mass defection from

June 15, 2006

In case you haven't noticed, there's a mass defection from and many bloggers are moving to wordpress, at least many Oracle bloggers.

I have seen Pete S and David Aldridge. I think Tom Kyte will be the next. It seems he has already setup an account.

So I guess I will setup one here as well. I plan to blog about Oracle and maybe some other random stuff.