oracle

CREATE TABLE players

(

code NUMBER(10) PRIMARY KEY,

lastname VARCHAR(20),

firstname VARCHAR(15),

position  VARCHAR2(20),

team VARCHAR2(20)

)

PCTFREE 10

PCTUSED 40

STORAGE

(

INITIAL 25k

NEXT 10k

MAXEXTENTS 10

MINEXTENTS 3

);

分区：

把一个大表的数据分成若干分区(partition)，各分区可以独立操作。

可以改善性能，分区使用的范围值。

可以对索引分区：一个分区索引的分区中的值范围可以与索引表使用的范围匹配，这种情况索引叫做局部索引 localindex，

如果索引分区不能与表分区值范围相匹配，就叫做全局索引。

可以对分区创建子分区

可以使用分区在物理上把数据分隔开。

分区键：

为每行确定分区的列的集合

包含：1~16列的顺序列表 ； 不能包含LEVEL,ROWID,MLSLABEL假列或者列的类型为ROWID ; 能包含NULL值的列

分区表：

除包含LONG,LONGRAW数据类型的表以外，都能够进行分区。

分区索引组织表：

只支持Rang，Hash分区 ； 

分区列必须是主键列的子集 ； 

第二索引能够分区 -- 本地和全局； 

OVERFLOW数据段总是同比表分区一起。

Range分区

基于分区键值范围将数据映射到建立的分区上。常用日期

CREATE TABLE sales_range

(

salesman_idNUMBER(5),

salesman_nameVARCHAR2(30),

sales_amountNUMVER(10),

sales_date DATE

)

PARTITION BYRANGE(sales_date)

(

PARTITION sales_jan2000 VALUES  LESS  THAN (TO_DATE('02/01/2000','DD/MM/YYYY')),

....

)

List分区

....

PARTITION BY LIST(sales_state)

(

PARTITION sales_west VALUES IN('A','B')

....

)

Hash分区

CREATE TABLE sales_hash

(

salesman_id NUMBER(5),

)

PARTITION BYHASH(salesman_id)

PARTITIONS  4

STORE IN (data1,tata2,....)

合成分区（Range-Hash）

CREATE TABLE sales_composite

(

)

PARTITION  BY  RANGE(sales_date)

SUBPARTITION  BY  HASH(salesman_id)

SUBPARTITIONS 4

PARTITIONS sales_jan2000 VALUES  LESS  THAN(TO_DATE('02/01/2000','DD/MM/YYYY'))

(

SUBPARTITION  sales_jan2000_1  TABLESPACE  data1,

...

)

...

 -

表大于2G，需要考虑分区。

考虑执行DML必须分区。

包含历史数据，需要添加新数据到最新分区

*********************************************** 分区索引：

p147

---------------------------------------------------------------------------

步骤：

设计表 -  定义如何使用数据块空间 - 定义每个表位置 - 考虑创建并行表 - 考虑使用NOLOGGING

 - 估计表大小，设置存储参数 - 规划大表

1.设计表

开发人员负责应用元素，包括表。

数据块管理员负责设置存储参数和为表定义集群。

表是标准化

列的数据类型合适

允许为空的列最后定义。

合适的时候表集群化，优化sql性能。

2.定义如何使用数据块空间

创建表时定义PCTFREE,PCTUSED参数，可以影响空间使用率和空间预留的商量，用于更新数据块的当前数据。

3.定义表的位置

CREATE TABLE 子句 TABLESPACE 标识表空间存储新表。

4.考虑并行创建表

子查询提供性能

5.NOLOGGING

CREATE TABLE ...  AS SELECT 使用 NOLOGGING子句。生成最少的重做日志信息

节约重做日志文件空间

减少时间

并行创建大表性能提高了

6.估计表大小，设置存储参数

确定数据库所需磁盘空间的总数。

使用单个表的估计值，更高的挂历磁盘空间

7.规划大表

UNLIMITED , MAXEXTENTS 建行对大对象的规划，减少磁盘浪费和碎片。提高使用率。

------------------------

列数据类型

DBA_TABLE , DBA_TAB_COLUMNS

数据类型：

CHAR

VARCHAR

VARCHAR2 2KB

LONG 变长字符数据2GB

NUMBER(1~38，-84~127) 数字数据

DATE 固定长度时间

RAW 二进制 255BYTE

LONG RAW 二进制2GB

RAWID 6BYTE

创建表：

CREATE  TABLE  emp(

empno  number(5) primary key,

ename varchar2(15) not null,

job varchar2(10),

mgr number(5),

hiredate date default(sysdate),

sal number(7,2)

deptno number(3) not null

constraint dept_fkey references dept

) 

PCTFREE 10

PCTUSED 40

TABLESPACE users

STORAGE (

INITIAL 50K

NEXT 50K

MAXEXTENTS 10

PCTINCREASE 25

);

查看表信息：

prefix：

DBA_

ALL_

USER_

TABLES

TAB_COLUMNS

ALL_TABLES

TAB_COMMENTS

COL_COMMENTS -

EXTERNAL_TABLES -外部表的数据源

TAB_HISTOGRAMS - 状态

TAB_COL_STATISTICS - 列视图中得到的统计信息

TAB_MODIFICATIONS - 被修改的表

UNUSED_COL_TABS - 没有使用列的表

PARTIAL_DROP_TABS -  部分完成DROP_COLUMN操作的表

外部表：

CONNECT / AS SYSDBA;

CREATE OR REPLACE DIRECTORY admin_dat_dir

AS '/flatfiles/data';

CREATE OR REPLACE DIRECTORY admin_log_dir

AS '/flatfile/log';

GRANT READ ON DIRECTORY admin_dat_dir TO hr;

GRANT WRITE ON TO hr;

CONNECT hr/hr

CREATE TABLE admin_ext_employees(

)

ORGANIZATION EXTERNAL

(

TYPE ORACLE_LOADER

)

**************************   P155

修改表：

ALTER / ALTER ANY TABLE  权限

修改存储位置：

ALTER TABLE emp MOVE STORAGE(

INITIAL 20K

NEXT 40K

MINEXTENTS 2

MAXEXTENTS 20

)

删除表：

DROP TABLE  emp;

DROP TABLE emp  CASCADE CONSTRANINTS;

索引：

RowID标识行，记录行的准确位置：所在文件，文件的块，块中行的地址

类型：

B-tree 索引：

B-tree集群索引

Hash集群索引：

全局和局部索引

逆序键索引

位图索引：小型数据

基于函数的索引：

域索引

建立索引：

CREATE  INDEX emp_ename ON emp(ename)

TABLESPACE users

STORAGE 

(

INITIAL 20K

NEXT 20K

PCTINCREASE 75

)

PCTFREE 0;

唯一索引：

CREATE UNIQUE INDEX dept_unique_index ON dept(dname)

TABLESPACE indx;

CREATE TABLE emp(

empno NUMBER(5) PRIMARY KEY, age INTEGER

)

ENABLE PRIMARY KEY USING INDEX

TABLESPACE users

PCTFREE 0;

显示创建索引时统计信息

CREATE INDEX emap_ename ON emp(ename)

COMPUTE STATISTICS;

查询索引：

P158

视图

创建视图：

CREATE  VIEW  sales_staff AS

SELECT .....  FROM  ...  WHERE 

外键关联：

CREATE  TABLE  dept(

deptno NUMBER(4) PRIMARY KEY,

dname .....

)

CREATE TABLE emp(

empno number(4) primary key,

....

deptno number(2),

FOREIGN KEY (DEPTNO)  REFERENCES DEPT(DEPTNO)

);

替换视图：

CREATE OR REPLACE VIEW

AS

...

同义词和序列

同义词(synonym)：

共有：PUBLIC

专用：制定用户的数据库中专用

创建SYNONYM:

CREATE  PUBLIC  SYNONYM public_emp FOR jward.emp

序列(sequence)： P163

提供唯一数值的顺序表