search
HomeDatabaseMysql TutorialOracle 11g维护分区(一)Adding Partitions

本节将介绍如何手动添加新的分区到分区表,并解释了为什么分区不能被指定添加到大部分分区索引。

添加分区

本节将介绍如何手动添加新的分区到分区表,并解释了为什么分区不能被指定添加到大部分分区索引。

向范围分区表添加一个分区

使用ALTER TABLE ... ADD PARTITION语句来添加一个新的分区到“高”端(表中最后一个分区的’HighValue’)。要在开始或在表的中间添加一个分区,使用SPLIT PARTITION子句。

例如,考虑这么一张表,sales,除了包含当前月份的数据,还包含之前12个月份的数据。 1999年1月1日,你增加一个分区月份,存储在表空间TSX。

ALTER TABLE sales

      ADD PARTITION jan99 VALUES LESS THAN ( '01-FEB-1999' )

      TABLESPACE tsx;

另外,和范围分区表相关的本地索引和全局索引仍然可用。

向哈希分区表添加一个分区

当你向一个哈希分区表添加分区时,数据库会对一个现有分区(由数据库选择)的所有数据行按照哈希函数进行重新排列,并将数据填充到新增的分区中。因此,如果表非空的话,添加一个哈希分区可能比较费时。

下面的语句显示了向scubagear表添加一个哈希分区的两种方式。选择第一条语句来添加哈希分区,分区名称由系统自动生成,并且分区存放在默认表空间。第二条语句同样也是增加一个分区,但是,明确指定了分区名称p_named和表空间名称gear5。

ALTER TABLE scubagear ADD PARTITION;

ALTER TABLE scubagear

      ADD PARTITION p_named TABLESPACE gear5;

索引可能被标识为UNUSABLE,如下表所述:

表类型

索引操作

普通表(堆表)

索引组织表

对于本地索引,和普通表的处理方式一样。

所有全局索引保持可用;

向列表分区表添加一个分区

下面的语句说明了如何向列表分区表添加一个新的分区,在示例中,指定了新增分区的存储属性和NOLOGGING属性。

ALTER TABLE q1_sales_by_region

  ADD PARTITION q1_nonmainland VALUES ('HI', 'PR')

      STORAGE (INITIAL 20K NEXT 20K) TABLESPACE tbs_3

      NOLOGGING;

描述新增分区的集合内的所有值不能存在于表的其他分区。

如果一个列表分区表包含默认分区,那么你不能给它添加新的分区,但是你可以拆分默认分区。通过这样做,你不仅有效地创建了一个你指定值的新分区,并且第二个分区保留为默认分区。

与列表分区表相关的本地和全局索引均保持可用。

向间隔分区表添加一个分区

你不能显式地向间隔分区表中添加一个分区,除非你西安锁定分区,这将触发创建分区。当该间隔的数据被插入时,数据库会自动创建一个间隔分区。一般情况下,只有在分区交换负载场景中,你才必须显式创建间隔分区。

要改变将来分区的间隔,我们可以在ALTER TABLE中使用SET INTERVAL子句。该子句将会改变超过当前最高边界的所有物化间隔分区的间隔。

你也可以使用SET INTERVAL子句来迁移一个存在的范围分区表或者符合范围分区表到间隔分区表或复合间隔分区表。如果要禁止创建新的分区,有效的恢复回一个范围分区表,在SET INTERVAL子句中指定一个空值即可。已创建的间隔分区将被转化为范围分区,对应的HighValue为当前最大值。

如果要提高日期的间隔范围,你必须确保你在新分区的相应边界。例如,在你的日常间隔分区表transactions的最高间隔分区边界是January 30, 2007,你想把间隔修改为一个月,那么下面的语句将会导致一个错误:

ALTER TABLE transactions SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH');

ORA-14767: Cannot specify this interval with existing high bounds

你必须创建另外一个以February 1, 2007为边界的日常间隔分区,这样才能成功修改间隔。

LOCK TABLE transactions PARTITION FOR(TO_DATE('31-JAN-2007','dd-MON-yyyy') IN SHARE MODE;

ALTER TABLE transactions SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH');

间隔分区表的第一个分区其实是一个范围分区,你可以拆分范围分区,在间隔分区表中添加更多的分区。

如果要禁用transactions表的间隔分区,使用以下语句:

ALTER TABLE transactions SET INTERVAL ();

向哈希复合分区表添加分区

可以在分区和哈希子分区两个级别来添加分区。

添加一个分区

向一个【范围|列表|间隔】-哈希复合分区表添加一个分区如前所述。对于一个间隔-哈希分区表,间隔分区是自动创建的。你可以指定SUBPARTITIONS子句来指定子分区的数量,或者指定SUBPARTITION子句来命名具体子分区。如果没有指定SUBPARTITIONS子句或者SUBPARTITION子句,那么分区将继承表级别的默认属性。对于一个间隔-哈希分区表,你只能向范围或者已经物化的间隔分区添加子分区。

这个例子向一个范围-哈希分区表sales添加了一个范围分区q1_2000,该分区用来存储2000年第一季度的数据。其中,有8个子分区存储在tbs5表空间。子分区不能显式使用表压缩。在这个例子中,子分区会继承分区级别的压缩属性并以压缩形式存储。

ALTER TABLE sales ADD PARTITION q1_2000

      VALUES LESS THAN (2000, 04, 01) COMPRESS

      SUBPARTITIONS 8 STORE IN tbs5;

添加一个子分区

你可以在ALTER TABLE语句中使用MODIFY PARTITION ... ADD SUBPARTITION子句来向一个【范围|列表|间隔】-哈希复合分区表添加一个哈希子分区。数据库将会根据哈希函数,对同一分区内的一个已有子分区的所有数据行重新进行哈希排列,并将其中部分数据填充到新增子分区中。对于一个间隔-哈希分区表,你只能向范围分区或者已经物化的间隔分区添加子分区。

在下面的示例中,一个存储在us1表空间的新哈希子分区us_loc5, 被添加到diving表的范围分区locations_us中。

ALTER TABLE diving MODIFY PARTITION locations_us

      ADD SUBPARTITION us_locs5 TABLESPACE us1;

除非你指定了UPDATE INDEXES子句,否则,必须重建和新增子分区以及重新进行哈希排列子分区相关的索引子分区。

向列表复合分区表添加分区

可以在分区和列表子分区两个级别来添加分区。

添加一个分区

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How does MySQL's licensing compare to other database systems?How does MySQL's licensing compare to other database systems?Apr 25, 2025 am 12:26 AM

MySQL uses a GPL license. 1) The GPL license allows the free use, modification and distribution of MySQL, but the modified distribution must comply with GPL. 2) Commercial licenses can avoid public modifications and are suitable for commercial applications that require confidentiality.

When would you choose InnoDB over MyISAM, and vice versa?When would you choose InnoDB over MyISAM, and vice versa?Apr 25, 2025 am 12:22 AM

The situations when choosing InnoDB instead of MyISAM include: 1) transaction support, 2) high concurrency environment, 3) high data consistency; conversely, the situation when choosing MyISAM includes: 1) mainly read operations, 2) no transaction support is required. InnoDB is suitable for applications that require high data consistency and transaction processing, such as e-commerce platforms, while MyISAM is suitable for read-intensive and transaction-free applications such as blog systems.

Explain the purpose of foreign keys in MySQL.Explain the purpose of foreign keys in MySQL.Apr 25, 2025 am 12:17 AM

In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.

What are the different types of indexes in MySQL?What are the different types of indexes in MySQL?Apr 25, 2025 am 12:12 AM

There are four main index types in MySQL: B-Tree index, hash index, full-text index and spatial index. 1.B-Tree index is suitable for range query, sorting and grouping, and is suitable for creation on the name column of the employees table. 2. Hash index is suitable for equivalent queries and is suitable for creation on the id column of the hash_table table of the MEMORY storage engine. 3. Full text index is used for text search, suitable for creation on the content column of the articles table. 4. Spatial index is used for geospatial query, suitable for creation on geom columns of locations table.

How do you create an index in MySQL?How do you create an index in MySQL?Apr 25, 2025 am 12:06 AM

TocreateanindexinMySQL,usetheCREATEINDEXstatement.1)Forasinglecolumn,use"CREATEINDEXidx_lastnameONemployees(lastname);"2)Foracompositeindex,use"CREATEINDEXidx_nameONemployees(lastname,firstname);"3)Forauniqueindex,use"CREATEU

How does MySQL differ from SQLite?How does MySQL differ from SQLite?Apr 24, 2025 am 12:12 AM

The main difference between MySQL and SQLite is the design concept and usage scenarios: 1. MySQL is suitable for large applications and enterprise-level solutions, supporting high performance and high concurrency; 2. SQLite is suitable for mobile applications and desktop software, lightweight and easy to embed.

What are indexes in MySQL, and how do they improve performance?What are indexes in MySQL, and how do they improve performance?Apr 24, 2025 am 12:09 AM

Indexes in MySQL are an ordered structure of one or more columns in a database table, used to speed up data retrieval. 1) Indexes improve query speed by reducing the amount of scanned data. 2) B-Tree index uses a balanced tree structure, which is suitable for range query and sorting. 3) Use CREATEINDEX statements to create indexes, such as CREATEINDEXidx_customer_idONorders(customer_id). 4) Composite indexes can optimize multi-column queries, such as CREATEINDEXidx_customer_orderONorders(customer_id,order_date). 5) Use EXPLAIN to analyze query plans and avoid

Explain how to use transactions in MySQL to ensure data consistency.Explain how to use transactions in MySQL to ensure data consistency.Apr 24, 2025 am 12:09 AM

Using transactions in MySQL ensures data consistency. 1) Start the transaction through STARTTRANSACTION, and then execute SQL operations and submit it with COMMIT or ROLLBACK. 2) Use SAVEPOINT to set a save point to allow partial rollback. 3) Performance optimization suggestions include shortening transaction time, avoiding large-scale queries and using isolation levels reasonably.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

Atom editor mac version download

Atom editor mac version download

The most popular open source editor