3.1.4. Particiones


Oracle Partitioning, presentado por primera vez en Oracle 8.0 en 1997, es una de las funcionalidades más importantes y exitosas de la base de datos Oracle que mejora el desempeño, la capacidad de administración y la disponibilidad de miles de aplicaciones. Oracle Database 11g introduce la octava generación de particionamiento y sigue ofreciendo una funcionalidad nueva, mejorada e innovadora; y nuevas técnicas de particionamiento que permiten a los clientes modelar más escenarios de negocio mientras un entorno nuevo y completo de automatización y asesoramiento de particiones permite el uso de Oracle Partitioning. Oracle Database 11g es la versión más amplia de particionamiento desde su primera presentación, y seguirá protegiendo la inversión de nuestros clientes en las funciones de particionamiento durante una década.

Beneficios del particionamiento

El particionamiento puede brindar grandes beneficios a una amplia variedad de aplicaciones al mejorar la capacidad de administración, el desempeño y la disponibilidad. No es inusual que el particionamiento mejore mucho más el desempeño de ciertas operaciones de mantenimiento y consultas.

Además, el particionamiento puede reducir enormemente el costo total de propiedad de los datos, al utilizar un enfoque de “archivo por niveles” para mantener la información relevante más antigua aún online en dispositivos de almacenamiento de bajo costo. Oracle Partitioning brinda un enfoque simple, efectivo, e incluso más avanzado al momento de considerar la Administración del Ciclo de Vida de la Información para grandes entornos.

El particionamiento también permite a los diseñadores y administradores de base de datos abordar algunos de los problemas más difíciles planteados por las aplicaciones de vanguardia. Es una herramienta clave para crear sistemas de múltiples terabytes o sistemas con requisitos de disponibilidad extremadamente altos.

Fundamentos del Particionamiento

El particionamiento permite subdividir una tabla, un índice o una tabla organizada por índices en partes más pequeñas. Cada parte del objeto de base de datos se denomina partición. Cada partición tiene su propio nombre, y puede, opcionalmente, tener sus propias características de almacenamiento. Desde la perspectiva de un administrador de base de datos, un objeto particionado tiene múltiples partes que pueden administrarse ya sea de manera conjunta o individual. Esto da al administrador una flexibilidad considerable en la administración del objeto particionado. No obstante, desde la perspectiva de la aplicación, una tabla particionada es idéntica a una tabla no particionada; no se necesitan modificaciones cuando se accede a una tabla particionada utilizando comandos SQL DML. Figura 1

Figura 1. Perspectiva del DBA y la aplicación de una tabla particionada

Los objetos de base de datos - tablas, índices y tablas organizadas por índices – son particionadas utilizando una 'clave de partición', un grupo de columnas que determinan en qué partición residirá una fila determinada. Por ejemplo, la tabla de ventas mostrada en la figura 1 se particiona por fecha de ventas utilizando una estrategia de particionamiento mensual; las tablas aparecen ante la aplicación como tablas únicas y “normales”. No obstante, el DBA puede administrar cada partición mensual individualmente, posiblemente utilizando distintos niveles de almacenamiento, aplicando la compresión de la tabla en los datos más antiguos, o almacenar todos los rangos de datos más antiguos en espacios de tabla con modo 'solo lectura'.

Independientemente de la estrategia de particionamiento de índices seleccionada, un índice puede acoplarse o no a la estrategia de particionamiento subyacente de la tabla subyacente. La estrategia de particionamiento de índices adecuada es elegida sobre la base de los requisitos comerciales, haciendo que el particionamiento se ajuste perfectamente para soportar cualquier clase de aplicación. Oracle Database 11g distingue tres tipos de índices particionados.

Asimismo, Oracle brinda un grupo integral de comandos SQL para administrar las tablas de particionamiento. Estos incluyen comandos para incorporar nuevas particiones, extraer, dividir, mover, fusionar, truncar y posiblemente comprimir particiones.

Particionamiento para la capacidad de administración

Oracle Partitioning permite que las tablas y lo índices se particionen en unidades más pequeñas y administrables, brindando a los administradores de bases de datos la capacidad de focalizarse en el enfoque de "dividir y conquistar " para la administración de datos.

Con el particionamiento, las operaciones de mantenimiento pueden focalizarse a partes particulares de tablas. Por ejemplo, un administrador de base de datos podría comprimir una sola partición que contenga los datos de una tabla del año 2006, en lugar de comprimir toda la tabla. Para las operaciones de mantenimiento en todo un objeto de la base de datos, es posible realizar estas operaciones por partición, dividiendo así el proceso de mantenimiento en partes más manejables.

Un uso típico del particionamiento para la capacidad de administración es soportar el proceso de carga de 'ventanas rotativas' en el depósito de datos. Supongamos que un DBA carga nuevos datos en una tabla semanalmente. Esa tabla podría ser particionada por rangos de manera que cada partición contenga una semana de datos. El proceso de carga consiste 1simplemente en la incorporación de una nueva partición.

Agregar una sola partición es mucho más efectivo que modificar toda la tabla, ya que el DBA no necesita modificar ninguna otra partición.

Otra ventaja de utilizar el particionamiento surge al momento de eliminar datos; se puede extraer una partición completa, lo cual es más efectivo y rápido que eliminar cada fila individualmente.

Particionamiento para el desempeño

Al limitar la cantidad de datos a ser examinados o sobre los cuales se trabajará, el particionamiento ofrece una cantidad de beneficios de desempeño. Estas características incluyen:

Particionamiento para la Disponibilidad

Los objetos de base de datos particionadas ofrecen independencia de particiones. Esta característica de independencia de particiones puede ser una parte importante de una estrategia de alta disponibilidad. Por ejemplo, si una partición de una tabla particionada no está disponible, todas las demás particiones de la tabla permanecen en línea y están disponibles. La aplicación puede continuar ejecutando consultas y realizando transacciones frente a esta tabla particionada, y estas operaciones de base de datos se ejecutarán exitosamente si no necesitan acceder a la partición que no se encuentra disponible. El administrador de base de datos puede especificar que cada partición se almacene en un espacio de tabla separado; esto permitiría al administrador realizar operaciones de backup y recuperación en cada partición individual, independientemente de otras particiones en la tabla. Por consiguiente, en caso de que ocurra un desastre, la base de datos podría recuperarse solo con las particiones que incluyen los datos activos, y luego los datos inactivos de las demás particiones podrían recuperarse a su debido tiempo, reduciendo así el tiempo de baja del sistema.

Asimismo, el particionamiento puede reducir el tiempo de baja programado. Las ventajas de desempeño brindadas por el particionamiento pueden permitir a los administradores de base de datos completar las operaciones de mantenimiento sobre los objetos de grandes bases en ventanas relativamente pequeñas.

Particinamiento - modelo para su empresa-

Oracle Database 11g ofrece el conjunto más completo de estrategias de particionamiento, permitiendo al cliente alinear de manera óptima la subdivisión de datos con los actuales requerimientos de negocio. Todas las estrategias de particionamiento dependen de métodos fundamentales de distribución de datos que pueden ser utilizados para una sola tabla (un solo nivel) o un conjunto de tablas particionadas. Asimismo, Oracle brinda una variedad de extensiones de particionamiento, aumentando la flexibilidad para la selección de la clave de partición, brindando características para la creación de partición automática a medida que sea necesario, y ofreciendo as1esoramiento de estrategias de partición para objetos no particionados.

Estrategias básicas de particionamiento

Oracle Partitioning ofrece tres métodos de distribución de datos fundamentales que regulan cómo se ubicarán los datos en las distintas particiones individuales, a saber:

Utilizando los métodos de distribución de datos antes mencionados, una tabla puede particionarse ya sea como una única tabla o una tabla particionada compuesta:

Extensiones de Particionamiento

Además de las estrategias básicas de particionamiento, Oracle brinda extensiones de particionamiento. Las extensiones en Oracle Database 11g se focalizan principalmente en dos objetivos:

  1. Mejorar significativamente la capacidad de administración de una tabla particionada.
  2. Extender la flexibilidad para definir una clave de particionamiento.

Las extensiones se denominan:

Particionamiento por Intervalos: Una nueva estrategia de particionamiento en Oracle Database 11g, el particionamiento por intervalos, extiende las capacidades del método de rangos para definir los rangos igualmente particionados utilizando una definición de intervalo. En vez de especificar los rangos individuales, Oracle creará cualquier partición automáticamente a medida que sea necesario cada vez que los datos para una partición se introduzcan por primera vez. El particionamiento por intervalos mejora notablemente la capacidad de administración de una tabla particionada. Por ejemplo, una tabla particionada podría definirse de modo que Oracle cree una nueva partición para cada mes del año calendario; luego se crea automáticamente una partición para 'September 2007' tan pronto como se ingrese el primer registro de ese mes en la base de datos.

Las técnicas disponibles para una tabla particionada por intervalos son: Intervalo, Intervalo-Lista, Intervalo-Elección Arbitraria, e Intervalo-Rango.

Particionamiento REF: Oracle Database 11g permite el particionamiento de una tabla aprovechando una relación principal/secundario existente. La estrategia de particionamiento de la tabla principal es inherente a su tabla secundaria sin la necesidad de almacenar las columnas clave de particionamiento de la tabla principal en la tabla secundaria. Sin el Particionamiento REF usted debe duplicar todas las columnas clave de particionamiento desde la tabla principal a la secundaria si desea aprovechar la misma estrategia de partición. El Particionamiento REF, por otra parte, permite particionar tablas naturalmente de acuerdo con el modelo de datos lógicos sin necesidad de almacenar las columnas clave de particionamiento, reduciendo así los gastos generales manuales de desnormalización y ahorro de espacio. El Particionamiento REF también hereda, de manera transparente todas las operaciones de mantenimiento que cambian la forma lógica de una tabla desde la tabla principal hasta la tabla secundaria. Asimismo, el Particionamiento REF permite automáticamente las uniones por partición de la tabla principal y secundaria, mejorando el desempeño para esta operación. Por ejemplo, la tabla principal ORDERS se particiona por Rango en la columna ORDER_DATE; su tabla secundaria ORDER ITEMS no contiene la columna ORDER_DATE pero puede ser particionada por referencia a la tabla ORDERS. Si la tabla ORDERS se particiona por mes, todos los objetos del pedido en 'Jan-2007' se almacenarán en una sola partición en la tabla ORDER ITEMS, particionada del mismo modo en la tabla principal ORDERS. Si una partición 'Feb-2007' se agrega a la tabla ORDERS

Oracle agregará transparentemente la partición equivalente a la tabla ORDER ITEMS.

Todas las estrategias básicas de particionamiento están disponibles para el Particionamiento REF.

Particionamiento basado en Columnas Virtuales: En versiones anteriores de Oracle, una tabla solo podía ser particionada si la clave de partición existía físicamente en la tabla. Las columnas virtuales, una nueva funcionalidad de Oracle Database 11g, eliminan esa restricción y permite que la clave de particionamiento se defina por una expresión, utilizando una o más columnas existentes de una tabla, y almacenando la expresión como metadatos solamente.

El particionamiento ha sido mejorado para permitir que una estrategia de partición se defina en columnas virtuales, logrando así una relación más completa de los requerimientos de negocios. No es inusual ver columnas sobrecargadas de información; por ejemplo, un ID de 10 dígitos puede incluir información de cuenta de la sucursal con la forma de los tres primeros dígitos. Con la extensión del Particionamiento basado en columnas virtuales, la tabla ACCOUNTS contiene una columna ACCOUNT_ID que puede ser extendida a una columna virtual (derivada) ACCOUNT_BRANCH que se deriva de los primeros tres dígitos de la columna ACCOUNT_ID que se convierte en la clave de particionamiento para esta tabla.

El Particionamiento basado en columnas virtuales es soportado con las estrategias básicas de particionamiento.

Asesor de Particionamiento

SQL Access Advisor en Oracle Database 11g ha sido mejorado para generar recomendaciones de particionamiento, además de las que ya se ofrecen para los índices, las vistas materializadas y los registros de vistas materializadas. Las recomendaciones generadas por SQL Access Advisor –ya sea para el Particionamiento individual o integral- mostrarán las ganancias anticipadas de desempeño que se obtendrán en caso de ser implementadas. El script generado puede implementarse manualmente o ser colocado en una cola dentro de Oracle Enterprise Manager.

Con la extensión del asesor de partición, los clientes no solo pueden obtener una recomendación específicamente para el particionamiento sino que también pueden obtener una recomendación holística más integral de SQL Access Advisor, para mejorar el desempeño colectivo de todas las sentencias SQL.

El Asesor de Particionamiento, integrado en SQL Access Advisor, es parte del Paquete de Ajuste de Oracle, una opción con licencia adicional. Puede utilizarse dentro de Enterprise Manager o por medio de una interface de líneas de comando.

Introducción a las Estrategias de Particionamiento y Extensiones

Estategía de particionamiento Distribución de datos Ejemplo de casos de negocio
Rangos De acuerdo con rangos de valores consecutivos. Rango de tablas de pedidos particionados por order_date
Listas De acuerdo con listados de valores desordenados Listado de tablas de pedidos particionado por país
Elección arbitraria De acuerdo con un algoritmo de elección arbitraria Elección arbitraria de tablas de pedidos particionadas por customer_id

Particionamiento Compuesto

  • Rango-Rango
  • Rango-Lista
  • Rango-Elección arbitraria
  • Lista-Lista
  • Lista-Rango
  • Lista-Elección arbitraria
De acuerdo con una combinación de dos de las técnicas básicas antes mencionadas de Particionamiento por Rangos, Listas, Elecciones arbitrarias e Intervalos

Las tablas de pedidos se particionan por rangos mediante order_date y se subparticionan mediante la elección arbitraria en customer_id

Las tablas de pedidos se particionan por rangos mediante order_date y se subparticionan mediante rango en shipment_date

Además de las estrategias de particionamiento disponibles, Oracle Database 11g ofrece las siguientes extensiones de particionamiento

Extensión de particionamiento Clave de particionamiento Ejemplo del caso de negocios

Particionamiento por Intervalos

Intervalo

Intervalo-Rango

Intervalo-Lista

Intervalo-Elección Arbitraria

Extensión de la Partición por Rango. Definido por un intervalo que brinda rangos de igual ancho (equi-width). Con la excepción de la primera partición, todas las particiones se crean automáticamente a medida que sea necesario cuando se presentan los datos de relación.

Tabla de pedidos particionada por order_date con un intervalo diario predefinido, que comienza con '01-Jan-2007'
Particionamiento REF

El particionamiento para una tabla secundaria es heredado de la tabla primaria a través de una clave primaria – relación de clave secundaria. Las claves de particionamiento no se almacenan en columnas reales en la tabla secundaria.

El rango de tablas de pedidos (Principal) se particiona por order_date y hereda la técnica de particionamiento para las tablas de las líneas de pedidos (secundarias).

La Columna order_date solo está presente en la tabla de pedidos principal.

Particionamiento basado en columnas virtuales

El particionamiento se define por una de las técnicas de partición antes mencionadas y la clave de particionamiento s1e basa en una columna virtual. Las columnas virtuales no se almacenan en el disco y solo existen como metadatos.

La tabla de pedidos posee una columna virtual que deriva de la región de ventas sobre la base de los primeros tres dígitos del número de cuenta del cliente. La tabla de pedidos luego se particiona por región de ventas.


Administración del ciclo de vida de la información con particionamiento

El actual desafío de almacenar una gran cantidad de datos al menor costo posible puede abordarse óptimamente utilizando Oracle Partitioning. La independencia de las particiones individuales es el activador clave para lidiar con la porción online de una estrategia de “archivo por niveles”. Especialmente en tablas que contienen datos históricos, la importancia y el patrón de acceso de los datos depende fuertemente de la antigüedad de los datos. El particionamiento permite a las particiones individuales (o grupos de particiones) ser almacenadas en diferentes niveles de almacenamiento, ofreciendo distintos precios y atributos físicos. Por ejemplo, una tabla de Pedidos que contiene el valor de 2 años de información podría tener solo el trimestre más reciente almacenado en un nivel de almacenamiento costoso y de alta calidad, y tener las tablas restantes (casi el 90% de la información) en un nivel de almacenamiento de bajo costo. A través de Oracle Partitioning, los costos de almacenamiento se reducen por factores (son comunes los ahorros de costo del 50% o más), sin afectar el acceso de los usuarios finales, optimizando así el costo de propiedad para la información almacenada.

Oracle ILM Assistant, que es una herramienta disponible en forma gratuita y puede descargarse desde OTN, muestra estos ahorros de costo y cómo particionar una tabla, y aconseja cuando es el momento de mover las particiones a otros niveles de almacenamiento.

Creating Range-Partitioned Tables and Global Indexes

The PARTITION BY RANGE clause of the CREATE TABLE statement specifies that the table or index is to be range-partitioned. The PARTITION clauses identify the individual partition ranges, and the optional subclauses of a PARTITION clause can specify physical and other attributes specific to a partition segment. If not overridden at the partition level, partitions inherit the attributes of their underlying table.

Creating a Range-Partitioned Table

Example 1 creates a table of four partitions, one for each quarter of sales. The columns sale_year, sale_month, and sale_day are the partitioning columns, while their values constitute the partitioning key of a specific row. The VALUES LESS THAN clause determines the partition bound: rows with partitioning key values that compare less than the ordered list of values specified by the clause are stored in the partition. Each partition is given a name (sales_q1, sales_q2, ...), and each partition is contained in a separate tablespace (tsa, tsb, ...).

Example 1 Creating a range-partitioned table

CREATE TABLE sales
  ( prod_id       NUMBER(6),
    cust_id       NUMBER,
    time_id       DATE,
    channel_id    CHAR(1),
    promo_id      NUMBER(6),
    quantity_sold NUMBER(3),
    amount_sold   NUMBER(10,2)
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
     TABLESPACE tsa.
   PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
     TABLESPACE tsb,
   PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
     TABLESPACE tsc,
   PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
     TABLESPACE tsd
 );

A row with time_id=17-MAR-2006 would be stored in partition sales_q1_2006.

In Example 2, more complexity is added to the example presented earlier for a range-partitioned table. Storage parameters and a LOGGING attribute are specified at the table level. These replace the corresponding defaults inherited from the tablespace level for the table itself, and are inherited by the range partitions. However, because there was little business in the first quarter, the storage attributes for partition sales_q1_2006 are made smaller. The ENABLE ROW MOVEMENT clause is specified to allow the automatic migration of a row to a new partition if an update to a key value is made that would place the row in a different partition.

Example 2 Creating a range-partitioned table with ENABLE ROW MOVEMENT

CREATE TABLE sales
  ( prod_id       NUMBER(6),
    cust_id       NUMBER,
    time_id       DATE,
    channel_id    CHAR(1),
    promo_id      NUMBER(6),
    quantity_sold NUMBER(3),
    amount_sold   NUMBER(10,2)
  )
 STORAGE (INITIAL 100K NEXT 50K) LOGGING
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
     TABLESPACE tsa STORAGE (INITIAL 20K NEXT 10K),
   PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
     TABLESPACE tsb,
   PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
     TABLESPACE tsc,
   PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
    TABLESPACE tsd
 )
 ENABLE ROW MOVEMENT;

Creating a Range-Partitioned Global Index

The rules for creating range-partitioned global indexes are similar to those for creating range-partitioned tables. Example 4-3 creates a range-partitioned global index on sale_month for the tables created in the previous examples. Each index partition is named but is stored in the default tablespace for the index.

Example 3 Creating a range-partitioned global index table

CREATE INDEX amount_sold_ix ON sales(amount_sold)
   GLOBAL PARTITION BY RANGE(sale_month)
      ( PARTITION p_100 VALUES LESS THAN (100),
        PARTITION p_1000 VALUES LESS THAN (1000),
        PARTITION p_10000 VALUES LESS THAN (10000),
        PARTITION p_100000 VALUES LESS THAN (100000),
        PARTITION p_1000000 VALUES LESS THAN (1000000),
        PARTITION p_greater_than_1000000 VALUES LESS THAN (maxvalue)
      );

Note: If your enterprise has databases using different character sets, use caution when partitioning on character columns, because the sort sequence of characters is not identical in all character sets. For more information, see Oracle Database Globalization Support Guide.

Creating Interval-Partitioned Tables

The INTERVAL clause of the CREATE TABLE statement establishes interval partitioning for the table. You must specify at least one range partition using the PARTITION clause. The range partitioning key value determines the high value of the range partitions, which is called the transition point, and the database automatically creates interval partitions for data beyond that transition point. The lower boundary of every interval partition is the non-inclusive upper boundary of the previous range or interval partition.

For example, if you create an interval partitioned table with monthly intervals and the transition point is at January 1, 2010, then the lower boundary for the January 2010 interval is January 1, 2010. The lower boundary for the July 2010 interval is July 1, 2010, regardless of whether the June 2010 partition was previously created. Note, however, that using a date where the high or low bound of the partition would be out of the range set for storage causes an error. For example, TO_DATE('9999-12-01', 'YYYY-MM-DD') causes the high bound to be 10000-01-01, which would not be storable if 10000 is out of the legal range.

For interval partitioning, the partitioning key can only be a single column name from the table and it must be of NUMBER or DATE type. The optional STORE IN clause lets you specify one or more tablespaces into which the database stores interval partition data using a round-robin algorithm for subsequently created interval partitions.

Example 4 specifies four partitions with varying interval widths. It also specifies that above the transition point of January 1, 2010, partitions are created with an interval width of one month.

Example 4 Creating an interval-partitioned table

CREATE TABLE interval_sales
    ( prod_id        NUMBER(6),
      cust_id        NUMBER,
      time_id        DATE,
      channel_id     CHAR(1),
     promo_id       NUMBER(6),
     quantity_sold  NUMBER(3),
     amount_sold    NUMBER(10,2)
    ) 
  PARTITION BY RANGE (time_id) 
  INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
    ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
      PARTITION p1 VALUES1 LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY')),
      PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
      PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );

The high bound of partition p3 represents the transition point. p3 and all partitions below it (p0, p1, and p2 in this example) are in the range section while all partitions above it fall into the interval section.

Creating Hash-Partitioned Tables and Global Indexes

The PARTITION BY HASH clause of the CREATE TABLE statement identifies that the table is to be hash-partitioned. The PARTITIONS clause can then be used to specify the number of partitions to create, and optionally, the tablespaces to store them in. Alternatively, you can use PARTITION clauses to name the individual partitions and their tablespaces.

The only attribute you can specify for hash partitions is TABLESPACE. All of the hash partitions of a table must share the same segment attributes (except TABLESPACE), which are inherited from the table level.

Creating a Hash Partitioned Table

Example 5 creates a hash-partitioned table. The partitioning column is id, four partitions are created and assigned system generated names, and they are placed in four named tablespaces (gear1, gear2, ...).

Example 5 Creating a hash-partitioned table

CREATE TABLE scubagear
     (id NUMBER,
      name VARCHAR2 (60))
   PARTITION BY HASH (id)
   PARTITIONS 4 
   STORE IN (gear1, gear2, gear3, gear4);

The following examples illustrate two methods of creating a hash-partitioned table named dept. In the first example the number of partitions is specified, but system generated names are assigned to them and they are stored in the default tablespace of the table.

CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))
     PARTITION BY HASH(deptno) PARTITIONS 16;

In the following example, names of individual partitions, and tablespaces in which they are to reside, are specified. The initial extent size for each hash partition (segment) is also explicitly stated at the table level, and all partitions inherit this attribute.

CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))
     STORAGE (INITIAL 10K)
     PARTITION BY HASH(deptno)
       (PARTITION p1 TABLESPACE ts1, PARTITION p2 TABLESPACE ts2,
        PARTITION p3 TABLESPACE ts1, PARTITION p4 TABLESPACE ts3);

If you create a local index for this table, the database constructs the index so that it is equipartitioned with the underlying table. The database also ensures that the index is maintained automatically when maintenance operations are performed on the underlying table. The following is an example of creating a local index on the table dept:

CREATE INDEX loc_dept_ix ON dept(deptno) LOCAL;

You can optionally name the hash partitions and tablespaces into which the local index partitions are to be stored, but if you do not do so, then the database uses the name of the corresponding base partition as the index partition name, and stores the index partition in the same tablespace as the table partition.

Creating a Hash-Partitioned Global Index

Hash-partitioned global indexes can improve the performance of indexes where a small number of leaf blocks in the index have high contention in multiuser OLTP environments. Hash-partitioned global indexes can also limit the impact of index skew on monotonously increasing column values. Queries involving the equality and IN predicates on the index partitioning key can efficiently use hash-partitioned global indexes.

The syntax for creating a hash partitioned global index is similar to that used for a hash partitioned table. For example, the statement in Example 6 creates a hash-partitioned global index:

Example 6 Creating a hash-partitioned global index

CREATE INDEX hgidx ON tab (c1,c2,c3) GLOBAL
     PARTITION BY HASH (c1,c2)
     (PARTITION p1  TABLESPACE tbs_1,
      PARTITION p2  TABLESPACE tbs_2,
      PARTITION p3  TABLESPACE tbs_3,
      PARTITION p4  TABLESPACE tbs_4);

Creating List-Partitioned Tables

The semantics for creating list partitions are very similar to those for creating range partitions. However, to create list partitions, you specify a PARTITION BY LIST clause in the CREATE TABLE statement, and the PARTITION clauses specify lists of literal values, which are the discrete values of the partitioning columns that qualify rows to be included in the partition. For list partitioning, the partitioning key can only be a single column name from the table.

Available only with list partitioning, you can use the keyword DEFAULT to describe the value list for a partition. This identifies a partition that accommodates rows that do not map into any of the other partitions.

As with range partitions, optional subclauses of a PARTITION clause can specify physical and other attributes specific to a partition segment. If not overridden at the partition level, partitions inherit the attributes of their parent table.

Example 7 creates a list-partitioned table. It creates table q1_sales_by_region which is partitioned by regions consisting of groups of U.S. states.

Example 7 Creating a list-partitioned table

CREATE TABLE q1_sales_by_region
      (deptno number, 
       deptname varchar2(20),
       quarterly_sales number(10, 2),
       state varchar2(2))
   PARTITION BY LIST (state)
      (PARTITION q1_northwest VALUES ('OR', 'WA'),
       PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),
       PARTITION q1_northeast VALUES  ('NY', 'VM', 'NJ'),
       PARTITION q1_southeast VALUES ('FL', 'GA'),
       PARTITION q1_northcentral VALUES ('SD', 'WI'),
       PARTITION q1_southcentral VALUES ('OK', 'TX'));

A row is mapped to a partition by checking whether the value of the partitioning column for a row matches a value in the value list that describes the partition.

For example, some sample rows are inserted as follows:

Unlike range partitioning, with list partitioning, there is no apparent sense of order between partitions. You can also specify a default partition into which rows that do not map to any other partition are mapped. If a default partition were specified in the preceding example, the state CA would map to that partition.

Example 8 creates table sales_by_region and partitions it using the list method. The first two PARTITION clauses specify physical attributes, which override the table-level defaults. The remaining PARTITION clauses do not specify attributes and those partitions inherit their physical attributes from table-level defaults. A default partition is also specified.

Example 8 Creating a list-partitioned table with a default partition

CREATE TABLE sales_by_region (item# INTEGER, qty INTEGER, 
             store_name VARCHAR(30), state_code VARCHAR(2),
             sale_date DATE)
     STORAGE(INITIAL 10K NEXT 20K) TABLESPACE tbs5 
     PARTITION BY LIST (state_code) 
     (
     PARTITION region_east
        VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ')
        STORAGE (INITIAL 8M) 
        TABLESPACE tbs8,
     PARTITION region_west
        VALUES ('CA','AZ','NM','OR','WA','UT','NV','CO')
        NOLOGGING,
     PARTITION region_south
        VALUES ('TX','KY','TN','LA','MS','AR','AL','GA'),
     PARTITION region_central 
        VALUES ('OH','ND','SD','MO','IL','MI','IA'),
     PARTITION region_null
        VALUES (NULL),
     PARTITION region_unknown
        VALUES (DEFAULT)
     );

Creating Reference-Partitioned Tables

To create a reference-partitioned table, you specify a PARTITION BY REFERENCE clause in the CREATE TABLE statement. This clause specifies the name of a referential constraint and this constraint becomes the partitioning referential constraint that is used as the basis for reference partitioning in the table. The referential constraint must be enabled and enforced.

As with other partitioned tables, you can specify object-level default attributes, and you can optionally specify partition descriptors that override the object-level defaults on a per-partition basis.

Example 9 creates a parent table orders which is range-partitioned on order_date. The reference-partitioned child table order_items is created with four partitions, Q1_2014, Q2_2014, Q3_2014, and Q4_2014, where each partition contains the order_items rows corresponding to orders in the respective parent partition.

Example 9 Creating reference-partitioned tables

CREATE TABLE orders
    ( order_id           NUMBER(12),
      order_date         TIMESTAMP WITH LOCAL TIME ZONE,
      order_mode         VARCHAR2(8),
      customer_id        NUMBER(6),
      order_status       NUMBER(2),
      order_total        NUMBER(8,2),
      sales_rep_id       NUMBER(6),
      promotion_id       NUMBER(6),
      CONSTRAINT orders_pk PRIMARY KEY(order_id)
    )
  PARTITION BY RANGE(order_date)
    ( PARTITION Q1_2015 VALUES LESS THAN (TO_DATE('01-APR-2014','DD-MON-YYYY')),
      PARTITION Q2_2014 VALUES LESS THAN (TO_DATE('01-JUL-2014','DD-MON-YYYY')),
      PARTITION Q3_2014 VALUES LESS THAN (TO_DATE('01-OCT-2014','DD-MON-YYYY')),
      PARTITION Q4_2014 VALUES LESS THAN (TO_DATE('01-JAN-2015','DD-MON-YYYY'))
    );

CREATE TABLE order_items
    ( order_id           NUMBER(12) NOT NULL,
      line_item_id       NUMBER(3)  NOT NULL,
      product_id         NUMBER(6)  NOT NULL,
      unit_price         NUMBER(8,2),
      quantity           NUMBER(8),
      CONSTRAINT order_items_fk
      FOREIGN KEY(order_id) REFERENCES orders(order_id)
    )
    PARTITION BY REFERENCE(order_items_fk);

If partition descriptors are provided, then the number of partitions described must exactly equal the number of partitions or subpartitions in the referenced table. If the parent table is a composite partitioned table, then the table has one partition for each subpartition of its parent; otherwise the table has one partition for each partition of its parent.

Partition bounds cannot be specified for the partitions of a reference-partitioned table.

The partitions of a reference-partitioned table can be named. If a partition is not explicitly named, then it inherits its name from the corresponding partition in the parent table, unless this inherited name conflicts with an existing explicit name. In this case, the partition has a system-generated name.

Partitions of a reference-partitioned table collocate with the corresponding partition of the parent table, if no explicit tablespace is specified for the reference-partitioned table's partition.

Creating Composite Partitioned Tables

To create a composite partitioned table, you start by using the PARTITION BY [RANGE | LIST] clause of a CREATE TABLE statement. Next, you specify a SUBPARTITION BY [RANGE | LIST | HASH] clause that follows similar syntax and rules as the PARTITION BY [RANGE | LIST | HASH] clause. The individual PARTITION and SUBPARTITION or SUBPARTITIONS clauses, and optionally a SUBPARTITION TEMPLATE clause, follow.

Creating Composite Range-Hash Partitioned Tables

The statement in Example 4-10 creates a range-hash partitioned table. Four range partitions are created, each containing eight subpartitions. Because the subpartitions are not named, system generated names are assigned, but the STORE IN clause distributes them across the 4 specified tablespaces (ts1, ...,ts4).

Example 10 Creating a composite range-hash partitioned table

CREATE TABLE sales
  ( prod_id       NUMBER(6),
    cust_id       NUMBER,
    time_id       DATE,
    channel_id    CHAR(1),
    promo_id      NUMBER(6),
    quantity_sold NUMBER(3),
    amount_sold   NUMBER(10,2)
  )
 PARTITION BY RANGE (time_id) SUBPARTITION BY HASH (cust_id)
  SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
 );

The partitions of a range-hash partitioned table are logical structures only, because their data is stored in the segments of their subpartitions. As with partitions, these subpartitions share the same logical attributes. Unlike range partitions in a range-partitioned table, the subpartitions cannot have different physical attributes from the owning partition, although they are not required to reside in the same tablespace.

Attributes specified for a range partition apply to all subpartitions of that partition. You can specify different attributes for each range partition, and you can specify a STORE IN clause at the partition level if the list of tablespaces across which the subpartitions of that partition should be spread is different from those of other partitions. All of this is illustrated in the following example.

CREATE TABLE emp (deptno NUMBER, empname VARCHAR(32), grade NUMBER)   
     PARTITION BY RANGE(deptno) SUBPARTITION BY HASH(empname)
        SUBPARTITIONS 8 STORE IN (ts1, ts3, ts5, ts7)
    (PARTITION p1 VALUES LESS THAN (1000),
     PARTITION p2 VALUES LESS THAN (2000)
        STORE IN (ts2, ts4, ts6, ts8),
     PARTITION p3 VALUES LESS THAN (MAXVALUE)
       (SUBPARTITION p3_s1 TABLESPACE ts4,
        SUBPARTITION p3_s2 TABLESPACE ts5));

To learn how using a subpartition template can simplify the specification of a composite partitioned table, see "Using Subpartition Templates to Describe Composite Partitioned Tables".

The following statement is an example of creating a local index on the emp table where the index segments are spread across tablespaces ts7, ts8, and ts9.

This local index is equipartitioned with the base table as follows:

Creating Composite Range-List Partitioned Tables

The range partitions of a range-list composite partitioned table are described as for non-composite range partitioned tables. This enables optional subclauses of a PARTITION clause to specify physical and other attributes, including tablespace, specific to a partition segment. If not overridden at the partition level, partitions inherit the attributes of their underlying table.

The list subpartition descriptions, in the SUBPARTITION clauses, are described as for non-composite list partitions, except the only physical attribute that can be specified is a tablespace (optional). Subpartitions inherit all other physical attributes from the partition description.

Example 11 illustrates how range-list partitioning might be used. The example tracks sales data of products by quarters and within each quarter, groups it by specified states.

Example 11 Creating a composite range-list partitioned table

CREATE TABLE quarterly_regional_sales
      (deptno number, item_no varchar2(20),
       txn_date date, txn_amount number, state varchar2(2))
  TABLESPACE ts4
  PARTITION BY RANGE (txn_date)
    SUBPARTITION BY LIST (state)
      (PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))
         (SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
          SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
          SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'),
          SUBPARTITION q1_1999_southcentral VALUES ('OK', 'TX')
         ),
       PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY'))
         (SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),
          SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),
          SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),
          SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')
         ),
       PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY'))
         (SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),
          SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION q3_1999_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION q3_1999_southeast VALUES ('FL', 'GA'),
          SUBPARTITION q3_1999_northcentral VALUES ('SD', 'WI'),
          SUBPARTITION q3_1999_southcentral VALUES ('OK', 'TX')
         ),
       PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))
         (SUBPARTITION q4_1999_northwest VALUES ('OR', 'WA'),
          SUBPARTITION q4_1999_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION q4_1999_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION q4_1999_southeast VALUES ('FL', 'GA'),
          SUBPARTITION q4_1999_northcentral VALUES ('SD', 'WI'),
          SUBPARTITION q4_1999_southcentral VALUES ('OK', 'TX')
         )
      );

A row is mapped to a partition by checking whether the value of the partitioning column for a row falls within a specific partition range. The row is then mapped to a subpartition within that partition by identifying the subpartition whose descriptor value list contains a value matching the subpartition column value.

For example, some sample rows are inserted as follows:

The partitions of a range-list partitioned table are logical structures only, because their data is stored in the segments of their subpartitions. The list subpartitions have the same characteristics as list partitions. You can specify a default subpartition, just as you specify a default partition for list partitioning.

The following example creates a table that specifies a tablespace at the partition and subpartition levels. The number of subpartitions within each partition varies, and default subpartitions are specified.

CREATE TABLE sample_regional_sales
      (deptno number, item_no varchar2(20),
       txn_date date, txn_amount number, state varchar2(2))
  PARTITION BY RANGE (txn_date)
    SUBPARTITION BY LIST (state)
      (PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))
          TABLESPACE tbs_1
         (SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
          SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
          SUBPARTITION q1_others VALUES (DEFAULT) TABLESPACE tbs_4
         ),
       PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY'))
          TABLESPACE tbs_2
         (SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),
          SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),
          SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),
          SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')
         ),
       PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY'))
          TABLESPACE tbs_3
         (SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),
          SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION q3_others VALUES (DEFAULT) TABLESPACE tbs_4
         ),
       PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))
          TABLESPACE tbs_4
      );

This example results in the following subpartition descriptions:

Creating Composite Range-Range Partitioned Tables

The range partitions of a range-range composite partitioned table are similar to non-composite range partitioned tables. This enables optional subclauses of a PARTITION clause to specify physical and other attributes, including tablespace, specific to a partition segment. If not overridden at the partition level, then partitions inherit the attributes of their underlying table.

The range subpartition descriptions, in the SUBPARTITION clauses, are similar to non-composite range partitions, except the only physical attribute that can be specified is an optional tablespace. Subpartitions inherit all other physical attributes from the partition description.

Example 12 illustrates how range-range partitioning might be used. The example tracks shipments. The service level agreement with the customer states that every order is delivered in the calendar month after the order was placed. The following types of orders are identified:

Example 12 Creating a composite range-range partitioned table

CREATE TABLE shipments
( order_id      NUMBER NOT NULL,
  order_date    DATE NOT NULL,
  delivery_date DATE NOT NULL,
  customer_id   NUMBER NOT NULL,
  sales_amount  NUMBER NOT NULL
)
PARTITION BY RANGE (order_date)
SUBPARTITION BY RANGE (delivery_date)
( PARTITION p_2006_jul VALUES LESS THAN (TO_DATE('01-AUG-2006','dd-MON-yyyy'))
  ( SUBPARTITION p06_jul_e VALUES LESS THAN (TO_DATE('15-AUG-2006','dd-MON-yyyy')),
    SUBPARTITION p06_jul_a VALUES LESS THAN (TO_DATE('01-SEP-2006','dd-MON-yyyy')),
    SUBPARTITION p06_jul_l VALUES LESS THAN (MAXVALUE)
  ),
  PARTITION p_2006_aug VALUES LESS THAN (TO_DATE('01-SEP-2006','dd-MON-yyyy'))
  ( SUBPARTITION p06_aug_e VALUES LESS THAN (TO_DATE('15-SEP-2006','dd-MON-yyyy')).
    SUBPARTITION p06_aug_a VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy')).
    SUBPARTITION p06_aug_l VALUES LESS THAN (MAXVALUE)
  ),
  PARTITION p_2006_sep VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
  ( SUBPARTITION p06_sep_e VALUES LESS THAN (TO_DATE('15-OCT-2006','dd-MON-yyyy'))
   SUBPARTITION p06_sep_a VALUES LESS THAN (TO_DATE('01-NOV-2006','dd-MON-yyyy')),
   SUBPARTITION p06_sep_l VALUES LESS THAN (MAXVALUE),
  )
, PARTITION p_2006_oct VALUES LESS THAN (TO_DATE('01-NOV-2006','dd-MON-yyyy'))
  ( SUBPARTITION p06_oct_e VALUES LESS THAN (TO_DATE('15-NOV-2006','dd-MON-yyyy')),
    SUBPARTITION p06_oct_a VALUES LESS THAN (TO_DATE('01-DEC-2006','dd-MON-yyyy')),
    SUBPARTITION p06_oct_l VALUES LESS THAN (MAXVALUE)
  ),
  PARTITION p_2006_nov VALUES LESS THAN (TO_DATE('01-DEC-2006','dd-MON-yyyy'))
  ( SUBPARTITION p06_nov_e VALUES LESS THAN (TO_DATE('15-DEC-2006','dd-MON-yyyy')),
    SUBPARTITION p06_nov_a VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy')),
    SUBPARTITION p06_nov_l VALUES LESS THAN (MAXVALUE)
  ),
  PARTITION p_2006_dec VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
  ( SUBPARTITION p06_dec_e VALUES LESS THAN (TO_DATE('15-JAN-2007','dd-MON-yyyy')),
    SUBPARTITION p06_dec_a VALUES LESS THAN (TO_DATE('01-FEB-2007','dd-MON-yyyy')),
    SUBPARTITION p06_dec_l VALUES LESS THAN (MAXVALUE)
  )
);

A row is mapped to a partition by checking whether the value of the partitioning column for a row falls within a specific partition range. The row is then mapped to a subpartition within that partition by identifying whether the value of the subpartitioning column falls within a specific range. For example, a shipment with an order date in September 2006 and a delivery date of October 28, 2006 falls in partition p06_oct_a.

To learn how using a subpartition template can simplify the specification of a composite partitioned table, see "Using Subpartition Templates to Describe Composite Partitioned Tables".

Creating Composite List-* Partitioned Tables

The concepts of list-hash, list-list, and list-range composite partitioning are similar to the concepts for range-hash, range-list, and range-range partitioning. However, for list-* composite partitioning you specify PARTITION BY LIST to define the partitioning strategy.

The list partitions of a list-* composite partitioned table are similar to non-composite range partitioned tables. This enables optional subclauses of a PARTITION clause to specify physical and other attributes, including tablespace, specific to a partition segment. If not overridden at the partition level, then partitions inherit the attributes of their underlying table.

The subpartition descriptions, in the SUBPARTITION or SUBPARTITIONS clauses, are similar to range-* composite partitioning methods.

For more information about the subpartition definition of a list-hash composite partitioning method, refer to "Creating Composite Range-Hash Partitioned Tables". For more information about the subpartition definition of a list-list composite partitioning method, refer to "Creating Composite Range-List Partitioned Tables". For more information about the subpartition definition of a list-range composite partitioning method, refer to "Creating Composite Range-Range Partitioned Tables".

The following sections show examples for the different list-* composite partitioning methods.

Creating Composite List-Hash Partitioned Tables

Example 13 shows an accounts table that is list partitioned by region and subpartitioned using hash by customer identifier.

Example 13 Creating a composite list-hash partitioned table

CREATE TABLE accounts
( id             NUMBER,.
  account_number NUMBER,
  customer_id    NUMBER,
  balance        NUMBER,
  branch_id      NUMBER,
  region         VARCHAR(2),
  status         VARCHAR2(1)
)
PARTITION BY LIST (region)
SUBPARTITION BY HASH (customer_id) SUBPARTITIONS 8
( PARTITION p_northwest VALUES ('OR', 'WA'),
  PARTITION p_southwest VALUES ('AZ', 'UT', 'NM'),
  PARTITION p_northeast VALUES ('NY', 'VM', 'NJ'),
 PARTITION p_southeast VALUES ('FL', 'GA'),
  PARTITION p_northcentral VALUES ('SD'  'WI'),
  PARTITION p_southcentral VALUES ('OK', 'TX')
);

To learn how using a subpartition template can simplify the specification of a composite partitioned table, see "Using Subpartition Templates to Describe Composite Partitioned Tables".

Creating Composite List-List Partitioned Tables

Example 14 shows an accounts table that is list partitioned by region and subpartitioned using list by account status.

Example 14 Creating a composite list-list partitioned table

CREATE TABLE accounts
( id             NUMBER,
  account_number NUMBER,
  customer_id    NUMBER,
  balance        NUMBER,
  branch_id      NUMBER,
  region         VARCHAR(2),
  status         VARCHAR2(1)
)
PARTITION BY LIST (region)
SUBPARTITION BY LIST (status)
( PARTITION p_northwest VALUES ('OR', 'WA')
  ( SUBPARTITION p_nw_bad VALUES ('B'),
    SUBPARTITION p_nw_average VALUES ('A'),
    SUBPARTITION p_nw_good VALUES ('G')
  ),
  PARTITION p_southwest VALUES ('AZ', 'UT', 'NM')
  ( SUBPARTITION p_sw_bad VALUES ('B'),
    SUBPARTITION p_sw_average VALUES ('A'),
    SUBPARTITION p_sw_good VALUES ('G')
  ),
  PARTITION p_northeast VALUES ('NY', 'VM', 'NJ')
  ( SUBPARTITION p_ne_bad VALUES ('B'),
    SUBPARTITION p_ne_average VALUES ('A'),
    SUBPARTITION p_ne_good VALUES ('G')
  ),
  PARTITION p_southeast VALUES ('FL', 'GA')
  ( SUBPARTITION p_se_bad VALUES ('B'),
    SUBPARTITION p_se_average VALUES ('A'),
    SUBPARTITION p_se_good VALUES ('G')
  ),
  PARTITION p_northcentral VALUES ('SD', 'WI')
  ( SUBPARTITION p_nc_bad VALUES ('B'),
    SUBPARTITION p_nc_average VALUES ('A'),
    SUBPARTITION p_nc_good VALUES ('G')
  ),
  PARTITION p_southcentral VALUES ('OK', 'TX')
  ( SUBPARTITION p_sc_bad VALUES ('B'),
    SUBPARTITION p_sc_average VALUES ('A'),
    SUBPARTITION p_sc_good VALUES ('G')
  )
);

To learn how using a subpartition template can simplify the specification of a composite partitioned table, see "Using Subpartition Templates to Describe Composite Partitioned Tables".

Creating Composite List-Range Partitioned Tables

Example 15 shows an accounts table that is list partitioned by region and subpartitioned using range by account balance. Note that row movement is enabled. Subpartitions for different list partitions could have different ranges specified.

Example 15 Creating a composite list-range partitioned table

CREATE TABLE accounts
( id             NUMBER,
  account_number NUMBER,
  customer_id    NUMBER,
  balance        NUMBER,
  branch_id      NUMBER,
  region         VARCHAR(2),
status         VARCHAR2(1)
)
PARTITION BY LIST (region)
SUBPARTITION BY RANGE (balance)
( PARTITION p_northwest VALUES ('OR', 'WA')
  ( SUBPARTITION p_nw_low VALUES LESS THAN (1000),
    SUBPARTITION p_nw_average VALUES LESS THAN (10000),
    SUBPARTITION p_nw_high VALUES LESS THAN (100000),
    SUBPARTITION p_nw_extraordinary VALUES LESS THAN (MAXVALUE)
  ),
  PARTITION p_southwest VALUES ('AZ', 'UT', 'NM')
  ( SUBPARTITION p_sw_low VALUES LESS THAN (1000),
    SUBPARTITION p_sw_average VALUES LESS THAN (10000),
    SUBPARTITION p_sw_high VALUES LESS THAN (100000),
    SUBPARTITION p_sw_extraordinary VALUES LESS THAN (MAXVALUE)
  ),
  PARTITION p_northeast VALUES ('NY', 'VM', 'NJ')
  ( SUBPARTITION p_ne_low VALUES LESS THAN (1000),
    SUBPARTITION p_ne_average VALUES LESS THAN (10000),
    SUBPARTITION p_ne_high VALUES LESS THAN (100000),
    SUBPARTITION p_ne_extraordinary VALUES LESS THAN (MAXVALUE)
  ),
  PARTITION p_southeast VALUES ('FL', 'GA')
  ( SUBPARTITION p_se_low VALUES LESS THAN (1000),
    SUBPARTITION p_se_average VALUES LESS THAN (10000),
    SUBPARTITION p_se_high VALUES LESS THAN (100000),
    SUBPARTITION p_se_extraordinary VALUES LESS THAN (MAXVALUE)
  ),
  PARTITION p_northcentral VALUES ('SD', 'WI')
  ( SUBPARTITION p_nc_low VALUES LESS THAN (1000),
    SUBPARTITION p_nc_average VALUES LESS THAN (10000),
    SUBPARTITION p_nc_high VALUES LESS THAN (100000),
    SUBPARTITION p_nc_extraordinary VALUES LESS THAN (MAXVALUE)
  ),
  PARTITION p_southcentral VALUES ('OK', 'TX')
  ( SUBPARTITION p_sc_low VALUES LESS THAN (1000),
    SUBPARTITION p_sc_average VALUES LESS THAN (10000),
    SUBPARTITION p_sc_high VALUES LESS THAN (100000),
    SUBPARTITION p_sc_extraordinary VALUES LESS THAN (MAXVALUE)
  )
) ENABLE ROW MOVEMENT;

Creating Composite Interval-* Partitioned Tables

The concepts of interval-* composite partitioning are similar to the concepts for range-* partitioning. However, you extend the PARTITION BY RANGE clause to include the INTERVAL definition. You must specify at least one range partition using the PARTITION clause. The range partitioning key value determines the high value of the range partitions, which is called the transition point, and the database automatically creates interval partitions for data beyond that transition point.

The subpartitions for intervals in an interval-* partitioned table are created when the database creates the interval. You can specify the definition of future subpartitions only with a subpartition template. To learn more about how to use a subpartition template, see "Using Subpartition Templates to Describe Composite Partitioned Tables".

Creating Composite Interval-Hash Partitioned Tables

You can create an interval-hash partitioned table with multiple hash partitions using one of the following methods:

If you do not use either of these methods, then future interval partitions get only a single hash subpartition.

Example 16 shows the sales table, interval partitioned using monthly intervals on time_id, with hash subpartitions by cust_id. Note that this example specifies multiple hash partitions, without any specific tablespace assignment to the individual hash partitions.

Example 16 Creating a composite interval-hash partitioned table

CREATE TABLE sales
  ( prod_id       NUMBER(6),
    cust_id       NUMBER,
    time_id       DATE,
    channel_id    CHAR(1),
    promo_id      NUMBER(6),
    quantity_sold NUMBER(3),
    amount_sold   NUMBER(10,2)
  )
 PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
 SUBPARTITION BY HASH (cust_id) SUBPARTITIONS 4
 ( PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy')))
PARALLEL;

The following example shows the same sales table, interval partitioned using monthly intervals on time_id, again with hash subpartitions by cust_id. This time, however, individual hash partitions are stored in separate tablespaces. Note that the subpartition template is used to define the tablespace assignment for future hash subpartitions. To learn more about how to use a subpartition template, see "Using Subpartition Templates to Describe Composite Partitioned Tables".

CREATE TABLE sales
  ( prod_id       NUMBER(6),
    cust_id       NUMBER,
    time_id       DATE,
    channel_id    CHAR(1),
    promo_id      NUMBER(6),
    quantity_sold NUMBER(3),
    amount_sold   NUMBER(10,2)
  )
 PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
 SUBPARTITION BY hash(cust_id)
   SUBPARTITION template
   ( SUBPARTITION p1 TABLESPACE ts1
     SUBPARTITION p2 TABLESPACE ts2
     SUBPARTITION p3 TABLESPACE ts3
     SUBPARTITION P4 TABLESPACE ts4
   )
 ( PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy'))
) PARALLEL;

Creating Composite Interval-List Partitioned Tables

The only way to define list subpartitions for future interval partitions is with the subpartition template. If you do not use the subpartitioning template, then the only subpartition that are created for every interval partition is a DEFAULT subpartition. To learn more about how to use a subpartition template, see "Using Subpartition Templates to Describe Composite Partitioned Tables".

Example 17 shows the sales table, interval partitioned using daily intervals on time_id, with list subpartitions by channel_id.

Example 17 Creating a composite interval-list partitioned table

CREATE TABLE sales
  ( prod_id       NUMBER(6),
    cust_id       NUMBER,
    time_id       DATE,
    channel_id    CHAR(1),
    promo_id      NUMBER(6),
    quantity_sold NUMBER(3),
    amount_sold   NUMBER(10,2)
  )
 PARTITION BY RANGE (time_id) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
 SUBPARTITION BY LIST (channel_id)
   SUBPARTITION TEMPLATE
   ( SUBPARTITION p_catalog VALUES ('C'),
     SUBPARTITION p_internet VALUES ('I'),
     SUBPARTITION p_partners VALUES ('P'),
     SUBPARTITION p_direct_sales VALUES ('S'),
    SUBPARTITION p_tele_sales VALUES ('T')
   )
 ( PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy')))
PARALLEL;

Creating Composite Interval-Range Partitioned Tables

The only way to define range subpartitions for future interval partitions is with the subpartition template. If you do not use the subpartition template, then the only subpartition that is created for every interval partition is a range subpartition with the MAXVALUE upper boundary. To learn more about how to use a subpartition template, see "Using Subpartition Templates to Describe Composite Partitioned Tables".

Example 18 shows the sales table, interval partitioned using daily intervals on time_id, with range subpartitions by amount_sold.

Example 18 Creating a composite interval-range partitioned table

CREATE TABLE sales
  ( prod_id       NUMBER(6),
    cust_id       NUMBER,
    time_id       DATE,
    channel_id    CHAR(1),
    promo_id      NUMBER(6),
    quantity_sold NUMBER(3),
   amount_sold   NUMBER(10,2)
  )
 PARTITION BY RANGE (time_id) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
SUBPARTITION BY RANGE(amount_sold)
   SUBPARTITION TEMPLATE
   ( SUBPARTITION p_low VALUES LESS THAN (1000),
     SUBPARTITION p_medium VALUES LESS THAN (4000),
     SUBPARTITION p_high VALUES LESS THAN (8000),
     SUBPARTITION p_ultimate VALUES LESS THAN (maxvalue)
   )
 ( PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy')))
PARALLEL;

Using Subpartition Templates to Describe Composite Partitioned Tables

You can create subpartitions in a composite partitioned table using a subpartition template. A subpartition template simplifies the specification of subpartitions by not requiring that a subpartition descriptor be specified for every partition in the table. Instead, you describe subpartitions only one time in a template, then apply that subpartition template to every partition in the table. For interval-* composite partitioned tables, the subpartition template is the only way to define subpartitions for interval partitions.

The subpartition template is used whenever a subpartition descriptor is not specified for a partition. If a subpartition descriptor is specified, then it is used instead of the subpartition template for that partition. If no subpartition template is specified, and no subpartition descriptor is supplied for a partition, then a single default subpartition is created.

Specifying a Subpartition Template for a *-Hash Partitioned Table

For range-hash, interval-hash, and list-hash partitioned tables, the subpartition template can describe the subpartitions in detail, or it can specify just the number of hash subpartitions.

Example 19 creates a range-hash partitioned table using a subpartition template:

Example 19 Creating a range-hash partitioned table with a subpartition template

CREATE TABLE emp_sub_template (deptno NUMBER, empname VARCHAR(32), grade NUMBER)
     PARTITION BY RANGE(deptno) SUBPARTITION BY HASH(empname)
     SUBPARTITION TEMPLATE
         (SUBPARTITION a TABLESPACE ts1,
          SUBPARTITION b TABLESPACE ts2,
          SUBPARTITION c TABLESPACE ts3,
          SUBPARTITION d TABLESPACE ts4
         )
    (PARTITION p1 VALUES LESS THAN (1000),
     PARTITION p2 VALUES LESS THAN (2000),
     PARTITION p3 VALUES LESS THAN (MAXVALUE)
    );

This example produces the following table description:

partition name_subpartition name
For interval-* subpartitioning, the subpartition names are system-generated in the form:
SYS_SUBPn

The following query displays the subpartition names and tablespaces:

SQL> SELECT TABLESPACE_NAME, PARTITION_NAME, SUBPARTITION_NAME
  2  FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME='EMP_SUB_TEMPLATE'
  3  ORDER BY TABLESPACE_NAME;

TABLESPACE_NAME PARTITION_NAME  SUBPARTITION_NAME
--------------- --------------- ------------------
TS1             P1              P1_A
TS1             P2              P2_A
TS1             P3              P3_A
TS2             P1              P1_B
TS2             P2              P2_B
TS2             P3              P3_B
TS3             P1              P1_C
TS3             P2              P2_C
TS3             P3              P3_C
TS4             P1              P1_D
TS4             P2              P2_D
TS4             P3              P3_D

12 rows selected.

Specifying a Subpartition Template for a *-List Partitioned Table

Example 20, for a range-list partitioned table, illustrates how using a subpartition template can help you stripe data across tablespaces. In this example, a table is created where the table subpartitions are vertically striped, meaning that subpartition n from every partition is in the same tablespace.

Example 20 Creating a range-list partitioned table with a subpartition template

CREATE TABLE stripe_regional_sales
            ( deptno number, item_no varchar2(20),
              txn_date date, txn_amount number, state varchar2(2))
   PARTITION BY RANGE (txn_date)
   SUBPARTITION BY LIST (state)
   SUBPARTITION TEMPLATE 
      (SUBPARTITION northwest VALUES ('OR', 'WA') TABLESPACE tbs_1,
       SUBPARTITION southwest VALUES ('AZ', 'UT', 'NM') TABLESPACE tbs_2,
       SUBPARTITION northeast VALUES ('NY', 'VM', 'NJ') TABLESPACE tbs_3,
       SUBPARTITION southeast VALUES ('FL', 'GA') TABLESPACE tbs_4,
       SUBPARTITION midwest VALUES ('SD', 'WI') TABLESPACE tbs_5,
       SUBPARTITION south VALUES ('AL', 'AK') TABLESPACE tbs_6,
       SUBPARTITION others VALUES (DEFAULT ) TABLESPACE tbs_7
      )
  (PARTITION q1_1999 VALUES LESS THAN ( TO_DATE('01-APR-1999','DD-MON-YYYY')),
   PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('01-JUL-1999','DD-MON-YYYY')),
   PARTITION q3_1999 VALUES LESS THAN ( TO_DATE('01-OCT-1999','DD-MON-YYYY')),
   PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))
  );

If you specified the tablespaces at the partition level (for example, tbs_1 for partition q1_1999, tbs_2 for partition q2_1999, tbs_3 for partition q3_1999, and tbs_4 for partition q4_1999) and not in the subpartition template, then the table would be horizontally striped. All subpartitions would be in the tablespace of the owning partition.

Using Multicolumn Partitioning Keys

For range-partitioned and hash-partitioned tables, you can specify up to 16 partitioning key columns. Use multicolumn partitioning when the partitioning key is composed of several columns and subsequent columns define a higher granularity than the preceding ones. The most common scenario is a decomposed DATE or TIMESTAMP key, consisting of separated columns, for year, month, and day.

In evaluating multicolumn partitioning keys, the database uses the second value only if the first value cannot uniquely identify a single target partition, and uses the third value only if the first and second do not determine the correct partition, and so forth. A value cannot determine the correct partition only when a partition bound exactly matches that value and the same bound is defined for the next partition. The nth column is investigated only when all previous (n-1) values of the multicolumn key exactly match the (n-1) bounds of a partition. A second column, for example, is evaluated only if the first column exactly matches the partition boundary value. If all column values exactly match all of the bound values for a partition, then the database determines that the row does not fit in this partition and considers the next partition for a match.

For nondeterministic boundary definitions (successive partitions with identical values for at least one column), the partition boundary value becomes an inclusive value, representing a "less than or equal to" boundary. This is in contrast to deterministic boundaries, where the values are always regarded as "less than" boundaries.

Example 21 illustrates the column evaluation for a multicolumn range-partitioned table, storing the actual DATE information in three separate columns: year, month, and day. The partitioning granularity is a calendar quarter. The partitioned table being evaluated is created as follows:

Example 21 Creating a multicolumn range-partitioned table

CREATE TABLE sales_demo (
   year          NUMBER, 
   month         NUMBER,
   day           NUMBER,
   amount_sold   NUMBER) 
PARTITION BY RANGE (year,month) 
  (PARTITION before2001 VALUES LESS THAN (2001,1),
   PARTITION q1_2001    VALUES LESS THAN (2001,4),
   PARTITION q2_2001    VALUES LESS THAN (2001,7),
   PARTITION q3_2001    VALUES LESS THAN (2001,10),
   PARTITION q4_2001    VALUES LESS THAN (2002,1),
   PARTITION future     VALUES LESS THAN (MAXVALUE,0));

REM  12-DEC-2000
INSERT INTO sales_demo VALUES(2000,12,12, 1000);
REM  17-MAR-2001
INSERT INTO sales_demo VALUES(2001,3,17, 2000);
REM  1-NOV-2001
INSERT INTO sales_demo VALUES(2001,11,1, 5000);
REM  1-JAN-2002
INSERT INTO sales_demo VALUES(2002,1,1, 4000);

The year value for 12-DEC-2000 satisfied the first partition, before2001, so no further evaluation is needed:

SELECT * FROM sales_demo PARTITION(before2001);

      YEAR      MONTH        DAY AMOUNT_SOLD
---------- ---------- ---------- -----------
      2000         12         12        1000

The information for 17-MAR-2001 is stored in partition q1_2001. The first partitioning key column, year, does not by itself determine the correct partition, so the second partitioning key column, month, must be evaluated.

SELECT * FROM sales_demo PARTITION(q1_2001);

      YEAR      MONTH        DAY AMOUNT_SOLD
---------- ---------- ---------- -----------
      2001          3         17        2000

Following the same determination rule as for the previous record, the second column, month, determines partition q4_2001 as correct partition for 1-NOV-2001:

SELECT * FROM sales_demo PARTITION(q4_2001);

      YEAR      MONTH        DAY AMOUNT_SOLD
---------- ---------- ---------- -----------
      2001         11          1        5000

The partition for 01-JAN-2002 is determined by evaluating only the year column, which indicates the future partition:

SELECT * FROM sales_demo PARTITION(future);

      YEAR      MONTH        DAY AMOUNT_SOLD
---------- ---------- ---------- -----------
      2002          1          1        4000

If the database encounters MAXVALUE in one of the partitioning key columns, then all other values of subsequent columns become irrelevant. That is, a definition of partition future in the preceding example, having a bound of (MAXVALUE,0) is equivalent to a bound of (MAXVALUE,100) or a bound of (MAXVALUE,MAXVALUE).

The following example illustrates the use of a multicolumn partitioned approach for table supplier_parts, storing the information about which suppliers deliver which parts. To distribute the data in equal-sized partitions, it is not sufficient to partition the table based on the supplier_id, because some suppliers might provide hundreds of thousands of parts, while others provide only a few specialty parts. Instead, you partition the table on (supplier_id, partnum) to manually enforce equal-sized partitions.

CREATE TABLE supplier_parts (
   supplier_id      NUMBER, 
   partnum          NUMBER,
   price            NUMBER)
PARTITION BY RANGE (supplier_id, partnum)
  (PARTITION p1 VALUES LESS THAN  (10,100),
   PARTITION p2 VALUES LESS THAN (10,200),
   PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE));

The following three records are inserted into the table:

INSERT INTO supplier_parts VALUES (5,5, 1000);
INSERT INTO supplier_parts VALUES (5,150, 1000);
INSERT INTO supplier_parts VALUES (10,100, 1000);

The first two records are inserted into partition p1, uniquely identified by supplier_id. However, the third record is inserted into partition p2; it matches all range boundary values of partition p1 exactly and the database therefore considers the following partition for a match. The value of partnum satisfies the criteria < 200, so it is inserted into partition p2.

SELECT * FROM supplier_parts PARTITION (p1);

SUPPLIER_ID    PARTNUM      PRICE
----------- ---------- ----------
          5          5       1000
          5        150       1000

SELECT * FROM supplier_parts PARTITION (p2);

SUPPLIER_ID    PARTNUM      PRICE
----------- ---------- ----------
          10       100       1000

Every row with supplier_id < 10 is stored in partition p1, regardless of the partnum value. The column partnum is evaluated only if supplier_id =10, and the corresponding rows are inserted into partition p1, p2, or even into p3 when partnum >=200. To achieve equal-sized partitions for ranges of supplier_parts, you could choose a composite range-hash partitioned table, range partitioned by supplier_id, hash subpartitioned by partnum.

Defining the partition boundaries for multicolumn partitioned tables must obey some rules. For example, consider a table that is range partitioned on three columns a, b, and c. The individual partitions have range values represented as follows:

P0(a0, b0, c0)
P1(a1, b1, c1)
P2(a2, b2, c2)
...
Pn(an, bn, cn)

The range values you provide for each partition must follow these rules:

Using Virtual Column-Based Partitioning

With partitioning, a virtual column can be used as any regular column. All partition methods are supported when using virtual columns, including interval partitioning and all different combinations of composite partitioning. A virtual column used as the partitioning column cannot use calls to a PL/SQL function.

Example 22 shows the sales table partitioned by range-range using a virtual column for the subpartitioning key. The virtual column calculates the total value of a sale by multiplying amount_sold and quantity_sold.

Example 22 Creating a table with a virtual column for the subpartitioning key

CREATE TABLE sales
  ( prod_id       NUMBER(6) NOT NULL,
    cust_id       NUMBER NOT NULL,
    time_id       DATE NOT NULL,
    channel_id    CHAR(1) NOT NULL,
    promo_id      NUMBER(6) NOT NULL,
    quantity_sold NUMBER(3) NOT NULL,
    amount_sold   NUMBER(10,2) NOT NULL,
    total_amount AS (quantity_sold * amount_sold)
  )
 PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
 SUBPARTITION BY RANGE(total_amount)
 SUBPARTITION TEMPLATE
   ( SUBPARTITION p_small VALUES LESS THAN (1000),
     SUBPARTITION p_medium VALUES LESS THAN (5000),
     SUBPARTITION p_large VALUES LESS THAN (10000),
     SUBPARTITION p_extreme VALUES LESS THAN (MAXVALUE)
   )
 (PARTITION sales_before_2007 VALUES LESS THAN
        (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
)
ENABLE ROW MOVEMENT
PARALLEL NOLOGGING;

As the example shows, row movement is also supported with virtual columns. If row movement is enabled, then a row migrates from one partition to another partition if the virtual column evaluates to a value that belongs to another partition.

Using Table Compression with Partitioned Tables

For heap-organized partitioned tables, you can compress some or all partitions using table compression. The compression attribute can be declared for a tablespace, a table, or a partition of a table. Whenever the compress attribute is not specified, it is inherited like any other storage attribute.

Example 23 creates a list-partitioned table with one compressed partition costs_old. The compression attribute for the table and all other partitions is inherited from the tablespace level.

Example 23 Creating a list-partitioned table with a compressed partition

CREATE TABLE costs_demo (
   prod_id     NUMBER(6),    time_id     DATE, 
   unit_cost   NUMBER(10,2), unit_price  NUMBER(10,2))
PARTITION BY RANGE (time_id)
   (PARTITION costs_old 
       VALUES LESS THAN (TO_DATE('01-JAN-2003', 'DD-MON-YYYY')) COMPRESS,
    PARTITION costs_q1_2003 
       VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
    PARTITION costs_q2_2003
       VALUES LESS THAN (TO_DATE('01-JUN-2003', 'DD-MON-YYYY')),
    PARTITION costs_recent VALUES LESS THAN (MAXVALUE));

Using Key Compression with Partitioned Indexes

You can compress some or all partitions of a B-tree index using key compression. Key compression is applicable only to B-tree indexes. Bitmap indexes are stored in a compressed manner by default. An index using key compression eliminates repeated occurrences of key column prefix values, thus saving space and I/O.

The following example creates a local partitioned index with all partitions except the most recent one compressed:

CREATE INDEX i_cost1 ON costs_demo (prod_id) COMPRESS LOCAL
   (PARTITION costs_old, PARTITION costs_q1_2003, 
    PARTITION costs_q2_2003, PARTITION costs_recent NOCOMPRESS);

You cannot specify COMPRESS (or NOCOMPRESS) explicitly for an index subpartition. All index subpartitions of a given partition inherit the key compression setting from the parent partition.

To modify the key compression attribute for all subpartitions of a given partition, you must first issue an ALTER INDEX...MODIFY PARTITION statement and then rebuild all subpartitions. The MODIFY PARTITION clause marks all index subpartitions as UNUSABLE.

Using Partitioning with Segments

Note: This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2). This sections discusses the functionality when using partitioning with segments:

Deferred Segment Creation for Partitioning

You can defer the creation of segments when creating a partitioned table until the first row is inserted into a partition. Subsequently, when the first row is inserted, segments are created for the base table partition, LOB columns, all global indexes, and local index partitions. Deferred segment creation can be controlled by the following:

You can force the creation of segments for an existing created partition with the ALTER TABLE ... MODIFY PARTITION ... ALLOCATE EXTENT SQL statement. This statement allocates one extent more than the initial number of extents specified during the CREATE TABLE.

Serializable transactions do not work with deferred segment creation. Inserting data into an empty table with no segment created, or into a partition of an interval partitioned table that does not have a segment yet, causes an error.

Truncating Segments That Are Empty

You can drop empty segments in tables and table fragments with the DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS procedure.

In addition, if a partition or subpartition has a segment, then the truncate feature drops the segment if the DROP ALL STORAGE clause is specified with the ALTER TABLE TRUNCATE PARTITION SQL statement.

Maintenance Procedures for Segment Creation on Demand

You can use the MATERIALIZE_DEFERRED_SEGMENTS procedure in the DBMS_SPACE_ADMIN package to create segments for tables and dependent objects for tables with the deferred segment property.

You can also force the creation of segments for an existing created table and table fragment with the DBMS_SPACE_ADMIN.MATERIALIZE_DEFERRED_SEGMENTS procedure. The MATERIALIZE_DEFERRED_SEGMENTS procedure differs from the ALTER TABLE ... MODIFY PARTITION ... ALLOCATE EXTENT SQL statement because it does not allocate one additional extent for the table or table fragment.

Creating Partitioned Index-Organized Tables

For index-organized tables, you can use the range, list, or hash partitioning method. The semantics for creating partitioned index-organized tables is similar to that for regular tables with these differences:

Specifying an OVERFLOW clause results in the overflow data segments themselves being equipartitioned with the primary key index segments. Thus, for partitioned index-organized tables with overflow, each partition has an index segment and an overflow data segment.

For index-organized tables, the set of partitioning columns must be a subset of the primary key columns. Because rows of an index-organized table are stored in the primary key index for the table, the partitioning criterion affects the availability. By choosing the partitioning key to be a subset of the primary key, an insert operation must only verify uniqueness of the primary key in a single partition, thereby maintaining partition independence.

Support for secondary indexes on index-organized tables is similar to the support for regular tables. Because of the logical nature of the secondary indexes, global indexes on index-organized tables remain usable for certain operations where they would be marked UNUSABLE for regular tables. For more information, refer to "Maintaining Partitions".

Creating Range-Partitioned Index-Organized Tables

You can partition index-organized tables, and their secondary indexes, by the range method. In Example 4-24, a range-partitioned index-organized table sales is created. The INCLUDING clause specifies that all columns after week_no are to be stored in an overflow segment. There is one overflow segment for each partition, all stored in the same tablespace (overflow_here). Optionally, OVERFLOW TABLESPACE could be specified at the individual partition level, in which case some or all of the overflow segments could have separate TABLESPACE attributes.

Example 24 Creating a range-partitioned index-organized table

CREATE TABLE sales(acct_no NUMBER(5), 
                   acct_name CHAR(30), 
                   amount_of_sale NUMBER(6), 
                   week_no INTEGER,
                   sale_details VARCHAR2(1000),
             PRIMARY KEY (acct_no, acct_name, week_no)) 
     ORGANIZATION INDEX 
             INCLUDING week_no
             OVERFLOW TABLESPACE overflow_here
     PARTITION BY RANGE (week_no)
            (PARTITION VALUES LESS THAN (5) 
                   TABLESPACE ts1,
             PARTITION VALUES LESS THAN (9) 
                   TABLESPACE ts2 OVERFLOW TABLESPACE overflow_ts2,
             ...
             PARTITION VALUES LESS THAN (MAXVALUE) 
                   TABLESPACE ts13);

Creating Hash-Partitioned Index-Organized Tables

Another option for partitioning index-organized tables is to use the hash method. In Example 4-25, the sales index-organized table is partitioned by the hash method.

Example 25 Creating a hash-partitioned index-organized table

CREATE TABLE sales(acct_no NUMBER(5), 
                   acct_name CHAR(30), 
                   amount_of_sale NUMBER(6), 
                   week_no INTEGER,
                   sale_details VARCHAR2(1000),
             PRIMARY KEY (acct_no, acct_name, week_no)) 
     ORGANIZATION INDEX 
             INCLUDING week_no
     OVERFLOW
          PARTITION BY HASH (week_no)
             PARTITIONS 16
             STORE IN (ts1, ts2, ts3, ts4)
             OVERFLOW STORE IN (ts3, ts6, ts9);

Note: A well-designed hash function is intended to distribute rows in a well-balanced fashion among the partitions. Therefore, updating the primary key column(s) of a row is very likely to move that row to a different partition. Oracle recommends that you explicitly specify the ENABLE ROW MOVEMENT clause when creating a hash-partitioned index-organized table with a changeable partitioning key. The default is that ENABLE ROW MOVEMENT is disabled.

Creating List-Partitioned Index-Organized Tables

The other option for partitioning index-organized tables is to use the list method. In the following example, the sales index-organized table is partitioned by the list method. Example 4-26 uses the example tablespace, which is part of the sample schemas in your seed database. Normally you would specify different tablespace storage for different partitions.

Example 26 Creating a list-partitioned index-organized table

CREATE TABLE sales(acct_no NUMBER(5), 
                   acct_name CHAR(30), 
                   amount_of_sale NUMBER(6), 
                   week_no INTEGER,
                   sale_details VARCHAR2(1000),
             PRIMARY KEY (acct_no, acct_name, week_no)) 
     ORGANIZATION INDEX 
             INCLUDING week_no
             OVERFLOW TABLESPACE example
     PARTITION BY LIST (week_no)
            (PARTITION VALUES (1, 2, 3, 4) 
                   TABLESPACE example,
             PARTITION VALUES (5, 6, 7, 8) 
                   TABLESPACE example OVERFLOW TABLESPACE example,
             PARTITION VALUES (DEFAULT) 
                   TABLESPACE example);

Partitioning Restrictions for Multiple Block Sizes

Use caution when creating partitioned objects in a database with tablespaces of different block sizes. The storage of partitioned objects in such tablespaces is subject to some restrictions. Specifically, all partitions of the following entities must reside in tablespaces of the same block size:

Therefore:

When you create or alter a partitioned table or index, all tablespaces you explicitly specify for the partitions and subpartitions of each entity must be of the same block size. If you do not explicitly specify tablespace storage for an entity, then the tablespaces the database uses by default must be of the same block size. Therefore, you must be aware of the default tablespaces at each level of the partitioned object.

Partitioning of Collections in XMLType and Objects

For the purposes of this discussion, the term Collection Tables is used for the following two categories: (1) ordered collection tables inside XMLType tables or columns, and (2) nested tables inside object tables or columns.

Partitioning when using XMLType or object tables and columns follows the basic rules for partitioning. When you partition Collection Tables, Oracle Database uses the partitioning scheme of the base table. Also, Collection Tables are automatically partitioned when the base table is partitioned. DML against a partitioned nested table behaves in a similar manner to that of a reference partitioned table.

The statement in Example 4-27 creates a nested table partition:

Example 27 Creating a nested table partition

CREATE TABLE print_media_part (
   product_id NUMBER(6),
   ad_id NUMBER(6),
   ad_composite BLOB,
   ad_sourcetext CLOB,
   ad_finaltext CLOB,
   ad_fltextn NCLOB,
   ad_textdocs_ntab TEXTDOC_TAB,
   ad_photo BLOB,
   ad_graphic BFILE,
   ad_header ADHEADER_TYP)
NESTED TABLE ad_textdocs_ntab STORE AS textdoc_nt
PARTITION BY RANGE (product_id)
  (PARTITION p1 VALUES LESS THAN (100),
   PARTITION p2 VALUES LESS THAN (200));

For an example of issuing a query against a partitioned nested table and using the EXPLAIN PLAN to improve performance, see "Collection Tables".

Note that Oracle Database provides a LOCAL keyword to equipartition a Collection Table with a partitioned base table. This is the default behavior in this release. The default in earlier releases was not to equipartition the Collection Table with the partitioned base table. Now you must specify the GLOBAL keyword to store an unpartitioned Collection Table with a partitioned base table. See Oracle Database SQL Language Reference for more information. Also, to convert your existing nonpartitioned collection tables to partitioned, use online redefinition, as illustrated in "Redefining Partitions Online".

Out-of-line (OOL) table partitioning is supported. However, you cannot create two tables of the same XML schema that has out-of-line tables. This means that exchange partitioning cannot be performed for schemas with OOL tables because it is not possible to have two tables of the same schema.

Performing PMOs on Partitions that Contain Collection Tables

Whether a partition contains Collection Tables or not does not significantly affect your ability to perform partition maintenance operations (PMOs). Usually, maintenance operations on Collection Tables are carried out on the base table. The following example illustrates a typical ADD PARTITION operation based on the preceding nested table partition:

ALTER TABLE print_media_part 
   ADD PARTITION p4 VALUES LESS THAN (400)
   LOB(ad_photo, ad_composite) STORE AS (TABLESPACE omf_ts1)
   LOB(ad_sourcetext, ad_finaltext) STORE AS (TABLESPACE omf_ts1)
   NESTED TABLE ad_textdocs_ntab STORE AS nt_p3;

The storage table for nested table storage column ad_textdocs_ntab is named nt_p3 and inherits all other attributes from the table-level defaults and then from the tablespace defaults.

You must directly invoke the following partition maintenance operations on the storage table corresponding to the collection column:

CONCLUSIÓN

Teniendo en cuenta la nueva y mejorada funcionalidad de particionamiento, Oracle Database 11g es la versión más importante desde la presentación de Oracle Partitioning en 1997. En cada lanzamiento,Oracle ha mejorado la funcionalidad de Particionamiento, ya sea incorporando nuevas técnicas de particionamiento, mejorando la escalabilidad, o extendiendo las capacidades de mantenimiento y capacidad de administración. Oracle tiene pensado seguir incorporando nuevas técnicas de particionamiento para garantizar que la técnica de particionamiento óptima se encuentra a disposición de cada requerimiento de negocios.

El particionamiento es para todos. Oracle Partitioning puede mejorar enormemente la capacidad de administración, desempeño y disponibilidad de casi cualquier aplicación de base de datos. El particionamiento puede aplicarse a las aplicaciones de avanzada y ciertamente puede ser un ingrediente de tecnología esencial para garantizar el éxito de estas aplicaciones. El particionamiento también puede aplicarse a aplicaciones de base de datos más comunes para simplificar la administración y los costos de administración de dichas aplicaciones. Debido a que el particionamiento es transparente para la aplicación, éste puede implementarse fácilmente ya que no se requieren cambios costosos y prolongados en las aplicaciones.

Fuentes:

http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin001.htm

Particionamiento en Oracle Database 11g Informe Ejecutivo de Oracle Junio de 2007



Anterior
Valid XHTML
home

Siguiente