CREATE TABLE ER
(
ROLL_ENT VARCHAR2(4 BYTE) NOT NULL,
ROLL_SUB_ENT VARCHAR2(3 BYTE) NOT NULL,
ROLL_ENT_DESCR VARCHAR2(50 BYTE),
ENT VARCHAR2(4 BYTE) NOT NULL,
SUB_ENT VARCHAR2(3 BYTE) NOT NULL,
ENT_DESCR VARCHAR2(50 BYTE)
);


CREATE UNIQUE INDEX REL_ER_IDX_PK ON ER
(ROLL_ENT, ROLL_SUB_ENT, ENT, SUB_ENT);


ALTER TABLE ER ADD (
CONSTRAINT REL_ER_IDX_PK
PRIMARY KEY
(ROLL_ENT, ROLL_SUB_ENT, ENT, SUB_ENT);


TOTAL NUMBER OF RECORDS FOR TABLE ER : 123542


CREATE TABLE CR
(
ACCT VARCHAR2(9 BYTE) NOT NULL,
ACCT_LVL VARCHAR2(2 BYTE) NOT NULL,
ACCT_ID VARCHAR2(9 BYTE) NOT NULL,
REL_TYPE VARCHAR2(10 BYTE) NOT NULL,
ACCT_TYPE VARCHAR2(2 BYTE) NOT NULL,
ACCT_DESCR VARCHAR2(43 BYTE),
POST_ACCT VARCHAR2(9 BYTE) NOT NULL,
POST_ACCT_TYPE VARCHAR2(2 BYTE),
POST_ACCT_DESCR VARCHAR2(43 BYTE),
SIGN_REVRSL NUMBER
);


CREATE INDEX CR_IDX_01 ON CR
(ACCT_ID, REL_TYPE, POST_ACCT);


CREATE UNIQUE INDEX CR_IDX_PK ON CR
(ACCT_ID, ACCT, REL_TYPE, POST_ACCT);


TOTAL NUMBER OF RECORDS FOR TABLE CR : 4721918


CREATE TABLE CHR
(
ENT VARCHAR2(4 BYTE) NOT NULL,
SUB_ENT VARCHAR2(3 BYTE) NOT NULL,
HIER_TBL_NUM VARCHAR2(3 BYTE) NOT NULL,
HIER_ROLL VARCHAR2(14 BYTE) NOT NULL,
HIER_CODE VARCHAR2(14 BYTE) NOT NULL,
SUM_FLAG VARCHAR2(14 BYTE) NOT NULL,
CTR_OR_HIER VARCHAR2(14 BYTE) NOT NULL,
CTR_DETAIL VARCHAR2(14 BYTE) NOT NULL,
CTR_DESCR VARCHAR2(50 BYTE)
);


CREATE INDEX CHR_IDX_01 ON CHR
(HIER_TBL_NUM, HIER_ROLL, SUM_FLAG);


CREATE UNIQUE INDEX CHR_IDX_PK ON CHR
(ENT, SUB_ENT, HIER_TBL_NUM, HIER_ROLL, SUM_FLAG, 
CTR_DETAIL, CTR_OR_HIER, HIER_CODE);


CREATE INDEX CHR_IDX_02 ON CHR
(ENT, SUB_ENT, HIER_TBL_NUM, CTR_OR_HIER, SUM_FLAG, CTR_DETAIL);


TOTAL NUMBER OF RECORDS FOR TABLE CHR : 24151811


CREATE TABLE TAC
(
ENT VARCHAR2(4 BYTE),
SUB_ENT VARCHAR2(3 BYTE),
POST_ACCT VARCHAR2(9 BYTE),
CTR VARCHAR2(7 BYTE),
POST_DATE DATE,
EFF_DATE DATE,
TXN_CODE VARCHAR2(2 BYTE),
TXN_TYPE VARCHAR2(1 BYTE),
TXN_AMOUNT NUMBER(17,2),
TXN_DESCR VARCHAR2(46 BYTE),
TXN_SOURCE VARCHAR2(1 BYTE)
);


CREATE INDEX TAC_IDX_01 ON TAC
(ENT, SUB_ENT, POST_ACCT, POST_DATE, EFF_DATE, TXN_AMOUNT);


CREATE INDEX TAC_IDX_PK ON TAC
(ENT, SUB_ENT, CTR, POST_ACCT, POST_DATE, EFF_DATE, TXN_AMOUNT);


TOTAL NUMBER OF RECORDS FOR TABLE TAC : 111042301

===================================================================
I have the following query to be tuned..

SELECT SUBSTR(chr.hier_code, 1, 5),
      tmp_txm.post_acct,
      SUBSTR(tmp_txm.txn_descr, 1, 5),
      SUM
      (
        CASE
          WHEN tmp_txm.txn_code IN ('01', '10') THEN 1
                WHEN tmp_txm.txn_code IN ('02', '20') THEN -1
                ELSE 0
        END
        *
        CASE
          WHEN cr.acct_type IN ('01', '02', '03', '04', '05', '06') THEN 1
                WHEN cr.acct_type IN ('11', '12', '13', '14') THEN -1
                ELSE 0
        END
        *
        cr.signal_revert
        *
        tmp_txm.txn_amount
      )
    FROM
      tac. txn
        INNER JOIN
      chr
        ON
          tmp_txm.ent = chr.ent AND
          tmp_txm.sub_ent = chr.sub_ent AND
          tmp_txm.ctr = chr.ctr_detail
        INNER JOIN
      cr
        ON tmp_txm.post_acct = cr.post_acct
        INNER JOIN
      er
        ON
          chr.ent = er.ent AND
          chr.sub_ent = er.sub_ent
    WHERE
      tmp_txm.eff_date BETWEEN TO_DATE('2005'||'01', 'YYYYMM') AND LAST_DAY(TO_DATE('2005'||'01', 'YYYYMM')) AND
      chr.hier_tbl_num = '11111' AND
      chr.sum_flag = 'DEL AND
      chr.ctr_or_hier = chr.ctr_detail AND
      chr.hier_code BETWEEN 'A' AND 'Z' AND
      cr.rel_type = ' ' AND
      cr.acct_id = 'MGT' AND
      cr.acct_lvl = '9' AND
      er.roll_ent = '123' AND
      er.roll_sub_ent = '111'
    GROUP BY
      SUBSTR(chr.hier_code, 1, 5),
      tmp_txm.post_acct,
      SUBSTR(tmp_txm.txn_descr, 1, 5)
    HAVING
      SUM
      (
        CASE
          WHEN tmp_txm.txn_code IN ('01', '10') THEN 1
                WHEN tmp_txm.txn_code IN ('02', '20') THEN -1
                ELSE 0
        END
        *
        CASE
          WHEN cr.acct_type IN ('01', '02', '03', '04', '05', '06') THEN 1
                WHEN cr.acct_type IN ('11', '12', '13', '14') THEN -1
                ELSE 0
        END
        *
        cr.signal_revert
        *
        tmp_txm.txn_amount
      ) <> 0

chr.sum_flag = 'DEL AND

tac. txn -- tac tmp_txm?

cr.signal_revert  -- cr doesn't have a signal_revert col