当前位置：文档之家 › Oracle 建立分区表

Oracle 建立分区表

  • 格式：doc
  • 大小：275.00 KB
  • 文档页数：6

Oracle 建立分区表

在Oracle 11g数据库中，根据对表或索引的分区方法可以创建五种类型的分区表：范围分区、散列分区、列表分区、组合范围散列分区和组合范围列表分区。每种分区表都有自己的特点，在创建分区表时，应当根据表应用情况选择合理的分区类型。

1．范围分区表

范围分区就是根据分区字段的取值范围进行分区，将数据存储在不同的分区段中。如果表的数据可以按照逻辑范围进行划分，并在不同范围内分布比较均衡，那么可以使用范围分区。例如，根据日期值进行分区，将不同日期的数据存储在不同的分区上。

如图10-2所示，假设有一个销售表SALES，该表的数据总量达到1000G，每个季度平均250G。如果使用普通表存储数据，那么1000G数据会存放到一个表段SALES中，那么在统计一季度销售数据时需要扫描1000G数据；如果使用分区表，则可以将一、二、三、四季度数据分别存放到不同分区段中，此时统计一季度销售数据时只需要扫描250G的数据。显而易见，使用范围分区可以大大降低I/O次数，从而提高了磁盘I/O性能。

普通表分区表一季度销售数据二季度销售数据三季度销售数据四季度销售数据

图10-2 分区表与普通表

例如，下面的示例建立一个范围分区表，将每个季度的销售数据部署到不同的表分区段。建立范围分区时，必须指定分区方法RANGE，分区列以及每个分区列值的具体范围。示例如下：

SQL> create table sales_range(

2 customer_id number(3),

3 sales_amount number(10,2),

4 sales_date date not null

5 )partition by range(sales_date)(

6 partition part_01 values less than(to_date('2008-04-01','yyyy-mm-dd')) tablespace space01,

7 partition part_02 values less than(to_date('2008-07-01','yyyy-mm-dd')) tablespace space02,

8 partition part_03 values less than(to_date('2008-10-01','yyyy-mm-dd')) tablespace space03,

9 partition part_o4 values less than(maxvalue) tablespace space04

10 );

表已创建。

如上所示，PARTITION BY RANGE(column)用于指定范围分区方法以及分区键列，

PARTITION用于指定每个分区的名称，如果不指定每个分区的名称，则Oracle会自动对分区命名。VALUES LESS THAN用于指定分布到该范围分区的数据范围，TABLESPACE用于指定分区段所在的表空间。执行下述语句后，不仅建立了分区表SALES，而且将分区段PART_01、PART_02、PART_03和PART_04分别部署到表空间SPACE01、SPACE02、SPACE03和SPACE04中。

在使用INSERT语句为范围分区表插入数据时，必须为分区列提供数据，并且分区列的数据必须符合相应的分区范围。当在分区表SALES上执行INSERT操作时，Oracle会根据SALES_DATE值的范围在相应分区中插入数据。示例如下：

SQL> alter session set nls_date_format='YYYY-MM-DD';

会话已更改。

SQL> insert into sales_range values(1,30000,'2008-02-25');

已创建 1 行。

SQL> insert into sales_range values(2,28500,'2008-05-17');

已创建 1 行。

SQL> insert into sales_range values(3,37000,'2008-11-02');

已创建 1 行。

SQL> insert into sales_range values(4,42500,'2008-08-15');

已创建 1 行。

执行以上INSERT操作后，第一条数据会被存放到PART_01分区，第二条数据会被存放到PART_02，第三条数据会被存放到PART_04分区，第四条数据会被存放在PART_03分区中。

建立分区表后，如果要查询该分区表的所有数据，与查询普通表没有任何区别，Oracle会在每个分区上执行全表扫描。示例如下：

SQL> set autotrace on explain

SQL> select * from sales_range;

建立分区表后，也可以通过在表名后指定分区名，以显示特定分区的所有数据，Oracle只会在相应的表分区上执行全表扫描。示例如下：

SQL> set autotrace on explain

SQL> select * from sales partition(part_02);

在建立分区表时，Oracle会为每个分区建立一个分区段，并且可以将不同分区部署到不同的表空间中。通过查询数据字典视图USER_SEGMENTS，可以显示分区段及其所在表空间。示例如下：

SQL> col tablespace_name format a15

SQL> col segment_name format a15

SQL> col partiton_name format a15

SQL> select segment_name,partition_name,tablespace_name

2 from user_segments

3 where segment_name='SALES_RANGE';

SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME

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

SALES_RANGE PART_01 SPACE01

SALES_RANGE PART_02 SPACE02

SALES_RANGE

PART_03 SPACE03

SALES_RANGE PART_O4 SPACE04

2．散列分区

散列分区是指按照Oracle提供的散列（HASH）函数计算列值数据，并最终按照函数结果分区大表数据。在进行范围分区时，有时用户无法对各个分区中可能具有的记录数目进行预测，这会导致某个分区中记录数目过多，而其他分区中记录很少的不平衡分区情况。在这种情况下，用户可以考虑创建散列分区表。

散列分区是通过HASH算法将数据均匀分布到各分区，通过在I/O设备上进行散列分区，从而使得这些分区大小一致。创建散列分区表时，必须反指定分区方法、分区列以及分区个数。

例如，下面是一个典型的散列分区的示例，该示例将销售数据表SALES_HASH中的数据依据CUSTOMER_ID进行散列分区：

SQL> create table sales_hash(

2 customer_id number(3),

3 sales_amount number(10,2),

4 sales_date date not null

5 )partition by hash(customer_id)

6 partitions 4

7 store in(space01,space02,space03,space04);

表已创建。

在上面的CREATE TABLE语句中，通过PARTITION BY HASH子句说明对表进行的是散列分区，然后使用PARTITIONS关键字指定分区的数目（不指定分区名称）。如果要将各个分区分散存储在不同的表空间中，可以使用STORE IN子句指定一个与分区数目相同的表空间列表，这时各个分区的名称由Oracle自动生成。

散列分区表的分区字段可以由多个字段组成。例如，使用下面的语句为销售记录创建另一个散列分区表SALES_HASH2，其中根据CUSTOMER_ID和SALES_DATE字段进行散列分区：

SQL> create table sales_hash2(

2 customer_id number(3),

3 sales_amount number(10,2),

4 sales_date date not null

5 )partition by hash(customer_id,sales_date)

6 (

7 partition part_01 tablespace space01,

8 partition part_02 tablespace space02,

注 意 Oracle认为NULL值大于一切非NULL值。因此，如果分区字段的值为NULL，则必须在某个分区中使用MAXVALUE关键字指定上限。

9 partition part_03 tablespace space03,

10 partition part_04 tablespace space04

11 );

表已创建。

在上面的语句，直接为各个分区指定了名称和所处表空间，因此不必再使用STOREIN子句。

在使用INSERT语句为散列分区表添加数据时，必须为分区列提供数据。当在散列分区表上执行INSERT操作时，Oracle会在分区列上使用内置散列函数进行运算，然后根据运算结果均匀地分布到不同分区段上。

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

3．列表分区

如果分区字段的值不能划分范围（非数字或日期数据类型），并且分区字段的取值范围只是一个包含少数值的集合，则可以对表进行列表分区。在进行列表分区时，需要为每个分区指定一个取值列表，分区字段的取值处于同一个取值列表中的行被存储在同一个分区中。

列表分区适用于那些分区字段是一些无序的或者无关的取值集合的表，在分区时分区字段只能是一个单独的字段。下面是一个典型的列表分区的示例：为销售记录表创建一个列表分表SALES_LIST，其中根据销售地点SALES_STATE字段进行列表分区：

合集下载

oracle分区表查询语句

oracle分区表查询语句

oracle分区表查询语句：

Oracle的分区表可以通过使用SELECT语句来进行查询。

查询所有数据：

SELECT * FROM table_name;

根据条件查询特定数据：

SELECT * FROM table_name WHERE condition;

其中，condition为需要指定的查询条件。

按照分区键值查询特定分区的数据：

SELECT * FROM table_name PARTITION (partition_key = value);

其中，table_name为表名，partition_key为分区键列名，value为分区键对应的值。

查询多个分区的数据：

SELECT * FROM table_name PARTITION (partition_key IN ('value1',

'value2'));

这里可以在IN子句中指定多个分区键的值。

查询不同分区之间的交集或并集：

SELECT * FROM table_name PARTITION (partition_key) INTERSECT

SELECT*FROM another_table_name PARTITION (another_partition_key);

-- 或者

SELECT * FROM table_name PARTITION (partition_key) UNION ALL

SELECT * FROM

Oracle 分区索引和全局索引

Oracle 分区索引和全局索引

Oracle 分区索引和全局索引

对于分区表而言，每个表分区对应一个分区段。当在分区表上建立索引时，即可以建立全局索引，也可以建立分区索引。对于合局索引，其索引数据会存放在一个索引段中；而对于分区索引，则索引数据都会被存放到几个索引分区段中。对索引进行分区的目的与对表进行分区是一样的，都是为了更加易于管理和维护巨型对象。

在Oracle中，一共可以为分区表建立三种类型的索引，下面分别介绍它们的特点和适用情况。

1．本地分区索引

本地分区索引是为分区表中的各个分区单独地建立分区，各个索引分区之间是相互独立的。本地分区索引相对比较简单，也比较容易管理。图10-4显示了本地分区索引和分区表之间的对应关系：

分区索引分区表

图10-4 本地分区索引与分区表

在为分区表创建本地索引后，Oracle会自动对表的分区和索引的分区进行同步处理。如果为分区表添加新的分区后，Oracle会自动为新分区建立新的索引。与此相反，如果表的分区依然存在，则用户将不能删除它所对应用的索引分区。在删除表的分区时，系统会自动删除所对应的索引分区。

例如，下面的语句为范围分区表SALES_RANGE创建本地分区索引：

SQL> create index sales_local_idx

2 on sales_range(customer_id) local;

索引已创建。

2．全局分区索引

全局分区索引是对整个分区表建立的索引，然后再由Oracle对索引进行分区。全局分区索引的各个分区之间不是相互独立的，索引分区与分区表之间也不是简单的一对一关系。图10-5显示了全局分区索引与分区表的对应关系。

分区索引分区表

图10-5 全局分区索引与分区表

例如，下面的语句为分区表SALES_LIST创建全局分区索引：

SQL> create index sales_global_part_idx

2 on sales_list(customer_id)

oracle分区的interval字符

oracle分区的interval字符

oracle分区的interval字符

在Oracle数据库中，分区是一种将表数据分成逻辑部分的方法，可以提高查询性能和管理数据的灵活性。Oracle 11g引入了分区表的自动管理特性，其中一个重要的概念是分区的间隔(interval)字符。

分区的间隔字符是指在分区表中定义分区的方式，它决定了分区之间的边界。使用间隔字符可以自动创建新的分区，并将新的数据插入到正确的分区中。这样一来，用户不需要手动创建每个分区和插入数据，大大简化了分区表的管理。

在Oracle中，可以使用不同的间隔字符来定义分区。其中一种常见的间隔字符是基于日期的间隔字符，比如按照月份进行分区。这种情况下，可以使用以下语句来创建一个按照月份分区的表：

CREATE TABLE sales (

sales_id NUMBER,

sales_date DATE,

sales_amount NUMBER

)

PARTITION BY RANGE (sales_date)

INTERVAL (INTERVAL '1' MONTH)

(

PARTITION sales_q1 VALUES LESS THAN (TO_DATE('01-APR-2022', 'DD-MON-YYYY'))

); 在上述例子中，sales表按照sales_date列的日期范围进行分区。INTERVAL '1'

MONTH表示每个分区的间隔为一个月。系统将自动创建新的分区，并将新的数据插入到正确的分区中。

另一个例子是使用数字的间隔字符。假设需要根据订单金额将表进行分区，可以使用以下语句创建一个按照金额范围分区的表：

CREATE TABLE orders (

order_id NUMBER,

order_date DATE,

order_amount NUMBER

)

PARTITION BY RANGE (order_amount)

oracle建立表空间的方法

oracle建立表空间的方法

oracle建立表空间的方法

Oracle数据库中建立表空间的方法主要有两种：一种是通过SQL命令的方式，另一种是通过Oracle客户端Enterprise Manager Console来创建。

通过SQL命令的方式创建表空间，需要先创建临时表空间和数据表空间，然后创建用户并指定表空间。具体步骤如下：

1. 创建临时表空间。可以通过以下命令创建：

```sql

create temporary tablespace user_temp

tempfile 'D：\oracle\oradata\Oracle9i\user_'

size 50m autoextend on next 50m

maxsize 20480m

extent management local;

```

2. 创建数据表空间。可以通过以下命令创建：

```sql

create tablespace user_data

logging

datafile 'D：\oracle\oradata\Oracle9i\user_'

size 50m autoextend on next 50m

maxsize 20480m

extent management local;

```

3. 创建用户并指定表空间。可以通过以下命令创建用户并将表空间指定给该用户：

```sql

create user username identified by password;

grant create session, create tablespace to username;

alter user username default tablespace user_data temporary

tablespace user_temp;

```

通过Oracle客户端Enterprise Manager Console来创建表空间，可以通过以下步骤完成：