Oracle10g系统管理14

61.设计表
规划表的类型
规划数据块空间使用
规划表的位置
使用并行方式建表
使用NOLOGGING选项建表
使用子查询(as select)建表时，通过指定nologging选项，可以最小化建表所生成的重做信息，从而提高建表性能。使用SQL*Loader或INSERT进行直接装载时，使用NOLOGGGING也可以加快数据装载速度。
使用COMPRESS选项建表
预计和规划表尺寸

 

行格式
建立普通表时，Oracle会为表分配相应的表段，并且表的所有数据都会存放到相应的表段中。在Oracle数据库中，行数据存储在数据块中，并且行长度可变。一般情况下，行数据是按照列定义顺序存放的。如果使用了LONG或LONG RAW类型，那么它们的数据总是放在行的尾部。
如果使用了LOB类型(CLOB和BLOB)并且该列的数据长度超过4000个字节。那么该列的数据会存放到相应的LOB段中
行头：包含列个数、行链、行锁等信息
列长度：如果列值小于250字节，则占用1个字节，否则占用3个字节
列值：列的实际数据，NULL不会占用空间

 

常用Oracle数据类型
CHAR(N)或CHAR(N BYTE)
CHAR(N CHAR)
VARCHAR2(N)或VARCHAR2(N BYTE)
VARCHAR2(N CHAR)
NUMBER(P,S)
DATE
TIMESTAMP
RAW(N)
LONG,LONG RAW,LOB

 

rowid
数据对象号6位-相对文件号3位-数据块号6位-行号3位
OOOOOO-FFF-BBBBBB-RRR

查询ROWID
ROWID是表的伪列，与其他列一样可以直接查询，因为其数据以掩码格式存放，用包DBMS_ROWID进行转换
select dname,rowid,dbms_rowid.rowid_row_number(rowid) row# from dept where deptno=10;

事务临时表是指数据只在当前事务内有效的临时表
create global temporary table temp1(cola int)
on commit delete rows;
insert into temp1 values(1);
commit;
select * from temp1;无数据
事务临时表temp1的数据只在当前事务内可以查看：当使用commit或rollback结束事务后，其临时数据会被自动清除

会话临时表是数据只在当前会话内有效的临时表
create global temporary table temp2(cola int)
on commit preserve rows;
insert into temp2 values(1);
commit;
select * from temp2;有数据
使用会话临时表时，如果使用commit提交事务，那么其数据仍然可以查询，但关闭会话后，Oracle会自动清除其临时数据

修改表的物理属性，使用alter table改变块空间使用参数pctfree和pctused时，新设置对所有块都起作用，但对于已分配块不会立即生效，
而使用alter table改变事务入口initrans时，该设置只会对将来分配的数据块生效。

重新组织表
alter table department move tablespace user02;
执行alter table命令重新组织表时，因为rowid会发生改变，从而导致表的所有索引转变为无效状态，所以在重新组织表之后必须重新建立索引

使用UNUSED选项删除列
alter table emp set unused column comm;
alter table emp drop unused columns checkpoint 1000;

 

62.管理索引表
索引表以B-树结构来组织表的数据，它是主键B-树索引的变种。对于普通表而言，其数据以无序方式存储。而对于索引表来说，其数据以B-树结构来组织，并且其页块既包含键列数据，也包含非键列数据。一般情况下，表及其索引数据分别存放在表段和索引段中。当在where子句中引用索引列时，首先定位索引数据并取得ROWID，然后根据ROWID取得表的数据。如果要经常基于主键列检索表数据，那么Oracle建议使用索引表。建立索引表时，Oracle会将表及其主键索引的数据一起存放到索引段中。当在where子句中引用主键列时，Oracle可以直接根据主键索引值取得表行数据。对于普通表来说，表、索引数据分别存放到表段、索引段，要占用更多空间。而对于索引表来书，键列和非键列的数据都被存放到主键索引段中。当经常使用主键列定位表数据时，应该建立索引表。使用索引表，一方面降低了磁盘和内存空间占用，另一方面也可以提高访问性能。

建立索引表
create table sales_info(
   id number(6) constraint pk_sale primary key,
   customer_name varchar2(30),
   sales_amount number(10,2),
   sales_date date,
   remark varchar2(2000)
)organization index tablespace user01
pctthreshold 20 including remark
overflow tablespace user02;

 

63.管理外部表
外部表是表结构被存放在数据字典，而表空间被存放在OS文件中的表。通过使用外部表，不仅可以在数据库中查询OS文件的数据，还可以使用INSERT方式将OS文件数据装载到数据库中，从而实现SQL*Loader所提供的功能。建立外部表后，可以查询外部表的数据，在外部表上执行连接查询，或对外部表的数据进行排序。在外部表上不能执行DML修改也不能在外部表上建立索引。

建立目录对象，并授予用户权限
create directory ext as 'd:\ext';
grant read,write on directory ext to scott;

建立外部表
create table ext_emp(
   id number(4),
   f_name varchar2(20),
   l_name varchar2(25),
   job varchar2(10),
   mgr number(4),
   hiredate date,
   sal number(8,2),
   comm number(2,2),
   dept_id number(4),
   email varchar2(25)
)organization external(
   type oracle_loader default directory ext
   access parameters(
      records delimited by newline fields terminated by ','
      missing field values are null(
         id,f_name,l_name,job,mgr,hiredate char date_format date mask "dd-mm-yyyy",sal,comm,dept_id,email
      )
   )location('emp.dat')
);

修改外部表
修改默认directory对象
alter table ext_emp default directory ext_new;
修改文件位置
alter table ext_emp location ('emp_1.dat');
修改访问参数
alter table ext_emp access parameters(fields terminated by ';');

显示特定用户的表
select table_name,num_rows,pct_free,blocks,chain_cnt from dba_tables where owner='SCOTT';

显示行所在实际位置
select deptno,dname,
       dbms_rowid.rowid_relative_fno(rowid) file#,
       dbms_rowid.rowid_block_number(rowid) block#,
       dbms_rowid.rowid_row_number(rowid) row# from dept;