인덱스 명명 규칙

인덱스는 주로 아래의 네 가지 유형으로 명명된다. 네 가지 유형으로 생성한 인덱스의 실행 계획을 살펴보자.

1. 테이블_접미어+순번
2. 접두어_테이블_순번
3. 접두어+순번_테이블
4. 테이블_칼럼_접미어

테스트를 위해 아래와 같이 테이블과 인덱스를 생성하자.

-- 1-1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
DROP TABLE t3 PURGE;

CREATE TABLE t1 (c1 NUMBER, c2 NUMBER, c3 NUMBER, c4 NUMBER);
CREATE TABLE t2 (c1 NUMBER, c2 NUMBER, c3 NUMBER, c4 NUMBER);
CREATE TABLE t3 (c1 NUMBER, c2 NUMBER, c3 NUMBER, c4 NUMBER);

CREATE INDEX t1_x01 ON t1 (c1);
CREATE INDEX t2_x01 ON t2 (c1);
CREATE INDEX t3_x01 ON t3 (c1);

CREATE INDEX ix_t1_01 ON t1 (c2);
CREATE INDEX ix_t2_01 ON t2 (c2);
CREATE INDEX ix_t3_01 ON t3 (c2);

CREATE INDEX ix01_t1 ON t1 (c3);
CREATE INDEX ix01_t2 ON t2 (c3);
CREATE INDEX ix01_t3 ON t3 (c3);

CREATE INDEX t1_c4_ix ON t1 (c4);
CREATE INDEX t2_c4_ix ON t2 (c4);
CREATE INDEX t3_c4_ix ON t3 (c4);

아래는 네 가지 패턴으로 생성한 인덱스의 실행 계획다. 각기 장단점이 있겠지만 가독성 측면에서 1번 유형(2-1)을 바람직해 보인다.

-- 2-1
SELECT /*+ ORDERED USE_NL(B) USE_NL(C) */
       *
  FROM t1 a, t2 b, t3 c
 WHERE a.c1 > 0
   AND b.c1 = a.c1
   AND c.c1 = b.c1;

-------------------------------------------------
| Id  | Operation                      | Name   |
-------------------------------------------------
|   0 | SELECT STATEMENT               |        |
|   1 |  NESTED LOOPS                  |        |
|   2 |   NESTED LOOPS                 |        |
|   3 |    NESTED LOOPS                |        |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1     |
|*  5 |      INDEX RANGE SCAN          | T1_X01 |
|   6 |     TABLE ACCESS BY INDEX ROWID| T2     |
|*  7 |      INDEX RANGE SCAN          | T2_X01 |
|*  8 |    INDEX RANGE SCAN            | T3_X01 |
|   9 |   TABLE ACCESS BY INDEX ROWID  | T3     |
-------------------------------------------------

-- 2-2
SELECT /*+ ORDERED USE_NL(B) USE_NL(C) */
       *
  FROM t1 a, t2 b, t3 c
 WHERE a.c2 > 0
   AND b.c2 = a.c2
   AND c.c2 = b.c2;

---------------------------------------------------
| Id  | Operation                      | Name     |
---------------------------------------------------
|   0 | SELECT STATEMENT               |          |
|   1 |  NESTED LOOPS                  |          |
|   2 |   NESTED LOOPS                 |          |
|   3 |    NESTED LOOPS                |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1       |
|*  5 |      INDEX RANGE SCAN          | IX_T1_01 |
|   6 |     TABLE ACCESS BY INDEX ROWID| T2       |
|*  7 |      INDEX RANGE SCAN          | IX_T2_01 |
|*  8 |    INDEX RANGE SCAN            | IX_T3_01 |
|   9 |   TABLE ACCESS BY INDEX ROWID  | T3       |
---------------------------------------------------

-- 2-3
SELECT /*+ ORDERED USE_NL(B) USE_NL(C) */
       *
  FROM t1 a, t2 b, t3 c
 WHERE a.c3 > 0
   AND b.c3 = a.c3
   AND c.c3 = b.c3;

--------------------------------------------------
| Id  | Operation                      | Name    |
--------------------------------------------------
|   0 | SELECT STATEMENT               |         |
|   1 |  NESTED LOOPS                  |         |
|   2 |   NESTED LOOPS                 |         |
|   3 |    NESTED LOOPS                |         |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1      |
|*  5 |      INDEX RANGE SCAN          | IX01_T1 |
|   6 |     TABLE ACCESS BY INDEX ROWID| T2      |
|*  7 |      INDEX RANGE SCAN          | IX01_T2 |
|*  8 |    INDEX RANGE SCAN            | IX01_T3 |
|   9 |   TABLE ACCESS BY INDEX ROWID  | T3      |
--------------------------------------------------

-- 2-4
SELECT /*+ ORDERED USE_NL(B) USE_NL(C) */
       *
  FROM t1 a, t2 b, t3 c
 WHERE a.c4 > 0
   AND b.c4 = a.c4
   AND c.c4 = b.c4;

---------------------------------------------------
| Id  | Operation                      | Name     |
---------------------------------------------------
|   0 | SELECT STATEMENT               |          |
|   1 |  NESTED LOOPS                  |          |
|   2 |   NESTED LOOPS                 |          |
|   3 |    NESTED LOOPS                |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1       |
|*  5 |      INDEX RANGE SCAN          | T1_C4_IX |
|   6 |     TABLE ACCESS BY INDEX ROWID| T2       |
|*  7 |      INDEX RANGE SCAN          | T2_C4_IX |
|*  8 |    INDEX RANGE SCAN            | T3_C4_IX |
|   9 |   TABLE ACCESS BY INDEX ROWID  | T3       |
---------------------------------------------------

어떤 유형이든 통일된 기준으로 관리하는 것이 가장 중요하다. 아래 쿼리로 인덱스 명 변경 스크립트를 생성할 수 있다. 순번은 칼럼 순서 기준이다.

-- 3
SELECT  a.table_name
      , a.index_name
      ,    'ALTER INDEX '
        || a.owner
        || '.'
        || a.index_name
        || ' RENAME TO '
        || SUBSTR (a.table_name, 1, 26)
        || '_'
        || a.postfix
        || CASE
               WHEN a.postfix <> 'PK'
               THEN LPAD (ROW_NUMBER () OVER (PARTITION BY a.table_owner, a.table_name,  a.postfix
                                                  ORDER BY b.column_id_list), 2, '0')
           END
        || ';' AS sql_text
   FROM (SELECT a.owner
              , a.index_name
              , a.table_owner
              , a.table_name
              , a.uniqueness
              , b.constraint_type
              , CASE
                    WHEN b.constraint_type = 'P' THEN 'PK'
                    WHEN a.uniqueness = 'UNIQUE' THEN 'U'
                    ELSE 'X'
                END AS postfix
           FROM dba_indexes a
              , dba_constraints b
          WHERE a.owner = 'OE' -- !
            AND a.index_type = 'NORMAL' -- !
            AND b.owner(+) = a.table_owner
            AND b.constraint_type(+) = 'P'
            AND b.table_name(+) = a.table_name
            AND b.index_owner(+) = a.owner
            AND b.index_name(+) = a.index_name) a
      , (SELECT   a.index_owner
                , a.index_name
                , a.table_owner
                , a.table_name
                , LISTAGG (LPAD (b.column_id, 3, '0'), ',') WITHIN GROUP
                      (ORDER BY a.column_position) AS column_id_list
             FROM dba_ind_columns a
                , dba_tab_cols b
            WHERE a.table_owner  = 'OE' -- !
              AND b.owner = a.table_owner
              AND b.table_name = a.table_name
              AND b.column_name = a.column_name
         GROUP BY a.index_owner
                , a.index_name
                , a.table_owner
                , a.table_name) b
   WHERE b.index_owner = a.owner
     AND b.index_name = a.index_name
     AND b.table_owner = a.table_owner
     AND b.table_name = a.table_name
ORDER BY a.table_owner
       , a.table_name
       , a.postfix
       , column_id_list;

TABLE_NAME           INDEX_NAME              SQL_TEXT
-------------------- ----------------------- -----------------------------------------------------------------------
CATEGORIES_TAB       SYS_C0011038            ALTER INDEX OE.SYS_C0011038 RENAME TO CATEGORIES_TAB_PK;
CATEGORIES_TAB       SYS_C0011041            ALTER INDEX OE.SYS_C0011041 RENAME TO CATEGORIES_TAB_U01;
CUSTOMERS            CUSTOMERS_PK            ALTER INDEX OE.CUSTOMERS_PK RENAME TO CUSTOMERS_PK;
CUSTOMERS            CUST_LNAME_IX           ALTER INDEX OE.CUST_LNAME_IX RENAME TO CUSTOMERS_X01;
CUSTOMERS            CUST_EMAIL_IX           ALTER INDEX OE.CUST_EMAIL_IX RENAME TO CUSTOMERS_X02;
CUSTOMERS            CUST_ACCOUNT_MANAGER_IX ALTER INDEX OE.CUST_ACCOUNT_MANAGER_IX RENAME TO CUSTOMERS_X03;
INVENTORIES          INVENTORY_IX            ALTER INDEX OE.INVENTORY_IX RENAME TO INVENTORIES_PK;
INVENTORIES          INV_PRODUCT_IX          ALTER INDEX OE.INV_PRODUCT_IX RENAME TO INVENTORIES_X01;
ORDERS               ORDER_PK                ALTER INDEX OE.ORDER_PK RENAME TO ORDERS_PK;
ORDERS               ORD_ORDER_DATE_IX       ALTER INDEX OE.ORD_ORDER_DATE_IX RENAME TO ORDERS_X01;
ORDERS               ORD_CUSTOMER_IX         ALTER INDEX OE.ORD_CUSTOMER_IX RENAME TO ORDERS_X02;
ORDERS               ORD_SALES_REP_IX        ALTER INDEX OE.ORD_SALES_REP_IX RENAME TO ORDERS_X03;
ORDER_ITEMS          ORDER_ITEMS_PK          ALTER INDEX OE.ORDER_ITEMS_PK RENAME TO ORDER_ITEMS_PK;
ORDER_ITEMS          ORDER_ITEMS_UK          ALTER INDEX OE.ORDER_ITEMS_UK RENAME TO ORDER_ITEMS_U01;
ORDER_ITEMS          ITEM_ORDER_IX           ALTER INDEX OE.ITEM_ORDER_IX RENAME TO ORDER_ITEMS_X01;
ORDER_ITEMS          ITEM_PRODUCT_IX         ALTER INDEX OE.ITEM_PRODUCT_IX RENAME TO ORDER_ITEMS_X02;
PRODUCT_DESCRIPTIONS PRD_DESC_PK             ALTER INDEX OE.PRD_DESC_PK RENAME TO PRODUCT_DESCRIPTIONS_PK;
PRODUCT_DESCRIPTIONS PROD_NAME_IX            ALTER INDEX OE.PROD_NAME_IX RENAME TO PRODUCT_DESCRIPTIONS_X01;
PRODUCT_INFORMATION  PRODUCT_INFORMATION_PK  ALTER INDEX OE.PRODUCT_INFORMATION_PK RENAME TO PRODUCT_INFORMATION_PK;
PRODUCT_INFORMATION  PROD_SUPPLIER_IX        ALTER INDEX OE.PROD_SUPPLIER_IX RENAME TO PRODUCT_INFORMATION_X01;
PROMOTIONS           PROMO_ID_PK             ALTER INDEX OE.PROMO_ID_PK RENAME TO PROMOTIONS_PK;
PURCHASEORDER        SYS_C0011037            ALTER INDEX OE.SYS_C0011037 RENAME TO PURCHASEORDER_U01;
WAREHOUSES           WAREHOUSES_PK           ALTER INDEX OE.WAREHOUSES_PK RENAME TO WAREHOUSES_PK;
WAREHOUSES           WHS_LOCATION_IX         ALTER INDEX OE.WHS_LOCATION_IX RENAME TO WAREHOUSES_X01;

24 행이 선택되었습니다.