SELECT d.homeaddress,
d.householdnum,
d.ownername,
d.homemanber,
d.statepoverty,
d.tuopinshuxing,
d.familytype,
d.holdattribute,
d.maincauses,
d.othercauses,
d.updatetime,
d.isreturnpoverty,
d.flog,
d.rn
FROM (SELECT t.homeaddress,
t.householdnum,
t.ownername,
t.homemanber,
t.statepoverty,
t.tuopinshuxing,
t.familytype,
t.holdattribute,
t.maincauses,
t.othercauses,
t.updatetime,
t.isreturnpoverty,
t.flog,
ROWNUM rn
FROM (SELECT distinct h.householdnum,
h.homeaddress,
h.ownername,
(SELECT "COUNT"("ID")
from POORPEOPLE
where HOUSEHOLDS = h.householdnum) homemanber,
h.statepoverty,
h.tuopinshuxing,
h.familytype,
h.holdattribute,
h.maincauses,
h.othercauses,
h.updatetime,
h.isreturnpoverty,
h.flog
FROM HOUSEHOLDS h, poorpeople p
WHERE h.HOUSEHOLDNUM = p.HOUSEHOLDS
and instr(h.address, '61') = 1
ORDER BY h.updatetime desc nulls last) T
where rownum <= 1 * 5) d
WHERE d.rn >= 1
uj5u.com熱心網友回復：
這SQL能不能優化要看造化，主要是 instr(h.address, '61') = 1這個條件，如果滿足條件的資料量很小，建個函式索引可以解決，我想應該不是這種情況。滿足條件的資料量很大，極端點，比如幾乎全表資料都滿足條件，那么問題就在order by上了，加個(updatetime desc,0)這樣的索引試試看，還不行的話上執行計劃。uj5u.com熱心網友回復：
試試這個：
select t.homeaddress,
t.householdnum,
t.ownername,
t.homemanber,
t.statepoverty,
t.tuopinshuxing,
t.familytype,
t.holdattribute,
t.maincauses,
t.othercauses,
t.updatetime,
t.isreturnpoverty,
t.flog,
t.rn
from (select distinct h.householdnum,
h.homeaddress,
h.ownername,
p.cnt,
h.statepoverty,
h.tuopinshuxing,
h.familytype,
h.holdattribute,
h.maincauses,
h.othercauses,
h.updatetime,
h.isreturnpoverty,
h.flog,
row_number()over(order by h.updatetime desc nulls last) rn
from households h
inner join (select t.households,count(id) as cnt from poorpeople group by t.households) p
on p.households = h.householdnum
and instr(h.address, '61') = 1
) t
where t.rn <= 5
uj5u.com熱心網友回復：
執行計劃貼一下，這樣才能確定問題，不然都是可能有問題uj5u.com熱心網友回復：
我覺得你把列里面的rownum去掉，這個是計數器，你只需要回傳五條記錄而已，在列里面不要加rownum轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/81526.html
標籤：開發