We have performance problems with some queries. We always use the rank() function to get the latest entries with a status. The question is, how to speed up things.
CREATE TABLE PAYMENT ( CLIENT VARCHAR2(30) NOT NULL, CUSTOMERID VARCHAR2(35) NOT NULL, PAYMENTID VARCHAR2(99) NOT NULL, ... CONSTRAINT PK_PAYMENT PRIMARY KEY (CLIENT, CUSTOMERID, PAYMENTID) ) CREATE TABLE PAYMENT_STATUS ( CLIENT VARCHAR2 (30) NOT NULL, CUSTOMERID VARCHAR2 (35) NOT NULL, PAYMENTID VARCHAR2 (99) NOT NULL, STAMP NUMBER (16) NOT NULL, STATUS VARCHAR2 (10) NOT NULL ) select * from (select SP.PAYMENTID, SP.CREDITORIBAN, SP.CREDITORBIC, SP.MANDATEVERSION, SP.MESSAGEREFERENCE, SP.AMOUNT, SP.CURRENCY, SP.PURPOSE, SP.TEMPLATE, SP.CATEGORY, SP.DUEDATE, SP.SOURCESYSTEMNAME, SP.PAYMENTTYPE, SP.SEQUENCETYPE, SP.PRENOTIFICATIONCHANNEL, SPS.STAMP, SPS.ADDITIONALINFO, SPS.STATUS, SP.INTERNALMANDATEID, SP.PAINMESSAGEID, SP.PAINCOLLECTORID, SP.EXTERNALMANDATEREFERENCE, SP.EXTERNALMESSAGEREFERENCE, rank() over (partition by SPS.PAYMENTID order by SPS.STAMP) LATEST from SDDM_PAYMENT SP, SDDM_PAYMENT_STATUS SPS where SP.CLIENT = 'Client' and SP.CUSTOMERID = 'Customer' and SPS.CUSTOMERID = SP.CUSTOMERID and SPS.CLIENT = SP.CLIENT and SPS.PAYMENTID = SP.PAYMENTID ) ALL_PAYMENTS where status = '020300' and LATEST = 1
I have ca. 250000 entries in payment and 400000 entries in payment_status and we have to handle 100M entries in the future. This query does take a long time (30s). How can i speed up the query? I also played a litte bit with indexes, but without results. Can someone help?