Periodically rebuilding of R-Tree Indexes
438473Feb 16 2005 — edited Feb 22 2005Hi Community!
I have some questions on r-tree indexes.
1. If there is need to periodically run rebuilding of r-tree indexes when their quality degrades after huge number of inserts/updates/deletes?
As it is said in "Oracle® Spatial User's Guide and Reference", chapter "1.7.1.1. R-tree Quality":
"A substantial number of insert and delete operations affecting an R-tree index may degrade the quality of the R-tree structure, which may adversely affect query performance....
... rebuilding the index may help the performance of queries."
But, as I realized, r-tree index has some "tree-like" structure, similiar to normal b-tree indexes. So, as it's said at AskTom site about b-tree indexes: sheduled rebuilding is "Sort of like a police officer sticking his hand into a dark room and shooting a gun, hoping to kill the bad guys. Problem is, there are good guys in the room too".
So - what point of view is correct?
If the answer on 1-st question is "yes, you need to rebuild R-tree index when its quality degrades", then I have few more questions:
2. What value of sdo_tune.rtree_quality function must be to run r-tree index rebuilding? 0.5? more or less?
3. I wrote a pl/sql procedure, which run r-tree index rebuilding if sdo_tune.quality_degradation<=0.5. It works just fine, when i'm running it manualy (from SQLplus, for example). But(!) when i tried to run it using the job (dbms_job), it fails with such error messeges:
"-29858 - ORA-29858: error occurred in the execution of ODCIINDEXALTER routine
ORA-29400: data cartridge error
ORA-01031: insufficient privileges
ORA-13249: internal error in Spatial index: [mdidxrbd]
ORA-13249: Error in Spatial index: index build failed
ORA-13249: Error in R-tree: [mdrcritbl]
ORA-13231: failed to create index table [MDRT_1C89E$] during R-tree creation
ORA-29400: data cartridge error
ORA-01031: insufficient privileges"
(note: i gave to my user privilege 'execute' on MDSYS.SPATIAL_INDEX indextype and on MDSYS.SDO_INDEX_METHOD_9I type - it does not help :-(
What privilege problems it may be?