global or local indexes
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: global or local indexes

Hybrid View

  1. #1
    Join Date
    Jul 2006
    Posts
    195

    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.

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    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.
    Last edited by stecal; 05-14-2011 at 06:01 PM.

  3. #3
    Join Date
    Jul 2006
    Posts
    195
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width