managetable.docx

ObjectivesStoring User DataRegular tablesPartitioned tablesIndex-organized tablesClustered tables普通表分区表 : 如果一个表太大了，那么就会在这个大表的基础上，在分为子表，叫做 partition. 每个 row 是存储在一起的，比如有1000行数据，可能前 500 rows 存储在一个 partition 里边，后 500 rows 存储在一个 partition里。每个分区是一个 segment, 并且可以存储在不同的tablespace中。Index-organized tables : 这个“索引表” 不同于以往意义的索引，它是将存储的数据本身按照某种顺序进行了存储，所以查询效率很高。with a primary key index on one or more of its columns. an index-organized table maintains a single B- tree containing the primary key of the table and other column values.集群 : Clustered tables , 很多表是有相关性的，比如 Join 多个表，那么，可以将多个表存储为集群，这样在查询时会提高效率。Clusters have a cluster key, which is used to identify the rows that need to be stored together.cluster key can consist of one or more columns.Tables in a cluster have columns that correspond to the cluster key.The cluster key is independent of the primary key.Data Type99% 用的是 built-in 类型, 99% 使用 Scalar数字型，文本型，日期型，二进制( LOB )尽量用 varchar2 , 不要使用 char,? 除非是固定格式，比如性别，只有男，女如果 oracle 内部表的列数超过 254列，oracle 会将一行分成几块来存储。会加重 I/O 负荷。索引是依附于表的。对于任何一张表都有 ROWID，隐含的。select ROWID, ID, NAME from employee;ROWID 的内容, 可以看到 ROWID 可以快速定位一条记录的位置。Table Structure90% 一行放在一个 database block 中。( 一个 database block 中还可以放行 )列存储的顺序通常就是你定义的列的顺序，并且如果某列的值是 NULL, 那么是不会存储该列的。Row data is stored in database blocks as variable-length records.?columns for a row are generally stored in the order in which they are defined and any trailing NULL are not stored. Note: A single byte for column length is required for non trailing NULL columns.Each row in a table may have a different number of columns. Each row in a table has :row header : 这行有多少列，还有链接信息，还有锁的情况。row lockrow data : 存储列的长度，还有列的值。列的值是紧挨着列的长度信息。 for each column, the oracle server stores the column length and value ( one byte is needed to store the column length if the column cannot exceed 250 bytes. A column that can be longer needs three length bytes. The column value is stored immediately following the column length bytes.)CREATE TABLE该命令比较复杂。类似准备创建数据库一样，有个样本比较好。创建 table