Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

Execution plan:

enter image description here

share|improve this question
    
What is the primary key of PAYMENT_STATUS and what other indexes are there in that table? –  ypercube Nov 16 '13 at 21:59
    
The only index i created on payment_status is create index payment_status_x1 on payment_status (client,customerid,paymentid) –  crash Nov 17 '13 at 12:55
    
I suppose you have no foreign key defined either. It would help if you added the execution plan. –  ypercube Nov 17 '13 at 13:05
    
Yes, there is no foreign key defined. –  crash Nov 17 '13 at 13:17
    
How many rows (on average) do you expect to be in payment for each (client, customerid)? How many rows are you expecting for the status = '020300'? Also, are you able to make any schema changes? –  Chris Saxon Nov 17 '13 at 15:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.