Search

Top 60 Oracle Blogs

Recent comments

Global Indexes

“Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part IV” (Hallo Spaceboy)

In Part I, Part II and Part III we looked at some advantages of Global Indexes that may not be obvious to some. One of the advantages of a Local Index vs. Non-Partitioned Global Index is that a Local Index being a smaller index structures may have a reduced BLEVEL in comparison. This can save […]

“Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part III” (Ricochet)

In Part I and Part II of this series, we looked at how Global Indexes can effectively perform “Partition Pruning” when the partition keys are specified in SQL predicates, by only using those index entries that have a Data Object of interest stored within the index Rowids. In this piece, I’ll cover the key performance […]

“Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part II (Aladdin Sane)

In Part I of this series, I highlighted how a Non-Partitioned Global Index on a Partitioned Table is able to effectively perform “Partition Pruning” by reading only the associated index entries to access just the table blocks of interest from relevant table partitions when the table partitioned keys are specified in an SQL Predicate. Understanding […]

“Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part I (The Jean Genie)

When it comes to indexing a partitioned table, many automatically opt for Local Indexes, as it’s often assumed they’re simply easier to manage and more efficient than a corresponding Global Index. Having smaller index structures that are aligned to how the table is partitioned certainly has various advantages. The focus in this little series is on […]

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

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