The select t.u nid, t.p roperty, t.v alue the from (
Select rownum as unid, SUM_MONTH, 'GRID_ID as property, GRID_ID as value from residents_portrait_character union all
Select rownum as unid, SUM_MONTH, 'STAY_TYPE as property, STAY_TYPE as value from residents_portrait_character union all
Select rownum as unid, SUM_MONTH, 'USER_CNT as property, USER_CNT as value from residents_portrait_character union all
Select rownum as unid, SUM_MONTH, 'AGE_LEVEL as property, AGE_LEVEL as value from residents_portrait_character union all
Select rownum as unid, SUM_MONTH, 'GENDER' as property, GENDER as the value from residents_portrait_character union all
Select rownum as unid, SUM_MONTH, 'FEE_LEVEL as property, FEE_LEVEL as value from residents_portrait_character union all
Select rownum as unid, SUM_MONTH, 'COMMUNITY_ID as property, COMMUNITY_ID as value from residents_portrait_character union all
Select rownum as unid, SUM_MONTH, 'STREET_ID as property, STREET_ID as value from residents_portrait_character union all
Select rownum as unid, SUM_MONTH, 'COUNTY_ID as property, COUNTY_ID as value from residents_portrait_character
) t where t.S UM_MONTH=201812
The order by UNID, case property
When 'GRID_ID then 1
When 'STAY_TYPE then 2
When 'USER_CNT then 3
When 'AGE_LEVEL then 4
When 'GENDER' then 5
When 'FEE_LEVEL then 6
When 'COMMUNITY_ID then 7
When 'STREET_ID then 8
When 'COUNTY_ID then 9
END;
For ordinary SUM_MONTH built index is the slow, 5.4 million data, check for half an hour

CodePudding user response:

SUM_MONTH data 5.4 million=201812?

CodePudding user response:

Estimation is union all data before 201812, all this should filter 201812 to merge data first, can see the execution plan predicate 201812 if there is a push inside collect view

CodePudding user response:

2018 - December, horizontal table 600000 rows, into a longitudinal table 540 rows