Challenges and Chances of the 11g Query Optimizer is the name of a presentation I gave at several events (e.g. Trivadis Performance Days, Oracle OpenWorld, DOAG Konferenz, UKOUG Conference) throughout 2011. Its abstract is the following: With every new release, the query optimizer is enhanced. Oracle...

At page 383 of my book I wrote the following sentence (BTW, the same information is also provided by Table 9-3 at page 381): With B-tree indexes, IS NULL conditions can be applied only through composite B-tree indexes when several SQL conditions are applied and at least one of them is not based on IS...

Even though in general parallel full table scans performs direct reads, some exceptions exist. The aim of this post is to show such an exception. For test purposes I build in my own schema a copy of the SH.SALES table (the one distributed by Oracle with the demo schemas…). On that table I build an ...

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...

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...

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...

In this post I would like to remove some misinformation about the utilization of hints with invisible indexes. Let’s start by providing you what two sources say about that topic: Oracle Database 11g: New Features for DBAs and Developers (by Sam R. Alapati and Charles Kim, page 132) “If you want the...

Invisible indexes are useful to temporarily hide an index from the query optimizer. In this post, instead of explaining what invisible indexes are, I would like to show whether the database engine uses invisible indexes to avoid false contention caused by unindexed foreign key. Let’s start by...