But in this case that would do no good (not that I could recommend buying 5 partitioning licenses for this problem anyhow). ArcCatalog is trying to determine the BBOX for all the data. The sdo_aggr_mgr function ignores the indexes (that have the data it needs) and instead does a FTS and re-compute of all the data. I still can't believe nothing has happened in all this time. What exactly are those spatial license fees doing for us again?

If I could only spend a week at Oracle with the source code, I could easily fix this problem. Very frustrating!

SDO_AGGR_MBR - I don't understand why this is a problem, as SDO_TUNE.EXTENT_OF uses the same indexes. Is it not a simple matter to enhance SDO_AGGR_MBR such that given the rowid's from other predicates (where id between 1 and 100 in your example), select the matching rows from the index tables, do the same min/max that extent_of uses, and return the results? How is that not "safe"?

BTW, the 11.2 documentation for SDO_AGGR_MBR states:

"The SDO_TUNE.EXTENT_OF function, documented in Chapter 31, also returns the MBR of geometries. The SDO_TUNE.EXTENT_OF function has better performance than the SDO_AGGR_MBR function *if the data is non-geodetic* and if a spatial index is defined on the geometry column;"

So - I thought SDO_TUNE.EXTENT_OF didn't work with geodetic data (and honestly had not used it since about 2006) - and the docs here seem to infer that it won't. But I tried the extend_of function on the table, and it does seem to work (although that will not immediately fix my problem since that is coded in their software). So... is SDO_TUNE.EXTENT_OF safe for use with geodetic SRIDs? Anything to watch out for?

Here I created two tables, T1 and T2. T2 has an index, T1 does not. The optimizer noticed there was an index on the column we were using on T2 and chose a index scan instead of a table scan. I would expect the same with the SDO_AGGR_GEOM function (sum up the mins and maxes).

yhu wrote:
And another example when I mean by "safely":

SELECT sdo_aggr_mbr(SDO_GEOM.SDO_CENTROID(shape, 0.05))
FROM cola_markets
WHERE id between 1 and 100;

i.e. sdo_aggr_mbr() can accept any geometry (temporary or persistent).

Yes, I get that it is not trivial. The optimizer has to recogize what is being passsed into the function (raw column or temporary geom). Then it has to ensure there is a spatial index exists. When those conditions are met, it can then scan the index with the pre-computed MBRs instead of re-computing all of them AFTER a FTS.