Hello,

i need some help to boost this SQL-Statement. The execution time is around 125ms.
During the runtime of my program this sql (better: equally structured sqls for different tables)
will be called 300.000 times.

The average row count in the tables lies around 10.000.000 rows and new rows (updates/inserts) will be added with a timestamp each day. Data which are interesting for this particular export-program lies in the last 1-3 days. Maybe this is helpful for an index to create. The data i need is the current valid row for a given id and the forerunner datarow to get the updates (if exists).

We use a Oracle 11g database and Dot.Net Framework 3.5

SQL-Statement to boost:

select 
  ID_SOMETHING,    -- Number(12)
  ID_CONTRIBUTOR,  -- Char(4 Byte)
  DATE_VALID_FROM, -- DATE
  DATE_VALID_TO    -- DATE

from
  TBL_SOMETHING XID

where
  ID_SOMETHING = :ID_INSTRUMENT
  and ID_CONTRIBUTOR = :ID_CONTRIBUTOR
  and DATE_VALID_FROM <= :EXPORT_DATE
  and DATE_VALID_TO >= :EXPORT_DATE

order by
  DATE_VALID_FROM asc;

Here i uploaded the current Explain-Plan for this query.

I'm not a database expert so i don't know which index-type would fit best for this requirement. I have seen that there are many different possible index-types which could be applied. Maybe Oracle Optimizer Hints are helpful, too.

Does anyone has a good idea for tuning this sql or can point me in a right direction?