CREATE UNIQUE INDEX islaam_vca_uk1 ON islaam_vca(em_code, start_date)
其次,请将日期存储为DATE数据类型,而不是字符串。
测试了一下,可能有情况,当查询将尝试TABLE ACCESS FULL表islaam_vca,所以你可以尝试:
SELECT /*+ALL_ROWS*/ distinct em_code --sorry about my interpretation of secret your hint , start_date , NVL(end_date, SYSDATE) end_date --don't know if end date is nullable or now , TRUNC(start_date) + level - 1 specific_date FROM (SELECT em_code , start_date , end_date FROM islaam_vca WHERE em_code = '2222') CONNECT BY level <= TRUNC(NVL(end_date, SYSDATE)) - TRUNC(start_date) + 1 order by specific_date;