I have a history table which has an 'ID' and 'TIMESTAMP' column as such
CREATE TABLE hist (
HIST_ID INTEGER,
HIST_TIMESTAMP TIMESTAMP,
ID INTEGER, -- this is the id of the table that is being tracked
--OTHER COLS
);
I also have an index on this table as such
CREATE INDEX hist_ix ON hist (ID, HIST_TIMESTAMP);
This table has a lot of inserts against it and currently has about 30m rows in it.
When I try to run the following query, oracle does a full table scan instead of using the index (which .. at least I believe .. it should be able to use).
SELECT ID, MAX(HIST_TIMESTAMP) FROM hist WHERE HIST_TIMESTAMP <= <<A TIMESTAMP>> GROUP BY ID;
It seems to me that Oracle should be able to use the index to quickly identify which id/timestamp pair is just to the "left" of a specific point in time quickly via looking at the id/timestamp index on an id-by-id basis, but it's insisting on a full table scan.
Any help would be appreciated to get this query running quicker.
I have ran the following to make sure the statistics were up to date
EXEC DBMS_STATS.GATHER_TABLE_STATS('<meh>','hist');
Also, there are about 1k distinct ID values in the hist table.
With regard to data distribution.. Of the ~1k IDs, 50 have less than 100 entries in the table, 70 have between 100 and 1000 entries, 146 have between 1000 and 10000 entries, and the rest range from 10k to 60k entries. Over half of the entries have at least 30k records.
min
/tstmp > foo
)? – Mat May 9 '16 at 18:57