Home Database Mysql Tutorial Oracle 11g维护分区(五)Merging Partitions

Oracle 11g维护分区(五)Merging Partitions

Jun 07, 2016 pm 03:21 PM

使用ALTER TABLE ... MERGE PARTITION语句将两个分区的内容合并到一个分区。原来的两个分区将会被删除,相关的本地索引也会被删除

合并分区

使用ALTER TABLE ... MERGE PARTITION语句将两个分区的内容合并到一个分区。原来的两个分区将会被删除,相关的本地索引也会被删除。对于哈希分区表或者复合哈希分区表的哈希子分区,你不能使用这个语句。

对于引用分区表,你不能合并分区。相反,父表的合并操作会级联到所有子表。尽管如此,当你在父表执行合并分区或者子分区操作时,你也可以使用DEPENDENT TABLES子句来为相关表设置特定属性。

如果涉及到的分区或者子分区包含数据,那么索引可能被标识为UNUSABLE,如下表所述:

表类型

索引操作

普通表(堆表) 

除非你在ALTER TABLE语句中指定UPDATE INDEXES字句:

   l  数据库将生成的所有相关本地索引分区或者子分区标识为UNUSABLE

   l  全局索引,或者分区全局索引的所有分区,被标识为UNUSABLE,必须重建。

索引组织表

   l  生成的所有相关本地索引分区标识为UNUSABLE

   l  所有全局索引保持可用。

合并范围分区

你可以将两个相邻的范围分区的内容合并到一个分区。不相邻的范围分区不能合并。合并生成的分区继承了这两个合并分区中更高的上限。

合并范围分区的一个原因是保持在线历史数据在较大的分区。例如,你可以有每日分区,然后将最旧的分区聚合为周分区,进而周分区可以聚合为月分区,等等。

合并间隔分区

你可以将两个相邻的间隔分区的内容合并到一个分区。不相邻的间隔分区不能合并。第一个间隔分区也能喝最高的范围分区进行合并。合并生成的分区继承了这两个合并分区中更高的上限。

合并间隔分区总是导致转换点移动到这两个合并分区中更高的上限。结果是间隔分区表的范围分区扩展到这两个合并分区中更高的上限。任何边界低于新合并生成分区的物化间隔分区,都会自动转化到范围分区,并且使用它们的间隔分区上限定义作为范围分区上限。

例如,虑一下间隔分区transactions

接下来,合并两个相邻的间隔分区。新分区会再有一个系统生成的名称。

合并列表分区

当你合并列表分区时,本合并的分区可以是任意两个分区。它们不必像范围分区那样必须是相邻的,因为列表分区默认是没有顺序的。生成的分区由原来两个分区的数据组成。如果你将默认分区和其他分区进行合并,那么新生成的分区是默认分区。

下面的语句合并了一个列表分区表的两个分区,新生成的分区继承了所有表级别的默认属性。语句指定了MAXEXTENTS子句。

合并复合哈希分区

当你合并复合哈希分区时,子分区会根据哈希函数重新分布到由SUBPARTITIONS n或者SUBPARTITIONS子句指定数量的分区。如果两个都没有指定,那么就使用表级别的默认值。

需要注意的是,分割一个复合哈希分区(在“分割一个复合哈希分区”章节讨论)和合并两个复合哈希分区,在属性的继承方面是不一样的。当一个分区被分割时,新的分区可以继承原分区的属性,因为只有一个父节点。但是,当合并分区时,必须继承表级别属性。

对于间隔哈希分区表,你只能合并两个相邻的间隔分区,或者时最高范围分区和第一个间隔分区。如“合并间隔分区”所述,当你在间隔哈希分区表合并分区时,转换点时会移动的。

下面的例子合并了两个范围哈希分区:

点击(此处)折叠或打开

 

合并复合列表分区

你可以在分区级别合并分区,也可以在列表子分区级别合并子分区。

在一个复合列表分区表中合并分区

在复合列表分区表中合并分区如前面所论述的“合并范围分区”。但是,当你合并两个复合列表分区时,生成的新分区将继承子分区模版的子分区描述,前提是该模版存在。如果不存在子分区模版,,那么将为新分区创建一个默认子分区。

对于间隔哈希分区表,你只能合并两个相邻的间隔分区,或者时最高范围分区和第一个间隔分区。如“合并间隔分区”所述,当你在间隔哈希分区表合并分区时,转换点时会移动的。

下面的语句合并了范围列表分区表stripe_regional_sales的两个分区。该表存在一个子分区模版。

 

新生成分区的数据由两个分区的数据组成。但是,可能存在数据库返回错误的情况。这个之所以会发生,可能是因为当同时满足下列条件时,数据可能会映射到新分区之外:

  • 合并后子分区的一些值可能并不属于子分区模版。

  • 子分区模版不包含默认分区定义。

  • 我们可以通过在默认子分区模版中指定一个默认分区来消除这个错误条件。 

    在一个复合列表分区表中合并子分区

    你可以合并同一分区中的任意两个列表子分区的内容。由此生成的子分区值列表描述包括所有被合并分区的值。

    下面的语句合并了范围列表分区表的两个子分区,并将新生成的子分区保存到表空间ts4:

    原始的两个分区的值列表如下:

  • 子分区q1_1999_northwest被描述为('WA','OR')

  • 子分区q1_1999_southwest被描述为('AZ','NM','UT')

  • 新生成子分区的值列表由上述两个子分区值列表的合集构成:

  • 子分区q1_1999_west被描述为('WA','OR','AZ','NM','UT')

  • 新生成子分区的存储表空间以及子分区属性都由分区级别的默认属性决定,除非你显式地指定。

    如果任何现有地子分区名称被重复使用,那么新生成子分区将会继承名称被重复使用子分区的属性。

    合并复合范围分区

    你可以在区级别合并分区,也可以在范围子分区级别合并子分区。

    在一个复合范围分区表中合并分区
    Statement of this Website
    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

    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

    Notepad++7.3.1

    Notepad++7.3.1

    Easy-to-use and free code editor

    SublimeText3 Chinese version

    SublimeText3 Chinese version

    Chinese version, very easy to use

    Zend Studio 13.0.1

    Zend Studio 13.0.1

    Powerful PHP integrated development environment

    Dreamweaver CS6

    Dreamweaver CS6

    Visual web development tools

    SublimeText3 Mac version

    SublimeText3 Mac version

    God-level code editing software (SublimeText3)

    Explain InnoDB Full-Text Search capabilities. Explain InnoDB Full-Text Search capabilities. Apr 02, 2025 pm 06:09 PM

    InnoDB's full-text search capabilities are very powerful, which can significantly improve database query efficiency and ability to process large amounts of text data. 1) InnoDB implements full-text search through inverted indexing, supporting basic and advanced search queries. 2) Use MATCH and AGAINST keywords to search, support Boolean mode and phrase search. 3) Optimization methods include using word segmentation technology, periodic rebuilding of indexes and adjusting cache size to improve performance and accuracy.

    When might a full table scan be faster than using an index in MySQL? When might a full table scan be faster than using an index in MySQL? Apr 09, 2025 am 12:05 AM

    Full table scanning may be faster in MySQL than using indexes. Specific cases include: 1) the data volume is small; 2) when the query returns a large amount of data; 3) when the index column is not highly selective; 4) when the complex query. By analyzing query plans, optimizing indexes, avoiding over-index and regularly maintaining tables, you can make the best choices in practical applications.

    Can I install mysql on Windows 7 Can I install mysql on Windows 7 Apr 08, 2025 pm 03:21 PM

    Yes, MySQL can be installed on Windows 7, and although Microsoft has stopped supporting Windows 7, MySQL is still compatible with it. However, the following points should be noted during the installation process: Download the MySQL installer for Windows. Select the appropriate version of MySQL (community or enterprise). Select the appropriate installation directory and character set during the installation process. Set the root user password and keep it properly. Connect to the database for testing. Note the compatibility and security issues on Windows 7, and it is recommended to upgrade to a supported operating system.

    Difference between clustered index and non-clustered index (secondary index) in InnoDB. Difference between clustered index and non-clustered index (secondary index) in InnoDB. Apr 02, 2025 pm 06:25 PM

    The difference between clustered index and non-clustered index is: 1. Clustered index stores data rows in the index structure, which is suitable for querying by primary key and range. 2. The non-clustered index stores index key values ​​and pointers to data rows, and is suitable for non-primary key column queries.

    What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)? What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)? Mar 21, 2025 pm 06:28 PM

    Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]

    How do you handle large datasets in MySQL? How do you handle large datasets in MySQL? Mar 21, 2025 pm 12:15 PM

    Article discusses strategies for handling large datasets in MySQL, including partitioning, sharding, indexing, and query optimization.

    MySQL: Simple Concepts for Easy Learning MySQL: Simple Concepts for Easy Learning Apr 10, 2025 am 09:29 AM

    MySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions.

    Explain different types of MySQL indexes (B-Tree, Hash, Full-text, Spatial). Explain different types of MySQL indexes (B-Tree, Hash, Full-text, Spatial). Apr 02, 2025 pm 07:05 PM

    MySQL supports four index types: B-Tree, Hash, Full-text, and Spatial. 1.B-Tree index is suitable for equal value search, range query and sorting. 2. Hash index is suitable for equal value searches, but does not support range query and sorting. 3. Full-text index is used for full-text search and is suitable for processing large amounts of text data. 4. Spatial index is used for geospatial data query and is suitable for GIS applications.

    See all articles