Clever Geek Handbook

Order by making a very slow application using oracle

In my application, I need to generate a transaction history report that is being done by all clients. I used Oracle 12c for my application. I have 300 thousand clients. This table lists the associated customer data and the transaction history table. I wrote a query to generate a monthly transaction history. It returns about 20 million records.

SELECT C.CLIENT_ID, CD.CLIENT_NAME, ...... FROM CLIENT C, CLIENT_DETAILS CD, 
TRANSACTION_HISTORY TH
--Condition part
ORDER BY C.CLIENT_ID

These 3 tables have correct indexes which are working fine. But when fetching data using order to show customers so that this request takes 8 hours to complete the batch process.

I analyzed the cost of the request. The cost is 80085. But when I delete the order on request, the cost becomes 200. So I deleted the order for now. But I need to show customers on order. I cannot use the limit. Is there a way to overcome this?

+3


source to share


1 answer


you can try indexing the client id on the table, which will speed up the table's performance for fetching data in a specific order.

you can use link to link: link



Hope this helps you.

0


source






More articles:


All Articles