列定義的順序和列儲存的順序

今天在看管理員手冊的時候，Oracle提到了索引組織表列的順序問題。於是做了個測試驗證了一下。

 

 

由於索引組織表是根據索引的格式儲存的表，所以主鍵的鍵值列應該儲存在前面，隨後才是表中的其他列，下面建立一個索引組織表，將主鍵列放在後面：

SQL> CREATE TABLE T_INDEX_ORG (NAME VARCHAR2(30), ID NUMBER PRIMARY KEY)
  2  ORGANIZATION INDEX;

表已建立。

SQL> DESC T_INDEX_ORG
 名稱                                                              是否為空? 型別
 ----------------------------------------------------------------- -------- ----------------
 NAME                                                                       VARCHAR2(30)
 ID                                                                NOT NULL NUMBER

用DESC看NAME列排在主鍵列的前面，查詢DBA_TAB_COLUMNS檢視：

SQL> SELECT TABLE_NAME, COLUMN_NAME, COLUMN_ID
  2  FROM USER_TAB_COLUMNS
  3  WHERE TABLE_NAME = 'T_INDEX_ORG';

TABLE_NAME                     COLUMN_NAME                     COLUMN_ID
------------------------------ ------------------------------ ----------
T_INDEX_ORG                    NAME                                    1
T_INDEX_ORG                    ID                                      2

可以看到NAME列仍然在主鍵列的前面，難道Oracle在儲存的時候真的先儲存其他列，最後才是主鍵列嗎，其實不然，這個列的順序只是定義的順序，真正的儲存順序需要查詢COL$表：

SQL> COL OBJECT_NAME FORMAT A30
SQL> SELECT OBJECT_ID, OWNER, OBJECT_NAME
  2  FROM DBA_OBJECTS
  3  WHERE OBJECT_NAME = 'T_INDEX_ORG'
  4  AND WNER = USER;

 OBJECT_ID OWNER                          OBJECT_NAME
---------- ------------------------------ ---------------
     93348 YANGTK                         T_INDEX_ORG

SQL> SELECT OBJ#, COL#, SEGCOL#, NAME
  2  FROM SYS.COL$
  3  WHERE OBJ# = 93348;

      OBJ#       COL#    SEGCOL# NAME
---------- ---------- ---------- ------------------------
     93348          1          2 NAME
     93348          2          1 ID

可以看到，SEGCOL#列表示的是儲存時列的順序，和預料的一樣，對於索引組織表，Oracle會先儲存主鍵列，然後是其他的非鍵值列。

除了索引組織表以外，還有一些可能導致列的定義與列的儲存順序不符，比如包含LONG列的情況。

Oracle的LONG列用於存放在表的最後一列：

SQL> CREATE TABLE T_LONG (ID NUMBER, C_LONG LONG, NAME VARCHAR2(30));

表已建立。

SQL> SELECT OBJECT_ID, OWNER, OBJECT_NAME
  2  FROM DBA_OBJECTS
  3  WHERE OBJECT_NAME = 'T_LONG'
  4  AND WNER = USER;

 OBJECT_ID OWNER                          OBJECT_NAME
---------- ------------------------------ ----------------------------
     93350 YANGTK                         T_LONG

SQL> SELECT OBJ#, COL#, SEGCOL#, NAME
  2  FROM SYS.COL$
  3  WHERE OBJ# = 93350;

      OBJ#       COL#    SEGCOL# NAME
---------- ---------- ---------- ------------------------------
     93350          1          1 ID
     93350          2          3 C_LONG
     93350          3          2 NAME

除了包含LONG的表，CLUSTER表也有同樣的現象，CLUSTER的索引列在儲存是排在第一位：

SQL> CREATE CLUSTER C_DEPT (DEPTNO NUMBER);

簇已建立。

SQL> CREATE TABLE DEPT (DEPT_NAME VARCHAR2(30), DEPT_NO NUMBER)
  2  CLUSTER C_DEPT (DEPT_NO);

表已建立。

SQL> CREATE TABLE EMP (EMP_NO NUMBER, EMP_NAME VARCHAR2(30), DEPT_NO NUMBER)
  2  CLUSTER C_DEPT (DEPT_NO);

表已建立。

SQL> SELECT OBJECT_ID, OWNER, OBJECT_NAME
  2  FROM DBA_OBJECTS
  3  WHERE WNER = USER
  4  AND OBJECT_NAME IN ('EMP', 'DEPT');

 OBJECT_ID OWNER                          OBJECT_NAME
---------- ------------------------------ ------------------------------
     93355 YANGTK                         DEPT
     93356 YANGTK                         EMP

SQL> SELECT OBJ#, COL#, SEGCOL#, NAME
  2  FROM SYS.COL$
  3  WHERE OBJ# IN (93355, 93356);

      OBJ#       COL#    SEGCOL# NAME
---------- ---------- ---------- ------------------------------
     93355          1          2 DEPT_NAME
     93355          2          1 DEPT_NO
     93356          1          2 EMP_NO
     93356          2          3 EMP_NAME
     93356          3          1 DEPT_NO

同樣HASH CLUSTER也具有相同的特性：

SQL> CREATE CLUSTER C_HASH (ID NUMBER)
  2  HASHKEYS 100;

簇已建立。

SQL> CREATE TABLE T_HASH (NAME VARCHAR2(30), ID NUMBER)
  2  CLUSTER C_HASH (ID);

表已建立。

SQL> SELECT OBJECT_ID, OWNER, OBJECT_NAME
  2  FROM DBA_OBJECTS
  3  WHERE WNER = USER
  4  AND OBJECT_NAME = 'T_HASH';

 OBJECT_ID OWNER                          OBJECT_NAME
---------- ------------------------------ ----------------
     93358 YANGTK                         T_HASH

SQL> SELECT OBJ#, COL#, SEGCOL#, NAME
  2  FROM SYS.COL$
  3  WHERE OBJ# = 93358;

      OBJ#       COL#    SEGCOL# NAME
---------- ---------- ---------- -------------------------
     93358          1          2 NAME
     93358          2          1 ID

除了上面這些例子外，函式索引，巢狀表，物件表都會新增一些隱藏列，這些也會對列的儲存順序有所影響。