Search code examples
sqloracle-databasequery-performancesql-optimization

oracle sql optimization

I have this query:

SELECT sd.sdt_service_type,
       sd.sdt_status,
       count(*) col_count
FROM mci_service_data sd
WHERE
     sd.sdt_version = 1
     AND sd.sdt_type = 'MMSP'
     AND sd.sdt_status in (?)
     AND(sd.STD_OPERATION_FLAG is null OR sd.STD_OPERATION_FLAG not like 'mark%')
     AND sd.sdt_office_id in
     (SELECT op.fld_ofs_id
      FROM mci_ofs_per op
      WHERE op.fld_per_id = ?)
group by sd.sdt_service_type,sd.sdt_status

in mci_service_data table there are indexes on mci_service_data(sdt_type, sdt_version, sdt_status, sdt_office_id) and mci_ofs_per(fld_per_id, fld_ofs_id).but this query takes time more than 10 seconds!

So,how this query will be optimize and faster?

Solution

  • For this query, I would recommend the following indexes:

    • mci_service_data(sdt_type, sdt_version, sdt_status, sdt_office_id)
    • mci_ofs_per(fld_per_id, fld_ofs_id)