SQL 查询优化：获取数据字典信息并排序

SELECT TO_CHAR(SERIAL_NUMBER) AS "serial", PARENT_DATAVALUE_ID AS "parentSerial", ID AS "id", DATA_VALUE AS "dataValue", DATA_VALUE_REMARK AS "dataText", SHOW_TEXT AS "showText", TO_CHAR(NVL(SCORE, 0)) AS "score" FROM (SELECT t.SERIAL_NUMBER, t.PARENT_DATAVALUE_ID, t.ID, t.DATA_VALUE, CASE WHEN t.similarity2 = 0 THEN t.DATA_VALUE_REMARK1 WHEN t.SIMILARITY2 >= t.SIMILARITY1 THEN t.DATA_VALUE_REMARK2 ELSE t.DATA_VALUE_REMARK1 END AS DATA_VALUE_REMARK, t.SHOW_TEXT AS SHOW_TEXT, t.SCORE, ROWNUM rn FROM (SELECT b.SERIAL_NUMBER, b.PARENT_DATAVALUE_ID, b.BASICDATAVALUE_ID AS ID, b.DATA_VALUE, b.DATA_VALUE_REMARK AS DATA_VALUE_REMARK1, alias.DATA_VALUE_REMARK AS DATA_VALUE_REMARK2, CASE WHEN a.ELEMENT_PD_ID = '862442E0D40D29C8E0537A5B14ACC19C' OR a.ELEMENT_PD_ID = 'BB2C7320B2CE41418C797387D6EE1B20' THEN (SELECT m1.PERSON_NAME || '(' || m2.DEPT_NAME || ')' || '(' || m1.EMP_NO || ')' FROM SYSMN.EHR_SERVICE_PERSONAL m1, SYSMN.SYS_DEPT_DICT m2 WHERE m1.PERSON_ID = b.BASICDATAVALUE_ID AND m1.DEPT_ID = m2.DEPT_ID AND ROWNUM = 1) ELSE b.SHOW_TEXT END AS SHOW_TEXT, b.SCORE, SYS.UTL_MATCH.edit_distance_similarity(?, b.DATA_VALUE_REMARK) AS SIMILARITY1, NVL2(alias.VALUE_ID, SYS.UTL_MATCH.edit_distance_similarity(?, alias.DATA_VALUE_REMARK), 0) AS SIMILARITY2 FROM SYSMN.SYS_DATA_ELEMENT a JOIN SYSMN.MAV_EMR_CORE_DICT b LEFT JOIN SYSMN.SYS_DICT_ALIAS alias ON b.BASICDATAVALUE_ID = alias.VALUE_ID AND alias.STOP_FLAG = '0' WHERE a.ELEMENT_ID = ? AND a.DEL_FLAG = '0' AND a.ELEMENT_PD_ID = b.BASICDATA_ID AND b.BASICDATAVALUE_ID NOT IN (SELECT b.DEPT_ID FROM SYSMN.SYS_DEPT_DICT b WHERE b.stop_flag = '1' OR b.OUT_INPAT_FLAG = (SELECT var_value FROM sysmn.sys_var WHERE var_code = 'DEPT_INTERNET_FLAG')) AND (b.HOSPITAL_ID = '0' OR b.HOSPITAL_ID = '80D024E29BF40C9DE0537A5B14AC1620') AND INSTR(?, b.BASICDATAVALUE_ID, 1) = 0 AND ((b.BASICDATA_ID, b.BASICDATAVALUE_ID) IN (SELECT f.LEFT_DICT_ID, f.LEFT_VALUE_ID FROM SYSMN.SYS_DICT_FILTER f WHERE (f.RIGHT_DICT_ID, f.RIGHT_VALUE_ID) IN ((SELECT ELEMENT_PD_ID FROM SYSMN.SYS_DATA_ELEMENT WHERE ELEMENT_ID = ?), ?)) OR (b.BASICDATA_ID, b.BASICDATAVALUE_ID) IN (SELECT f.RIGHT_DICT_ID, f.RIGHT_VALUE_ID FROM SYSMN.SYS_DICT_FILTER f WHERE (f.LEFT_DICT_ID, f.LEFT_VALUE_ID) IN ((SELECT ELEMENT_PD_ID FROM SYSMN.SYS_DATA_ELEMENT WHERE ELEMENT_ID = ?), ?))) ORDER BY (SELECT WEIGHT FROM IDR.ORDERS_FILE_INDEX_DATA_STAT WHERE DEPT_ID = ? AND ELEMENT_ID = ? AND DATA_CODE = b.BASICDATAVALUE_ID) DESC NULLS LAST, GREATEST(SIMILARITY1, SIMILARITY2) DESC, b.SCORE ASC, b.SERIAL_NUMBER, b.PY_CODE) t WHERE ROWNUM <= ? * ?) WHERE rn > (? - 1) * ?