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),
    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;

    count(s.id) as count
    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


Taking into consideration all combinations of predicates can be used

One option would be a bitmap index on the non ID, non TIMESTAMP column.

One index per column.

Beware of its it's downsides.

  • serialization
  • possible slower DML

Luckily, the newer versions of Oracle allow you to experiment with indexing strategies.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.