Efficient way to retrieve just today's data ?
452507May 23 2007 — edited May 24 2007I'm wondering what the best way in Oracle is to retreive just records created with todays date. There may be millions of rows, but only a thousand or so for today. I'm thinking an index on the timestamp column, and a query that would cause an index range scan. Something like where someColumn >= trunc(sysdate) and someColumn < trunc(sysdate)+1
Is there a better way than this ? Example:
--Create some dummy test data, and index the datetime column
CREATE TABLE t (id number,dt TIMESTAMP(6));
INSERT INTO t SELECT rownum, sysdate - mod(rownum,10000) FROM all_objects;
commit;
CREATE INDEX t_idx1 ON t(dt);
--query that uses index
select * from t where dt >= trunc(sysdate) and dt < trunc(sysdate)+1