2

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.

Improve this question
4
  • You don't say db version, the average number of rows in the table and average number of rows pulled for each status when the query runs. Those are useful pieces of information for analysis. and also multiple times is few times a day/hour/minute ?
    – Raj
    Commented May 17, 2017 at 15:08
  • if you are licensed for partitioning, then I'd recommend partitioning on the day. FTS could, sometimes be faster than index lookups.
    – Raj
    Commented May 18, 2017 at 0:54
  • @Raj, wanted to avoid version specific solution to keep the application portable to other databases. These are mainly reconciliation queries with external systems. For InProgress states, it runs every 5 minutes (0.2% relevant records compared to full table) and for Failed and Success states it runs once in a day ( 2%) which would keep progressively decreasing.
    – pradosh nair
    Commented May 18, 2017 at 2:25
  • Are you licensed for partitioning? What are some typical values that you'll pass in for the date filters? Will they always be within the last X days? How quickly does the query run now and how quickly do you need it to run? Does a query that runs once a day really need to be fast?
    – Joe Obbish
    Commented May 18, 2017 at 3:27

1 Answer 1

Reset to default
1

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.

Improve this answer
5
  • had two concerns with created_time was that one it will be almost as big as the original table due to the number of records and second was skewing of the index.
    – pradosh nair
    Commented May 18, 2017 at 2:32
  • Do you mean that number of distinct values of created_time is close to number of records? Then it it's great selectivity, and very good for index. In addition , it seems that clustering factor for such an index will be very close to number of leaf blocks which is very good as well. Not sure I follow skewing index- do you mean you will have millions of rows for one day, and nothing for 3 months in a row ? From what I see in your question, it's rather STATUS data is skewed ` - very few for one status, and many for others, so optimizer may not choose the right plan.
    – a1ex07
    Commented May 18, 2017 at 14:43
  • you are right, going ahead with your recommendation.
    – pradosh nair
    Commented May 18, 2017 at 14:50
  • give it a try first on test environment first. never test on production :-) no matter how good solution is (or appears)
    – a1ex07
    Commented May 18, 2017 at 15:01
  • :) "by going ahead" I meant, I will try that option from test/uat and then production. Also i acknowledge that this solution will definitely work till medium database and may need different strategies once it reach large/very large sizes.
    – pradosh nair
    Commented May 18, 2017 at 15:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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