第四章:索引
51Testing软件测试网ie%OH us+~bg 1.creating function-based indexes51Testing软件测试网$M{.c"n)a3e|
51Testing软件测试网!Qd!K0B)?x"w sql> create index summit.item_quantity on summit.item(quantity-quantity_shipped);
"}(F3`#]!ui l1s0
51Testing软件测试网fWhM*J
j!l HXX*r 2.create a B-tree index
$YxS)q%S^%}S9tY0
a:^E9Ls!fm0 sql> create [unique] index index_name on table_name(column,.. asc/desc) tablespace
$T[Wn+z8ljX7g0
51Testing软件测试网8Q8\3?J*R
\ sql> tablespace_name [pctfree integer] [initrans integer] [maxtrans integer]
,K(K~K)gg1Dgd(b0
G$VhY%VM0 sql> [logging | nologging] [nosort] storage(initial 200k next 200k pctincrease 0
&vGh5Sn:OP,E0
51Testing软件测试网%G4y?Z@9O!};? sql> maxextents 50);
$Gr|Oh0
q0X1sy5~e\4Q1r Dq Y0 3.pctfree(index)=(maximum number of rows-initial number of rows)*100/maximum number of rows
gA{R0Q;Cl
@0
51Testing软件测试网zB|8T)qCf`J 4.creating reverse key indexes
?BVX$asP_0
51Testing软件测试网!DD9U%L%i6ba)a
Qk/N/Q.h sql> create unique index xay_id on xay(a) reverse pctfree 30 storage(initial 200k51Testing软件测试网;wI2g
l/O`y u#k
51Testing软件测试网v.YZWpK2t)_s sql> next 200k pctincrease 0 maxextents 50) tablespace indx;51Testing软件测试网'X1|
G
y1a0g-G
51Testing软件测试网2U'T9A6Ew4K 5.create bitmap index51Testing软件测试网
_wy/djCozc
ze%IFw)ym H0 sql> create bitmap index xay_id on xay(a) pctfree 30 storage( initial 200k next 200k51Testing软件测试网BD%gfH&j/ZM
i
fON3M"j:P}6|0 sql> pctincrease 0 maxextents 50) tablespace indx;51Testing软件测试网p%gEI#BJ}d
51Testing软件测试网,MKT&g&Xa_o 6.change storage parameter of index
#z2^xQ2~zG0_ @0
CL9Iuh,\;pf(w0 sql> alter index xay_id storage (next 400k maxextents 100);51Testing软件测试网3]~U,g,V;B{~T
Am r8l
p:M9iY0 7.allocating index space51Testing软件测试网6`Vp,y/g4M F8s~m
51Testing软件测试网)[3{!A@(B(a%EKe sql> alter index xay_id allocate extent(size 200k datafile 'c:/oracle/index.dbf');51Testing软件测试网`%g3yTp8`(w?
G8R#z2mx%o IW Y0 8.alter index xay_id deallocate unused;
8`F9v.x5x Y w3M~0