Recently to do a statistical analysis of related functions, demand is to analyze the statistics a certain period of time (the time period, such as the 2017-03-20 08:00 ~ 2017-03-20 08:00) the starting point of the top ten residential area to the total number of departure of the taxi, and the statistics were sent to the ten village in the first nine in every region of the grid number,

problem: now the query statistics data of a week (about 55 w) time at about 18 s, response time is too long,
Now trying to reason is many times larger amount of data packet sorting result in query too slow, personal rookie is a beginner, feel the SQL statements to optimize,

So I want to ask you is there a better solution, control response within 5 s, SQL optimization or other implementation scheme,


USES the oracle database, the main table TSRT_TAXI_hourdemand table structure is as follows, the data quantity is about 60 million, the daily growth of about 8 w, TIME there is an index


From the table TSRT_TAXI_TRAFFICZONE, the structure is as follows, the data of more than 300


By grouping query, SQL as follows,
As

 with t(select * 
The from (select a. *, b.A REA_ID SGRID_ZONE, c.a. REA_ID EGRID_ZONE 
The from (select a. *, 
CASE 
The WHEN TO_CHAR (a.T IME, 'HH24: MI) BETWEEN' 08:00 and 
'at 10:00 THEN 
1 
The WHEN TO_CHAR (a.T IME, 'HH24: MI) BETWEEN' 17:00 and 
THEN '8' 
2 
The ELSE 
0 
END as unit 
The from TSRT_TAXI_hourdemand a 
Where a.T IME & gt;=
00 to_date (' 2017-03-26 ', '- dd yyyy - mm HH24: mi') 
And a.T IME & lt;=
23 to_date (' 2017-03-27 ', '- dd yyyy - mm HH24: mi')) a 
Left the join TSRT_TAXI_TRAFFICZONE b 
On b.G RID_ID=a.S GRID_ID 
Left the join TSRT_TAXI_TRAFFICZONE c 
Be sad RID_ID chtistina georgina rossetti.british poetess on=a.E GRID_ID) 
WHERE SGRID_ZONE IS NOT NULL 
AND EGRID_ZONE IS NOT NULL 
AND SGRID_ZONE!=EGRID_ZONE 
) 
The select b.A REA_NAME SGRID_NAME, 
B.L ONGITUDE SGRID_LON, 
B.L ATITUDE SGRID_LAT, 
C.a. REA_NAME EGRID_NAME, 
C.L ONGITUDE EGRID_LON, 
C.L ATITUDE EGRID_LAT, 
FLOW_NO, 
Rn 
The from (select b.S GRID_ZONE, b.E GRID_ZONE, b. LOW_NO, b.r n as an rn 
The from (select a. *, 
Row_number () over (partition by a.S GRID_ZONE order by a.F LOW_NO DESC) rn 
The from (select t.S GRID_ZONE, 
T.E GRID_ZONE, 
The sum (t.F LOW_NO) as FLOW_NO 
The from t 
Where t.S GRID_ZONE in 
(select b.S GRID_ZONE 
The from (SELECT t.S GRID_ZONE, SUM (T.F LOW_NO) 
The FROM t 
Group by SGRID_ZONE 
The ORDER BY the SUM (T.F LOW_NO) DESC) b 
Where ROWNUM & lt;=10) 
Group by t.S GRID_ZONE, t.E GRID_ZONE) 
 a) bWhere b.r n & lt; A 10) 
Left the join ttcb_trans_area b 
On b.I D=a.S GRID_ZONE 
Left the join ttcb_trans_area c 
On c.I D=a.E GRID_ZONE; 

CodePudding user response:

Data volume of about 60 million, the daily growth of about 8 w, TIME there is an index

Data volume is not small, it is recommended that do TIME partition in TIME, every day a partition, and establish corresponding partitioning index;

CodePudding user response:

There try to build a materialized views in the grouping sorting

CodePudding user response:

reference 1st floor wmxcn2000 response:

data volume of about 60 million, the daily growth of about 8 w, TIME there is an index

Data volume is not small, it is recommended that do TIME partition in TIME, every day a partition, and establish corresponding partitioning index;

This scheme, I can't build a partition, partition index, no ordinary index quickly, before

refer to the second floor u012557814 response:

there try to build a materialized views in the grouping sorting

In view of the result set grouping sorting, query result set date, if can't build materialized views

CodePudding user response:

reference hdl1930 reply: 3/f

this scheme, I can't build a partition, partition index, no ordinary index quickly, before the

The where condition, use the partitioning column?

CodePudding user response:

reference 4 floor wmxcn2000 response:

Quote: reference hdl1930 reply: 3/f

This scheme, I can't build a partition, partition index, no ordinary index quickly, before the

The where condition, use the partitioning column?

Refers to the the where condition?
Where t.S GRID_ZONE in...