partitioning

arupnanda's picture

New York Oracle User Group Fall Conference Materials

Thank you all who attended my sessions at NYOUG Fall Conference this morning. I appreciate spending you most precious commodity - your time - with me. I sincerely hope you found both the presentations enlightening as well as entertaining.

Please see the details of the sessions below along with the download links.

Keynote: Oracle 12c Gee Whiz Features

Yet another Oracle version is out and so are about 1500 new features in a variety of areas. Some are well marketed (e.g. pluggable database or the multitenant option) and some shine by their sheer usefulness. And there are some that do not get a whole lot of coverage but are are hidden gems. In this session you learned 12 broad areas of Oracle Database 12c I feel are worth learning about to make your job as a DBA or developer better, easier, smoother and, in some cases, even make it possible what was hitherto impossible or impractical.

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:

To prevent automated spam submissions leave this field empty.
Syndicate content