I am not really sure what timaspan and time_interval really is all about, but they're defined in:
-- packages/acs-events/sql/timespan-create.sql -- This script defines the data models for both time_interval and timespan.
Our tables have quite a few of those:
SQL> SELECT COUNT(*) FROM timespans; COUNT(*) ---------- 12681 SQL> SELECT COUNT(*) FROM time_intervals; COUNT(*) ---------- 25362
And I'm pretty sure Oracle will slow down due to parsing of the query, instead of bind'ing the variables. But that couldn't be the major problem here.
Yes, I'm sure it doesn't run this slow on SloanSpace.
I'll talk to our dba on monday, try to figure out if there's problems with our Oracle.
I tried myself yesterday to use some Oracle tools to get an explain plan. Seemed getting 3 (full) tables took 64 out of a total of 80 costs. But I don't remember the details, and I'm most certainly no dba. :-\