--ENABLE AUTO TRACING, ETC FROM SQL*PLUS FOR VERBOSE INPUT

SPOOL PARTITION_DELETE_TEST.LOG;
SET TIMING ON;
SET AUTOTRACE ON;
ALTER SESSION SET TIMED_STATISTICS = TRUE;

--DROP/CREATE A SAMPLE TRANSACTION TABLE AND ADD APPLICABLE LOCAL INDEXES

DROP SEQUENCE TRANS_SAMPLE_SEQ;
DROP TABLE TRANS_SAMPLE;
CREATE TABLE TRANS_SAMPLE (TXN_ID NUMBER(10) NOT NULL, CREATE_DATE DATE NOT NULL) TABLESPACE TEST;
ALTER TABLE TRANS_SAMPLE ADD CONSTRAINT TRANS_SAMPLE_PK PRIMARY KEY (TXN_ID) DEFERRABLE  USING INDEX TABLESPACE TEST;
CREATE INDEX TRANS_SAMPLE_DATE_IDX ON TRANS_SAMPLE (CREATE_DATE) TABLESPACE TEST;
CREATE SEQUENCE TRANS_SAMPLE_SEQ;

--INSERT DATA AND THEN ANALYZE INTO THE NON-PARTITIONED TABLE
DECLARE
BEGIN
   FOR J IN REVERSE 0 .. 35
   LOOP
      FOR I IN 1 .. 50000
      LOOP
         INSERT INTO TRANS_SAMPLE
            SELECT TRANS_SAMPLE_SEQ.NEXTVAL, TRUNC (SYSDATE) - J
              FROM DUAL;
      END LOOP;
      COMMIT;
   END LOOP;
END;
/

ANALYZE TABLE TRANS_SAMPLE COMPUTE STATISTICS;
ANALYZE INDEX TRANS_SAMPLE_DATE_IDX COMPUTE STATISTICS;
ANALYZE INDEX TRANS_SAMPLE_PK COMPUTE STATISTICS;

SELECT COUNT (*)
  FROM TRANS_SAMPLE;

SELECT   CREATE_DATE, COUNT (TXN_ID)
    FROM TRANS_SAMPLE
GROUP BY CREATE_DATE;

-- PERFORM THE PURGE AND SEE HOW LONG IT TAKES
DELETE      TRANS_SAMPLE
      WHERE CREATE_DATE < TRUNC(SYSDATE) - 30;

SELECT COUNT (*)
  FROM TRANS_SAMPLE;

SELECT   CREATE_DATE, COUNT (TXN_ID)
    FROM TRANS_SAMPLE
GROUP BY CREATE_DATE;

SPOOL OFF;