I have a table in DB oracle with like 1million entries. The most important columns are the ID (primary key) and CREATE_TIMESTAMP. This last column is timestamp(6).
What i want to do is a query that returns the latest 20 rows that were created, optimized…
i tried this queries:
select * from async_events
WHERE CREATE_TIMESTAMP >= trunc(sysdate)-1 and CREATE_TIMESTAMP <= trunc(sysdate) and rownum<=5
select * from async_events
where CREATE_TIMESTAMP >= CURRENT_TIMESTAMP(6) - interval '5' day
this queries works, but they take at least 2minutes because they don’t avoid the exausting search in all rows.
PoL -> your query works but it takes more than 2 minutes because the LIMIT doesnt avoid the search of all rows. First it gets all rows ordering by timestamp, then it returns the rows with limitation number we said.
rosoft2001 -> That would be perfect if the primary key is a SEQUENCE number. But it is not… I dont know how to get the latest inserted rows with a random primary key
I can only come upon some hacks for solutions, which might work or not depending on your system, but surely are not the most elegant solutions in the world:)
If you control the creation of the new records, you could put a copy of all new records in another db/table, where you control the table type and structure and can make indexes. So this table to could serve only for the purpose of querying newly added records.
If there is no deletions/updates of records in the table that you query AND if you query the table more often than records are added. You might save the 20 records + a Count of records, and then start out with a COUNT query to see if new records where added. If not you can use the saved records.
if it is not needed to have completely updated data, you might make a copy of the whole table at some interval (fx once a day) and query that. (off course the copy should be in a faster table-structure).
Your solution is well fancy and explained. But im not able to implement it. This project is from my company and im not able to create/alter tables or something about Database level.
Even more i dont know if there is a solution for my problem with my limitations… maybe i have to bother my boss to treat this in DB