博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

Oracle 命令大汇总-索引与约束管理

Posted on 2007-06-26 17:41  徐正柱-  阅读(409)  评论(1编辑  收藏  举报
第四章:索引 
    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; 

    第五章:约束
    1.define constraints as immediate or deferred 
    sql> alter session set constraint[s] = immediate/deferred/default; 
    set constraint[s] constraint_name/all immediate/deferred; 
    2. sql> drop table table_name cascade constraints 
    sql> drop tablespace tablespace_name including contents cascade constraints 
    3. define constraints while create a table 
    sql> create table xay(id number(7) constraint xay_id primary key deferrable 
    sql> using index storage(initial 100k next 100k) tablespace indx); 
    primary key/unique/references table(column)/check 
    4.enable constraints 
    sql> alter table xay enable novalidate constraint xay_id; 
    5.enable constraints 
    sql> alter table xay enable validate constraint xay_id;