global or local indexes

[stecal]

What is your partition key?

If you have a range-part. table (date), and a customer ID and an order number (with one cust ID mapping to one order ID; and order ID maps to one and only one customer ID, so each order ID is unique), would you need a primary key? In this case, your order ID could be the PK, but most of your queries are based on who and when (customer ID and date). In this example, you would probably want to use local partitioned indexes on customer ID. A query of "what orders did customer X place last quarter" turns into partition pruning and then local partition index lookup using the customer ID.

So, what are the 12 indexes based on?

An example from Oracle below. If the local index were unique - it does not mean that an employee cannot exist in other departments, but that when an employee does exist within a department, that ID appears only once within that partition (plus the partition key requirement for the index).

Example of Index Creation: Starting Table Used for Examples

 CREATE TABLE employees
(employee_id NUMBER(4) NOT NULL,
 last_name VARCHAR2(10), 
 department_id NUMBER(2))
PARTITION BY RANGE (department_id)
(PARTITION employees_part1 VALUES LESS THAN (11) TABLESPACE part1, 
 PARTITION employees_part2 VALUES LESS THAN (21) TABLESPACE part2, 
 PARTITION employees_part3 VALUES LESS THAN (31) TABLESPACE part3);
Example of a Local Index Creation

 CREATE INDEX employees_local_idx ON employees (employee_id) LOCAL;

A local index can be unique. However, in order for a local index to be unique, the partitioning key of the table must be part of the index's key columns. Unique local indexes are useful for OLTP environments.