Tuning a query to fetch all the records in a day with certain status

indexoracleperformancequery-performance

We are currently using Oracle Database:

Consider a table TRANSACTION with the fields TRAN_ID, STATUS, CREATED_TIME, ….

The below query gets executed multiple times in a day.

select tran_id 
from TRANSACTION table 
where created_time <= TO_DATE(:1,'YYYY-MM-DD HH24:MI:SS') 
and created time > TO_DATE(:2, 'YYYY-MM-DD HH24:MI:SS') 
AND STATUS = :3`

Possible values are I- InProgress, F- Failed, S - Successful. Currently only TRAN_ID is Indexed, so the table goes through a full table scan.

Have the following options:

  1. Introduce an index on status, this will solve the problem for handling 'In Progress' transaction as they can be low, but failed and success would be higher (However query on 'failed' or 'success' would run once or twice in a day).
  2. Introduce a composite index(status,created_at) but as I understand the data would get skewed as time progresses and index table will also grow rapidly.
  3. Move the data from the in progress table to another history table at regular intervals so that it can work coupled with option a. coupled with quite some application changes.

I am not sure if there are other options available.

Best Answer

B-tree Index on Status won't help - it has 3 distinct values, and full table scan will be much faster. Bitmap index should work better in such case, but TRANSACTION table name implies lots of DML , so it shouldn't be used.

Some improvement may be gained by partitioning transaction table by status, and then create local index on created_time. In a sense it's similar to (3), but don't require application changes, and moving rows to proper partition is done by the engine itself. However, it is quite an expensive option, and should be used for really huge databases .

In my opinion, index on created_time will work just fine for most of the cases.