Oracle Spatial中的空間索引

Oracle Spatial中的空間索引 Oracle Spatial可對空間資料進行R-tree索引，每個空間圖層（Spatial Layer）的空間索引元資訊都可以在USER_SDO_INDEX_METADATA視圖中找到。
具體的索引資料儲存在MDRT欄位開頭的表中, 每個空間圖層都會對應一個索引表（表的格式是MDRT_[...]$），空間索引表中的主要資料是MBR

通過空間索引中繼資料視圖（USER_SDO_INDEX_METADATA）可以查到每個空間圖層的空間索引名、空間索引資料表名、R-tree索引的根節點ROWID，R-tree的分支因子（又叫扇出fanout，即R-tree節點的最大子節點數）及其它相關資訊。

索引的建立：
1. R-tree索引：
CREATE INDEX customers_sidx ON customers(location)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;

2. 四叉樹索引：
CREATE INDEX customers_sidx ON customers(location)
INDEXTYPE  IS MDSYS.SPATIAL_INDEX
PARAMETERS ('SDO_LEVEL=8');

在Oracle Spatial中，四叉樹索引不如R-tree索引，因為：
1. 只能對二維非geodesic資料建立四叉樹索引；
2. 使用者自己要對四叉樹索引的參數進行調整，而R-tree索引的參數比較好調，也更自動。

關於四叉樹索引可以參考文檔： Oracle Spatial Quadtree Indexing

索引的效率：
1. 如果資料表空間指定為ASSM資料表空間（user_tablespaces表中segment_space_management為AUTO即是），索引中的LOB資料將是SECUREFILE LOB的，這比一般的BASIC LOB快。

2. 空間索引建立過程中會臨時產生許多工作表，建立完後會刪除這些工作表，這一過程中(大量不同大小的表的建立和刪除，其資料量大約是要索引的表的200-300倍）會使得資料表空間（tablespace）產生許多片段從而影響資料表空間效率，可以為這些工作表指定獨立的資料表空間（即指定WORK_TABLESPACE）避免這一效率損失：
CREATE INDEX customers_sidx ON customers(location)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS ('WORK_TABLESPACE=SYSAUX');
注意工作資料表空間不得使用temporacy資料表空間。如果不指定工作資料表空間，預設同要索引之表的資料表空間。

3. 要索引的geometry列如果都是相同形狀的幾何體（例如都是點），在建立索引時指定要索引空間圖層中的幾何體類型會提高查詢速度： Plsql代碼  

1. CREATE INDEX customers_sidx ON customers(location)   
2. INDEXTYPE IS MDSYS.SPATIAL_INDEX   
3. PARAMETERS ('LAYER_GTYPE=POINT');  

CREATE INDEX customers_sidx ON customers(location)INDEXTYPE IS MDSYS.SPATIAL_INDEXPARAMETERS ('LAYER_GTYPE=POINT');

4. 如果需要在事務中進行大量的資料刪除/插入時可以考慮為空白間索引設定SDO_DML_BATCH_SIZE參數，在事務中刪除/插入資料後並不會馬上就更新索引，而是在事務提交時、或者刪除/插入的數量達到某個批量值時統一索引更新，這個值（即SDO_DML_BATCH_SIZE）預設為1000，對於大多數操作這個值足夠了。但是如果你的表在工作中會有大量刪除/插入操作，那麼可以考慮將這個值設得更大以提高效率，代價是更多的記憶體和系統資源消耗：
CREATE INDEX  customers_sidx ON  customers(location)
INDEXTYPE  IS MDSYS.SPATIAL_INDEX
PARAMETERS ('SDO_DML_BATCH_SIZE=5000');

這個值最好設在5000-10000之間。

另一個提高大量插入刪除效率的方法是在大大量操作之前刪除索引,之後再重建索引

5. 對於一個有N個記錄的表建立空間索引：
1). R-tree的空間索引表大概需要100×3N個位元組的儲存空間；
2). 在R-tree空間索引建立過程中，在臨時的資料資料表空間中需要200×3N到300×3N位元組的額外儲存空間。
可以通過下列語句估算為一個空間圖層建立R-tree索引需要的儲存空間大小：
SELECT sdo_tune.estimate_rtree_index_size('SPATIAL', 'CUSTOMERS', 'LOCATION') sz FROM dual;
sz
-----------------
1

結果為1，表示
1). 索引資料需要1M位元組的儲存空間，這是索引資料本身所需的儲存空間，此外在索引建立過程中2到3倍這個值的儲存空間；
2). 當建立空間索引時，指定session參數SORT_AREA_SIZE為這個值（1MB）會最佳化索引建立過程。

6. 如果使用SDO_NN空間操作符的效率問題
1) SDO_NN空間操作時，空間索引會遮蔽其它索引，因此不要期望使用SDO_NN空間操作符時在WHERE語句中加入更多的限制條件會加快查詢速度，這時對SDO_BATCH_SIZE參數進行微調有可能會提高查詢效率。
SELECT ct.id, ct.name, ct.customer_grade
FROM  competitors comp, customers ct
WHERE comp.id=1
AND ct.customer_grade='GOLD'
AND SDO_NN(ct.location,  comp.location)='TRUE'
AND ROWNUM<=5
ORDER BY ct.id;
儘管customer_grade欄位有索引，但是這不會加快空間查詢的速度，執行時可能先找出10條最近的記錄，看是否是'GOLD'的，如果不是則找出接下來10條最近的記錄，。。。。。直到所有'GOLD'的使用者有5條為止。

如果預計5條'GOLD'使用者肯定在前100個最近的記錄裡，則通過設定SDO_BATCH_SIZE參數為100可以加快查詢速度：
SELECT ct.id, ct.name, ct.customer_grade
FROM  competitors comp, customers ct
WHERE comp.id=1
AND ct.customer_grade='GOLD'
AND SDO_NN(ct.location,  comp.location, 'SDO_BATCH_SIZE=100' )='TRUE'
AND ROWNUM<=5
ORDER BY ct.id;

如果你不知道SDO_BATCH_SIZE該設為多少，就設為0，索引會在使用合適的內部值。

2) 限定SDO_NN返回的記錄數量會加快查詢的速度，這通過調整SDO_NUM_RES設定：
SELECT ct.id, ct.name, ct.customer_grade
FROM  competitors comp, customers ct
WHERE comp.id=1
AND SDO_NN(ct.location,  comp.location, 'SDO_NUM_RES=5')='TRUE' ;
效果與這個相同：
SELECT ct.id, ct.name, ct.customer_grade
FROM competitors comp, customers ct
WHERE comp.id=1
AND SDO_NN(ct.location,  comp.location)='TRUE'
AND ROWNUM<=5
ORDER BY ct.id;

空間索引與分區
上面建立的索引是全表範圍內的，是“全域”索引，在對資料庫表進行分區後，可以在每個分區上建立一個“本地(Local)”索引。
進行帶空間操作符的空間查詢時，Oracle會在每個分區的索引上進行查詢，然後將各個分區上的查詢結果彙集，最後將結果返回給使用者。因此，分區索引並不總能加快查詢速度。
分區也會影響查詢返回的結果：
例如SDO_NN操作符中的SDO_NUM_RES參數會指定合格結果數量，但是如果是分區索引的話，則在每個分區上都得滿足此參數，如果有3個分區，那麼最終返回的結果數量將是SDO_NUM_RES×3，而不是SDO_NUM_RES。

註：以上討論的分區是由限制的，只能是range分區，list分區和雜湊分割都不行

空間索引與並行
建立索引時可以指定索引並行，例如： Plsql代碼  

1. CREATE INDEX customers_sidx ON customers(location)   
2. INDEXTYPE IS MDSYS.SPATIAL_INDEX   
3. PARALLEL [parallel_degree];  

CREATE INDEX customers_sidx ON customers(location)INDEXTYPE IS MDSYS.SPATIAL_INDEXPARALLEL [parallel_degree];

並行參數parallel_degree是可選的，它定義了並行度，如果大於1，則索引建立時會並行進行。

但查詢時無法指定並行，查詢的並行實際上與資料庫表的分區有關，也就是說查詢會在每個分區上並行的進行。因此設定表的並行度並分區會提高使用空間索引的空間分析操作的效能。 Plsql代碼  

1. ALTER TABLE customers PARALLEL 2 ;  

ALTER TABLE customers PARALLEL 2 ;

空間索引的重建
在對錶進行大量（大約30%）刪除後，對空間索引進行重建可以提高未來資料的查詢效率： Plsql代碼  

1. ALTER  INDEX customers_sidx  REBUILD ;  

ALTER  INDEX customers_sidx  REBUILD ;

重建時也可以指定參數： Plsql代碼  

1. ALTER  INDEX customers_sidx REBUILD   
2. PARAMETERS ('layer_gtype=POINT');  

ALTER  INDEX customers_sidx REBUILDPARAMETERS ('layer_gtype=POINT');

注意：
1. ALTER INDEX是一個DDL語句，因此會導致當前事務的提交；
2. 空間索引重建是個耗時操作，它以獨佔鎖定的方式阻止了在空間索引上的其它操作，從而造成相應空間查詢操作的阻塞，不過可以指定ONLINE關鍵字避免這種堵塞發生： Plsql代碼  

1. ALTER  INDEX customers_sidx REBUILD  ONLINE   
2. PARAMETERS ('layer_gtype=POINT');  

ALTER  INDEX customers_sidx REBUILD  ONLINEPARAMETERS ('layer_gtype=POINT');

ONLINE重建索引的過程：

重建的索引資料儲存到新索引表上，重建時舊的索引表仍在，舊的查詢在舊索引上，因此重建過程不會影響進行中的查詢，在新索引建好後再切換到新的索引表上。