7.2　字段

字段對象對于SQL語句的執行效率也有很大的影響。影響因素主要體現在兩個方面——字段存儲順序和字段類型，下面分別說明。

1.字段存儲順序

字段存儲順序會影響訪問性能。下面我們先觀察一下行記錄的存儲結構。

其中，H表示記錄頭，L表示字段長度，D表示字段內容。

從上面的結構可見，數據庫不知道一條記錄中每個字段的偏移量。如果需要定位字段2，必須從字段1開始，接著根據字段1的長度來定位字段2。靠近記錄開始的字段定位速度明顯快于末尾的字段。因此，在做表設計時，將訪問頻繁的字段放在前面。

2.字段類型

如果說字段存儲順序對訪問性能有一定影響，那么字段類型對訪問性能就有著更顯著的影響。常見的問題是：隱式數據類型轉化；錯誤數據類型帶來的成本估算異常。

下面通過兩個示例分別說明，先舉一個隱式數據類型轉化的示例。

---

SQL> create table t1 (owner varchar2(30),object_name varchar2(128),object_id varchar2(100));
//表已創建

SQL> insert into t1(owner,object_name,object_id) select owner,object_name,object_id from dba_object
已創建 18869 行。

SQL> commit;
//提交完成

SQL> create index idx_t1_id on t1(object_id);
//索引已創建

SQL> set autotrace on
SQL> select * from t1 where object_id=20;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   135 |    27   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |   135 |    27   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_NUMBER("OBJECT_ID")=20)
/*
從上面輸出可見，表T1的OBJECT_ID字段保存的是數字，測試中估計創建了文本類型，導致模擬選擇錯誤字段的情況發生。當執行一個正常的查詢時，由于類型不一致，優化器進行了隱式的數據類型轉換，從Predicate Information中可以看出來，進行了一次TO_NUMBER操作。由于數據類型轉換，整體執行計劃走了全表掃描
*/
    
//下面我們看看正常情況下的執行計劃
SQL> select * from t1 where object_id='20';
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |   135 |    16   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID
                           | T1        |     1 |   135 |    16   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN | IDX_T1_ID |    78 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"='20')
//從上面輸出可見，引用了正確的數據類型后，走了索引的范圍掃描

---

下面看一下因為數據類型異常導致的優化器估算異常的示例。

---

SQL> create table t_test(id number,v1 varchar2(20),n1 number,d1 date);
//表已創建

SQL> insert into t_test select rownum,
  2         to_char(to_date('2001-01-01','yyyy-mm-dd') + (rownum-1),'yyyy-mm-dd'),
  3         to_char(to_date('2001-01-01','yyyy-mm-dd') + (rownum-1),'yyyymmdd'),
  4         to_date('2001-01-01','yyyy-mm-dd') + (rownum-1) from dual
5  connect by rownum<= (to_date('2010-12-31','yyyy-mm-dd') - to_date('2001-01-01','yyyy-mm-dd'));
//已創建 3651 行

SQL> exec dbms_stats.gather_table_stats('hf', 't_test');
//PL/SQL 過程已成功完成

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
//會話已更改

SQL> select * from t_test where rownum<10;
        ID V1                                             N1 D1
---------- ------------------------------------- ----------- -------------------
       685 20021116                                 20021116 2002-11-16 00:00:00
       686 20021117                                 20021117 2002-11-17 00:00:00
       687 20021118                                 20021118 2002-11-18 00:00:00
       688 20021119                                 20021119 2002-11-19 00:00:00
       689 20021120                                 20021120 2002-11-20 00:00:00
       690 20021121                                 20021121 2002-11-21 00:00:00
       691 20021122                                 20021122 2002-11-22 00:00:00
       692 20021123                                 20021123 2002-11-23 00:00:00
       693 20021124                                 20021124 2002-11-24 00:00:00
//已選擇9行
//上面創建了一張測試表，包含3個字段，保存的信息都是“日期”。后面插入了10年的日期數據

SQL> select * from t_test
where d1 between to_date('2001-01-01','yyyy-mm-dd') and to_date('2002-01-01', 'yyyy-mm-dd');
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |   366 | 10614 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_TEST |   366 | 10614 |     7   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("D1"<=TO_DATE(' 2002-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "D1">=TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
//上面測試中按日期類型字段進行范圍掃描，優化器評估返回366條記錄，這是十分精準的

SQL> select * from t_test where v1 between '20010101' and '20020101';
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |   402 | 10854 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_TEST |   402 | 10854 |     7   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("V1"<='20020101' AND "V1">='20010101')
/*
如果使用文本字段進行類似的查詢，優化器評估返回402條記錄，這較上面測試存在一定偏差。為什么會造成這一現象？原因就是優化器針對文本的范圍選擇率的評估不如日期類型精準
*/

SQL> select * from t_test where n1 between 20010101 and 20020101;
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |   402 | 10854 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_TEST |   402 | 10854 |     7   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N1"<=20020101 AND "N1">=20010101)
//數字類型與文本類型類似

---