global or local indexes

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.

Thanks to all who answer.

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

Code:

---

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.

Here is my layout including the number of times each column was
accessed. Should anything be converted to a local index.

CREATE TABLE XXX.REQ
(
REQUEST_SEQ NUMBER NOT NULL,
MTAS_SYSTEM VARCHAR2(15 BYTE) NOT NULL,
ACCOUNTID VARCHAR2(64 BYTE),
ACCTNUM VARCHAR2(64 BYTE),
ASLAMT VARCHAR2(64 BYTE),
ASLIND VARCHAR2(64 BYTE),
BILLERID VARCHAR2(64 BYTE),
BILLTRAN VARCHAR2(64 BYTE),
CARRNAME VARCHAR2(64 BYTE),
CARRNUMLD VARCHAR2(16 BYTE),
CUSTID VARCHAR2(64 BYTE),
CUSTNAME VARCHAR2(64 BYTE),
DATA VARCHAR2(16 BYTE),
DEVICEMAKE VARCHAR2(64 BYTE),
DEVICEMODEL VARCHAR2(64 BYTE),
DEVTYPE VARCHAR2(64 BYTE),
DIALCLASS VARCHAR2(32 BYTE),
DIRECTION VARCHAR2(32 BYTE),
DISCREASON VARCHAR2(128 BYTE),
ESN VARCHAR2(32 BYTE),
ESN2 VARCHAR2(16 BYTE),
EVENTID VARCHAR2(64 BYTE),
FAMILYSHARESETID VARCHAR2(64 BYTE),
HAREGION VARCHAR2(64 BYTE),
HTLNNUM VARCHAR2(32 BYTE),
LCC VARCHAR2(16 BYTE),
LDN VARCHAR2(16 BYTE),
LOSTSTOLENACTION VARCHAR2(32 BYTE),
MDN VARCHAR2(16 BYTE),
MDNCHG VARCHAR2(16 BYTE),
MEID VARCHAR2(32 BYTE),
MEID2 VARCHAR2(20 BYTE),
MIN VARCHAR2(16 BYTE),
OLDLCC VARCHAR2(16 BYTE),
OLDMDN VARCHAR2(16 BYTE),
OLDMIN VARCHAR2(16 BYTE),
PHONEINBOX VARCHAR2(32 BYTE),
PINCD VARCHAR2(16 BYTE),
PINTYPE VARCHAR2(16 BYTE),
PORTED VARCHAR2(8 BYTE),
PREPAYCHG VARCHAR2(64 BYTE),
PRIORITY VARCHAR2(16 BYTE),
REQDATE VARCHAR2(64 BYTE),
REQID VARCHAR2(64 BYTE),
REQTIME VARCHAR2(64 BYTE),
REQTYPE VARCHAR2(64 BYTE),
REQUESTCMD VARCHAR2(128 BYTE),
ROUTECLS VARCHAR2(64 BYTE),
SIMCARD VARCHAR2(64 BYTE),
SOURCE VARCHAR2(64 BYTE),
SOURCECLASS VARCHAR2(64 BYTE),
STARTDATE VARCHAR2(64 BYTE),
STATICIP VARCHAR2(64 BYTE),
SUBGROUP VARCHAR2(64 BYTE),
TRNTYPE VARCHAR2(64 BYTE),
USRID VARCHAR2(64 BYTE),
CREATE_DATE DATE,
FEATURES VARCHAR2(4000 BYTE),
MACID VARCHAR2(12 BYTE),
IMSI_VZW VARCHAR2(15 BYTE),
IMSI_VOD VARCHAR2(15 BYTE),
ICCID VARCHAR2(20 BYTE),
PRLBASELINE VARCHAR2(12 BYTE),
EQUIPMENTACTION VARCHAR2(16 BYTE)
)
PARTITION BY RANGE (CREATE_DATE)


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

SIMCARD 1 0 0 0 0 0 1
PRIORITY 1 0 0 0 0 0 1
EQUIPMENTACTION 1 0 0 0 1 0 2
CUSTID 3 0 0 0 0 0 3
DATA 0 0 0 0 5 1 6
REQID 6 0 0 0 0 0 6
DEVICEMAKE 5 0 0 0 1 1 7
LOSTSTOLENACTION 4 0 0 0 1 4 9
LCC 7 0 0 0 0 3 10
MEID2 0 2 0 0 0 11 13
DEVTYPE 16 0 0 0 0 0 16
ESN2 3 4 0 0 0 17 24
DISCREASON 27 0 0 0 0 2 29
PORTED 46 0 0 0 0 0 46
SOURCE 52 0 0 0 1 1 54
ICCID 91 0 0 0 0 4 95
USRID 102 0 0 0 2 2 106
DEVICEMODEL 15 0 0 0 96 1 112
IMSI_VOD 273 0 0 0 1 3 277
SUBGROUP 459 0 0 0 0 1 460
PRLBASELINE 1193 247 0 0 0 1 1441
MACID 1666 0 0 0 2 10 1678
SOURCECLASS 3575 1 0 0 0 0 3576
FEATURES 3 0 0 0 6278 11 6292
ESN 5666 1969 0 0 6 20 7661
IMSI_VZW 6188 2819 0 20 0 0 9027
MEID 8157 1968 0 0 9 40 10174
TRNTYPE 11232 0 0 0 8 0 11240
MIN 11809 6254 0 20 5 28 18116
CREATE_DATE 5 0 6259 13533 0 150 19947
MDN 24576 6286 0 0 1 15 30878
MTAS_SYSTEM 35 31657 0 0 0 0 31692
REQUEST_SEQ 108 35234 0 11 0 0 35353