Incorporating updates in domain indexes: experiences with Oracle spatial R-trees

Much research has been devoted to scalable storage and retrieval techniques for domain databases such as spatial, text, XML and gene sequence data. Many efficient indexing techniques have been developed in this context. Given the improvement in the underlying technology, database applications are increasingly using domain data in transactional semantics. We examine the issue of when during the lifetime of a transaction is it better to incorporate updates in domain indexes. We present our experiences with R-tree indexes in Oracle. We examine two approaches for incorporating updates in spatial R-tree indexes: the first at update time, and the second at commit time. The first approach immediately incorporates changes in the index right away using system transactions and at commit time makes them visible to other transactions. The second approach, referred to as the deferred-incorporate approach, defers the updates in a secondary table and incorporates the changes in the index only at commit time. In experiments on real data sets, we compare the performance of the two approaches. For most transactions with reasonable number of update operations, we observe that the deferred approach outperforms the immediate-incorporate approach significantly for update operations and with appropriate optimizations achieves comparable query performance.