Oracle Indexes

Richard Foote's picture

12c Indexing Extended Data Types Part II (15 Steps)

Finally, at long long last, I have a spare 30 minutes in my life to complete this blog entry !! As discussed previously, Oracle has extended the maximum length of varchar2, nvarchar and raw columns to 32K, but this comes with some challenges when it comes to indexing such columns due to restrictions on the […]

Richard Foote's picture

12c Indexing Extended Data Types Part I (A Big Hurt)

The maximum size for VARCHAR2, NVARCHAR and RAW columns has been extended to 32767 bytes with the Oracle 12c Database. However, indexing such columns with standard indexes comes with some challenges. These extended data types are not enabled by default within the database but can easily be done so by following these steps: Restart the […]

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

Syndicate content