Given I have SOURCE_EVENT table in Oracle:
create table SOURCE_EVENT ( ID NUMBER(19) not null constraint PK_SOURCE_EVENT primary key, SOURCE_SYSTEM VARCHAR2(50 char) not null, EVENT_TYPE VARCHAR2(60 char), EVENT_TIMESTAMP TIMESTAMP(6), CONTRACT_NUMBER VARCHAR2(255 char), PERSON_CUID NUMBER(19), ) /
I want to effectively filter by all columns in this table and filtering should be dynamic, i.e. user can select in UI any combination of input fields.
I also use paging to display pages of 10 source events and total number of source events.
Current situation is I have 30 million rows stored in this table.
Examples of queries:
select * from (select s.id as id, s.source_system as source_system, s.event_type as event_type, s.event_timestamp as event_timestamp, s.contract_number as contract_number, s.person_cuid as person_cuid from SOURCE_EVENT s where s.source_system = 'source' AND s.event_type = 'event' order by s.id desc) where rownum <= 10; select count(s.id) as count from SOURCE_EVENT s where s.event_timestamp between TO_TIMESTAMP('2020-10-22 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF9') and TO_TIMESTAMP( '2020-10-23 23:59:59.999999999', 'YYYY-MM-DD HH24:MI:SS.FF9')
What is recommended way how to set up indexes?
Taking into consideration all combinations of predicates can be used