In this short post I would like to point out a non-obvious issue that one of my customers recently hit. On the one hand, it’s a typical case where the query optimizer generates a different (suboptimal) execution plan even though nothing relevant (of course, at first sight only) was changed. On the other hand, in […]
Partition-wise operations are not something new. I do not remember when they were introduced, but at that time the release number was still a single digit. Anyway, the aim of this post is not to describe the basics, but only to describe what is new in that area in 12c and 18c. The new features […]
One of my customers that recently upgraded to 12c hit a bug (22913528) that I think is good to be aware of. Note that as the title of this post states, the problem only occur in 12.1.0.2. At least, I wasn’t able to reproduce it in any other version. To reproduce it you simply need […]
One year ago I wrote a post entitled Deferred Segment Creation. If you read the comments related to it you can see that several people were concerned by the fact that it was not possible to easily get rid of segments associated to empty tables. That was with version 11.2.0.1. Now we have version 11.2.0.2 […]
Recenty the following question was posted on oracle-l (I paraphrase…): With Oracle Database it is possible to create something similar to Teradata’s sparse indexes? Since the question is an interesting one, I decided to write this short post. First of all, I have to say that such a feature is not supported by the CREATE […]
When two tables are equi-partitioned on their join keys, the query optimizer is able to take advantage of partition-wise joins. To make sure that the tables are equi-partitioned, as of Oracle Database 11g reference partitioning can be used. In fact, per definition, with reference partitioning all “related” tables have exactly the same partitioning schema. If […]
You can add the COMPUTE STATISTICS clause to the CREATE INDEX statement. It instructs the SQL statement to gather and store index statistics in the data dictionary, while creating the index. This is useful because the overhead associated with the gathering of statistics while executing this SQL statement is negligible. In Oracle9i, the gathering of […]
Zero-size unusable indexes and index partions is a small but useful feature of Oracle Database 11g Release 2. Simply put, its aim is to save space in the database by immediately releasing the segment associated to unusable indexes or index partitions. To illustrate this, let’s have a look to an example… Create a partitioned table, […]
As of Oracle Database 11g it is possible to use a virtual column as partition key. In this post I do not want to discuss how it works and whether this is good or not… Instead, I would like to show you that the feature might lead to wrong results. First of all, I would […]
The patchset 11.1.0.7 introduces in the package DBMS_STATS a new value for the parameter GRANULARITY. The description provided by the development team in $ORACLE_HOME/rdbms/admin/dbmsstat.sql is the following: ‘APPROX_GLOBAL AND PARTITION’ – This option is similar to ‘GLOBAL AND PARTITION’. But the global statistics are aggregated from partition level statistics. It will aggregate all statistics except […]