oracle里的常用命令（四）：索引

oracle里的常用命令

第四章：索引

 

1.creating function-based indexes

sql> create index summit.item_quantity on summit.item(quantity-quantity_shipped);

 

2.create a B-tree index

sql> create [unique] index index_name on table_name(column,.. asc/desc) tablespace

sql> tablespace_name [pctfree integer] [initrans integer] [maxtrans integer]

sql> [logging | nologging] [nosort] storage(initial 200k next 200k pctincrease 0

sql> maxextents 50);

 

3.pctfree(index)=(maximum number of rows-initial number of rows)*100/maximum number of rows

 

4.creating reverse key indexes

sql> create unique index xay_id on xay(a) reverse pctfree 30 storage(initial 200k

sql> next 200k pctincrease 0 maxextents 50) tablespace indx;

 

5.create bitmap index

sql> create bitmap index xay_id on xay(a) pctfree 30 storage( initial 200k next 200k

sql> pctincrease 0 maxextents 50) tablespace indx;

 

6.change storage parameter of index

sql> alter index xay_id storage (next 400k maxextents 100);

 

7.allocating index space

sql> alter index xay_id allocate extent(size 200k datafile 'c:/oracle/index.dbf');

 

8.alter index xay_id deallocate unused;