To perform an archive of selected partitions, specify a conditional expression in the PARTITIONS WHERE option in an ARCHIVE script. This conditional expression should only reference the column(s) that determine the partitioning for the table being archived.
PARTITIONS WHERE Keyword
Use the PARTITIONS WHERE option to specify the conditional expression, which contains a definition of the rows to be archived. To be effective, limit this expression to the columns that determine the partitioning for the table to be archived.
These restrictions apply to the use of PARTITIONS WHERE:
- The object is an individual table (not a database).
- The source table has a PARTITIONS BY expression defined.
- The archive is an all-AMP archive (not a dictionary, cluster, or journal archive).
- The INDEXES option is not used.
- If the table belongs to a database that is specified in the ARCHIVE statement, the table is excluded from the database-level object (with EXCLUDE TABLES) and is individually specified.
- Any name specified in the conditional expression is within the table being specified. (Using table aliases and references to databases, tables, or views that are not specified within the target table result in an error.) It is recommended that the only referenced columns in the PARTITIONS WHERE condition be the partitioning columns or system-derived column PARTITION of the table. References to other columns do not contribute to partition elimination, and might accidentally qualify more partitions than intended.
Examples of ARCHIVE Keywords
This example executes an archive of all the rows in the TransactionHistory table in the SYSDBA database for the month of July 2002:
ARCHIVE DATA TABLES (SYSDBA.TransactionHistory) (PARTITIONS WHERE (! TransactionDate BETWEEN DATE ‘2002-07-01’ AND DATE ‘2002-07-31’ !) ) , RELEASE LOCK, FILE=ARCHIVE;