Performance tuning of Oracle SQL query

Question

Can someone help me out to tune this query? It's taking 1 minute time to return the data in sqldeveloper.

SELECT
  masterid, notification_id, notification_list, typeid,
  subject, created_at, created_by, approver, sequence_no,
  productid, statusid, updated_by, updated_at, product_list,
  notification_status, template, notification_type, classification
FROM 
( 
  SELECT
    masterid, notification_id, notification_list, typeid, subject,
    approver, created_at, created_by, sequence_no, productid,
    statusid, updated_by, updated_at, product_list, notification_status,
    template, notification_type, classification,
    ROW_NUMBER() OVER(ORDER BY masterid DESC)AS r
  FROM 
  (
    SELECT DISTINCT
      a.masterid         AS masterid,
      a.maxid            AS notification_id,
      notification_list,
      typeid,
      noti.subject       AS subject,
      noti.approver      AS approver,
      noti.created_at    AS created_at,
      noti.created_by    AS created_by,
      noti.sequence_no   AS sequence_no,
      a.productid        AS productid,
      a.statusid         AS statusid,
      noti.updated_by    AS updated_by,
      noti.updated_at    AS updated_at,
      (
        SELECT LISTAGG(p.name,',') WITHIN GROUP(ORDER BY p.id) AS list_noti
        FROM product p
        INNER JOIN notification_product np ON np.product_id = p.id
        WHERE  notification_id = a.maxid
      ) AS product_list,
      (
        SELECT description
        FROM notification_status
        WHERE id = a.statusid
      ) AS notification_status,
      (
        SELECT name
        FROM template
        WHERE id = a.templateid
      ) AS template,
      (
        SELECT description
        FROM notification_type
        WHERE id = a.typeid
      ) AS notification_type,
      (
        SELECT tc.description
        FROM template_classification tc
        INNER JOIN notification nt ON tc.id = nt.classification_id
        WHERE  nt.id = a.maxid
      ) AS classification
    FROM
    (
      SELECT
        nm.id                       AS masterid,
        nm.product_id               AS productid,
        nm.notification_status_id   AS statusid,
        nm.template_id              AS templateid,
        nm.notification_type_id     AS typeid,
        (
          SELECT MAX(id)
          FROM notification
          WHERE notification_master_id = nm.id
        ) AS maxid,
        (
          SELECT LISTAGG(n.id,',') WITHIN GROUP(ORDER BY nf.id) AS list_noti
          FROM notification n
          WHERE notification_master_id = nm.id
        ) AS notification_list
      FROM notification_master nm
      INNER JOIN notification nf ON nm.id = nf.notification_master_id
      WHERE nm.disable = 'N'
      ORDER BY nm.id DESC
    ) a
    INNER JOIN notification noti 
      ON a.maxid = noti.id
      AND 
      (
        (
          (
            TO_DATE('01-jan-1970','dd-MM-YYYY') +
            numtodsinterval(created_at / 1000,'SECOND')
           ) < 
           (current_date + INTERVAL '-21' DAY)
        )
        OR (typeid exists(2,4) AND statusid = 4)
      )
  )
)
WHERE r BETWEEN 11 AND 20

Answer 1

DISTINCT is very often an indicator for a badly written query. A normalized database doesn't contain duplicate data, so where do the duplicates suddenly come from that you must remove with DISTINCT? Very often it is your own query producing these. Avoid producing duplicates in the first place, so you don't need DISTINCT later.

In your case you are joining with the table notification in your subquery a, but you are not using its rows in that subquery; you only select from notification_master_id.

After all, you want to get notification masters, get their latest related notification (by getting its ID first and then select the row). You don't need hundreds of subqueries to achieve this.

Some side notes:

• To get the description from template_classification you are joining again with the notification table, which is not necessary.
• ORDER BY in a subquery (ORDER BY nm.id DESC) is superfluous, because subquery results are per standard SQL unsorted. (Oracle violates this standard sometimes in order to apply ROWNUM on the result, but you are not using ROWNUM in your query.)
• It's a pity that you store created_at not as a DATE or TIMESTAMP, but as a number. This forces you to calculate. I don't think this has a great impact on your query, though, because you are using it in an OR condition.
• CURRENT_DATE gets you the client date. This is rarely wanted, as you select data from the database, which should of course not relate to some client's date, but to its own date SYSDATE.

If I am not mistaken, your query can be shortened to:

SELECT
  nm.id                      AS masterid,
  nf.id                      AS notification_id,
  nfagg.notification_list    AS notification_list,
  nm.notification_type_id    AS typeid,
  nf.subject                 AS subject,
  nf.approver                AS approver,
  nf.created_at              AS created_at,
  nf.created_by              AS created_by,
  nf.sequence_no             AS sequence_no,
  nm.product_id              AS productid,
  nm.notification_status_id  AS statusid,
  nf.updated_by              AS updated_by,
  nf.updated_at              AS updated_at,
  (
    SELECT LISTAGG(p.name, ',') WITHIN GROUP (ORDER BY p.id)
    FROM product p
    INNER JOIN notification_product np ON np.product_id = p.id
    WHERE np.notification_id = nf.id
  ) AS product_list,
  (
    SELECT description
    FROM notification_status
    WHERE id = nm.notification_status_id
  ) AS notification_status,
  (
    SELECT name
    FROM template
    WHERE id = nm.template_id
  ) AS template,
  (
    SELECT description
    FROM notification_type
    WHERE id = nm.notification_type_id
  ) AS notification_type,
  (
    SELECT description
    FROM template_classification
    WHERE id = nf.classification_id
  ) AS classification
FROM notification_master nm
INNER JOIN
(
  SELECT
    notification_master_id,
    MAX(id) AS maxid,
    LISTAGG(id,',') WITHIN GROUP (ORDER BY id) AS notification_list
  FROM notification
  GROUP BY notification_master_id
) nfagg ON nfagg.notification_master_id = nm.id
INNER JOIN notification nf
   ON nf.id = nfagg.maxid
  AND
  (
    (
      DATE '1970-01-01' + NUMTODSINTERVAL(nf.created_at / 1000, 'SECOND')
        < CURRENT_DATE + INTERVAL '-21' DAY
    )
    OR (nm.notification_type_id IN (2,4) AND nm.notification_status_id = 4)
  )
WHERE nm.disable = 'N'
ORDER BY nm.id DESC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

As mentioned, you may want to replace CURRENT_DATE with SYSDATE.

I recommend the following indexes for the query:

CREATE INDEX idx1 ON notification_master (disable, id, notification_status_id, notification_type_id);
CREATE INDEX idx2 ON notification (notification_master_id, id, created_at);

A last remark on paging: In order to skip n rows to get the next n, the whole query must get executed for all data and then all result rows be sorted only to pick n of them at last. It is usually better to remember the last fetched ID and then only select rows with a higher ID in the next execution.