Dear community,
I have the below table def and query.
The volume is 200 million plus rows.
What index would it suited to speed up the query ?
This table is mixed work load both transactional (insert and update ) and read.
Index idea:
CREATE index tbl_1 on tbl(cnt,r_creation_dt,c_id,a_action,last_modified_by);
CREATE index tbl_2 on tbl(cnt,c_status,r_creation_dt);
CREATE bitmap index tbl_3 on tbl(c_status);
tbl defintion below:
R_ID NOT NULL VARCHAR2(30)
C_ID NUMBER
N_ID VARCHAR2(40)
CREATED_BY NOT NULL VARCHAR2(30)
LAST_MODIFIED_BY VARCHAR2(30)
C_STATUS CHAR(1)
A_ACTION CHAR(1)
R_CREATION_DT TIMESTAMP(6)
CNT NUMBER(38)
SQL:
SELECT
c_id,
a_action,
cnt,
last_modified_by
FROM
tbl
WHERE
c_status IN(
'N',
'F'
)
AND cnt<=5
GROUP BY
cnt,
r_creation_dt,
case_id,
anonymize_action,
last_modified_by
ORDER BY
r_creation_dt
FETCH FIRST 1000 ROWS ONLY;