/*
Phase 7 - Doctor Performance recommended checks/indexes
Review with DBA and execution plans before running in production.
The dashboard reads from KPI_DOCTOR_PERF_DETAIL_JOB by YEARID, EMP_ID, SPECIAL_SPEC_ID.
*/

-- Check existing indexes first:
SELECT index_name, column_name, column_position
FROM all_ind_columns
WHERE table_name = 'KPI_DOCTOR_PERF_DETAIL_JOB'
ORDER BY index_name, column_position;

-- Optional candidate index if missing and the table is large:
-- CREATE INDEX IDX_KPI_DOCTOR_YEAR_EMP ON KPI_DOCTOR_PERF_DETAIL_JOB (YEARID, EMP_ID);

-- Optional candidate index for specialty ranking:
-- CREATE INDEX IDX_KPI_DOCTOR_YEAR_SPEC ON KPI_DOCTOR_PERF_DETAIL_JOB (YEARID, SPECIAL_SPEC_ID);

-- Gather stats after any structural change:
-- BEGIN
--   DBMS_STATS.GATHER_TABLE_STATS(ownname => 'WATANY', tabname => 'KPI_DOCTOR_PERF_DETAIL_JOB', cascade => TRUE);
-- END;
-- /
