In case you didn't notice, the bug-fix for this was included in the 126.96.36.199 patchset, and the 12.x release. So no more index-only work-around!
Today I will get to finally see the results on one of our dev databases, as the AIX version (what I'm now running) of 188.8.131.52 was just released this week.
Although the documentation with the patch doesn't mention anything about doing this, I think it is at least not a bad idea to un-do the work-around for 184.108.40.206- 220.127.116.11 before upgrading:
connect /as sysdba
alter session set current_schema=MDSYS;
ASSOCIATE STATISTICS WITH INDEXTYPES spatial_index USING sdo_statistics;
ASSOCIATE STATISTICS WITH PACKAGES sdo_3gl USING sdo_statistics;
ASSOCIATE STATISTICS WITH PACKAGES prvt_idx USING sdo_statistics;
BTW - If anyone knows if that is included in the upgrade script (I would certainly hope so - but I don't see it), please chime in. This is for out-of-place upgrades.
Thank you Bryan.
I seem to be running into this problem quite hard, on spatial queries with a query to find the filtering geometry and some other filter clauses in my query.
Did you find any negative effects or side effects from the DISASSOCIATE STATISTICS commands ?
The negative side effect is that with the DISASSOCIATE STATISTICS commands, the spatial index is always used.
While that is a good thing when you have selective queries, it is a very bad thing when you select all or a good portion of a table's data. It first finds the candidate in the index, uses the rowid to look up the row, and then returns it - over and over - instead of just doing a full table scan and discarding the few (if any) rows that do not fit the criteria.