Oracle SQL 查询优化：按相似度和评分排序获取数据

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