一.Managing tables
Creating a table:guidelines
.place tables in separate tablespaces
.use locally-managed tablespace to avoid fragmentation
.use few standard extent sizes for tbales to reduce tablespace fragmentation
建数据表
create table schema.table_name(column type)
pctfree n
pctused n
initrans n
maxtrans n
tablespace tablespace_name
说明:上面的n为参数,前两个表示百分比;后面两个是整数，表示并发数
eg.
create table ivan.t7(a varchar(10));
pctfree 10
pctused 80
initrans 5
maxtrans 100
tablespace example
改变数据表参数
alter table schema.table_name
pctfree n
pctused n
手工分配区
alter table schema.table_name
allocate extent (size integer [k | m ]
datafile 'filename')
eg.
alter table ivan.t7
allocate extent (size 100k
datafile 'd:\oracle9i\oradata\example.dbf');
说明：所分配区所在的数据文件必须属于同一个表空间;
将数据表从一个表空间移至另一个表空间
alter table schema.table_name
move tablespace tablespace_name
eg.
alter table ivan.t7
move tablespace tools
说明:操作后务必要重建索引;
删除数据表中的某一列
alter table schema.table_name
drop column column_name
cascade constrains checkpoint n
alter table schema.table_name --如果执行上面出现错误时使用
drop columns continue checkpoint n
---------针对数据量很大的表-----------------
alter table schema.table_name
set unused column column_name cascade constraints
alter table schema.table_name
drop unused columns checkpoint n
alter table schema.table_name --如果执行上面出现错误时使用
drop columns continue chkeckpoint n
-----------------------------------------
eg.
alter table ivan.t3
drop column t3_x
cascade constraints checkpoint 500
说明:1.checkpoint n是指每n行才生成一次undo信息;
2.若在删除过程中出现错误,则必须要执行下面的语句
alter table ivan.t3
drop columns continue;
二.Managing indexes
Creating indexes:guidelines
.balance query and DML needs
.place in separate tablespace
.use uniform extent size
.consider NOLOGGING for large indexes
.INITRANS should generally be higher on indexes than on the corresponding tables
Creating indexes
create [unique | bitmap ] index [schema].index
on [schema].table (column [asc | desc] )
[tablespace tablespace_name]
[pctfree integer]
[initrans integer]
[maxtrans integer]
[storage-clause]
[logging|nologging]
Allocating and deallocating index space
alter index [schema].index
{allocate extent (size integer [k|m]
datafile 'filename' )
|deallocate unused }
Rebuiding index
alter index [schema].index rebuild
[tablespace tablespace_name]
[pctfree integer]
[initrans integer]
[maxtrans integer]
[storage-clause] --->对应dictionary,不适合local management extent
[logging | nologging]
[reverse | noreverse]
Others
alter index [schema].index coalesce;
drop index [schema].index;
analyze index [schema].index validate stucture;---->index_stats
alter index [schema].index [monitoring | nomonitoring] usage; ----->v$object_usage
三.Managing constraints
Type constraints
.NOT NULL -->NN
.UNIQUE -->UN
.PRIMARY KEY -->PK
.FOREIGN KEY -->FK
.CHECK -->CK
Constraint states
.DISABLE NOVALIDATE
.DISABLE VALIDATE
.ENABLE NOVALIDATE
.ENABLE VALIDATE
Constraint(约束)创建有两种方式
.与表一起创建
.先创建表,后创建约束
column datatype [constraint constraint_clause]
{ [not] null
| unique [ using index index_clause]
| primary key [ using index index_clause]
| references [schema].table [(column)]
[on delete cascade]
| check ( condition )
}
constraint_clause:==[ not deferrable | deferrable [ initially |
{immediate | deferred }]]
[disable | enable [novalidate | validate ]]
[ constraint constraint_clause ]
{ primary key (column [, column ] ...)
[ using index index_clause]
| unique (column[, column ] ...)
[ using index index_clause]
| foreign key (column[, column ] ...)
references [schema].table [ (column[, column ] ...) ]
[ on delete cascade ]
| check ( condition )
}
constraint_clause 同上
改变constraint的states
alter table [schema].table
[disable | enable [ novalidate | validate ]] constraint_name
