Database Reference
In-Depth Information
Global indexes make sense in many cases in OLTP systems. Table data can be partitioned by only one key—one
set of columns. However, you may need to access the data in many different ways. You might partition
EMPLOYEE
data
by
LOCATION
in the table, but you still need fast access to
EMPLOYEE
data by
•
DEPARTMENT
: Departments are geographically dispersed. There is no relationship between a
department and a location.
•
EMPLOYEE_ID
: While an employee ID will determine a location, you don't want to have to
search by
EMPLOYEE_ID
and
LOCATION
, hence partition elimination cannot take place on the
index partitions. Also,
EMPLOYEE_ID
by itself must be
unique
.
JOB_TITLE
: There is no relationship between
JOB_TITLE
and
LOCATION
. All
JOB_TITLE
values
may appear in any
LOCATION
.
There is a need to access the
EMPLOYEE
data by many different keys in different places in the application, and
speed is paramount. In a data warehouse, we might just use locally partitioned indexes on these keys and use parallel
index range scans to collect a large amount of data fast. In these cases, we don't necessarily need to use index partition
elimination. In an OLTP system, however, we do need to use it. Parallel query is not appropriate for these systems; we
need to provide the indexes appropriately. Therefore, we will need to make use of global indexes on certain fields.
The following are the goals we need to meet:
•
•
Fast access
•
Data integrity
•
Availability
Global indexes can help us accomplish these goals in an OLTP system. We will probably not be doing sliding
windows, auditing aside for a moment. We will not be splitting partitions (unless we have a scheduled downtime),
we will not be moving data, and so on. The operations we perform in a data warehouse are not done on a live OLTP
system in general.
Here is a small example that shows how we can achieve the three goals just listed with global indexes. I am going
to use simple,
single partition
global indexes, but the results would not be different with global indexes in multiple
partitions (except for the fact that availability and manageability would
increase
as we added index partitions). We
start by creating tablespaces
P1
,
P2
,
P3
, and
P4
, then create a table that is range partitioned by location,
LOC
, according
to our rules, which place all
LOC
values less than
'C'
into partition
P1
, those less than
'D'
into partition
P2
, and so on:
EODA@ORA12CR1> create tablespace p1 datafile size 1m autoextend on next 1m;
Tablespace created.
EODA@ORA12CR1> create tablespace p2 datafile size 1m autoextend on next 1m;
Tablespace created.
EODA@ORA12CR1> create tablespace p3 datafile size 1m autoextend on next 1m;
Tablespace created.
EODA@ORA12CR1> create tablespace p4 datafile size 1m autoextend on next 1m;
Tablespace created.
EODA@ORA12CR1> create table emp
2 (EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7,2),
8 COMM NUMBER(7,2),
9 DEPTNO NUMBER(2) NOT NULL,
