Database Reference
In-Depth Information
The error message is pretty clear. The global index
must
be prefixed. So, when would you use a global index?
We'll take a look at two system types, data warehouse and OLTP, and see when they might apply.
Data Warehousing and Global Indexes
In the past, data warehousing and global indexes were pretty much mutually exclusive. A data warehouse implies certain
things, such as large amounts of data coming in and going out. Many data warehouses implement a sliding window
approach to managing data—that is, drop the oldest partition of a table and add a new partition for the newly loaded data.
In the past (Oracle8
i
and earlier), these systems would have avoided the use of global indexes for a very good reason: lack
of availability. It used to be the case that most partition operations, such as dropping an old partition, would
invalidate
the
global indexes, rendering them unusable until they were rebuilt. This could seriously compromise availability.
In the following sections, we'll take a look at what is meant by a
sliding window of data
and the
potential
impact
of a global index on it. I stress the word “potential” because we'll also look at how we may get around this issue and
how to understand what getting around the issue might imply.
Sliding Windows and Indexes
The following example implements a classic sliding window of data. In many implementations, data is added to a
warehouse over time and the oldest data is aged out. Many times, this data is range partitioned by a date attribute, so
that the oldest data is stored together in a single partition, and the newly loaded data is likewise stored together in a
new partition. The monthly load process involves the following:
•
Detaching the old data
: The oldest partition is either dropped or exchanged with an empty
table (turning the oldest partition into a table) to permit archiving of the old data.
•
Loading and indexing of the new data
: The new data is loaded into a work table and indexed
and validated.
•
Attaching the new data
: Once the new data is loaded and processed, the table it is in is
exchanged with an empty partition in the partitioned table, turning this newly loaded data in a
table into a partition of the larger partitioned table.
This process is repeated every month, or however often the load process is performed; it could be every day
or every week. We will implement this very typical process in this section to show the impact of global partitioned
indexes and demonstrate the options we have during partition operations to increase availability, allowing us to
implement a sliding window of data and maintain continuous availability of data.
We'll process yearly data in this example and have fiscal years 2014 and 2015 loaded up. The table will be
partitioned by the
TIMESTAMP
column, and it will have two indexes created on it—one is a locally partitioned index on
the
ID
column, and the other is a global index (nonpartitioned, in this case) on the
TIMESTAMP
column:
EODA@ORA12CR1> CREATE TABLE partitioned
2 ( timestamp date,
3 id int
4 )
5 PARTITION BY RANGE (timestamp)
6 (
7 PARTITION fy_2014 VALUES LESS THAN
8 ( to_date('01-jan-2015','dd-mon-yyyy') ) ,
9 PARTITION fy_2015 VALUES LESS THAN
10 ( to_date('01-jan-2016','dd-mon-yyyy') )
11 )
12 /
Table created.
