0

I have a transactions table where when the data is fairly distributed across the accounts, the select query is very fast to fetch records for a particular account and order by transaction id, say:

select trans_id, account_no, file_content 
from  transactions_view 
where account_no =:accountNo 
order by trans_id

transaction_view is backed by transactions table. transaction_view is a normal view. account_no and trans_id are indexed in table.

So, if I add 1 million records first for account (2) transactions. Then I add the next million transaction across accounts 1 to 10. So the above select query is still fast for account 2 (low ms). But for other accounts it becomes very slow 10s+.

Any recommendations how to deal with skewed data and ordering data by indexed field to improve the query to become fast when fetching for any account?

There are currently around 60 accounts. I tried hash partition on account_no on table. Table also has range partition on date

I noticed if I change order by to account_no, trans_id. And create composite index with (account_no, trans_id) becomes faster. Will do more tests and see.

Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
user518066
  • 1,277
  • 3
  • 23
  • 35
  • How may distinct accounts you have ? Did you try Range or Hash partition for the Transaction table. – Himanshu Kandpal Jun 17 '21 at 17:12
  • 1
    Did you gather statistics after adding all those records? Do you have histograms for ACCOUNT_NO? – APC Jun 17 '21 at 18:12
  • Post your [execution plan](https://stackoverflow.com/a/34975420/4808122). And yes, if you are doing `index range scan` you will profit if the table data are in same / similar order as the index keys. On the other hand, you will not want to do `index access` to get *millions* of rows. – Marmite Bomber Jun 17 '21 at 18:24
  • Please, provide index definition, execute your `select` statement with `/*+gather_plan_statistics*/` hint, then run `select * from table(dbms_xplan.display_cursor(format=> 'BASIC +PREDICATE ALLSTATS LAST'))` in the same session and compare *real* execution plans for fast and slow statements. This will tell you *what* is different. If you need to find *why*, you'll need to examine 10053 trace event. – astentx Jun 17 '21 at 21:00
  • A SQL Monitor report for each query would be even more useful – BobC Jun 17 '21 at 21:12

0 Answers0