最佳顺序是:查询经常使用的字段放在前面。
一个数据块中包含若干记录,记录中每个字段有不同的大小,所以每个字段包含两部分。前一部分是数据的长度,后一部分是数据本身。数据库引擎不知道一条记录中每个字段的偏移量,如果需要定位字段3,必须从字段1开始,接着根据字段1的长度定位到字段2。最后,根据字段2的长度来定位字段3。无论何时一条含有多个字段的记录,靠近记录开始的地方的字段定位的速度会明显快于靠近记录末尾的字段。
下面来做个试验:
SQL> create table TEST
(
N1 NUMBER,N2 NUMBER,N3 NUMBER,N4 NUMBER,N5 NUMBER,N6 NUMBER,N7 NUMBER,N8 NUMBER,
N9 NUMBER,N10 NUMBER,N11 NUMBER,N12 NUMBER,N13 NUMBER,N14 NUMBER,N15 NUMBER,N16 NUMBER,
N17 NUMBER,N18 NUMBER,N19 NUMBER,N20 NUMBER,N21 NUMBER,N22 NUMBER,N23 NUMBER,N24 NUMBER,
N25 NUMBER,N26 NUMBER,N27 NUMBER,N28 NUMBER,N29 NUMBER,N30 NUMBER,N31 NUMBER,N32 NUMBER,
N33 NUMBER,N34 NUMBER,N35 NUMBER,N36 NUMBER,N37 NUMBER,N38 NUMBER,N39 NUMBER,N40 NUMBER,
N41 NUMBER,N42 NUMBER,N43 NUMBER,N44 NUMBER,N45 NUMBER,N46 NUMBER,N47 NUMBER,N48 NUMBER,
N49 NUMBER,N50 NUMBER,N51 NUMBER,N52 NUMBER,N53 NUMBER,N54 NUMBER,N55 NUMBER,N56 NUMBER,
N57 NUMBER,N58 NUMBER,N59 NUMBER,N60 NUMBER,N61 NUMBER,N62 NUMBER,N63 NUMBER,N64 NUMBER,
N65 NUMBER,N66 NUMBER,N67 NUMBER,N68 NUMBER,N69 NUMBER,N70 NUMBER,N71 NUMBER,N72 NUMBER,
N73 NUMBER,N74 NUMBER,N75 NUMBER,N76 NUMBER,N77 NUMBER,N78 NUMBER,N79 NUMBER,N80 NUMBER,
N81 NUMBER,N82 NUMBER,N83 NUMBER,N84 NUMBER,N85 NUMBER,N86 NUMBER,N87 NUMBER,N88 NUMBER,
N89 NUMBER,N90 NUMBER,N91 NUMBER,N92 NUMBER,N93 NUMBER,N94 NUMBER,N95 NUMBER,N96 NUMBER,
N97 NUMBER,N98 NUMBER,N99 NUMBER,N100 NUMBER,N101 NUMBER,N102 NUMBER,N103 NUMBER,N104 NUMBER,
N105 NUMBER,N106 NUMBER,N107 NUMBER,N108 NUMBER,N109 NUMBER,N110 NUMBER,N111 NUMBER,N112 NUMBER,
N113 NUMBER,N114 NUMBER,N115 NUMBER,N116 NUMBER,N117 NUMBER,N118 NUMBER,N119 NUMBER,
N120 NUMBER,N121 NUMBER,N122 NUMBER,N123 NUMBER,N124 NUMBER,N125 NUMBER,N126 NUMBER,N127 NUMBER,
N128 NUMBER,N129 NUMBER,N130 NUMBER,N131 NUMBER,N132 NUMBER,N133 NUMBER,N134 NUMBER,N135 NUMBER,
N136 NUMBER,N137 NUMBER,N138 NUMBER,N139 NUMBER,N140 NUMBER,N141 NUMBER,N142 NUMBER,N143 NUMBER,
N144 NUMBER,N145 NUMBER,N146 NUMBER,N147 NUMBER,N148 NUMBER,N149 NUMBER,N150 NUMBER,N151 NUMBER,
N152 NUMBER,N153 NUMBER,N154 NUMBER,N155 NUMBER,N156 NUMBER,N157 NUMBER,N158 NUMBER,N159 NUMBER,
N160 NUMBER,N161 NUMBER,N162 NUMBER,N163 NUMBER,N164 NUMBER,N165 NUMBER,N166 NUMBER,N167 NUMBER,
N168 NUMBER,N169 NUMBER,N170 NUMBER,N171 NUMBER,N172 NUMBER,N173 NUMBER,N174 NUMBER,N175 NUMBER,
N176 NUMBER,N177 NUMBER,N178 NUMBER,N179 NUMBER,N180 NUMBER,N181 NUMBER,N182 NUMBER,N183 NUMBER,
N184 NUMBER,N185 NUMBER,N186 NUMBER,N187 NUMBER,N188 NUMBER,N189 NUMBER,N190 NUMBER,N191 NUMBER,
N192 NUMBER,N193 NUMBER,N194 NUMBER,N195 NUMBER,N196 NUMBER,N197 NUMBER,N198 NUMBER,N199 NUMBER,
N200 NUMBER,N201 NUMBER,N202 NUMBER,N203 NUMBER,N204 NUMBER,N205 NUMBER,N206 NUMBER,N207 NUMBER,
N208 NUMBER,N209 NUMBER,N210 NUMBER,N211 NUMBER,N212 NUMBER,N213 NUMBER,N214 NUMBER,N215 NUMBER,
N216 NUMBER,N217 NUMBER,N218 NUMBER,N219 NUMBER,N220 NUMBER,N221 NUMBER,N222 NUMBER,N223 NUMBER,
N224 NUMBER,N225 NUMBER,N226 NUMBER,N227 NUMBER,N228 NUMBER,N229 NUMBER,N230 NUMBER,N231 NUMBER,
N232 NUMBER,N233 NUMBER,N234 NUMBER,N235 NUMBER,N236 NUMBER,N237 NUMBER,N238 NUMBER,N239 NUMBER,
N240 NUMBER,N241 NUMBER,N242 NUMBER,N243 NUMBER,N244 NUMBER,N245 NUMBER,N246 NUMBER,N247 NUMBER,
N248 NUMBER,N249 NUMBER,N250 NUMBER,N251 NUMBER,N252 NUMBER,N253 NUMBER,N254 NUMBER,N255 NUMBER
35 );
表已创建。
SQL> insert into test select
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,
37,38,39,40,41,42,43,44,45,46,47,48,49,50,
51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,
84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,
101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125
,126,127,128,129,130,131,132,133,134,135,136,137,138,
139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163
,164,165,166,167,168,169,170,171,172,173,174,175,176,
177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201
,202,203,204,205,206,207,208,209,210,211,212,213,214,
215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239
,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255
from dual connect by level<=1E4;
已创建10000行。
SQL> commit;
提交完成。
SQL> exec dbms_stats.gather_table_stats(user,'TEST',cascade=>true);
PL/SQL 过程已成功完成。
SQL> set timing on
SQL> declare
j number;
begin
for i in 1..1000 loop
select count(n1) into j from test;
end loop;
end;
/
PL/SQL 过程已成功完成。
已用时间: 00: 00: 03.53
PL/SQL 过程已成功完成。
已用时间: 00: 00: 03.43
j number;
begin
for i in 1..1000 loop
select count(n255) into j from test;
end loop;
end;
/
PL/SQL 过程已成功完成。
已用时间: 00: 00: 09.78
SQL> /
PL/SQL 过程已成功完成。
已用时间: 00: 00: 09.51