Results 1 to 2 of 2

Thread: Data Purging

  1. #1
    Join Date
    Aug 2011
    Posts
    1

    Unanswered: Data Purging

    We have 2 table:

    - BE Table
    Name Null Type
    ---------------------- -------- --------------
    BID NOT NULL NUMBER(10)
    BUSINESSENTITYTYPEID NOT NULL NUMBER(10)
    PARENTID NUMBER(10)
    VERSION NOT NULL NUMBER(10)
    FULLNAME VARCHAR2(255)
    PHONE VARCHAR2(255)
    FAX VARCHAR2(255)
    EMAIL VARCHAR2(255)
    WEBSITE VARCHAR2(255)
    LOCALE VARCHAR2(255)
    OFFICIALREGISTRATIONID VARCHAR2(255)
    STREETSHORT VARCHAR2(255)
    STREETLONG CLOB
    CITY VARCHAR2(255)
    PROVINCEORSTATE VARCHAR2(255)
    COUNTRYCODE VARCHAR2(255)
    POSTALORZIPCODE VARCHAR2(255)
    FIRSTNAME VARCHAR2(255)
    LASTNAME VARCHAR2(255)
    MIDDLENAME VARCHAR2(255)
    HONORIFIC VARCHAR2(255)
    BIRTHDATE DATE
    SIN VARCHAR2(255)
    EXTENSIONSHORT RAW(2000 BYTE)
    EXTENSIONLONG BLOB
    ALTERNATEID VARCHAR2(25)
    DELETED CHAR(1)

    -- CUSTPAYMETHOD Table
    Name Null Type
    ---------------------- -------- --------------
    PAYMENTMETHODID NOT NULL NUMBER(10)
    CUSTID NOT NULL VARCHAR2(255)
    EFFECTIVESTARTDATE NOT NULL DATE
    EFFECTIVEENDDATE DATE
    SUSPENDED NOT NULL CHAR(1)
    VERSION NOT NULL NUMBER(10)


    1) Insert a new parameter CUST_PMNT_MTHD_RETENTION_PERIOD into system_parameter table(which is only a parameter table).

    2)Create a new Customer(user id) through the Customer application and then find out the custID for the new customer.

    Store Proc will Update the CustID in the CustPayMethod table to the new customer ID (that was created) when the EFFECTIVESTARTDATE older than CUST_PMNT_MTHD_RETENTION_PERIOD days[will provide] and suspended=Y.

    copy records with custid=1(anonymous user) and startdata older than retention_period and suspended='Y' to Table CUSTPAYMETHOD

    Eventually,delete record from BE table.

    --
    Create a SP to "relink" the custpaymethod table records that meet the folllowing criteria to the special user
    CUSTID=1 (anonymous user)
    EFFECTIVESTARTDATE older than CUST_PMNT_MTHD_RETENTION_PERIOD days SUSPENDED='Y'.



    When ever the BE table is read it gives more than 80K records from cust_id=1 and so on and becomes a bit slow ..

    Now, when we copy the records from BE to CUSTPAYMETHOD (assume 30k). Next time when we read the table it should show us the customerid from 3001based on New Customer's(user created) Customerid.

    Note:
    ----
    CUSTOMERID column is logically linked BUSINESSENTITYID column (FK not enforced)

    We don't have to pass parameter as we know the cust_id=1,flag for suspended='Y' and Eff_date between date1 and date2..We have accept from/to Period....

    Probably a loop to insert....

    This particular SP will run in all env and the id is going to be different how we are going to handle this...

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    And your question is????
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •