Oracle 11 g data optimization-CodePudding

The SELECT d.h. omeaddress,
D.h. ouseholdnum,
D.o wnername,
D.h. omemanber,
D.s tatepoverty,
Which uopinshuxing,
D.f amilytype,
D.h. oldattribute,
D.m aincauses,
D.o thercauses,
D.u pdatetime,
D.i sreturnpoverty,
D.f log,
D.r n
The FROM (SELECT Todd Harper omeaddress,
Todd Harper ouseholdnum,
T.o wnername,
Todd Harper omemanber,
T.s tatepoverty,
T.t uopinshuxing,
T.f amilytype,
Todd Harper oldattribute,
T.m aincauses,
T.o thercauses,
T.u pdatetime,
T.i sreturnpoverty,
T.f log,
ROWNUM rn
The FROM (SELECT distinct h.h ouseholdnum,
H.h omeaddress,
H.o wnername,
(SELECT "COUNT" (" ID ")
The from POORPEOPLE
Where HOUSEHOLDS=h.h homemanber ouseholdnum),
H.s tatepoverty,
H.t uopinshuxing,
H.f amilytype,
H.h oldattribute,
H.m aincauses,
H.o thercauses,
H.u pdatetime,
H.i sreturnpoverty,
H.f log
The FROM HOUSEHOLDS h, poorpeople p
WHERE h.H OUSEHOLDNUM=p.H OUSEHOLDS
And instr (h.a ddress, '61')=1
The ORDER BY h.u pdatetime desc crosstab last) T
Where rownum & lt;
=1 * 5) dWHERE d.r n & gt;=1

CodePudding user response:

It can SQL optimization depends on nature, mainly instr (h.a ddress, '61')=1 this condition, if meet the conditions for the amount of data is small, build a functional index can be solved, I think I should not this kind of situation, meet the conditions for the amount of data is very big, the extreme points, such as almost all the table data meet the conditions, so the question is over my order by adding a (updatetime desc, 0) this index try, still no word on the execution plan,

CodePudding user response:

Try this:

 
The select Todd Harper omeaddress, 
Todd Harper ouseholdnum, 
T.o wnername, 
Todd Harper omemanber, 
T.s tatepoverty, 
T.t uopinshuxing, 
T.f amilytype, 
Todd Harper oldattribute, 
T.m aincauses, 
T.o thercauses, 
T.u pdatetime, 
T.i sreturnpoverty, 
T.f log, 
T.r n 
The from (select distinct h.h ouseholdnum, 
H.h omeaddress, 
H.o wnername, 
P.c nt, 
H.s tatepoverty, 
H.t uopinshuxing, 
H.f amilytype, 
H.h oldattribute, 
H.m aincauses, 
H.o thercauses, 
H.u pdatetime, 
H.i sreturnpoverty, 
H.f log, 
Row_number () over (order by h.u pdatetime desc crosstab last) rn 
The from households h 
Inner join (select Todd Harper ouseholds, count (id) as the from CNT poorpeople group by Todd Harper ouseholds) p 
On p.h ouseholds=h.h ouseholdnum 
And instr (h.a ddress, '61')=1 
) t 
Where t.r n & lt;=5

CodePudding user response:

Execution plan posted once, so as to determine problem, or may have a problem

CodePudding user response:

I think you the rownum column there are removed, this is the counter, you only need to return the five records, don't add rownum inside the column