SQL optimization-CodePudding

Select count (*)
The FROM flight_info f
INNER JOIN spa_plan p
ON f.d est_code=conviction yourself epAirportCode
WHERE f.a rea_type='D'
And (to_date (p.t imeseries | | "| | conviction yourself eptime, 'YYYY/MM/DD HH24mi') - (case when f.a rrival_time & lt; F.t akeoff_time then to_date (f.f light_date | | "| | substr (f.a rrival_time, 1, 2) | | ':' | | substr (f.a rrival_time, 3, 2) | | '00', '- dd yyyy - MM HH24: mi: ss') + 1 else to_date (f.f light_date | |" | | substr (f.a rrival_time, 1, 2) | | ':' | | substr (f.a rrival_time, 3, 2) | | '00', '- dd yyyy - MM HH24: mi: ss') end)) * 24 & gt;=3;

Combined with the last time is more than 3 days don't go index both table go a full table scan query 18 seconds

CodePudding user response:

Filter expressions of so complicated, how can you go to the general index, or modify the conditions, or to create complex index of function, the premise is need to comply with the requirements for the less amount of data,

CodePudding user response:

reference 1st floor minsic78 response:

filter expressions of so complicated, how can you go to the general index, or modify the conditions, or to create complex index of function, the amount of data that need to fit the premise condition is less,

Good conditions how to modify the point

CodePudding user response:

See the SQL first think of is the If you dont leave me. I will by your side until the life end is translated into "you don't leave me, I will perish together with you", check a 3 days data, conditions that complicated?
Select count (*)
The FROM flight_info f
INNER JOIN spa_plan p
ON f.d est_code=conviction yourself epAirportCode
WHERE f.a rea_type='D'
And p.t imeseries & gt;=to_char (sysdate - 3, 'yyyy/mm/dd)
And p.t imeseries & lt; To_char (sysdate + 1, 'yyyy/mm/dd)
May not meet your requirements, but you lean toward the idea to SQL,

CodePudding user response:

Without changing the existing table results, on the basis of this statement is difficult to optimize, how can have such a burning brain design, at the very least, take minutes when (date) (month) (year) and put into a field,

CodePudding user response:

reference 4 floor AHUA1001 response:

without changing the existing table results, on the basis of this statement is difficult to optimize, how can have such a burning brain design, at the very least, take minutes when (date) (month) (year) and put into a field,

I was too difficult

CodePudding user response:

Stick the execution plan, query condition is to use less than index,

CodePudding user response:

refer to 6th floor Rotel - zhi-dong liu reply:

paste the execution plan, query condition is to use less than index,

A query plan is a full table scan

CodePudding user response:

The CREATE INDEX idx_n1 ON spa_plan (to_date (p.t imeseries | | "| | conviction yourself eptime, 'YYYY/MM/DD HH24mi'));
The SELECT/* + use_concat */COUNT (*)
The FROM flight_info f
INNER JOIN spa_plan p
ON f.d est_code=conviction yourself epAirportCode
WHERE f.a rea_type='D'
AND ((f.a rrival_time & lt; F.t akeoff_time AND
To_date (p.t imeseries | | "| | conviction yourself eptime, 'YYYY/MM/DD HH24mi')=
To_date (f.f light_date | | "| | substr (f.a rrival_time, 1, 2) | | ':' | |
The substr (f.a rrival_time, 3, 2) | | ': 00,
'- dd yyyy - MM HH24: mi: ss') + 1 + 3/24) OR
(f.a rrival_time & gt;=f.t akeoff_time AND
To_date (p.t imeseries | | "| | conviction yourself eptime, 'YYYY/MM/DD HH24mi')=
To_date (f.f light_date | | "| | substr (f.a rrival_time, 1, 2) | | ':' | |
The substr (f.a rrival_time, 3, 2) | | ': 00,
'- dd yyyy - MM HH24: mi: ss') + 3/24));
No specific amount of data, if you want to go the index guess F table is very small, go full table, drive P table again, which in turn can, give it a try

CodePudding user response:

If it is winForm can use SQL to catch first 3 days data, details with front desk filter to filter,
Speed will be faster

CodePudding user response:

The

reference wind sharp Vivian reply: the eighth floor shine

CREATE INDEX idx_n1 ON spa_plan (to_date (p.t imeseries | | "| | conviction yourself eptime, 'YYYY/MM/DD HH24mi'));
The SELECT/* + use_concat */COUNT (*)
The FROM flight_info f
INNER JOIN spa_plan p
ON f.d est_code=conviction yourself epAirportCode
WHERE f.a rea_type='D'
AND ((f.a rrival_time & lt; F.t akeoff_time AND
To_date (p.t imeseries | | "| | conviction yourself eptime, 'YYYY/MM/DD HH24mi')=
To_date (f.f light_date | | "| | substr (f.a rrival_time, 1, 2) | | ':' | |
The substr (f.a rrival_time, 3, 2) | | ': 00,
'- dd yyyy - MM HH24: mi: ss') + 1 + 3/24) OR
(f.a rrival_time & gt;=f.t akeoff_time AND
To_date (p.t imeseries | | "| | conviction yourself eptime, 'YYYY/MM/DD HH24mi')=
To_date (f.f light_date | | "| | substr (f.a rrival_time, 1, 2) | | ':' | |
The substr (f.a rrival_time, 3, 2) | | ': 00,
'- dd yyyy - MM HH24: mi: ss') + 3/24));
No specific amount of data, if you want to go the index guess F table is very small, go full table, drive P table again, which in turn can, try

Not equal to, it is good to should be greater than or equal to, slowly

CodePudding user response:

references to the tenth floor qsc1818518 response:

Quote: refer to the eighth floor sunny wind sharp Vivian reply:

The CREATE INDEX idx_n1 ON spa_plan (to_date (p.t imeseries | | "| | conviction yourself eptime, 'YYYY/MM/DD HH24mi'));
The SELECT/* + use_concat */COUNT (*)
The FROM flight_info f
INNER JOIN spa_plan p
ON f.d est_code=conviction yourself epAirportCode
WHERE f.a rea_type='D'
AND ((f.a rrival_time & lt; F.t akeoff_time AND
To_date (p.t imeseries | | "| | conviction yourself eptime, 'YYYY/MM/DD HH24mi')=
To_date (f.f light_date | | "| | substr (f.a rrival_time, 1, 2) | | ':' | |
The substr (f.a rrival_time, 3, 2) | | ': 00,
'- dd yyyy - MM HH24: mi: ss') + 1 + 3/24) OR
(f.a rrival_time & gt;=f.t akeoff_time AND
To_date (p.t imeseries | | "| | conviction yourself eptime, 'YYYY/MM/DD HH24mi')=
nullnullnullnullnullnullnullnullnullnullnullnullnullnull