I have a range partitioned table that contains close to 100 million rows.
This table has a primary key and 12 non unique global indexes. I did not
design the table, I inherited it.
Is there a rule of thumb to follow as to whether the indexes on this table
should be changed to local instead of global indexes? In addition, would
I be better off converting the indexes to parititions.
This table used for ADHOC queries so examing every explain plan would be
an arduous task therefore I am looking for general rules to follow.
Are global indexes better than local indexes or vice versa in this situation.
Or is there some queries I can use to determine the best method to
organize my indexes.
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,
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.
CREATE INDEX (ESN)
CREATE INDEX (SUBSTR("MIN",1,6))
CREATE INDEX (ICCID)
CREATE INDEX (IMSI_VOD)
CREATE INDEX (IMSI_VZW)
CREATE INDEX (LCC);
CREATE INDEX (MACID)
CREATE INDEX (MDN)
CREATE INDEX (MEID)
CREATE INDEX (MIN)
CREATE INDEX (SOURCECLASS)
CREATE INDEX (SOURCE)
CREATE INDEX (TRNTYPE)
CREATE INDEX (USRID)
PRIMARY KEY (REQUEST_SEQ, MTAS_SYSTEM)
COLUMN_NAME EQUALITY EQUIJOIN NONEUIJOIN RANGE LIKE NULL TOTAL
PREDS PREDS PREDS PREDS PREDS PREDS