Oracle Spatial Index learning Summary

1. Create a spatial index

1) before creating an index, you always need to insert metadata for the space layer.

2) if an index fails to be created, it must be deleted before it can be created.

Drop index customers_sidx;

Create an index:

Create index customers_sidx on customers (location)

Indextype is MDSYS. spatial_index

During index creation, Oracle checks the sdo_srid of the index column and the SRID in user_sdo_geom_metadata for matching. If not, Oracle produces a ora-13365 error.

For spatial index information, you can view user_sdo_index_metadata or the simple user_sdo_index_info view.

The spatial index table is stored in this sdo_index_table field and always starts with mdrt. You cannot treat a spatial index table as a normal table-that is, you cannot move it from one tablespace to another, or delete or copy it. Otherwise, invalid spatial indexes may occur and subsequent reconstruction of spatial query operators or spatial indexes may fail.

2. Spatial index parameters

Create index <index_name> On <table_name> (<columnname>)

Indextype is MDSYS. spatial_index

Parameters('Parameter _ string');

1) tablespaceParameters

You can specify the tablespace used to store the index table. In addition to the tablespace parameter, you can also specify the other two parameters initial and next

Create index cola_markets_spatial_geo_idx on cola_markets (location)

Indextype is MDSYS. spatial_index

Parameters ('Tablespace = gmapdata next = 5 Initial = 10 K');

If the tablespace is locally managed, the initial and next parameters are redundant, that is, Oracle ignores them even if they are specified.

Note: whether the tablespace is locally managed can be accessed throughCheck whether the segment_space_management field in the user_tablespaces view is auto.

2) Work_tablespace Parameter

During index creation, the R-tree index performs sorting on the entire dataset, so some worksheets are generated. However, these worksheets will be deleted at the end of the index creation process. Creating and deleting a large number of tables of different sizes can cause a lot of table space fragments. To avoid this problem, you can use the work_tablespace parameter to specify a separate tablespace for these worksheets.

Create index cola_markets_spatial_geo_idx on cola_markets (location)

Indextype is MDSYS. spatial_index

Parameters ('Work_tablespace= Gmapdata');

After the worksheet space is specified, the index and data will no longer produce fragments during index creation. If no worksheet space is specified, the worksheet and index are created in the same tablespace by default.

3) Layer_gtype

This parameter specifies that the geometric data of the index column is of a specific type. This facilitates integrity checks and sometimes accelerates the execution of query operators.

Create index cola_markets_spatial_geo_idx on cola_markets (location)

Indextype is MDSYS. spatial_index

Parameters ('Layer_gtype = point ');

4)Sdo_index_dimsParameters

This parameter specifies the dimension of the spatial index. The default value is 2.

Create index cola_markets_spatial_geo_idx on cola_markets (location)

Indextype is MDSYS. spatial_index

Parameters ('Sdo_index_dims= 3 ');

5) Sdo_dml_batch_size Parameter

This parameter is used to specify the batch size when a transaction is inserted, deleted, or updated in batches (for a transaction with a large number of inserts, this parameter should be set to 5000 or 10000 ). 1000 by default.

Create index cola_markets_spatial_geo_idx on cola_markets (location)

Indextype is MDSYS. spatial_index

Parameters ('Sdo_dml_batch_size = 5000 ');

Note: If you expect to execute a transaction that contains a large number of insert (or delete or update) operations on a table with spatial indexes, the value of sdo_dml_batch_size should be set to 5000 or 10000 in the create index statement or subsequent alter_index rebuild statement.

6) Sdo_level Parameter

Specify the value of sdo_level to create a quad-tree index. The performance tuning of the quad-tree needs to be performed explicitly, so it is not recommended.

Create index cola_markets_spatial_geo_idx on cola_markets (location)

Indextype is MDSYS. spatial_index

Parameters ('Sdo_level= 8 ');

3. Spatial Index View

User_sdo_index_metadata and user_sdo_index_info (the latter is relatively simple)

You can view the index parameters in the user_sdo_index_metadata view.

For a set of N rows in a table, the R-tree spatial index requires 100*3 * n Bytes as the storage space of the spatial index table. In addition, during the index creation process, the R-tree index requires an additional 200*3 * n to 300*3 * n Bytes as the storage space for the temporary worksheet.

-- View the index size of a table

Select sdo_tune.estimate_rtree_index_size ('gwm ', 'cola _ markets', 'shape') SZ from dual;

Sdo_tune.estimate_rtree_index_size (

Schemaname in varchar2,

Tabname in varchar2,

Colname in varchar2,

Partname in varchar2 default null

) Return number;

Or

Sdo_tune.estimate_rtree_index_size (

Number_of_geoms in integer,

Db_block_size in integer,

Sdo_rtr_pctfree in integer default 10,

Num_dimensions in integer default 2,

Is_geodetic in integer default 0

) Return number;

4. function-based spatial index

Create or replace function gcdr_geometry (street_number varchar2,

Street_name varchar2,

City varchar2,

State varchar2,

Postal_code varchar2)

Return MDSYS. sdo_geometry deterministic is

Begin

Return (sdo_gcdr.geocode_as_geometry ('spatial ',

Sdo_keywordarray (street_number | ''|

Street_name,

City | ''| state |'' |

Postal_code ),

'Us '));

End;

Create index cola_markets_spatial_geo_idx on cola_markets

(

Gcdr_geometry (street_number, street_name, city, state, postal_code)

)

Indextype is MDSYS. spatial_index

Parameters ('layer _ gtype = point ');

If you create a spatial index on a function of the sdo_geometry object with a geocode address, you should specify the 'layer _ gtype = point' parameter in the create index statement '. If this parameter is not specified, the query speed will be slow.

5. Local partition space index

Create a local index on a partitioned table

Conditions for creating a local index for a partitioned table:

You can only create local spatial indexes on range_partitioned tables, but not on list-or hash-partitioned tables.

Advantages of local spatial indexes over creating a global index:

Easy to manage: rebuilding the local index on a specified partition does not affect other partitions.

Scalability: to improve performance, you can query only the specified partitions. spatial indexes can be created in parallel on each partition.

Create index cola_markets_spatial_geo_idx on cola_markets (location)

Indextype is MDSYS. spatial_index

Parameters ('tablespace = gmapdata ')

Local

(

Partition IP1 parameters ('Tablespace = gmapdata '),

Partition ip2,

Partition IP3

);

6. Parallel Indexing

Syntax:

Create index cola_markets_spatial_geo_idx on cola_markets (location)

Indextype is MDSYS. spatial_index

Parameters [parallel_degree ];

-- Create a parallel Index

Create index cola_markets_spatial_geo_idx on cola_markets (location)

Indextype is MDSYS. spatial_index

Parameters parallel;

-- Modify to Parallel Index

Alter table cola_markets parallel 2;

No matter whether the created index is a local partition index or a global index, if parallel degree is greater than 2, the index is created in parallel.

7. Rebuilding indexes online

After a large number of delete operations (typically 30%) are performed on a table with spatial indexes, rebuilding the related spatial indexes will make the index relatively compact, this allows you to more effectively serve subsequent queries.

Alter index cola_markets_spatial_geo_idx rebuild;

-- You can also specify parameters

Alter index cola_markets_spatial_geo_idx rebuild parameters ('layer _ gtype = point ');

Alter index is a DDL statement, alter index... Rebuild is a blocking statement. Therefore, if there is any concurrently executed DML on the index (possibly in different sessions), the command will be blocked until it gets the mutex lock on the index. Similarly, after the reconstruction starts, any DML statement executed concurrently on the table or index will be blocked. Therefore, to ensure that the query is not blocked during index reconstruction, you can use alter index... Specify the keyword online in the rebuild statement.

Alter index cola_markets_spatial_geo_idxRebuild OnlineParameters ('layer _ gtype = point ');