Different geometry storage is queried differently. ST_GEOMETRY uses LOB, while
SDEGEOMETRY uses LONG RAW (which is basically your 2.5x difference right there).
Spatial index preference would be determined by information you haven't provided
(the size of the search window, the envelopes of the layers, and the number of
features returned by the query).
One quarter of the features is probably past the point where a spatial index
would be appropriate. The exact ratio may have been changed over time.
Re above: I am still a little confused as to what i expect would be going on with these spatial queries. As I would have thought the most efficient way to query a large polygon geodatabase is (almost always?) using a spatial index?
Why would you expect that? Index efficacy is an established science. There's nothing
special about polygons which would suddenly improve the cost/benefit relationship of
index I/O to full table scan I/O; in fact, I would expect polygons to be *more* expensive
than simpler features.
I try to avoid any query which returns a significant
fraction of a large table, so most of my efforts are spent optimizing for small random
You've left out too many details to begin to evaluate why this one query performs in the
manner it does.