C_plan there are about 400000 records in the following the beginning of this statement is based on the input end query plan departure flights per month (STD)
But don't know why there are three or four minutes long response time couldn't accept beg god to help improve the query speed I don't appreciate also very welcome for other statements

 select to_char (TRUNC (add_months (to_date (' 2015-08-01 00:00:00 ', '- DD YYYY - MM HH24: MI: SS'), (1) b.n o), 'MM'), 'YYYY - MM) name, 
(select count (distinct planid) from c_plan a where clause (adep='ZBAA') and 
STD BETWEEN TRUNC (add_months (to_date (' 2015-08-01 00:00:00 ', '- DD YYYY - MM HH24: MI: SS'), (1) b.n o), 'MM) 
And TRUNC (add_months (to_date (' 2015-08-01 00:00:00 ', '- DD YYYY - MM HH24: MI: SS'), b.n o), 'MM) - 1/86400) as the value 
The from (select level no from dual connect by level & lt;=
(select ceil (MONTHS_BETWEEN (to_date (' 23:59:59 2016-10-01 ', '- DD YYYY - MM HH24: MI: SS'), to_date (' 2015-08-01 00:00:00 ', '- DD YYYY - MM HH24: MI: SS'))) from dual)) 
 b

CodePudding user response:

What kind of table structure, the building also said;

CodePudding user response:

reference 1st floor wmxcn2000 response:

what kind of table structure, the building Lord also said;

In fact has pretty long table:
No. - plan only
Planid varchar2 (64),
- added time
T_add date default sysdate,
- the last modified time
T_mod date default sysdate,
- plans to terminate/remove time
T_del date,
- plan,
Planno number (6),
- the flight no.
The acid varchar2 (8),
- take off airport
Adep varchar2 (4),
- landing airport
Ades varchar2 (4),
Scheduled departure time
STD date,

- is expected to take off timeEtd date,
- the actual departure time
Atd date,
- plan landing time
The sta date,

- is expected to landEta date,
- the actual landing time
Ata date,
- the actual launch time
Atp date,
- COBT
CTP date,
- CTOT
CTD date,
- TOBT
Tobt date,
- TSAT
Tsat date,
- airlines input ready time
Ardt date,
- start sliding time
Tax_time date,
- tower are ready time
Rdy_time date,
- plan state
The status varchar2 (4),
- take off the runway
Rwy_dep varchar2 (3),
- landing runway
Rwy_arr varchar2 (3),
Takeoff and landing mark
-Flytype number (1),
Model -
Planetype varchar2 (4),
- drafting
Turb varchar2 (1),

- alternate airportAltn varchar2 (4),
- registration number
Plane_ident varchar2 (10),
VIP sign
-Vvips varchar2 (1),
Task -
Task varchar (4),
- at home and abroad attribute
Company_country number (1),
- slots
Gate varchar2 (5),
- wingspan
Wing varchar (1),
Variable sliding time
-VTT number (2),
- big
Direction number (1),

-- big small directionDir_detail number (1),
- departure point
Corridor_dep varchar (10),
- port
Corridor_arr varchar (10),
- point out
Point_outer varchar (10),
- the point
Point_inner varchar (10),
The flow control of corresponding
Res_reason number (2),
- lock CTOT
Ctd_lock date,
- the first CTOT
CtdFPL0 date,
- the first COBT
CtpFPL0 date,
- 1 hour before EOBT CTOT
CtdFPL6 date,
- 1 hour before EOBT COBT
CtpFPL6 date,
- 30 minutes before the EOBT CTOT
CtdFPL3 date,
- 30 minutes before the EOBT COBT
CtpFPL3 date,
- when the RDY CTOT
CtdRDY date,
- when the RDY COBT
CtpRDY date,
- launched CTOT
CtdPUSH date,
- launched COBT
CtpPUSH date,
- when LIN CTOT
CtdLIN date,
- start de-icing time
Deice_beg date,
- the end of the deicing time
Deice_end date,
- is expected to de-icing length
Deice_pre number (2),
- in addition to the ice a
Deice_pos varchar (10),
- in addition to the ice
Deice_area varchar (10),
- in addition to the ice logo
Deice_flag number (1),
- the first TSAT
TSAT_first date,

- TOBT input personnelTOBT_name varchar2 (10),
- waiting for sign
Wait_flag number (1),
- the way into the waiting pool
Wait_reason number (1),
-
the waiting pool of timeWait_outTime date,
- the waiting pool way
Wait_outType number (1),
- minimum TOBT
Tobt_min date,
- the largest TOBT
Tobt_max date,
- airlines
Company varchar (5),
- analysis of the reason
Delay_10 varchar (4),
Determine the cause of the out - traffic system
Delay_11 varchar (4),
- the cause of the tower are
Delay_12 varchar (4),
The reason - the company give
Delay_13 varchar (4),
- flights before
Prev_planid varchar (64),
- the original route
The route varchar2 (300),
- analytical route
Route_ana varchar2 (1024),
- the main route
Route_main varchar (10),
- the source of the input TOBT
Tobt_type number (1),
Limited - mark
Limit_flag number (1),
- note
Add_msg varchar2 (200),
- the final delay reason
Delay_reason varchar2 (4),
- actual start boarding time
ASBT date,
- shut doors time
GCTM date,
- in addition to the ice start time
ACZT date,
- in addition to the end of the ice time
AEZT date,
- to de-icing wait for a time
DIHD date,
- enter the deicing a time
DIIN date,
Slip out of deicing - a time
DITO date,
- tower original delay reason
Delay_twr_org varchar2 (100),
- into a time
T_ove date

CodePudding user response:

Change the scalar subquery to join query

CodePudding user response:

nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull