简单描述一下列的顺序对表的影响。
一般来说，表中列的顺序对表没有什么影响。如果应用程序需要依赖表中列的顺序，说明应用程序设计本身就有问题。
而且建立表时，表中定义的列的顺序并非和表中列的存储顺序保持一致，Oracle会自动根据需要进行调整。具体描述可以参考：http://yangtingkun.itpub.net/post/468/487302
但是凡事都有例外，有些时候列的顺序还是比较重要的。
举个简单的例子，在设计表结构时，如果将可为空的列放在表的最后，可以节省空间。
如果最后的列为空，那么存储的时候Oracle直接省略掉这个列，而如果空的列在表的中间，Oracle还要存储1字节的列长度信息。
看一个极端一点的例子：
SQL> CREATE TABLE T1
2 (ID NUMBER PRIMARY KEY,
3 C1 NUMBER,
4 C2 NUMBER,
5 C3 NUMBER,
6 C4 NUMBER,
7 C5 NUMBER,
8 C6 NUMBER,
9 C7 NUMBER,
10 C8 NUMBER,
11 C9 NUMBER,
12 C10 NUMBER,
13 CREATED DATE DEFAULT SYSDATE NOT NULL);
表已创建。
SQL> CREATE TABLE T2
2 (ID NUMBER PRIMARY KEY,
3 CREATED DATE DEFAULT SYSDATE NOT NULL,
4 C1 NUMBER,
5 C2 NUMBER,
6 C3 NUMBER,
7 C4 NUMBER,
8 C5 NUMBER,
9 C6 NUMBER,
10 C7 NUMBER,
11 C8 NUMBER,
12 C9 NUMBER,
13 C10 NUMBER);
表已创建。
SQL> INSERT INTO T1 (ID) SELECT ROWNUM FROM DBA_OBJECTS;
已创建71112行。
SQL> INSERT INTO T2 (ID) SELECT ROWNUM FROM DBA_OBJECTS;
已创建71112行。
SQL> COMMIT;
提交完成。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1')
PL/SQL 过程已成功完成。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T2')
PL/SQL 过程已成功完成。
SQL> SELECT TABLE_NAME, AVG_ROW_LEN
2 FROM USER_TABLES
3 WHERE TABLE_NAME IN ('T1', 'T2');
TABLE_NAME AVG_ROW_LEN
------------------------------ -----------
T1 12
T2 12
在这个例子中，T1和T2唯一的区别在于CREATED列的位置，来比较NULL列在表中间和表的最后的区别。
根据统计信息看，两个表的行平均长度相等，但是这是假象。DBMS_STATS包对AVG_ROW_LEN的计算有问题，如果换成ANALYZE来进行分析就可以看到明显的区别：
SQL> ANALYZE TABLE T1 COMPUTE STATISTICS;
表已分析。
SQL> ANALYZE TABLE T2 COMPUTE STATISTICS;
表已分析。
SQL> SELECT TABLE_NAME, AVG_ROW_LEN
2 FROM USER_TABLES
3 WHERE TABLE_NAME IN ('T1', 'T2');
TABLE_NAME AVG_ROW_LEN
------------------------------ -----------
T1 26
T2 16
和预期结果一样，由于NULL值在表的中间，因此Oracle需要1个字节表示NULL的长度，由于存在10个可空的列，因此T1比T2的平均行长度多了10字节。
当然这里要讨论的重点不是这个，对于现在计算机的存储容量来说，这个方法所能节省的空间微乎其微。
有一种情况，列的顺序会真正的影响表的访问性能，这就是带OVERFLOW段的索引组织表。
索引组织表是根据表的主键来存储表中的数据，表中的数据以索引的格式存放。因此当表的大部分查询都是通过主键扫描进行，那么使用索引表省去了先扫描主键索引，再扫描表的过程。
但是如果索引组织表的记录长度比较大，那么就是使得一个BLOCK中只能保存很少量的记录，使得索引的高度增加，降低索引组织表的性能。Oracle为了解决这个矛盾，提供了OVERFLOW段，将索引组织表中不经常访问的字段放到OVERFLOW段中。
显然如果要访问OVERFLOW段中的内容，还需要额外的IO才能获取，因此如果经常需要访问的字段保存在OVERFLOW段中，势必会影响索引组织表的性能。
而索引组织表的OVERFLOW段有两种定义方式：PCTTHRESHOLD和INCLUDING。无论使用哪种方法，都是表中靠后的字段被放到OVERFLOW段中的机会更大。
Oracle先计算主键列的长度，然后从前到后依次累加表中列的长度，如果长度除以BLOCK的大小超过了PCTTHRESHOLD的值，则当前列和剩下的列都放到OVERFLOW段中。INCLUDING方式更简单，先是主键列，然后从表的前面依次向后，直到INCLUDE指定的主键列为止，剩下的部分都放到OVERFLOW段中。
通过一个简单的例子来说明这个问题：
SQL> CREATE TABLE T_INDEX1 (ID NUMBER PRIMARY KEY,
2 NAME VARCHAR2(30),
3 CREATED DATE DEFAULT SYSDATE)
4 ORGANIZATION INDEX
5 INCLUDING NAME OVERFLOW;
表已创建。
SQL> CREATE TABLE T_INDEX2 (ID NUMBER PRIMARY KEY,
2 CREATED DATE DEFAULT SYSDATE,
3 NAME VARCHAR2(30))
4 ORGANIZATION INDEX
5 INCLUDING CREATED OVERFLOW;
表已创建。
SQL> INSERT INTO T_INDEX1 (ID, NAME)
2 SELECT ROWNUM, OBJECT_NAME
3 FROM DBA_OBJECTS;
已创建71118行。
SQL> INSERT INTO T_INDEX2 (ID, NAME)
2 SELECT ROWNUM, OBJECT_NAME
3 FROM DBA_OBJECTS;
已创建71118行。
SQL> COMMIT;
提交完成。
SQL> SET AUTOT ON
SQL> SELECT ID, NAME FROM T_INDEX1 WHERE ID = 1;
ID NAME
---------- ------------------------------
1 ICOL$
执行计划
----------------------------------------------------------
Plan hash value: 2619309072
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 2 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| SYS_IOT_TOP_93514 | 1 | 30 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=1)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
462 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT ID, NAME FROM T_INDEX2 WHERE ID = 1;
ID NAME
---------- ------------------------------
1 ICOL$
执行计划
----------------------------------------------------------
Plan hash value: 4243257855
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 2 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| SYS_IOT_TOP_93517 | 1 | 30 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=1)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
462 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到，访问第二张表要比访问第一张表多出1个逻辑读。不要小看这1个逻辑读，对于当前的情况，性能已经相差了50%。
如果索引组织表列的顺序没有设计好，导致经常访问的列存放到OVERFLOW段中，势必影响索引组织表的访问性能，降低使用索引组织表所带来的性能优势。
SQL> SET AUTOT TRACE
SQL> WITH A AS (SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM < 10000)
2 SELECT ID, NAME FROM T_INDEX1 WHERE ID IN (SELECT RN FROM A);
已选择9999行。
执行计划
----------------------------------------------------------
Plan hash value: 727517522
-------------------------------------------------------------------------------------------
|Id| Operation |Name |Rows |Bytes|Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 43| 4 (25)|00:00:01|
| 1| NESTED LOOPS | | 1 | 43| 4 (25)|00:00:01|
| 2| VIEW |VW_NSO_1 | 1 | 13| 2 (0)|00:00:01|
| 3| HASH UNIQUE | | 1 | 13| | |
| 4| VIEW | | 1 | 13| 2 (0)|00:00:01|
| 5| COUNT | | | | | |
| 6| CONNECT BY WITHOUT FILTERING| | | | | |
| 7| FAST DUAL | | 1 | | 2 (0)|00:00:01|
|*8| INDEX UNIQUE SCAN |SYS_IOT_TOP_93514| 1 | 30 | 0 (0)|00:00:01|
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("ID"="$nso_col_1")
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
10667 consistent gets
0 physical reads
0 redo size
318091 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9999 rows processed
SQL> WITH A AS (SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM < 10000)
2 SELECT ID, NAME FROM T_INDEX2 WHERE ID IN (SELECT RN FROM A);
已选择9999行。
执行计划
----------------------------------------------------------
Plan hash value: 4073295543
----------------------------------------------------------------------------------------------------
|Id| Operation |Name |Rows|Bytes|Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1| 43| 4 (25)|00:00:01|
| 1| NESTED LOOPS | | 1| 43| 4 (25)|00:00:01|
| 2| VIEW |VW_NSO_1 | 1| 13| 2 (0)|00:00:01|
| 3| HASH UNIQUE | | 1| 13| | |
| 4| VIEW | | 1| 13| 2 (0)|00:00:01|
| 5| COUNT | | | | | |
| 6| CONNECT BY WITHOUT FILTERING| | | | | |
| 7| FAST DUAL | | 1| | 2 (0)|00:00:01|
|*8| INDEX UNIQUE SCAN |SYS_IOT_TOP_93517| 1| 30| 0 (0)|00:00:01|
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("ID"="$nso_col_1")
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
20666 consistent gets
0 physical reads
0 redo size
318091 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9999 rows processed
可以看到，将数据量增大后，二者的效率相差就更加明显了。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-608785/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-608785/
扫一扫
1339
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
