Oracle database is far larger amount of will appear after a few minutes before the data
Should be how to optimize the bosses

Computation is mainly big article in there will be thousands of even tens of thousands of data and processing and add more than one tomorrow I clap SQL implore you to help take a look at this problem

CodePudding user response:

, analyzing the specific problems, you should follow about provide SQL table illustration to help you

CodePudding user response:

Yeah yeah I clap SQL tomorrow

CodePudding user response:

This is the SQL is a little long I send everybody to help you trouble

CodePudding user response:

The select b.b ranchname branchName, mount ranchname supebranchName, a. ranchnum branchNum, m. eviceSum, o.r unningDays1 * m. eviceSum, o.r unningDays1 runningDays, decode (o.r unningDays1 * m. eviceSum, 0, 0, round ((NVL (h.t ransferNum, 0) + NVL (i.O PENCARDNUM, 0) + NVL (d.P ASSWORDNUM, 0) + NVL (e.P ERSONINFONUM, 0) + NVL (f.P RINTNUM, 0) + NVL (g.S IGNNUM, 0) + NVL (John casteen ARDRELOSSNUM, 0) + NVL (k.I NTELLDEPOSITNUM, 0) + NVL (l.F INANCNUM, 0) + NVL (n.L IVEPAYMENTNUM, 0))/(o.r unningDays1 * m. eviceSum), 1)) perDayDevNum, Margaret spellings UMDAYS,

NVL (h.t ransferNum, 0) + NVL (i.O PENCARDNUM, 0) + NVL (d.P ASSWORDNUM, 0) + NVL (e.P ERSONINFONUM, 0) + NVL (f.P RINTNUM, 0) + NVL (g.S IGNNUM, 0) + NVL (John casteen ARDRELOSSNUM, 0) + NVL (k.I NTELLDEPOSITNUM, 0) + NVL (l.F INANCNUM, 0) + NVL (n.L IVEPAYMENTNUM, 0) countNum,


Decode (to_char (NVL (h.t ransferMoney, 0) + NVL (k.I NTELLDEPOSITMONEY, 0) + NVL (l.F INANCMONEY, 0), 'fm99999999999999999990.0099999'), '00', '0', to_char (NVL (h.t ransferMoney, 0) + NVL (k.I NTELLDEPOSITMONEY, 0) + NVL (l.F INANCMONEY, 0), 'fm99999999999999999990.0099999)) countMoney,



NVL (h.t ransferNum, 0) transferNum,


Decode (to_char (NVL (h.t ransferMoney, 0), 'fm99999999999999999990.0099999'), '00', '0', to_char (NVL (h.t ransferMoney, 0), 'fm99999999999999999990.0099999)) transferMoney,

NVL (k.I NTELLDEPOSITNUM, 0) INTELLDEPOSITNUM,

Decode (to_char (NVL (k.I NTELLDEPOSITMONEY, 0), 'fm99999999999999999990.0099999'), '00', '0', to_char (NVL (k.I NTELLDEPOSITMONEY, 0), 'fm99999999999999999990.0099999)) INTELLDEPOSITMONEY,

NVL (l.F INANCNUM, 0) FINANCNUM,

Decode (to_char (NVL (l.F INANCMONEY, 0), 'fm99999999999999999990.0099999'), '00', '0', to_char (NVL (l.F INANCMONEY, 0), 'fm99999999999999999990.0099999)) FINANCMONEY,

NVL (n.L IVEPAYMENTNUM, 0) LIVEPAYMENTNUM,

Decode (to_char (NVL (n.L IVEPAYMENTMONEY, 0), 'fm99999999999999999990.0099999'), '00', '0', to_char (NVL (n.L IVEPAYMENTMONEY, 0), 'fm99999999999999999990.0099999)) LIVEPAYMENTMONEY,

NVL (i.O PENCARDNUM, 0) openCardNum, NVL (d.P ASSWORDNUM, 0) passwordNum, NVL (e.P ERSONINFONUM, 0) personinfoNum, NVL (f.P RINTNUM, 0) printNum, NVL (g.S IGNNUM, 0) signNum, NVL (John casteen ARDRELOSSNUM, 0) CARDRELOSSNUM from IB_LOG_LOGMASTER_LOG_BAK a
Left the join (select * from (select a. *, rownum ru from (select BranchNum from IB_UPM_BRANCH_INFO connect by nocycle the prior BranchNum=SupeBranchNum start with BranchNum='99999') a)) h on h.b ranchnum=a. ranchnum
Left the join IB_UPM_BRANCH_INFO b on b.b ranchnum=a. ranchnum
Left the join V_BRANCHINFO_VIEW c on b.S UPEBRANCHNUM=mount RANCHNUM
Left the join (select count (BRANCHNUM) PASSWORDNUM, BRANCHNUM from IB_LOG_LOGMASTER_LOG_BAK where TRANCODE in (' pcva. Trade. B036.01 ', 'pcva. Trade. B082.01') and RETURNCODE='000000' and TRANDATE between '" + __REQ__ (" startTime ") + "' and '" + __REQ__ [] "endTime" + "'
And branchnum in (select branchnum from ib_upm_branch_info START WITH branchnum in (" + __REQ__ [] "branchId" + ") CONNECT BY the PRIOR branchnum=supebranchnum) group BY branchnum) d on db RANCHNUM=a. RANCHNUM
Left the join (select count (BRANCHNUM) PERSONINFONUM, BRANCHNUM from IB_LOG_LOGMASTER_LOG_BAK
Where TRANCODE='pcva. Trade. B008.01' and RETURNCODE='000000' and TRANDATE between '" + __REQ__ (" startTime ") + "' and '" + __REQ__ [] "endTime" + "'
And branchnum in (select branchnum from ib_upm_branch_info START WITH branchnum in (" + __REQ__ [] "branchId" + ") CONNECT BY the PRIOR branchnum=supebranchnum) group BY branchnum) e on e.B RANCHNUM=a. RANCHNUM

Left the join (select count (BRANCHNUM) PRINTNUM, BRANCHNUM from IB_LOG_LOGMASTER_LOG_BAK
Where (TRANCODE in (' pcva. Trade. B084.01 ', 'pcva. Trade. B163.01') or OPRFLAG in (' D001 ', 'D002', 'B004') or (TRANCODE in (' pcva. Trade. B207.01 ', 'pcva. Trade. B214.01', 'pcva. Trade. B216.01') and OPRTRANNAME in (' notice query/cancel ', 'notice to establish'))) and RETURNCODE='000000' and TRANDATE between '" + __REQ__ (" startTime ") + "' and '" + __REQ__ [] "endTime" + "'
And branchnum in (select branchnum from ib_upm_branch_info START WITH branchnum in (" + __REQ__ [] "branchId" + ") CONNECT BY the PRIOR branchnum=supebranchnum) group BY branchnum) f on f.B RANCHNUM=a. RANCHNUM

Left the join (select count (BRANCHNUM) SIGNNUM, BRANCHNUM from IB_LOG_LOGMASTER_LOG_BAK where (SIGNFLAG='S' or (TRANCODE='pcva. Trade. B080.01' and RETURNCODE='000000')) and TRANDATE between '" + __REQ__ (" startTime ") + "' and '" + __REQ__ [] "endTime" + "'
And branchnum in (select branchnum from ib_upm_branch_info START WITH branchnum in (" + __REQ__ [] "branchId" + ") CONNECT BY the PRIOR branchnum=supebranchnum) group BY branchnum) g on g.B RANCHNUM=a. RANCHNUM

nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull