Any better way of writing the below query

vinutauro

SELECT TRANSACTION_DATE,
ROWS_MET_SLO_PERC,
FLOOR(SCORE) AVERAGE_SCORE,
CASE
WHEN AVERAGE_SCORE = 4 THEN
'A'
WHEN AVERAGE_SCORE = 3 THEN
'B'
WHEN AVERAGE_SCORE = 2 THEN
'C'
WHEN AVERAGE_SCORE = 1 THEN
'D'
ELSE
'F'
END AS GRADE
FROM
(
SELECT TRANSACTION_DATE,
SUM(ROWS_MET_SLO_COUNT) / SUM(TOTAL_ROWS) * 100 ROWS_MET_SLO_PERC
FROM mONTHLY_DATA B
JOIN PRODUCT_DATA A
ON B.id = A.id
WHERE TRANSACTION_DATE IN ( ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -2), ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1))
GROUP BY TRANSACTION_DATE
) SLO_TABLE
JOIN
(
SELECT *
FROM GRADE_TABLE
WHERE ACTION_IND = 1
) G
ON SLO_TABLE.ROWS_MET_SLO_PERC >= G.LOWER_RANGE
AND SLO_TABLE.ROWS_MET_SLO_PERC < G.UPPER_RANGE

SruthiA

could you please create a support case and provide table DDL's, explain plan of the query? is it performing slower?

vinutauro

Below is the DDL. Performance is not slow but just want to know if this query can be rewritten in a simple way without nesting it
Thanks in advance

CREATE TABLE GRADE_TABLE
(
LOWER_RANGE numeric(7,2),
UPPER_RANGE numeric(7,2),
ACTION_IND int,
SCORE int,
UPDT_DT_TM timestamptz NOT NULL DEFAULT statement_timestamp()
)
ORDER BY ACTION_IND,LOWER_RANGE,UPPER_RANGE;

CREATE TABLE MONTHLY_DATA
(
ID integer,
TRANSACTION_DATE date,
TOTAL_ROWS int,
ROWS_MET_SLO_COUNT int,
UPDT_DT_TM timestamptz NOT NULL default STATEMENT_TIMESTAMP() ENCODING COMMONDELTA_COMP
)
ORDER BY TRANSACTION_DATE,ID;

CREATE TABLE PRODUCT_DATA
(
OWNER VARCHAR(100),
ID NUMERIC(10,0),
NAME VARCHAR(255),
UPDT_DT_TM TIMESTAMPTZ NOT NULL DEFAULT STATEMENT_TIMESTAMP() ENCODING COMMONDELTA_COMP
)
ORDER BY ID;

SruthiA

could you please share explain plan and projection design of the tables involved in the query?

koumaran

Maybe try with this. Would be better if I could have the explain plan and projection designs for sure.

WITH SLO_TABLE as (
SELECT TRANSACTION_DATE,
SUM(ROWS_MET_SLO_COUNT) / SUM(TOTAL_ROWS) * 100 ROWS_MET_SLO_PERC
FROM mONTHLY_DATA B
WHERE TRANSACTION_DATE BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -2) AND ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)
AND ID in (select ID from PRODUCT_DATA)
GROUP BY TRANSACTION_DATE
),
RESULT as (SELECT * FROM GRADE_TABLE G join SLO_TABLE ON ACTION_IND = 1 AND SLO_TABLE.ROWS_MET_SLO_PERC >= G.LOWER_RANGE AND SLO_TABLE.ROWS_MET_SLO_PERC < G.UPPER_RANGE)
SELECT TRANSACTION_DATE,
ROWS_MET_SLO_PERC,
FLOOR(SCORE) AVERAGE_SCORE,
DECODE(AVERAGE_SCORE,
4,'A',
3,'B',
2,'C',
1,'D',
'F') GRADE
FROM RESULT;