Incorporating Updates in Domain Indexes: Experiences with Oracle Spatial R-trees

• Authors: Ravi Kanth V. Kothuri;Siva Ravada;Ning An
• Venue: ICDE '04 Proceedings of the 20th International Conference on Data Engineering
• Year: 2004

Much research has been devoted to scalable storage andretrieval techniques for domain databases such as spatial,text, xml and gene sequence data. Many efficient indexingtechniques have been developed in this context. Given theimprovement in the underlying technology, database applicationsare increasingly using domain data in transactionalsemantics. In this paper, we examine the issue of when duringthe lifetime of a transaction is it better to incorporateupdates in domain indexes. We present our experiences withR-tree indexes in Oracle.We examine two approaches for incorporating updatesin spatial R-tree indexes: the first at update time, and thesecond at commit time. The first approach immediatelyincorporates changes in the index right away using systemtransactions and at commit time makes them visibleto other transactions. The second approach, referred toas the deferred-incorporate approach, defers the updatesin a secondary table and incorporates the changes in theindex only at commit time. In experiments on real datasets, we compare the performance of the two approaches.For most transactions with reasonable number of updateoperations, we observe that the deferred approach outperformsthe immediate-incorporate approach significantlyfor update operations and with appropriate optimizationsachieves comparable query performance.