Oracle partitioning interview questions

on

FAQ: Oracle partitioning 

1. What is partitioning?
Partitioning is the process of separating data into logical subsets. In the same way as normal tables and indexes, partitions are physical segments for which extents are allocated (see Note 666061).
2. Which objects can be partitioned?
Tables and indexes can be partitioned. All combinations are theoretically possible:
  • Table not partitioned, relevant index partitioned
  • Table partitioned, relevant index not partitioned
  • Table partitioned, relevant index partitioned
Up to 64,000 partitions are possible. Objects with LONG fields and LONG RAW fields cannot be partitioned.
3. What are the advantages of partitioning?
  • Improved administration because many operations can be carried out on separate partitions (data loads, index creation and rebuilding, backup/recovery and so on).
  • Partitions that are no longer required can simply be dropped (no reorganization to recover disk space and no time consuming DELETEs are necessary).
  • You can improve the performance of queries if only specific partitions have to be read.
  • Partitioning is transparent for the application and does not require any change to SQL queries and DML statements.
  • Avoid redo log data when you delete old data because, unlike a DELETE, a DROP or TRUNCATE does not generate redo log data.
4. What problems can occur in connection with partitioning?
ORA-014508:
Validation of partitioned objects with VALIDATE STRUCTURE encounters an ORA-14508 error if the utlvalid.sql script was not executed for the executing database user. To avoid the error, see Note 514178 and execute utlvalid.sql for all relevant users.
ORA-14400:
ORA-14400 occurs if you want to insert an entry in the table that does not match the value range of any partition. In such a case, you must compare the value of the entry with the definitions of the partitions to determine the cause of the error.
Queries that do not contain the partitioning criterion in the WHERE clause, must scan ALL partitions.  In these cases, the effort increases in proportion to the number of partitions.
5. How many types of partitioning exist?
Range partitioning
Each partition includes data from a specific value range, for example:
Column YEAR: JAN 01 2002 - DEC 31 2002 -> Partition 1
Column YEAR: JAN 01 2003 - DEC 31 2003 -> Partition 2
Column YEAR: JAN 01 2004 - DEC 31 2004 -> Partition 3
List partitioning
For each partition, there is a list specifying the values to be assigned to this partition, for example:
Column COUNTRY: "Germany", "France" -> Partition 1 (Europe)
COLUMN COUNTRY: "USA", "Mexico" -> Partition 2 (America)
If you specify a default partition, all records that do not match any list are included in this partition.
Hash partitioning
A hash algorithm is used to distribute the records at random to the individual partitions.
Combined partitioning: Several of the above partitioning types can be combined in the form of PARTITION and SUBPARTITION (for example, range hash or range list).
The following different partitioning methods are used in relation to indexes:
Local partitioning: There is a 1:1 relationship between the table and index partitions. Each index partition contains the values of exactly one related table partition.
Global partitioning: The index partitioning is independent of the table partitioning in question. An index partition can contain values from different table partitions.
6. What is the Oracle syntax for creating partitioned objects?
Example of range partitioning:

CREATE TABLE MONTHS (MONTH NUMBER, DAYS NUMBER)
PARTITION BY RANGE (MONTH)
( PARTITION WINTER VALUES LESS THAN (4),
   PARTITION SPRING VALUES LESS THAN (7),
   PARTITION SUMMER VALUES LESS THAN (10),
   PARTITION AUTUMN VALUES LESS THAN (13));
Example of list partitioning:

CREATE TABLE MONTHS (MONTH VARCHAR(20), DAYS NUMBER)
PARTITION BY LIST (MONTH)
( PARTITION WINTER VALUES ('January', 'February', 'March'),
 PARTITION SPRING VALUES ('April', 'May', 'June'),
 PARTITION SUMMER VALUES ('July', 'August', 'September'),
 PARTITION AUTUMN VALUES ('October', 'November', 'December'));
Example of hash partitioning:

CREATE TABLE MONTHS (MONTH VARCHAR(20), DAYS NUMBER)
PARTITION BY HASH(MONTH)
PARTITIONS 4;
7. How can I find out which objects are partitioned?
The following statement provides all objects with partitions at Oracle level:

SELECT TABLE_OWNER OWNER, 'TABLE' TYPE, TABLE_NAME NAME
FROM DBA_TAB_PARTITIONS
GROUP BY TABLE_OWNER, TABLE_NAME
UNION
SELECT INDEX_OWNER, 'INDEX', INDEX_NAME
FROM DBA_IND_PARTITIONS
GROUP BY INDEX_OWNER, INDEX_NAME;
8. How do I determine all the partitions of a partitioned object?
You can receive the partitions of a table as follows:

SELECT * FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = '';
You can determine the partitions of an index as follows:

SELECT * FROM DBA_IND_PARTITIONS WHERE INDEX_NAME = '';
9. Where does Oracle store administrative information for partitions?
Oracle stores administrative information for the partitions in the DBA_PART_TABLES and DBA_PART_INDEXES views, for example:
  • PARTITIONING_TYPE: Partitioning type
  • PARTITION_COUNT: Number of partitions
  • DEF_*: Default values for memory and configuration parameters (see Note 666061).
  • The DBA_PART_KEY_COLUMNS view contains the columns to which an object is partitioned:
  • NAME: Name of the partitioned object
  • COLUMN_NAME: Column name
  • COLUMN_POSITION: Position of the column during partitioning
  • The DBA_TAB_PARTITIONS and DBA_IND_PARTITIONS views contain the high value of the partition (in other words, during range partitioning, the value that was specified with LESS THAN):
  • HIGH_VALUE: high value of the partition
10. Are objects partitioned by default in the R/3 environment?
Partitioned objects are not delivered in normal SAP R/3 Systems. On the other hand, many InfoCube tables in the Business Warehouse area (BW) are partitioned.
11. Can I partition additional R/3 objects depending on demand?
As of 4. 6B, R/3 DDIC supports a simple range partitioning of tables based on exactly one column. This functionality is required when you use BW.
Note 742243 describes the prerequisites and possibilities in Release 4.6C or higher for creating objects as range, list or hash partitioned. However, no options will be available for defining subpartitions. Indexes can only ever be partitioned in the same way as the underlying tables (or not at all). See also Note 105047.
 If you want to use partitioning that is not supported by your R/3 Release, you can also partition the objects manually at Oracle level. Note, however, that this  partitioning can disappear if you perform certain actions (such as table conversions).
12. Are objects partitioned by default in the Oracle DDIC?
As of Oracle9i, multiple objects belonging to the Logminer (see Note 701562) and whose names start with LOGMNR or LOGSTDBY are partitioned when created under the SYSTEM user, for example:

LOGMNR_TS$
LOGMNR_TYPE$
LOGMNR_USER$
LOGSTDBY$APPLY_PROGRESS
As of Oracle 10g, STREAMS$_APPLY_SPILL_MSGS_PART and a large number of tables from the workload repository (WRH$_...) are also partitioned.
13. Can I deactivate partitioning in BW?
 If you use BW functions, then partitioned objects are an essential prerequisite for good database performance. Therefore deactivation of the Oracle  partitioning option is not permitted in this case.
 However, if BW is automatically installed when you install another product (such as NetWeaver04), but you do not actually use BW, you can deactivate the  partitioning option to reduce the license costs for a database that is directly licensed with Oracle (for example). For more information, see Note 859841.
14. Where can I find more information about partitioning?
Oracle9i Online Documentation:

Oracle9i Database Concepts
-> 11 Partitioned Tables and Indexes

========================================================================

What is local and global index?

Local index is where the index is equipartitioned with its table, the index is partitioned on the same columns, with the same number of partitions and the same partition bounds as its table.
So each index partition is associated with exactly one partition of the underlying table, that is, all keys in an index partition refer only to rows stored in a single corresponding table partition.
Local index can be subpartitioned.
Example for local index definition:
CREATE BITMAP INDEX SH.SALES_CUST_BIX ON SH.SALES (CUST_ID) LOCAL;
Global index is an index which is not local. It can be partitioned or non partitioned. If it is partitioned, then it is normally not equipartitioned with the table, a single index partition can point to any or all table partitions. See example in Document 69715.1 and Document 165656.1. (It is possible to create a global index which is equipartitioned with its table, but it does not make sense doing that as Oracle will not take advantage of the equipartitioning when generating query plans or executing partition maintenance operations.) The highest partition of a global index must have a partition bound that includes all values that are MAXVALUE. This insures that all rows in the underlying table can be represented in the index.
Global index cannot be subpartitioned.
Note, you can create partitioned index on non partitioned table as well.

What is prefixed and non prefixed index?

The index is called prefixed if the leading column(s) in the index definition is (are) the partition key column(s), otherwise it is called non prefixed.  See examples for creation prefixed and non prefixed local indexes in Document 165938.1 and Document 166112.1 respectively.

Can I create nonprefixed global partitioned index?

No, Oracle does not support global nonprefixed partitioned indexes. Global partitioned index must be prefixed, otherwise ORA-14038 occurs when trying to create such.

Can I create UNIQUE partitioned index?

A local index can be created UNIQUE if the index partitioning columns form a subset of the index columns (e.g. local prefixed index always satisfies the latter condition). This restriction guarantees that rows with identical index keys always map into the same partition, where uniqueness violations can be detected.

Global partitioned index can be created UNIQUE index since the partitioning columns always form a subset of the index columns (global partitioned index must be prefixed).
See also Note 74224.1 How to Create Primary Key Partitioned Indexes

Can I create partitioned bitmap index?

Yes, as a local index. Bitmap index cannot be global partitioned index, it can only be local or non partitioned (hence bitmap index on nonpartitioned table cannot be partitioned).

What types of partitioning can I choose for a global index?

Global partitioned index can be partitioned only by range or by hash. In a range partitioned global index, each index partition contains values defined by a partition bound. In a hash partitioned global index, each partition contains values determined by the Oracle Database hash function.

Can i subpartition global index?

No you cannot subpartition global index, but you can partition it. The only way to end up with index subpartition is to have subpartitioned table with local index.

Why indexes on partitioned table become UNUSABLE?

By default, many table maintenance operations on partitioned tables invalidate (mark UNUSABLE) the global indexes and affected local index partitions. At partition maintenance of the table, all partitions of a global index are affected.
You must then rebuild the entire index or each of the affected index partitions.
Whether an operation marks the affected index partition unusable depends on the partition type (e.g. add partition does not mark global and affected local indexes unusable for range partitioned table, but it may do for hash partitioned table), see details under 'Maintaining Partitioned Tables' in Oracle Database Administrator's Guide (10.2 version of the document is referenced here).
Unusable index segments do not consume any space in 11.2 (be aware of bug mentioned in Document 10258337.8)
See also

What happens with my queries against partition table having unusable indexes?

Unusable indexes are ignored by the optimizer (by default), this is due to instance parameter
SKIP_UNUSABLE_INDEXES = [TRUE | FALSE ] (default value: TRUE)

This setting disables error reporting of indexes and index partitions marked UNUSABLE, because the optimizer choses access path alternative to the unusable index or index partition. If you do not want the database to choose an alternative execution plan to avoid the unusable elements, you should set this parameter to FALSE.
Partitioned indexes can be used by the optimizer even if some partitions are unusable. Prior to 11.2 this can be done only when static partition pruning occurs (when partition elimination occurs at parse time as opposed to at runtime) and only access of usable index partitions mandatory. With 11.2, intelligent rewrite of queries using UNION ALL in the presence of partially unusable indexes was introduced, which provides transparent internal rewrite, usable index partitions will be used, full partition access for unusable index partitions (see Note 1638318.1 for more details).

How can I prevent indexes become UNUSABLE during maintenance operation of table partitions?

If you specify UPDATE INDEXES in your ALTER TABLE statement for the maintenance operation. Specifying this clause tells the database to update the index (both global and affected local index partition) at the time it executes the maintenance operation DDL statement.

In order to update global indexes only, use UPDATE GLOBAL INDEXES clause.

The following operations support the UPDATE INDEXES clause:

    ADD      PARTITION | SUBPARTITION
    COALESCE PARTITION | SUBPARTITION
    DROP     PARTITION | SUBPARTITION
    EXCHANGE PARTITION | SUBPARTITION
    MERGE    PARTITION | SUBPARTITION
    MOVE     PARTITION | SUBPARTITION
    SPLIT    PARTITION | SUBPARTITION
    TRUNCATE PARTITION | SUBPARTITION

The following implications are worth noting when you specify UPDATE INDEXES:
  • The partition DDL statement takes longer to execute, because indexes that otherwise would be marked UNUSABLE are now updated. However, you must compare this increase with the time it takes to execute DDL without updating indexes, and then rebuild all indexes. A rule of thumb is that it is faster to update indexes if the size of the partition is less than 5% of the size of the table.
  • The DROP, TRUNCATE and EXCHANGE operations may no longer be fast operations. Again, you must compare the time it takes to do the DDL and then rebuild all the indexes.
Note: in version 12 global index maintenance related to DROP and TRUNCATE table partition operations is optimized. The global index maintenance can be scheduled independently of the DROP or TRUNCATE table partition operation, and until global index maintenance done the invalid entries in the global index will be ignored by any queries, DDL, DMLS against the partitioned table. (The feature is based on that indexes can now be created on a subset of the partitions of a table, hence no longer necessary to mark the whole global index unusable at table pmops.) 
Note 1482264.1 How to Drop/Truncate multiple partitions in Oracle 12C
Note 1482460.1 How to create Partial Global Indexes for Partitioned tables in Oracle 12c
  • When you update a table with a global index, the index is updated in place. The updates to the index are logged, and redo and undo records are generated. In contrast, if you rebuild an entire global index, you can do so in NOLOGGING mode. Furthermore rebuilding the entire index manually may be able to create a more compact, more efficient index.
  • If an index or index partition was unusable before the partition maintenance operation (pmop), then it stays unusable after pmop as well even if the update indexes clause was specified.

What are the performance implications of local indexes?

  • Availability is increased because actions that make data invalid or unavailable in a partition affect this partition only.  
  • Partition maintenance is simplified. When moving a table partition, or when data ages out of a partition, only the associated local index partition must be rebuilt or maintained.
  • Partition elimination/pruning during SQLs against the partitioned table with predicate on the partition key (prefixed more often allows for partition elimination than non prefixed).
  • Non prefixed local index is useful if it is important to have quick access according to a column which is not the partition key (e.g. look up for value account_number column, hence the account_number is placed as a leading column of the index), while it is also important to have the index equipartitioned with the table e.g. to support the time interval for rolling out old data and rolling in new data (e.g. partition key is time_id column, rolling out/in data is done by partition maintenance commands). This scenario often happens in historical databases.
  • Local prefixed index could be beneficial to "fast split" of partition. Oracle executes internal SQLs to decide whether fast split is possible or not. The execution plan of certain internal SQLs of such, e.g. to determine whether any of the resulting partitions will be empty, is done by queries like the following, which are to determine whether there are rows in the partition to be split relative to the AT value:

    (i)  select /*+ FIRST_ROWS(1) PARALLEL("MY_PART_TABLE", 1) */ 1 from "MY_PART_TABLE" [SUB]PARTITION ("MY_PART_TABLE_1_MAX")  where (((("A"< AT_VALUE)))) and rownum < 2
    (ii) select /*+ FIRST_ROWS(1) PARALLEL("MY_PART_TABLE", 1) */ 1 from "MY_PART_TABLE" [SUB]PARTITION ("MY_PART_TABLE_1_MAX")  where (((("A" >= AT_VALUE OR "A" IS NULL)))) and rownum < 2

    The above SQLs could take time, particularly when the table is large and global index is chosen to range scan according to partition key value.
    The existence of a prefixed local index could make it faster as it only needs to scan the index partition that belongs to the partition to be split, as shown below:

    Row Source Operation
    -----------------------------------------------------------
      COUNT STOPKEY
       PARTITION RANGE SINGLE PARTITION: 3 3
        INDEX ... SCAN PARTITION: 3 3 
  • Local indexes simplify the task of tablespace incomplete recovery. To recover a partition or subpartition of a table to a point in time, you must also recover the corresponding index entries to the same point in time. The only way to accomplish this is with a local index. Then you can recover the corresponding table and index partitions or subpartitions.
  • Unusable indexes do not consume space starting from 11.2 (see Document 1266458.1 for more information).

What are the performance implications of global indexes?

  • Global index can be useful where rapid access, data integrity, and availability are important. In an OLTP system, a table may be partitioned by one key, for example, the employees.department_id column, but an application may need to access the data with many different keys, for example, by employee_id or job_id. Global indexes can be useful in this scenario as global indexes are prefixed and can provide better performance than local nonprefixed indexes because they minimize the number of index partition probes (cf. local prefixed more often allows for partition elimination than non prefixed mentioned in the previous section).
  • To create UNIQUE index if the table partitioning columns do not form a subset of the index columns, hence local unique index cannot be created.
  • Global indexes are harder to manage than local indexes. At partition maintenance of the table, all partitions of a global index are affected.
  • Partition elimination/pruning during SQLs against the partitioned table: prefixed - always allows for partition elimination.
  • The hash index partitioning can improve performance of indexes where a small number leaf blocks in the index have high contention in multiuser OLTP environment. In some OLTP applications, index insertions happen only at the right edge of the index. This situation could occur when the index is defined on monotonically increasing columns (e.g. column value is populated by a sequence). In such situations, the right edge of the index becomes a hotspot because of contention for index pages, buffers, latches for update, and additional index maintenance activity, which results in performance degradation.
With hash partitioned global indexes index entries are hashed to different partitions based on partitioning key and the number of partitions. This spreads out contention over number of defined partitions, resulting in increased throughput.
With hash partitioning, an index entry is mapped to a particular index partition based on the hash value generated by a hash function in the Oracle Database. In order to be the hash partitioning effective the number of hash partitions should be the power of 2 and the values tuples of the partition key column(s) should be sufficiently large compared to the number of hash partitions so that balance of hash partitioned can be achieved. Having 5 distinct values of the hash partition key that needs to be mapped to 8 hash partition cannot eliminate skew if it exists as the hash function is deterministic, that is, to the same partition key value, the same hash partition is mapped, on the other hand different partition key values could map to the same hash partition.
The syntax to create hash-partitioned global index is very similar to hash-partitioned table. Queries involving equality and IN predicates on index partitioning key can efficiently use global hash partitioned index to answer queries quickly by eliminating index partitions.
  • Unusable indexes do not consume space starting from 11.2.

How to Determine Whether an Index is Global or Local

To determine whether an index is global or local, query the column, LOCALITY, in DBA_PART_INDEXES.

The only corner case which this doesn't cover is when the table is partitioned, but has a global index which is ** not ** partitioned, since such an index would not be tracked by DBA_PART_INDEXES. However, one could deduce that case by comparing the column, PARTITIONED, of DBA_INDEXES with the same column in DBA_TABLES. Finding a value of 'NO' in the former, but 'YES' in the latter, would signal a non-partitioned, global index.
=======================================================================

Dropping Table Partitions

You can drop partitions from range, interval, list, or composite *-[range | list] partitioned tables. For interval partitioned tables, you can only drop range or interval partitions that have been materialized. For hash-partitioned tables, or hash subpartitions of composite *-hash partitioned tables, you must perform a coalesce operation instead.
Note: You cannot drop a partition from a reference-partitioned table. Instead, a drop operation on a parent table cascades to all descendant tables.
Use one of the following statements to drop a table partition or subpartition:
ALTER TABLE ... DROP PARTITION to drop a table partition
    ALTER TABLE ... DROP SUBPARTITION to drop a subpartition of a composite *-[range | list] partitioned table
To preserve the data in the partition, use the MERGE PARTITION statement instead of the DROP PARTITION statement.

If local indexes are defined for the table, then this statement also drops the matching partition or subpartitions from the local index. All global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE unless either of the following is true:
  •     You specify UPDATE INDEXES (Cannot be specified for index-organized tables. Use UPDATE GLOBAL INDEXES instead.)
  •     The partition/subpartition being dropped is empty.
Note: If a table contains only one partition, you cannot drop the partition. Instead, you must drop the table.
You cannot drop the highest range partition in the range-partitioned section of an interval-partitioned or interval-* composite partitioned table.

Dropping a Partition from a Table that Contains Data and Global Indexes

If the partition contains data and one or more global indexes are defined on the table, then use one of the following methods to drop the table partition.

Method 1

Leave the global indexes in place during the ALTER TABLE ... DROP PARTITION statement. Afterward, you must rebuild any global indexes (whether partitioned or not) because the index (or index partitions) has been marked UNUSABLE. The following statements provide an example of dropping partition dec98 from the sales table, then rebuilding its global nonpartitioned index.
ALTER TABLE sales DROP PARTITION dec98;
ALTER INDEX sales_area_ix REBUILD;
If index sales_area_ix were a range-partitioned global index, then all partitions of the index would require rebuilding. Further, it is not possible to rebuild all partitions of an index in one statement. You must issue a separate REBUILD statement for each partition in the index. The following statements rebuild the index partitions jan99_ix, feb99_ix, mar99_ix, ..., dec99_ix.
ALTER INDEX sales_area_ix REBUILD PARTITION jan99_ix;
ALTER INDEX sales_area_ix REBUILD PARTITION feb99_ix;
ALTER INDEX sales_area_ix REBUILD PARTITION mar99_ix;
...
ALTER INDEX sales_area_ix REBUILD PARTITION dec99_ix;
This method is most appropriate for large tables where the partition being dropped contains a significant percentage of the total data in the table.

Method 2

Issue the DELETE statement to delete all rows from the partition before you issue the ALTER TABLE ... DROP PARTITION statement. The DELETE statement updates the global indexes.

For example, to drop the first partition, issue the following statements:
DELETE FROM sales partition (dec98);
commit;
ALTER TABLE sales DROP PARTITION dec98 UPDATE GLOBAL INDEXES;
This method is most appropriate for small tables, or for large tables when the partition being dropped contains a small percentage of the total data in the table.

Method 3

Specify UPDATE INDEXES in the ALTER TABLE statement. Doing so causes the global index to be updated at the time the partition is being dropped.
ALTER TABLE sales DROP PARTITION dec98 UPDATE INDEXES;

Dropping a Partition Containing Data and Referential Integrity Constraints

If a partition contains data and the table has referential integrity constraints, choose either of the following methods to drop the table partition. This table has a local index only, so it is not necessary to rebuild any indexes.

Method 1

If there is no data referencing the data in the partition to drop, then you can disable the integrity constraints on the referencing tables, issue the ALTER TABLE ... DROP PARTITION statement, then re-enable the integrity constraints.

This method is most appropriate for large tables where the partition being dropped contains a significant percentage of the total data in the table. If there is still data referencing the data in the partition to be dropped, then ensure the removal of all the referencing data so that you can re-enable the referential integrity constraints.

Method 2

If there is data in the referencing tables, then you can issue the DELETE statement to delete all rows from the partition before you issue the ALTER TABLE ... DROP PARTITION statement. The DELETE statement enforces referential integrity constraints, and also fires triggers and generates redo and undo logs. The delete can succeed if you created the constraints with the ON DELETE CASCADE option, deleting all rows from referencing tables as well.
DELETE FROM sales partition (dec94);
commit;
ALTER TABLE sales DROP PARTITION dec94;
This method is most appropriate for small tables or for large tables when the partition being dropped contains a small percentage of the total data in the table.

Dropping Interval Partitions

You can drop interval partitions in an interval-partitioned table. This operation drops the data for the interval only and leaves the interval definition intact. If data is inserted in the interval just dropped, then the database again creates an interval partition.

You can also drop range partitions in an interval-partitioned table. The rules for dropping a range partition in an interval-partitioned table follow the rules for dropping a range partition in a range-partitioned table. If you drop a range partition in the middle of a set of range partitions, then the lower boundary for the next range partition shifts to the lower boundary of the range partition you just dropped. You cannot drop the highest range partition in the range-partitioned section of an interval-partitioned table.

The following example drops the September 2007 interval partition from the sales table. There are only local indexes so no indexes or index partitions are made unusable.
ALTER TABLE sales DROP PARTITION FOR(TO_DATE('01-SEP-2007','dd-MON-yyyy'));
Interval partitioning is an extension to range partitioning in which, beyond a point in time, partitions are defined by an interval. Interval partitions are automatically created by the database when data is inserted into the partition.
ORA-14758 is expected if you try to drop the last partition on which the interval partitions are based. Have a look at the Document 882681.1 to have example on this.

Note: You cannot drop the last partition of the interval partition. Have a look at Document 882681.1 for more information.
For more information on dropping interval partitioning see Document 1285036.1 .

Dropping Index Partitions

You cannot explicitly drop a partition of a local index. Instead, local index partitions are dropped only when you drop a partition from the underlying table.

If a global index partition is empty, then you can explicitly drop it by issuing the ALTER INDEX ... DROP PARTITION statement. But, if a global index partition contains data, then dropping the partition causes the next highest partition to be marked UNUSABLE. For example, you would like to drop the index partition P1, and P2 is the next highest partition. You must issue the following statements:
ALTER INDEX npr DROP PARTITION P1;
ALTER INDEX npr REBUILD PARTITION P2;

Note: You cannot drop the highest partition in a global index.

Truncating Partitions

Use the ALTER TABLE ... TRUNCATE PARTITION statement to remove all rows from a table partition. Truncating a partition is similar to dropping a partition, except that the partition is emptied of its data, but not physically dropped.

You cannot truncate an index partition. However, if local indexes are defined for the table, the ALTER TABLE ... TRUNCATE PARTITION statement truncates the matching partition in each local index. Unless you specify UPDATE INDEXES, any global indexes are marked UNUSABLE and must be rebuilt. (You cannot use UPDATE INDEXES for index-organized tables. Use UPDATE GLOBAL INDEXES instead.)

Truncating Table Partitions Containing Data and Global Indexes

Method 1
Leave the global indexes in place during the ALTER TABLE ... TRUNCATE PARTITION statement. In this example, table sales has a global index sales_area_ix, which is rebuilt.
ALTER TABLE sales TRUNCATE PARTITION dec98;
ALTER INDEX sales_area_ix REBUILD;
This method is most appropriate for large tables where the partition being truncated contains a significant percentage of the total data in the table.

Method 2
Run the DELETE statement to delete all rows from the partition before you issue the ALTER TABLE ... TRUNCATE PARTITION statement. The DELETE statement updates the global indexes, and also fires triggers and generates redo and undo logs.

For example, to truncate the first partition, run the following statements:
DELETE FROM sales PARTITION (dec98);
commit;
ALTER TABLE sales TRUNCATE PARTITION dec98;
This method is most appropriate for small tables, or for large tables when the partition being truncated contains a small percentage of the total data in the table.

Method 3
Specify UPDATE INDEXES in the ALTER TABLE statement. This causes the global index to be truncated at the time the partition is truncated.
ALTER TABLE sales TRUNCATE PARTITION dec98 UPDATE INDEXES;

Truncating a Partition Containing Data and Referential Integrity Constraints

If a partition contains data and has referential integrity constraints, then you cannot truncate the partition. If no other data is referencing any data in the partition to remove, then choose either of the following methods to truncate the table partition.

Method 1
Disable the integrity constraints, run the ALTER TABLE ... TRUNCATE PARTITION statement, then re-enable the integrity constraints. This method is most appropriate for large tables where the partition being truncated contains a significant percentage of the total data in the table. If there is still referencing data in other tables, then you must remove that data to be able to re-enable the integrity constraints.

Method 2
Issue the DELETE statement to delete all rows from the partition before you issue the ALTER TABLE ... TRUNCATE PARTITION statement. The DELETE statement enforces referential integrity constraints, and also fires triggers and generates redo and undo logs. Data in referencing tables is deleted if the foreign key constraints were created with the ON DELETE CASCADE option.

Note: You can substantially reduce the amount of logging by setting the NOLOGGING attribute (using ALTER TABLE ... MODIFY PARTITION ... NOLOGGING) for the partition before deleting all of its rows.
DELETE FROM sales partition (dec94);
commit;
ALTER TABLE sales TRUNCATE PARTITION dec94;
This method is most appropriate for small tables, or for large tables when the partition being truncated contains a small percentage of the total data in the table.

Truncating a Subpartition

You use the ALTER TABLE ... TRUNCATE SUBPARTITION statement to remove all rows from a subpartition of a composite partitioned table. Corresponding local index subpartitions are also truncated.
ALTER TABLE diving
TRUNCATE SUBPARTITION us_locations
DROP STORAGE;

How to alter/drop maxvalued table partitioned?

Method 1
Please have a look at section "Splitting a Partition of a Range-Partitioned Table" at Splitting Partitions.
Example:
ALTER TABLE TDH1 SPLIT PARTITION
TDH1_MAX AT (TO_DATE(' 2011-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
INTO (PARTITION TDH1_P26, PARTITION TDH1_MAX) UPDATE INDEXES PARALLEL;
Method 2
If the maxvalue partition does not contain ANY data at all, then the DBA can also explore the possibility of dropping the maxvalue partition, add new partitions using ADD PARTITION and then add the MAXVALUE partition again.
Method 2 would be faster. But it can be used ONLY IF MAXVALUE partition does not contain any data.
Method 3
If MAXVALUE partition contains data:

1) EXCHANGE the MAXVALUE partition with an empty table of matching structure
2) DROP the MAXVALUE partition
3) ADD new partitions.
4) ADD the MAXVALUE partition.
5) INSERT (with APPEND hint?) into the newly created partitions.

Only step 1 and step 5 will take time. Rest of the steps will be fast.
Note: Please test the approaches on UAT before trying it on production.

How to Drop Partitioned Tables

Please have a look at Dropping Partitioned Tables at "Dropping Partitioned Tables".

Updating Indexes Automatically

By default, many table maintenance operations on partitioned tables invalidate (mark UNUSABLE) the corresponding indexes or index partitions. You must then rebuild the entire index or, for a global index, each of its partitions. The database lets you override this default behavior if you specify UPDATE INDEXES in your ALTER TABLE statement for the maintenance operation. Specifying this clause tells the database to update the indexes at the time it executes the maintenance operation DDL statement.

If you issue DROP or TRUNCATE PARTITION statement and specify clause UPDATE INDEXES or UPDATE GLOBAL INDEXES, at the end of the operation Oracle updates global indexes, which could take time.
Without the clause UPDATE INDEXES or UPDATE GLOBAL INDEXES the global indexes need to be rebuilt.

Dropping/Truncating partitions in 12c

You can drop or truncate multiple partitions or subpartitions with a single statement.

Document 1482264.1 How to Drop/Truncate Multiple Partitions in Oracle 12C

=====================================================================

Comments

  1. Unknown5 July 2018 at 02:58

    good content,very clear and brief.thank you..

    ReplyDelete
  2. Unknown11 September 2018 at 19:26

    difference between normal table and index organized table info i want

    ReplyDelete

Post a Comment

Oracle DBA Information