Best Practices for Building Oracle 10gr2 Spatial Database

OK. it is reviewing my old mail from internal and find this is worthy reading.

• Spatial index creation -
• Specify WORK_TABLESPACE - During spatial index creation, the process creates intermediate tables that get dropped when the index is complete. The intermediate tables can take up to 2 times the size of the final index. If WORK_TABLESPACE is not specified, the intermediate tables are created in the same tablespace as the final index, causing fragmentation, and possible performance degradation. You can use SDO_TUNE.ESTIMATE_RTREE_INDEX_SIZE, and multiply the result by 2 to provide guidance on sizing the work tablespace. The work tablespace can be re-used to create other spatial indexes.
  
• Specify LAYER_GTYPE, especially when working with point only layers. If a point only layer stores it's points in the SDO_ORDINATE_ARRAY, you can still specify LAYER_GTYPE=POINT on spatial index creation. This can help query performance when performing spatial analysis.
  
• Specify SDO_NON_LEAF_TBL for very large spatial indexes (not necessary for smaller spatial indexes) - This generates two spatial index tables instead of one. The smaller spatial index table is the non leaf table, which is traversed most often during spatial analysis. It can be beneficial to pin the non leaf table into the buffer pool, since it is accessed most often. Below is an example.
  

• -- Create the index

  CREATE INDEX geod_counties_sidx

  ON geod_counties(geom)

  INDEXTYPE IS MDSYS.SPATIAL_INDEX

  PARAMETERS ('sdo_non_leaf_tbl=TRUE');

• -- Find the non leaf index table name

  SELECT sdo_nl_index_table

  FROM user_sdo_index_metadata

  WHERE sdo_index_name='GEOD_COUNTIES_SIDX';

  ----------

  MDNT_A930$

• -- Pin the table in memory

  ALTER TABLE MDNT_A930$ STORAGE(BUFFER_POOL KEEP);

• Transportable tablespace support for spatial indexes in Oracle 10g -
• If moving large amounts of spatial data and spatial indexes from one database instance to another, you may want to consider transportable tablespaces. Beginning with Oracle 10g, transportable tablespaces support transporting spatial indexes, as long as the source and target platforms are the same Endian format.
• If you plan to transport tablespaces, you may want to reconsider how data and indexes are mapped to tablespaces to help optimize your transport strategy.
• Geometry validation -
  
• It is good practice to ensure geometries are valid prior to index creation or running spatial analysis. This can be accomplished in bulk fashion with SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT, or on a geometry by geometry basis with SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT.
• Point data in the SDO_POINT attribute of the SDO_GEOMETRY object -
• When possible, store point data in the SDO_POINT attribute of the SDO_GEOMETRY object instead of the SDO_ORDINATES attribute.
  
• Partitioned local spatial indexes - If you are considering this, here are some best practices:
• For parallel creation of spatial indexes on partitioned tables, we now recommend a different strategy instead of using the PARALLEL keyword.
  

When creating a partitioned LOCAL spatial index, if any partitions index fails (for example, tablespace full or for some other reason), you must start at the beginning again (no way to continue from where you left off). Instead, here is a better approach.

First create the LOCAL spatial index with the UNUSABLE keyword. This runs very quickly and only creates metadata associated with the index:

• CREATE INDEX sp_idx ON my_table (location) INDEXTYPE IS mdsys.spatial_index PARAMETERS (‘tablesapce=tb_name work_tablespace=work_tb_name’) LOCAL UNSUABLE;

Hypothetically, lets say you have 100 partitions and 10 processors. Build 10 scripts, each with 10 ALTER INDEX REBUILD statements… but do not use the PARALLEL parameter. For example:

• ALTER INDEX sp_idx REBUILD PARITION ip1;
• ALTER INDEX sp_idx REBUILD PARITION ip2;
• etc...

By running all 10 scripts at the same time, each processor will be working on a single partitions index, but all the processors will still be busy working on their own set of ALTER INDEX statements.

If any of the ALTER INDEX commands failed, you do not have to rebuild any that have already successfully completed.