oracle 11g 数据优化

Question

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

Answer 1

这SQL能不能优化要看造化，主要是 instr(h.address, '61') = 1这个条件，如果满足条件的数据量很小，建个函数索引可以解决，我想应该不是这种情况。满足条件的数据量很大，极端点，比如几乎全表数据都满足条件，那么问题就在order by上了，加个(updatetime  desc,0)这样的索引试试看，还不行的话上执行计划。

Answer 2

试试这个：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
 

Answer 3

我觉得你把列里面的rownum去掉，这个是计数器，你只需要返回五条记录而已，在列里面不要加rownum