count 列存放的位置，越往后開銷的cpu越大

最佳字段順序（結(jié)論：越往后的列訪問CPU開銷大）


驗(yàn)證腳本1 （先構(gòu)造出表和數(shù)據(jù)）
SET SERVEROUTPUT ON
SET ECHO ON
---構(gòu)造出有25個(gè)字段的表T
DROP TABLE t;
DECLARE
  l_sql VARCHAR2(32767);
BEGIN
  l_sql := 'CREATE TABLE t (';
  FOR i IN 1..25 
  LOOP
    l_sql := l_sql || 'n' || i || ' NUMBER,';
  END LOOP;
  l_sql := l_sql || 'pad VARCHAR2(1000)) PCTFREE 10';
  EXECUTE IMMEDIATE l_sql;
END;
/
----將記錄還有這個(gè)表T中填充
DECLARE
  l_sql VARCHAR2(32767);
BEGIN
  l_sql := 'INSERT INTO t SELECT ';
  FOR i IN 1..25
  LOOP
    l_sql := l_sql || '0,';
  END LOOP;
  l_sql := l_sql || 'NULL FROM dual CONNECT BY level <= 10000';
  EXECUTE IMMEDIATE l_sql;
  COMMIT;
END;
/




--驗(yàn)證腳本2（一次訪問該表各字段驗(yàn)證） 
execute dbms_stats.gather_table_stats(ownname=>user, tabname=>'t')
SELECT num_rows, blocks FROM user_tables WHERE table_name = 'T';
--以下動(dòng)作觀察執(zhí)行速度，比較發(fā)現(xiàn)COUNT(*)最快，COUNT(最大列）最慢
DECLARE
  l_dummy PLS_INTEGER;
  l_start PLS_INTEGER;
  l_stop PLS_INTEGER;
  l_sql VARCHAR2(100);
BEGIN
  l_start := dbms_utility.get_time;
  FOR j IN 1..1000
  LOOP
    EXECUTE IMMEDIATE 'SELECT count(*) FROM t' INTO l_dummy;
  END LOOP;
  l_stop := dbms_utility.get_time;
  dbms_output.put_line((l_stop-l_start)/100);


  FOR i IN 1..25
  LOOP
    l_sql := 'SELECT count(n' || i || ') FROM t';
    l_start := dbms_utility.get_time;
    FOR j IN 1..1000
    LOOP
      EXECUTE IMMEDIATE l_sql INTO l_dummy;
    END LOOP;
    l_stop := dbms_utility.get_time;
    dbms_output.put_line((l_stop-l_start)/100);
  END LOOP;
END;
/
--結(jié)論：
--原來優(yōu)化器是這么搞的：列的偏移量決定性能，列越靠后，訪問的開銷越大。由于count(*)的算法與列偏移量無關(guān)，所以count(*)最快。
--后面還有看圖說話，看看結(jié)果輸出的趨勢(shì)圖，就更了然了。