Need help in tuning a sql-query

Question

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_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

Create a composite index:

CREATE INDEX ix_something_s_c_d ON tbl_something (id_something, id_contributor, date_valid_from)

Unfortunately, you are searching for a constant within the range of two columns, not for a column within a range of two constants, so the last field is not very selective. It can help in ordering, though.

Answer 2

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.

Answer 3

You say:

Data which are interesting for this particular export-program lies in the last 1-3 days.

Does this mean that you are interested in rows where DATE_VALID_FROM is within the last three days? If so, you may get more joy from an index which looks like this:

create index something_idx 
  on tbl_something (date_valid_from, id_something, id_contributor, date_valid_to)
/

Including date_valid_to means that the index read can satisfy the query without touching the table at all. Leading with date_valid_from puts all the rows which are likely to interest you in the same chunk of index space.

The above assumes that your 300,000 calls are for various different values of id_something and id_contributor. If that assumption is false - say they are all for the same id_contributor or you execute 50,000 calls for the same id_contributor in succession - then it would make more sense to lead with (id_contributor, date_valid_from ...). As is usually the case with query tuning the specifics of the business logic are crucial to finding a happy outcome. Oh, and benchmarking different ideas is essential.

I do agree with AmmoQ that executing the same statement 300,000 times in the one process sounds like a RBAR implementation that might be more suited to a set-oriented approach.