CREATE TABLE TRANSACTION_LOG (
TRANS_ID VARCHAR(32) NOT NULL,
ACCOUNT_ID VARCHAR(32) NOT NULL,
TRANS_TYPE VARCHAR(20) NOT NULL,
TRANS_AMOUNT DECIMAL(18, 2) NOT NULL,
BALANCE_BEFORE DECIMAL(18, 2) NOT NULL,
BALANCE_AFTER DECIMAL(18, 2) NOT NULL,
TRANS_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
TRANS_STATUS VARCHAR(20) NOT NULL,
CONSTRAINT PK_TRANSACTION_LOG PRIMARY KEY (TRANS_ID, TRANS_TIME)
) PARTITION BY RANGE (TRANS_TIME)
(
PARTITION P202601 STARTING ‘2026-01-01’ ENDING ‘2026-01-31’,
PARTITION P202602 STARTING ‘2026-02-01’ ENDING ‘2026-02-28’,
PARTITION P202603 STARTING ‘2026-03-01’ ENDING ‘2026-03-31’,
PARTITION P202604 STARTING ‘2026-04-01’ ENDING ‘2026-04-30’
);

CREATE OR REPLACE PROCEDURE SP_ADD_PARTITION(
IN p_partition_name VARCHAR(20),
IN p_start_date DATE,
IN p_end_date DATE
)
LANGUAGE SQL
BEGIN
DECLARE v_sql VARCHAR(1000);

SET v_sql = ‘ALTER TABLE TRANSACTION_LOG ADD PARTITION ‘ ||
p_partition_name || ‘ STARTING ”’ ||
TO_CHAR(p_start_date, ‘YYYY-MM-DD’) || ”’ ENDING ”’ ||
TO_CHAR(p_end_date, ‘YYYY-MM-DD’) || ””;

EXECUTE IMMEDIATE v_sql;
END;

CREATE UNIQUE INDEX IDX_TRANS_PK ON TRANSACTION_LOG(TRANS_ID, TRANS_TIME);

CREATE INDEX IDX_TRANS_ACCOUNT ON TRANSACTION_LOG(ACCOUNT_ID, TRANS_TIME DESC);
CREATE INDEX IDX_TRANS_TYPE ON TRANSACTION_LOG(TRANS_TYPE, TRANS_TIME DESC);
CREATE INDEX IDX_TRANS_STATUS ON TRANSACTION_LOG(TRANS_STATUS, TRANS_TIME DESC);

SELECT * FROM TRANSACTION_LOG
WHERE TRANS_TIME BETWEEN ‘2026-04-01’ AND ‘2026-04-30’
AND ACCOUNT_ID = ‘A001’;

SELECT
DATE(TRANS_TIME) AS TRANS_DATE,
TRANS_TYPE,
COUNT(*) AS TRANS_COUNT,
SUM(TRANS_AMOUNT) AS TOTAL_AMOUNT
FROM TRANSACTION_LOG
WHERE TRANS_TIME BETWEEN ‘2026-04-01’ AND ‘2026-04-30’
GROUP BY DATE(TRANS_TIME), TRANS_TYPE
ORDER BY TRANS_DATE, TRANS_TYPE;