Oracle Indexes

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

Reuse Of Empty Index Leaf Blocks (Free Four)

A recent question by Stalin Subbiah has prompted me to write a quick post on the reuse of empty leaf blocks. In part, the question asked: “Is there anyway I could monitor the effectiveness of empty blocks being reused from freelist of an index resulting from purge process that we are planning to start soon?” […]

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 […]

Richard Foote's picture

12c: Intro To Multiple Indexes On Same Column List (Repetition)

From an indexing perspective, one of the bigger ticket items introduced with Oracle Database 12c is the new capability to create multiple indexes on the same column list. It’s even a feature listed in the New Features Guide This can be particularly useful when you want to change the index definition in some way but you […]

Richard Foote's picture

Oracle Database 12c Released – Lots of Indexing Stuff To Talk About

Finally, Oracle have released the long awaited 12c Database. You can now download it from OTN and other usual places. This is good news as my tongue was getting rather sore from having to bite on it for so long From an indexing point of view, there are lots of fantastic new features, especially from […]

Richard Foote's picture

Clustering Factor Calculation Improvement Part II (Blocks On Blocks)

My previous post on the new TABLE_CACHED_BLOCKS statistics gathering preference certainly generated some interest My blog hits for the week have gone off the charts !! One of the concerns raised by this new capability was that setting such a preference might result in really unrealistic and inaccurate Clustering Factor (CF) values, especially for those […]

Syndicate content