Oracle Spatial 索引學習小結

1、空間索引的建立

1）建立索引之前總是要為空白間層插入中繼資料

2）如果之前建立的索引失敗了，必須先刪除才能建立

Drop index customers_sidx;

建立索引：

Create index customers_sidx on customers(location)

Indextype is mdsys.spatial_index

在索引建立過程中，Oracle檢查索引列的sdo_srid和user_sdo_geom_metadata中的srid是否匹配，如果不匹配，Oracle會產生ora-13365錯誤。

空間索引資訊可查看user_sdo_index_metadata或者較簡單的user_sdo_index_info視圖。

空間索引表格儲存體在這個SDO_INDEX_TABLE欄位中，總是以MDRT開頭。不能將一個空間索引表和普通的表一樣對待-即不能將它從一個資料表空間移到另一個資料表空間，也不能將它刪除、複製等。否則，會出現無效的空間索引並導致後續的空間查詢操作符或空間索引重建失敗。

2、空間索引的參數

create INDEX <INDEX_NAME> ON <TABLE_NAME>(<COLUMNNAME>)

INDEXTYPE IS MDSYS.SPATIAL_INDEX

PARAMETERS('PARAMETER_STRING');

1)TABLESPACE參數

通過該參數，可以指定哪個資料表空間來儲存空間索引表。除了TABLESPACE參數外，還可以指定另外兩個參數INITIAL和NEXT

create index cola_markets_spatial_geo_idx on cola_markets(LOCATION)

indextype is mdsys.spatial_index

parameters ('TABLESPACE=gmapdata next=5 INITIAL=10k');

如果資料表空間是本地管理的，那麼INITIAL和NEXT參數就是多餘的，即即使指定了他們，Oracle也會忽略它們。

註：資料表空間是否是本地管理的，可以通過user_tablespaces視圖的segment_space_management欄位是否為auto來驗證。

2）work_tablespace參數

在索引建立過程中，R-tree索引會在整個的資料集上執行排序操作，因此會產生一些工作表。不過這些工作表在索引建立過程結束時會被刪除。建立和刪除大量不同大小的表會使資料表空間產生很多片段。為避免這種情況，可以通過使用work_tablespace參數來為這些工作表指定一個單獨的資料表空間。

create index cola_markets_spatial_geo_idx on cola_markets(LOCATION)

indextype is mdsys.spatial_index

parameters ('work_tablespace=gmapdata');

指定了工作資料表空間後，索引和資料就不會再索引建立過程中產生片段。如果沒有指定工作資料表空間，則預設工作表和索引被建立在同一個資料表空間。

3）layer_gtype

該參數指定了索引列的幾何資料為特定類型幾何體。這有助於完整性檢查，有時還可加快查詢操作符的執行速度。

create index cola_markets_spatial_geo_idx on cola_markets(LOCATION)

indextype is mdsys.spatial_index

parameters ('layer_gtype =point');

4）sdo_index_dims參數

該參數指定了空間索引的維數，預設為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參數

該參數用於指定一個事務中批量插入/刪除/更新時得批量大小（對有大量插入的事務，該參數應設為5000或10000）。預設為1000.

create index cola_markets_spatial_geo_idx on cola_markets(LOCATION)

indextype is mdsys.spatial_index

parameters ('sdo_dml_batch_size=5000');

註：如果預計會對一個含有空間索引的表執行含有大量插入（或刪除、更新）操作的事務，就應在create index語句或隨後的alter_index rebuild語句中將sdo_dml_batch_size的值設為5000或10000.

6）sdo_level參數

指定sdo_level參數的值來建立一個四叉樹索引。四叉樹需要顯式地進行效能調優，因此不被推薦使用。

create index cola_markets_spatial_geo_idx on cola_markets(LOCATION)

indextype is mdsys.spatial_index

parameters ('sdo_level=8');

3、空間索引視圖

user_sdo_index_metadata和user_sdo_index_info（後一個較簡單）

可以從user_sdo_index_metadata視圖中查看索引設定的參數。

對於一張表中n行資料的一個集合，R-tree空間索引大致需要100*3*N位元組作為空白間索引表的儲存空間。另外，在建立索引的過程中，r-tree索引還需要額外200*3*N到300*3*N位元組作為臨時工作表的儲存空間。

--查看錶索引大小

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、基於函數的空間索引

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');

如果在一個地理編碼地址到sdo_geometry對象的函數上建立空間索引，就應該在create index語句中指定參數'LAYER_GTYPE=POINT'。如果不指定該參數，查詢速度會很慢。

5、本地分區空間索引

在分區表上建立本地索引

分區表建立本地索引的條件：

只能在range_partitioned表上建立本地空間索引，而不能在list-或hash-partitioned表上建立空間索引。

本地空間索引相對於建立一個全域索引的優點：

易管理性：重建指定分區上的本地索引不會影響其他分區。

易伸縮性：為提高效能，可只在指定分區上進行查詢，空間索引可在每個分區上並行建立。

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、並行索引

文法：

create index cola_markets_spatial_geo_idx on cola_markets(LOCATION)

indextype is mdsys.spatial_index

parameters【parallel_degree】;

--建立並行索引

create index cola_markets_spatial_geo_idx on cola_markets(LOCATION)

indextype is mdsys.spatial_index

parameters parallel;

--修改為並行索引

Alter table cola_markets parallel 2;

不管建立的索引是一個本地分區索引還是一個全域索引，如果parallel degree大於2，則索引的建立就會並存執行。

7、線上重建索引

當對一個含有空間索引的表進行大量（典型值為30%）刪除操作後，對相關空間索引進行重建將使得該索引相對緊湊，從而可以更有效地服務隨後的查詢。

Alter index cola_markets_spatial_geo_idx rebuild;

--也可以指定參數

Alter index cola_markets_spatial_geo_idx rebuild parameters ('layer_gtype =point');

Alter index是一個DDL語句，Alter index…rebuild是個阻塞語句。因此如果在索引上有任何一個並發執行的DML（可能在不同的會話中），那麼該命令將被阻塞直到它獲得索引上的互斥鎖。同理，在重建開始後，任何一個在表或索引上並發執行的DML語句都會被阻塞。所以為保證查詢在索引重建時不被阻塞，可在Alter index…rebuild語句中指定關鍵詞ONLINE。

Alter index cola_markets_spatial_geo_idx rebuild online parameters ('layer_gtype =point');