Hi,
I have very large table which constantly grows. The search is executed by ID column, which is part of PK.
create table TEST (
ID varchar2(20) primary key,
VALUE varchar2(20),
CREATED_TS timestamp default := systimestamp
);
Same time most of select queries are looking for relatively recent records (i.e. few months). To avoid full index scan all the time I can try to execute the query in two steps - first, I search with additional predicate for time frame, i.e.
select * from TEST where ID='xyz' and CREATED_TS > systimestamp-100
an only if record is not found I will execute query for all dates -
select * from TEST where ID='xyz'
99% of time that will be limited to only one query. Fine, especially if we add an index on (CREATED_TS, ID). But such approach is problematic when I need to search for list of ID values, i.e. using
"ID in ('val1', 'val2', 'val3'...)"
syntax. In such case I need narrow the list for the 2nd step by values not found on 1st step. That's is very inconvenient.
Can the same logic be implemented using built-in Oracle functionality? For example I was thinking about partitioning of the table by CREATED_TS ranges. If somehow it is possible to let Oracle know that it should start search in the most recent partition then the challenge could be solved. But I don't know optimization hints for ordered partition scanning.
I don't think this is unusual situation, so expect some already existing solution from Oracle.
Thank you,
Alexander