Oracle Indexes

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

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

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

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

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

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

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

Important !! Clustering Factor Calculation Improvement (Fix You)

Believe me, this article is worth reading I’m currently not allowed to discuss Oracle 12c Database goodies but I am allowed to discuss things perhaps initially intended for 12c that are currently available and already back-ported to 11g. This includes a wonderful improvement in the manageability of how the Clustering Factor (CF) of an index can now […]

Storage Indexes vs Database Indexes IV: 8 Column Limit (Eight Line Poem)

As Exadata Storage Indexes (SI) are purely memory only structures located on the Exadata storage servers, care needs to be taken in how much memory they can potentially consume. As a result, there is a limit of 8 columns (or 8 SIs) that can be defined for a given 1M storage region at any point […]

Storage Indexes vs Database Indexes Part III: Hidden Values (Hide Away)

OK, my holiday to Hawaii is now slowing fading away into distant memory. Time for a new post In my previous post on differences between Exadata Storage Indexes and Database Indexes Part II, I discussed how the clustering of data within the data is an important factor (pun fully intended !!) in the performance and […]