Which three operations can be performed as multipartition operations in Oracle?
A.
Merge partitions of a list partitioned table
B.
Drop partitions of a list partitioned table
C.
Coalesce partitions of a hash-partitioned global index.
D.
Move partitions of a range-partitioned table
E.
Rename partitions of a range partitioned table
F.
Merge partitions of a reference partitioned index
Explanation:
Multipartition maintenance enables adding, dropping, truncate, merge, split
operations on multiple partitions.
A: Merge Multiple Partitions:
The new “ALTER TABLE … MERGE PARTITIONS ” help merge multiple partitions or
subpartitions with a single statement. When merging multiple partitions, local and global index
operations and semantics for inheritance of unspecified physical attributes are the same for
merging two partitions.
B: Drop Multiple Partitions:
The new “ALTER TABLE … DROP PARTITIONS ” help drop multiple partitions or subpartitions
with a single statement.
Example:
view plaincopy to clipboardprint?
SQL> ALTER TABLE Tab_tst1 DROP PARTITIONS
Tab_tst1_PART5, Tab_tst1_PART6, Tab_tst1_PART7;
Table altered
SQL>
Restrictions :
– You can’t drop all partitions of the table.
– If the table has a single partition, you will get the error: ORA-14083: cannot drop the only partitionof a partitioned.
These operations cannot be performed on reference-partitioned tables. If performed on a parent table, then these operations cascade to all descendant tables.
http://docs.oracle.com/cd/B28359_01/server.111/b32024/part_admin.htm#i1006820
Answers are A B and D
http://docs.oracle.com/cd/E16655_01/server.121/e17613/part_admin002.htm#VLDBG1120
single and multiple subpartitions operations:
ADD PARTITION, DROP PARTITION, MERGE PARTITIONS, SPLIT PARTITION, TRUNCATE PARTITION
in case of Reference partion all these actions “If performed on a parent table, then these operations cascade to all descendant tables”
Only one partition can be moved in a time.
A B and F
I think F is right:
Maintenance of Global Partitioned Indexes
By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:
ADD (HASH)
COALESCE (HASH)
DROP
EXCHANGE
MERGE
MOVE
SPLIT
TRUNCATE
ABF
ABF – OK
ABF
abc
You can remove multiple partitions or subpartitions from a range or list partitioned table with the DROP PARTITION and DROP SUBPARTITION clauses of the ALTER TABLE statement. B is correct.
When merging multiple list partitions, the resulting partition value list are the union of the set of partition value list of all of the partitions to be merged. A is also corrcet.
A, B, F
Multi-Partition Maintenance Operations
• Add / truncate / drop partition allows you to add / truncate /
drop multiple partitions in one single operation.
• Split partition and merge partitions operations allow you to
roll out data into smaller partitions or to roll up data into
larger partition
• Prior to Oracle Database 12c, Oracle allowed splitting into
and merging of only two partitions using the ALTER
TABLE split and merge partition DDLs
Split partition and merge partitions operations allow you to roll out data into smaller partitions or to roll up data into a larger partition.
Prior to Oracle Database 12c, Oracle allowed splitting into and merging of only two partitions using the alter table split and merge partition DDLs. As a result, to split a partition into N partitions or to merge N partitions into one, (N-1) DDLs must be issued. For instance, you may want to roll up data for the last year by merging all the monthly partitions. Because only two partitions can be merged at a time, it requires issuing 11 alter table merge partition DDLs. This is not only cumbersome, but it is also expensive due to multiple data reads and writes. An alternative approach to merge multiple range partitions is to load the data from the N source partitions into a new non-partitioned table using the CREATE TABLE … AS SELECT (CTAS) statement. Next, you drop the first (N-1) source partitions and exchange the N-th source partition with the new table. The N-th source partition now contains data from the N partitions that were to be merged and may be renamed to the target partition name. This approach reduces data movement compared to issuing (N-1) merge partition DDLs. For example, when rolling up monthly partitions for the last year, the customer will issue a CTAS, 11 drop partition and an exchange partition DDL to achieve the same result with lesser data movement.
A, B, F
I think only A and B are correct
F is incorrect, you cannot Merge partitions of a reference partitioned table
ABF
“E” is correct:
SQL> CREATE TABLE t1
2 (id NUMBER,
description VARCHAR2( 3 50),
created_date 4 DATE)
PARTITION BY RANGE (created_d 5 ate)
6 (PARTITION part_2014 VALUES LESS THAN (TO_DATE(’01/01/2015′, ‘DD/MM/YYYY’)));
Table created.
SQL> ALTER TABLE t1 RENAME PARTITION part_2014 TO part_2016;
Table altered.
A,B,E
That is not a mulitpartition operation – you just rename one partition.
ABE
http://docs.oracle.com/database/121/VLDBG/GUID-79391819-DCEF-46AC-977D-199BD2044DA2.htm#VLDBG14070
Oracle Database 12c further allows partition maintenance operations on multiple partitions as single atomic
operation: for example, you can merge the three partitions ‘January 2012’, ‘February 2012’, and ‘March 2012’ into a
single partition ‘Q1 2012’ with a single merge partition operation.
Another typical usage of partitioning for manageability is to support a ‘rolling window’ load process in a data
warehouse. Suppose that a DBA loads new data into a table on daily basis. That table could be range-partitioned so
that each partition contains one day of data. The load process is simply the addition of a new partition. Adding a
single partition is much more efficient than modifying the entire table, since the DBA does not need to modify any
other partitions.
http://www.oracle.com/technetwork/database/options/partitioning/partitioning-wp-12c-1896137.pdf