4
Partitioning Point Data

Spatial Cartridge provides the essential functions, procedures, and scripts for using and managing both spatially indexed data and partitioned point data. The information in this chapter is relevant only to users utilizing table partitioning for very large quantities of point data.

4.1 Overview

Partitioning is a technique where data is loaded into tables that automatically subdivide when a predefined maximum size is reached. During subdivision, data is moved from the parent partition to the child partitions and the parent partition is dropped. Storage parameters for child partitions are inherited from the root partition and can be changed at any time.

A partitioned table has a partition key that is an HHCODE column created by encoding multidimensional point data using the SDO_ENCODE() function. In the partitioning process, at each subdivision, data is subdivided into 2n partitions where n is the number of dimensions encoded in the HHCODE column. You can encode up to 32 dimensions using Spatial Cartridge.

4.2 Partitioning Process

This manual does not attempt to provide the information necessary for fully utilizing table partitioning for point data. The following is a high-level description of the partitioning process:

1. Start with an Oracle8 table containing multidimensional point data. For example, columns of X and Y coordinate data from a blueprint or map.
2. Create a table or view from the original Oracle8 table containing the columns you want, plus a new HHCODE column.

   An HHCODE column is a new data type used to encode multiple dimensions into a unique orderable value. HHCODE is not a point, but rather a bounded cell representing an object space in as many dimensions as have been defined. An HHCODE data type is defined as RAW(255).

3. Create the HHCODE data type by encoding multiple dimensions into a single value using the SDO_ENCODE() function. The HHCODE data type will be used as the partition key.
4. Register a partitioned table in the Spatial Cartridge data dictionary using the SDO_ADMIN.REGISTER_PARTITION_INFO() procedure. This procedure takes the name of a table, the name of the partition key column, and the maximum number of records you want stored in a partition before it subdivides.
5. Call the SDO_ADMIN.PARTITION() procedure with the name of the table or view containing the partition key column and the tablespace in which the partitions should be created. In this step, the data is partitioned based on dimensions encoded in the HHCODE column.
6. If the underlying table has constraints, grants, or triggers, the owner needs to use the SDO_ADMIN.PROPAGATE_GRANTS() procedure to set those properties on the partitions.
7. To add more partitioned point data, load the data into a table, and call SDO_ADMIN.PARTITION() again. The dimensions encoded in the HHCODE column must have the same boundaries to be loaded into the existing partitioned table.
8. After you have added data multiple times, or after adding or deleting a large amount of data, there may be partitions that exceed the high-water mark or there may be partitions that can be merged. Call the SDO_ADMIN.REPARTITION() procedure to reorganize the partitioned table. Repartitioning is a computation-intensive task that should be performed only when necessary.

4.3 Function Details

See the following sections for details of the functions supporting partitioned point data:

• Chapter 5, "Administrative Procedures"
  • SDO_ADMIN.ALTER_HIGH_WATER_MARK
  • SDO_ADMIN.DROP_PARTITION_INFO
  • SDO_ADMIN.PARTITION
  • SDO_ADMIN.PROPAGATE_GRANTS
  • SDO_ADMIN.REGISTER_PARTITION
  • SDO_ADMIN.REPARTITION
  • SDO_ADMIN.VERIFY_PARTITIONS
• Chapter 9, "Partitioned Point Data Functions"
  • SDO_BVALUETODIM
  • SDO_COMPARE
  • SDO_DATETODIM
  • SDO_DECODE
  • SDO_ENCODE
  • SDO_TO_BVALUE
  • SDO_TO_DATE
• Appendix A, "Sample SQL Scripts and Tuning Tips"
  • altpart.sql
  • drppart.sql
  • sdogrant.sql

---