Oracle query last 5 records where sparsely populated


Walk the last 'n' max(timestamp) records:

AND id = 123
AND a.timestamp   >=
(SELECT MAX(TIMESTAMP) FROM data_table WHERE id = 123 AND TIMESTAMP   <
(SELECT MAX(TIMESTAMP) FROM data_table WHERE id = 123 AND TIMESTAMP   <
(SELECT MAX(TIMESTAMP) FROM data_table WHERE id = 123 AND TIMESTAMP   <
(SELECT MAX(TIMESTAMP) FROM data_table WHERE id = 123 AND TIMESTAMP   <
(SELECT MAX(TIMESTAMP) FROM data_table WHERE id = 123)))))

This method was chosen because the data must be ordered descending and using any particular time range may cover an enormous amount of high activity data or little. Always wanted the last 5 records and didn't want to incur high sort costs when lots of data was in the window.

The index columns included (id, timestamp) This is a very fast query because the timestamp b-tree can be walked quickly.

Comments

Popular posts from this blog

Sites, Newsletters, and Blogs

Oracle JDBC ReadTimeout QueryTimeout