About index partitioning for Oracle

Index partitioning can improve performance of queries in large tables. You can use the DDL configuration element <ora-index-partitioning> in database-config.xml to specify Oracle index partitioning.

The <ora-index-partitioning> element is a subelement of <ora-index-ddl>, which is itself a subelement of <ora-table-ddl>. For example:

<database name="PolicyCenterDatabase" dbtype="oracle">
  ...
  <upgrade>
    ...
    <ora-db-ddl>
      <ora-table-ddl name="Table Name">
        <ora-index-ddl key-columns="column1,column2,...">
          <ora-index-partitioning 
            partitioning-type="LOCAL|HASH|RANGE" 
            // The next two elements apply only to the RANGE partitioning type.
              range-partitioning-column-list="column1,column2,...">
                <ora-index-range-partition value-list=
                    "number1|’string1’,number2|’string2’,..."/>
                <ora-index-range-partition value-list=
                     "number1a|’string1a’,number2a|’string2a’,..."/>
                ...
          </ora-index-partitioning 
        </ora-index-ddl>
      </ora-table-ddl>
    </ora-db-ddl>
  </upgrade>
</database>

The use of the partitioning-type attribute is mandatory. This attribute takes one of the following values.

LOCAL	The only allowed attribute is the partitioning-type attribute. PolicyCenter partitions the index as the table is partitioned.
HASH	PolicyCenter hash-partitions the index globally on the leading key of the index by using the number of partitions specified or the default number 128.
RANGE	PolicyCenter range-partitions the index by using the range-partitioning-column-list columns and the values specified in the ora-index-range-partition elements under this element. The range-partitioning-column-list element takes a comma-delimited list of columns to use for range-partitioning this index. This element requires the definition of one or more ora-index-range-partition elements. The ora-index-range-partition defines the value range for each partition in value-list. It defines a comma-delimited, ordered list of literal values corresponding to the column list defined in range-partitioning-column-list. Place any single String value inside single quotation marks. Surround the entire list of values by double quotation marks. PolicyCenter uses these defined values in the SQL clause VALUES LESS THAN(value_list). Do not specify the last range, which is always VALUES LESS THAN (MAXVALUE[, MAXVALUE, ...]). Note: PolicyCenter does not support the use of a date column for range-partitioning of indexes.

For example, the following database block defines an index range partitioning that uses five partitions and two column values per partition. The final partition, created automatically by PolicyCenter, uses the following values for the two columns defined in range-partitioning-column-list:

• ADDRESSBOOKUID – At least 'ab:830' and less than MAXVALUE
• RETIRED – At least 0 and less than MAXVALUE

  <database name="pcDatabase" dbtype="oracle">
  ...
    <upgrade degree-parallel-ddl="1" verifyschema="true">
      <ora-db-ddl>
        <tablespaces admin="pc_ADMIN" index="pc_INDEX" op="pc_OP" 
                     staging="pc_STAGING" typelist="pc_TYPELIST"/>
        <ora-table-ddl table-name="pc_CLAIM">
          <ora-index-ddl key-columns="ADDRESSBOOKUID, RETIRED, SUBTYPE, ID"> 
            <ora-index-partitioning partitioning-type="RANGE"
                  range-partitioning-column-list="ADDRESSBOOKUID, RETIRED">
              <ora-index-range-partition value-list="'ab:20', 0"/>
              <ora-index-range-partition value-list="'ab:40', 0"/>
              <ora-index-range-partition value-list="'ab:60', 0"/>
              <ora-index-range-partition value-list="'ab:830', 0"/>
            </ora-index-partitioning>
          </ora-index-ddl>
        </ora-table-ddl>
      </ora-db-ddl>
    </upgrade>
  </database>

See also

• The <ora-index-ddl> database configuration element