Need help in tuning a sql-query

Question

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_SOMETHING
  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?

Answer 1

The explain plan looks as good as it can get, but that doesn't necessarily mean much. The index proposed by Quassnoi is exactly what I would propose, too.

Anyway, doing 300000 similar queries in your program makes me ask: is this necessary? Maybe you can reach the same goal with less queries, each doing a bit more.

If you can't avoid doing so many queries, you should at least use prepared statements. If you use LINQ, that's compiled statements for you. This way, you avoid the parse overhead which is likely a considerable part of the overall expenditure, especially for such simple queries.