Richard Foote's picture

12c Asynchronous Global Index Maintenance Part III (Re-Make/Re-Model)

As I discussed previously in Part I, the space occupied by orphaned row entries associated with asynchronously maintained global indexes is not automatically reclaimed by subsequent DML operations within the index. Hence the need to clean out these orphaned index entries via the various options discussed in Part II. However, a good question by Jason […]

Richard Foote's picture

12c Asynchronous Global Index Maintenance Part II (The Space Between)

In Part I, I discussed how global indexes can now be asynchronously maintained in Oracle 12c when a table partition is dropped or truncated. Basically, when a table partition is dropped/truncated with the UPDATE GLOBAL INDEXES clause, Oracle simply keeps track of the object numbers of those table partitions and ignores any corresponding rowids within the […]

Richard Foote's picture

12c Asynchronous Global Index Maintenance Part I (Where Are We Now ?)

I previously looked at how global index maintenance was performed when dropping a table partition prior to Oracle Database 12c. Let’s see how things have now changed since the introduction of 12c. Let’s start by creating the same partitioned table and global indexes as previously: If we look at the current state of affairs, all […]

Richard Foote's picture

Global Index Maintenance – Pre 12c (Unwashed and Somewhat Slightly Dazed)

Before I discuss another Oracle Database 12c new feature, Asynchronous Global Index Maintenance, thought it might be worthwhile discussing how Global Indexes were handled prior to 12c. I’ll begin by creating and populating a simple range partitioned table: I’ll now create two global indexes, one non-partitioned, the other partitioned: So we currently have two happy […]

Richard Foote's picture

12c Partial Indexes For Partitioned Tables Part II (Vanishing Act)

In Partial Indexes Part I, we looked at how it was possible with the 12c database  to create a Partial Index based on data from only selected table partitions. The resultant Partial Index can be either a Global or Local Index. In Part I, we only really looked at Global Indexes, so let’s look at […]

Richard Foote's picture

12c Partial Indexes For Partitioned Tables Part I (Ignoreland)

In my opinion, one of the unsung “Heroes” of the new 12c Oracle database are improvements associated with Partitioning. There are lots of really nice capabilities introduced in the 12c database that significantly improves the manageability of partitioned environments. One of those new capabilities is the ability to now create both local and (importantly) global […]

Jonathan Lewis's picture

Partitioning 12c

Most useful presentation of OOW so far, from Hermann Baer of Oracle on improvements in partitioning features in 12c – and there are lots of useful ones, including:

Online move of a partition – so easy to compress a partition when it has reached its final “read-only” state

multiple partition maintenance in one operation – e.g. merge 3 monthly partitions into one quarterly partition, or split one partition into 24 (think about “how do I partition a non-partitioned table”, and 12c has just made it easier and quicker – exchange it into an empty partitioned table, then do one massive split).

partial indexing – define which partitions of a partitioned table should be included in the indexes you create on the table – and the optimizer also knows that different partitions need different plans (an enhancement of “table expansion”.

Jonathan Lewis's picture

Recursive SQL

A question came up on the OTN database forum a few days ago asking why a very simple query against a hash partitioned table was doing so much physical I/O to get a very small amount of data from a single partition:

I have table hash-partitioned on an indexed column “id” which is non-unique and part of my primary key. Inside each partition, rows with the same id are located close to each other which is done by dbms_redefinition reorg using orderby_cols. The intention is to reduce the amount of physical reads since there are no queries that don’t filter on the id column.

Jonathan Lewis's picture

Truncate Partition

A recent posting on the OTN database forum (which I didn’t answer correctly on my first attempt) raised the problem of truncating partitions when there are referential integrity constraints in place. Let’s start with a demonstration of the problem:

randolf.geist's picture

Incremental Partition Statistics Review


Here is a summary of the findings while evaluating Incremental Partition Statistics that have been introduced in Oracle 11g.

The most important point to understand is that Incremental Partition Statistics are not "cost-free", so anyone who is telling you that you can gather statistics on the lowest level (partition or sub-partition in case of composite partitioning) without any noticeable overhead in comparison to non-incremental statistics (on the lowest level) is not telling you the truth.

Although this might be obvious I've already personally heard someone making such claims so it's probably worth to mention.

In principle you need to test on your individual system whether the overhead that is added to each statistics update on the lowest level outweighs the overhead of actually gathering statistics on higher levels, of course in particular on global level.

Syndicate content