Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Efficient way to retrieve just today's data ?

452507May 23 2007 — edited May 24 2007
I'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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 21 2007
Added on May 23 2007
22 comments
2,515 views