Please note that Bug 11797492, which you mentioned, is not a spatial bug.
If you'd like to discuss your spatial index problems, please make them specific.
Edited by: yhu on Jul 27, 2011 6:19 AM
Thank you for looking at the spatial forum, us users really appreciate this. But - really? How was my SR 3-2097288191 not specific? Please explain.
In a nutshell: SDO_FILTER is broken in 11gR2. It makes spatial selections slow as molasses in winter. Oracle's response is that it is NOT a bug, and was put in as a feature enhancement for the elusive 12 version.
Out of the blue a few weeks later Oracle contacted me by phone (I guess so there is no electronic record) and told me that they realized it was a bug, and a patch for the 11 series would be forthcoming.
It didn't. I and the others here are still waiting.
As far as we are concerned, unless you accept the (user provided) workaround that fixes 126.96.36.199 (and as reported here by a user does no longer work with 188.8.131.52) - 11gR2 is completely unusable for vector spatial operations. SDO_FILTER is a CORE function for spatial - not some fringe item.
So at this moment, the only option is to stay with the still functional 10gR2 release. If some security patch that we are supposed to apply breaks it, we will simply apply for a waiver to keep from pushing it to production.
If / when Oracle moves this to a #1 concern and actually put out a patch to correct this defect we will not move to 11gR2. And since 10gR2 works fine for us, we simply see no reason to pay for support for spatial that does not seem to exist.
Who knows, at this rate, 12 may be out before anything changes. And maybe 12 will correct the issue, and maybe not. All we have is Oracle's word on this at the moment.
You mentioned that the following workaround worked for you on 184.108.40.206:
DISASSOCIATE STATISTICS FROM INDEXTYPES spatial_index FORCE
DISASSOCIATE STATISTICS FROM PACKAGES sdo_3gl FORCE;
DISASSOCIATE STATISTICS FROM PACKAGES prvt_idx FORCE;
If so, it will also work on 220.127.116.11 . Have you tried 18.104.22.168? Anyway, if you
find any problems, please let us know.
Bug 11797492 is related to query transformation, i.e. a view (not a table)
is used in the from clause. Note that this is not a spatial bug, as far as I know.
Good news... I have several USAF Oracle reps pushing for a resolution to this now. Something may actually happen.
I think they are finally understanding that this IS a serious bug and should be fixed in 11g. It is currently marked as "Development Working". I'll keep you all advised if/when a proper patch is created. We may yet be able to use 11g in production before 12 comes out.
As to the 22.214.171.124 issue, the "not recommended but we have no other solution" work-around does still work on an upgraded 126.96.36.199 version. However - that index work-around does not avoid the recursive SELECT DIMINFO call that still slows down spatial selects.
Update... things are looking up:
A request has been submitted on Bryan's behalf to have the fix for Bug 9743250 "FULL TABLE SCANS IN SPATIAL QUERIES" included in 188.8.131.52.0 when it becomes available, which should be before June 2013.
In case you didn't notice, the bug-fix for this was included in the 184.108.40.206 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 220.127.116.11 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 18.104.22.168- 22.214.171.124 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.
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.