Oracle：7、其他模式对象

目录

一、表分区

1.1范围分区

1.2散列分区

1.3列表分区

1.4组合范围散列分区

1.5组合范围列表分区

1.6修改分区表

二、分区索引和全局索引

2.1、本地分区索引

2.2、全局分区索引

2.3、全局非分区索引

三、外部表

四、临时表

五、簇与簇表

5.1索引簇

5.2散列簇

六、视图

七、序列

八、同义词

---

一、表分区

对表进行分区后，每一个分区都具有相同的逻辑属性（字段名，数据类型，约束等。）但是各个分区的物理属性可以不同（不同的存储参数或者表空间）。

1.1范围分区

范围分区根据字段的取值范围进行分区，将数据存储在不同的数据段中。一般，这个字段值不同的范围中分布比较均匀可以考虑。如：日期。

create table table_name(
column1 column_type,
...
)partition by range(column2)(
 partition part_01 values less than(100) tablespace space01,
 partition part_02 values less than(200) tablespace space02,
 partition part_03 values less than(300) tablespace space03,
 partition part_04 values less than(maxvalue) tablespace space04
);

• partition by range(column):用于指定范围分区方法以及分区列。
• partition:用于指定每个分区的名称，不指定则oracle自动命名。
• values less than:用于指定分布到该范围的数据。

select * 
from table_name 
partition(part_02);
--直接查询对应的分区

-- 直接查询对应的分区

select segment_name,partition_name,tablespace_name 
from user_segments 
where segment_name='table_name';

通过字典视图user_segment查看分区段及其所在表空间。

注：oracle认为null值大于一切非null值。如果分区字段的值为null，则必须使用maxvalue关键字指定上限。

1.2散列分区

    散列分区跟范围分区类似，只是它使用函数返回值作为范围，以平衡各分区记录的分布。

create table table_name(
column1 column_type,
...
)partition by hash(column1)
partitions 4
store in(space01,space02,space03,space04);

• partition by hash:子句说明对表进行散列分区。
• partitions:指定分区数目
• store in:指定一个数目与分区数目相同的列表。

如果写明分区的名称和存储的表空间就不必使用store in。

create table table_name(
column1 column_type,
...
)partition by hash(column1,column2)
(
 partition part_01 tablespace space01,
 partition part_02 tablespace space02,
 partition part_03 tablespace space03,
 partition part_04 tablespace space04
);

散列分区表的查询与普通表没有任何区别，oracle会在分区表上执行全表扫描，如果这where子句中引用分区列，则oracle会自动根据内置散列函数确定记录所在的散列分区。

1.3列表分区

    如果分区字段值不能划分范围（非数字，日期），并且分区字段的取值范围只是包含少数值的集合（枚举），则可以对表进行列表分区。

create table table_name(
column1 column_type,
...
)
partition by list(column2)(
 partition part_01 values ('a','b'),
 partition part_02 values ('z','y')
);

• partition by list:说明对标进行列表分区。
• partition:指定分区名以及他所包含的范围。
   

1.4组合范围散列分区

    顾名思义，即结合范围分区与散列分区进行的分区操作，即二级分区。将数据进行范围分区后，再在各分区内进行散列分区。

create table table_name(
column1 column_type,
...
)
partition by range(column1)
subpartition by hash(column2)
subpartition 3(
 partition part_01 values less than(100)
 ( subpartition p_1_1 tablespace space01,
   subpartition p_1_2 tablespace space02,
   subpartition p_1_3 tablespace space03),
 partition part_02 values less than(200)
 ( subpartition p_2_1 tablespace space01,
   subpartition p_2_2 tablespace space02,
   subpartition p_2_3 tablespace space03),
 partition part_03 values less than(300)
 ( subpartition p_3_1 tablespace space01,
   subpartition p_3_2 tablespace space02,
   subpartition p_3_3 tablespace space03),
 ...
);

• partition by range:根据column1列进行范围分区。
• subpartition by hash:根据column2列进行散列分区。

总共分区数目是：范围分区数*散列分区数

1.5组合范围列表分区

    顾名思义，即结合范围分区与列表分区进行的分区操作，即二级分区。将数据进行范围分区后，再在各分区内进行列表分区。

create table table_name(
 column1 column_type,
 ...
)
 partition by range(column1)
 subpartition byy list(column2)(
  partition part_01 values less than(100)
  ( subpartition p_1_1 values('a','b') tablespace space01,
    subpartition p_1_2 values('c','d') tablespace space02)
  )
  partition part_02 values less than(200)
  ( subpartition p_2_1 values('a','b') tablespace space01,
    subpartition p_2_2 values('c','d') tablespace space02)
  )
  ...
);

• partition by range:根据column1列进行范围分区。
• subpartition by list:根据column2列进行列表分区。

总共分区数目是：范围分区数*列表分区数

1.6修改分区表

alter table table_name 
add partition partition_name values less than(xxx);  

--范围分区增加分区。

alter table table_name 
split partition part_03 at (xxx) 
into (partition part_03_01,partition part_03_02);

--范围分区拆分分区（在范围内增加分区），由part_03拆分为part_03_01和part_03_02。 xxx应当是part_03范围之内的值。

alter table table_name 
add partition partition_name;

--散列分区增加分区。

alter table table_name 
add partition partition_name values('xxx','yyy');

--列表分区增加分区。

alter table table_name 
add partition partition_name values less than(xxx);

--组合范围散列分区增加主分区（不指定子分区个数则使用表级默认子分区个数）

alter table table_name 
modify partition partition_name add subpartition;

--组合范围散列分区增加子分区。

alter table table_name 
add partition partition_name values less than (xxx)(
 subpartition p_5_1 values ('aaa','bbb') tablespace space01,
 subpartition p_5_2 values ('ccc','ddd') tablespace space02
);

--组合范围列表分区增加主分区，并指定子分区。

alter table table_name 
modify partition partition_name 
add subpartition p_5_3 values ('aaa','bbb');

--组合范围列表分区增加子分区。

alter table table_name 
drop partition partition_name;
--范围分区，删除分区。

--范围分区，删除分区。

alter talbe table_name 
drop subpartition subpartition_name;
--组合范围列表分区，删除子分区。

--组合范围列表分区，删除子分区。

alter talbe table_name 
coalesce partition; 
--散列分区或组合范围散列分区，删除分区（其实算合并分区）。

--散列分区或组合范围散列分区，删除分区（其实算合并分区）。

alter table table_name 
modify partition partition_name 
coalesce subpartition;
--组合范围散列分区，删除子分区（其实算合并分区）。

--组合范围散列分区，删除子分区（其实算合并分区）。

select segment_name,partition_name,tablespace_name 
from user_segments 
where segment_name = 'table_name';
--查看表的分区情况

--查看表的分区情况

create table table_name 
as select * from table_name_fq;

--将分区表数据交换到普通表。

alter table table_name 
rename partition partition_name_old 
to partition_name_new;
--修改分区表名称。

--修改分区表名称。

alter table table_name 
merge partitions partition_1,partition_2 
into partition partition_3;
--合并分区。

--合并分区。

alter table table_name 
move partition partition_name 
tablespace tablespace_name;
--重组分区。将特定分区中的数据迁移到其他表空间。

--重组分区。将特定分区中的数据迁移到其他表空间。
 

二、分区索引和全局索引

索引与表一样都是需要存储的，对索引分区的目的与对表分区的目的是一样的。分区索引则索引数据存放到几个索引分区段中。

2.1、本地分区索引

本地分区索引即对应每个分区表建立分区索引，实行一对一的对应关系。

create index index_name 
on table_name(column1) local;

2.2、全局分区索引

全局分区索引，即索引对表的所有分区建立统一索引，然后再对这个统一的索引进行分区。从而得到的是每一个分区索引对应全体的分区表。

create index index_name on table_name(column1)
global partition by range(column1)
(
 partition part1 values less than(xxx) tablespace space1,
 partition part2 values less than(maxvalue) tablespace space2
)

如代码所示，全局分区索引其实是将索引也按照一定的规则进行分区。若这个分区规则跟表分区一致，那么他在逻辑上应该是等同与本地分区索引的。

2.3、全局非分区索引

其实就是普通的索引，不过这个索引是建立是分区表上的，而不是普通表。

create index index_name on table_name(column1);

 

三、外部表

    外部表指的是其数据内容是OS文件，仅仅将表结构存储在oracle中，oracle按照“表”声明的规则解析外部OS文件，将其数据对应到表结构中，从而由sql进行查看，排序等（不能进行DML操作）。

create table table_name
(column1 column_type,
column2 column_type,
...
)
organization external
(type oracle_loader
default directory exterior_data
access parameters(
records delimited by new line
[badfile exterior_data:'xxx.text']
[logfile exterior_data:'xxx.log']
fields terminated by ','
)
location('filename.ext')
)[reject limit unlimited];

type:指定访问外部表数据时用的驱动程序。
exterior_data:这个是需要事先进行声明的路径（该路径不包含文件名），例：

create directory exterior_data as 'd:\aaa\bbb';--创建路径。
grant read,write on directory exterior_data to 用户名;--给指定用户授权访问这个路径的权限。

access parameters:驱动程序访问数据文件时进行转换的参数设置。records delimited by指定一行数据的分隔符，fields terminated by指定字段间的分隔符。
reject limit:用于指定获取外部数据文件时，允许出现的错误数量。unlimited允许所有错误，默认为0。
badfile:用于指定获取外部数据文件时，出现错误的记录保存于其指定的目录中。对应还有nobadfile，默认情况是badfile，生成与该文件名相同的.bad文件。
logfile:用于指定记录错误信息的日志文件。与badfile不同，此处记录的是读取文件时，相关的错误。对应还有nologfile，默认情况是logfile生成与该文件名相同的.log文件。

 

alter table table_name 
default direct ext_new;
--指定新的外部表路径。

--指定新的外部表路径。

alter table table_name 
location('ccc.ext');
--指定新的外部表名称。

--指定新的外部表名称。
 

alter table table_name 
access parameters(fields terminated by 'x');
--指定新的分隔符。

--指定新的分隔符。

 

四、临时表

oracle中的临时表是“静态”的，用户不需要在每次使用的时候重新建立，其结构在创建后被作为模式对象存储在数据字典中，由此避免每次需要临时表时的重新创建。临时表这创建时不分配存储空间，而是在插入数据时才分配存储空间，因为临时表是基于事务或者会话的，当事务或者会话结束，那么临时表的数据全部消失，其存储也会被收回。

create global temporary table table_name(
column column_type,
...
)
on commit [delete|preserve] rows;

• on commit delete rows:指定临时表是基于事务的。当事务提交后，临时表记录自动删除。
• on commit preserve rows:指定临时表是基于会话的。当用户断开与服务器的连接，临时表记录将删除。

 

五、簇与簇表

簇的概念其实就是将多个表耦合在一起，从而提高访问速度，降低特定sql的I/O次数。它通过2张或多张表的共同的列进行关联，从而组合到一起（从而将2张表存放到一个段中，否则在2个段中。）

• 选择簇主要考虑优化select，而不是insert和update。
• 创建簇-创建簇表-创建簇索引

5.1索引簇

创建簇：

create cluster cluster_name(column column_type)
pctfree 20 pctused 60
size 500 tablespace space01;

• column:定义簇键列。
• pctfree:数据块预留百分比。
• pctused:数据库可用百分比（低于该百分比则可以执行insert操作）。 此处若定义的pctfree和pctused会覆盖簇表的表级定义。
• size:指定每个簇键值相关行数据所占用的总计空间（单位：字节）。

创建簇表：

create table table_name(
column1 column_type
...
)cluster cluster_name(column1);

此处定义column1为簇键列，如此建立2个或多个簇表后，就可以将他们耦合到一起。

创建簇索引：

create index index_name 
on cluster cluster_name 
tablespace tablespace_name;
--不需要指定列明，oracle自动根据簇键建立索引。

--不需要指定列明，oracle自动根据簇键建立索引。
 

alter cluster cluster_name pctfree xx pctused xxx;
alter cluster cluster_name size 1024; 
--修改size

--修改size

注：删除簇时，需要先删除簇表。
 

5.2散列簇

散列簇使用散列函数定位行数据。散列簇在等值查询中比较有优势（范围查询不应该考虑散列簇，另散列簇适合静态表，若数据变化频繁则不考虑使用散列簇）

create cluster cluster_name(column column_type)
size 500 hashkeys 1000 hash is mod(column,100)
tablespace space01;

hash is 指定散列函数。
略
P248

 

六、视图

视图是一个虚拟表，它由存储的查询构成，对于他的输出可以看作一张表。

create [or replace] view <viiew_name> [(alias[,alias]...)]
as <subquery>
[with check option [constraint constraint_name]]
[with read only];

• alias:指定视图列的别名。
• subquery:指定视图对应的查询语句(select语句，它将从真实表中查询数据。)
• with check option:定义视图的check约束，即对视图的操作需要满足子查询的条件,即where子句。对视图的修改可以以视图查询得到。
• read only:指定只读。

注：当使用函数获取值时，必须指定别名。重新定义视图就可以对视图进行修改。
 

select text from user_views where view_name=upper('view_name');--查询视图对应的select语句。

--查询视图对应的select语句。

drop view view_name;

-- 删除视图

 

七、序列

序列是oracle提供的用于生成一系列唯一数字的数据库对象。一般作为主键值。

create sequence sequence_name
[start with n]
[increment by n]
[minvalue n | nominvalue]
[maxvalue n | nomaxvalue]
[cache n| nocache]
[cycle | nocycle]
[order | noorder];

修改序列

alter sequence sequence_name
...;

sequence的调用（下一个序列值）：

sequence_name.nextval 
-- 【下一个序列值】

   

八、同义词

    同义词的作用是隐藏对象的实际名称和所有者信息。
 

create [public] synonym synonym_name 
from user.table_name;
--创建同义词，public指定为公共同义词。

--创建同义词，public指定为公共同义词。
 

drop [public] synonym synonym_name;
 --删除同义词。

--删除同义词。