1

Topic: Request optimization

Good afternoon! Help to optimize request, please,

with
W2MUNTYPEPAR as (select ID from T_DEAPRMDSC where CODE=W2_PKG_PAYPARAM.fGet_W2_MUNTYPEDOMAIN);
bank_attr as (select
m. CODE;
m. FORM_CODE;
m. NORD;
m.id dea_id;
m.dep_id dea_dep_id;
c. CODE as CLI_CODE;
m. CLI_ID;
m. CLI_DEP_ID;
SUBSTR (G_PKGCLI.FGETCLILONGNAME (m. CLI_DEP_ID, m. CLI_ID), 1, 250) CLI_NAME;
b. CODE as BNK_CODE;
bh. LONGNAME as CLI_BNK_NAME;
a. CODE_ACC CLI_CODE_ACC;
G_PkgCli.fGetCliTaxCode (c. DEP_ID, c. ID) RNN
from G_CLI c, M_MUNDEA m, t_ord t;
G_CLIBNKATR a, G_BNK b, G_BNKHST bh
where c. DEP_ID = m. CLI_DEP_ID
and c. ID = m. CLI_ID
and t.id=m.id
and t.dep_id=m.dep_id
and a. ID = m. CLI_ID and a. DEP_ID = m. CLI_DEP_ID and a. NORD = m. NORD
and b. ID (+) = a. BNK_ID and bh. ID (+) = b. ID
and P_OperDay between bh. FROMDATE and bh. TODATE
and m.code ='AVANGARD'
and t.arcfl = ' 0'
and rownum=1
/* and m. CODE = ' 2808 '*/)
select
' 1 ' providerType;
' kz ' reg_code;
m. CODE;
c. CODE as CLI_CODE;
m.longname pName;
SUBSTR (G_PKGCLI.FGETCLILONGNAME (d. CLI_DEP_ID, d. CLI_ID), 1, 250) CLI_NAME;
b. CODE as BNK_CODE;
bh. LONGNAME as CLI_BNK_NAME;
a. CODE_ACC CLI_CODE_ACC;
G_PkgCli.fGetCliTaxCode (c. DEP_ID, c. ID) RNN;
m. FORM_CODE formCode;
prm.parvalue as GROUP_CODE;
bs_dom.dLongname (W2_PKG_PAYPARAM.fGet_W2_MUNTYPEDOMAIN,prm.parvalue) GROUP_NAME;
' - ' prim;
d.id dea_id;
d.dep_id dea_dep_id
from G_CLI c, T_PROCMEM mem, T_ORD t, T_DEA d, M_MUNDEA m;
G_CLIBNKATR a, G_BNK b, G_BNKHST bh, T_DEAPRM prm, W2MUNTYPEPAR
where c. DEP_ID = d. CLI_DEP_ID
and c. ID = d. CLI_ID
and t. DEP_ID = d. DEP_ID
and t. ID = d. ID
and m. ID = d. ID
and m. DEP_ID = d. DEP_ID
and m. DEP_ID = mem. DEP_ID (+)
and m. ID = mem. ORD_ID (+)
and mem. MAINFL (+) = ' 1'
and a. ID = m. CLI_ID and a. DEP_ID = m. CLI_DEP_ID and a. NORD = m. NORD
and b. ID (+) = a. BNK_ID and bh. ID (+) = b. ID
and prm.dep_id=m.dep_id and prm.id=m.id and prm.dea_id=W2MUNTYPEPAR.ID
and P_OperDay between bh. FROMDATE and bh. TODATE and t.arcfl = ' 0'
and P_OperDay between d. FROMDATE and nvl (d. TODATE, p_maxdate)
and t. ARCFL = 0 and d. ARESTFL = 0
and ((T_PKGDEAPRM.fPar (idDea => m.id, depDea => m.dep_id, idPar => 70751) = ' 1 ')
or ((d.dep_id in (select ID from c_dep CONNECT BY ID=PRIOR ID_HI START WITH ID=2650))) and (not (m.code like ' AVANG % ')))
union
select
' 2 ' providerType;
substr (dd.prim, 1, instr (dd.prim, '; ')-1) reg_code;
-- r.name reg_name;
b. CODE || ' - ' || dd.constval code;
b. CLI_CODE;
' AVANGARD - ' || dd. NAME pName;
b. CLI_NAME;
b. BNK_CODE;
b. CLI_BNK_NAME;
b. CLI_CODE_ACC;
b. RNN;
b. FORM_CODE formCode;
substr (dd.code, 7,6) as GROUP_CODE;
bs_dom.dLongname (W2_PKG_PAYPARAM.fGet_W2_MUNTYPEDOMAIN,substr (dd.code, 7,6)) GROUP_NAME;
dd.prim;
b.dea_id;
b.dea_dep_id
from bank_attr b;
C_VALLIST dd
where (dd. CODE in (' M_GWS_SPPMOB ', ' M_GWS_SPPCAB ', ' M_GWS_SPPINT ', ' M_GWS_SPPUTI '))

2

Re: Request optimization

100500

3

Re: Request optimization

SELECT STATEMENT, GOAL = RULE
SORT UNIQUE
UNION-ALL
FILTER
FILTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS OUTER
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS OUTER
TABLE ACCESS BY INDEX ROWID COLVIR T_DEAPRMDSC_STD
INDEX UNIQUE SCAN COLVIR AK_T_DEAPRMDSC_STD_CODE
INDEX UNIQUE SCAN COLVIR PK_T_DEAPRMDSC_LNG
TABLE ACCESS BY INDEX ROWID COLVIR T_DEAPRM
INDEX RANGE SCAN COLVIR FK_T_DEAPRM_DSC
TABLE ACCESS BY INDEX ROWID COLVIR M_MUNDEA
INDEX UNIQUE SCAN COLVIR PK_M_MUNDEA
TABLE ACCESS BY INDEX ROWID COLVIR G_CLIBNKATR
INDEX UNIQUE SCAN COLVIR PK_G_CLIBNKATR
TABLE ACCESS BY INDEX ROWID COLVIR G_BNK
INDEX UNIQUE SCAN COLVIR PK_G_BNK
TABLE ACCESS BY INDEX ROWID COLVIR T_DEA
INDEX UNIQUE SCAN COLVIR PK_T_DEA
TABLE ACCESS BY INDEX ROWID COLVIR T_ORD
INDEX UNIQUE SCAN COLVIR PK_T_ORD
TABLE ACCESS BY INDEX ROWID COLVIR G_CLI
INDEX UNIQUE SCAN COLVIR PK_G_CLI
TABLE ACCESS BY INDEX ROWID COLVIR T_PROCMEM
INDEX RANGE SCAN COLVIR FK_PROCMEM_ORDERS
TABLE ACCESS BY INDEX ROWID COLVIR G_BNKHST
INDEX RANGE SCAN COLVIR PK_G_BNKHST
FILTER
CONNECT BY WITH FILTERING (UNIQUE)
TABLE ACCESS BY INDEX ROWID COLVIR C_DEP
INDEX UNIQUE SCAN COLVIR PK_C_DEP
NESTED LOOPS
CONNECT BY PUMP
TABLE ACCESS BY INDEX ROWID COLVIR C_DEP
INDEX UNIQUE SCAN COLVIR PK_C_DEP
NESTED LOOPS
NESTED LOOPS OUTER
VIEW COLVIR
SORT UNIQUE
UNION-ALL
CONCATENATION
FILTER
TABLE ACCESS BY INDEX ROWID COLVIR C_VALLIST_STD
INDEX RANGE SCAN COLVIR PK_C_VALLIST_STD
INDEX RANGE SCAN COLVIR PK_C_VALLIST_STD
FILTER
TABLE ACCESS BY INDEX ROWID COLVIR C_VALLIST_STD
INDEX RANGE SCAN COLVIR PK_C_VALLIST_STD
INDEX RANGE SCAN COLVIR PK_C_VALLIST_STD
FILTER
TABLE ACCESS BY INDEX ROWID COLVIR C_VALLIST_STD
INDEX RANGE SCAN COLVIR PK_C_VALLIST_STD
INDEX RANGE SCAN COLVIR PK_C_VALLIST_STD
FILTER
TABLE ACCESS BY INDEX ROWID COLVIR C_VALLIST_STD
INDEX RANGE SCAN COLVIR PK_C_VALLIST_STD
INDEX RANGE SCAN COLVIR PK_C_VALLIST_STD
CONCATENATION
TABLE ACCESS BY INDEX ROWID COLVIR C_VALLIST_STD
INDEX RANGE SCAN COLVIR PK_C_VALLIST_STD
TABLE ACCESS BY INDEX ROWID COLVIR C_VALLIST_STD
INDEX RANGE SCAN COLVIR PK_C_VALLIST_STD
TABLE ACCESS BY INDEX ROWID COLVIR C_VALLIST_STD
INDEX RANGE SCAN COLVIR PK_C_VALLIST_STD
TABLE ACCESS BY INDEX ROWID COLVIR C_VALLIST_STD
INDEX RANGE SCAN COLVIR PK_C_VALLIST_STD
TABLE ACCESS BY INDEX ROWID COLVIR C_VALLIST_LNG
INDEX UNIQUE SCAN COLVIR PK_C_VALLIST_LNG
VIEW COLVIR
COUNT STOPKEY
FILTER
NESTED LOOPS OUTER
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS OUTER
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID COLVIR M_MUNDEA
INDEX RANGE SCAN COLVIR AK_M_MUNDEA_CODE
TABLE ACCESS BY INDEX ROWID COLVIR G_CLIBNKATR
INDEX UNIQUE SCAN COLVIR PK_G_CLIBNKATR
TABLE ACCESS BY INDEX ROWID COLVIR G_BNK
INDEX UNIQUE SCAN COLVIR PK_G_BNK
TABLE ACCESS BY INDEX ROWID COLVIR T_ORD
INDEX UNIQUE SCAN COLVIR PK_T_ORD
TABLE ACCESS BY INDEX ROWID COLVIR G_CLI
INDEX UNIQUE SCAN COLVIR PK_G_CLI
TABLE ACCESS BY INDEX ROWID COLVIR G_BNKHST
INDEX RANGE SCAN COLVIR PK_G_BNKHST

The execution plan I apply

4

Re: Request optimization

gister;
Result the plan in a type

set lines 1000
set pages 0
with... select / * + gather_plan_statistics*/. from.;
select * from table (dbms_xplan.display_cursor (format =>'ALL ALLSTATS LAST '));

5

Re: Request optimization

gister;
Without the information about estimated/actual rows, etc. - the plan looks not bad. Until it will be clarified that in it there are any wild readings of millions lines of an index billions times.

6

Re: Request optimization

SQL_ID dmsx1u445cc8f, child number 2
-------------------------------------
select d. ID, d. DEP_ID, d. FROMDATE, d. TODATE, d. ARESTFL,
t. DORD, t. CODE, t. ARCFL, t. VAL_ID,
substr (T_PkgVal.fGetCode (t. VAL_ID), 1,30) as VALCODE, c. CODE as
CLI_CODE, SUBSTR (G_PKGCLI.FGETCLILONGNAME (d. CLI_DEP_ID, d. CLI_ID), 1,
250) CLI_NAME, d. CLI_DEP_ID, d. CLI_ID, m. NORD, b. CODE as
BNK_CODE, a. CODE_ACC, m. CODE as PAY_CODE, ch. TAXCODE RNN,
m. CODE_BC, m. GRP, T_PkgDeaPrm.fParByCode (m. ID, m. DEP_ID, ' MD_KNP ')
KNP, dep. CODE as DEP_CODE, m. IMP_ID, p. ID as PROC_ID, p. BOP_ID,
p. NSTAT, s. LONGNAME as STAT_NAME, s. EDITFL, s. FINISHFL,
s. COLOR as STATCOLOR, C_PkgRatify.isRatWait (p. ID) as VRFFL,
C_PkgRatify.fOprRatCancel (p. ID) as RETFL, ad. TT_ID, m. CMSTYPE,
m. CMSPAYFL, m. SVPAYFL, P_PkgErr.fHasErr (m. DEP_ID, m. ID) as ERRFL
from G_CLI c, G_CLIHST ch, G_CLIBNKATR a, G_BNK b, C_DEP dep,
T_ARLDEA ad, T_BOP_STAT s, T_PROCESS p, T_PROCMEM mem, T_ORD t,
T_DEA d, M_MUNDEA m where c. DEP_ID = d. CLI_DEP_ID and c. ID =
d. CLI_ID a
Plan hash value: 3535002211
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | FILTER | | | | |
| 2 | NESTED LOOPS | | | | |
| 3 | NESTED LOOPS | | | | |
| 4 | NESTED LOOPS OUTER | | | | |
| 5 | NESTED LOOPS OUTER | | | | |
| 6 | NESTED LOOPS | | | | |
| 7 | NESTED LOOPS | | | | |
| 8 | NESTED LOOPS | | | | |
| 9 | NESTED LOOPS | | | | |
| 10 | NESTED LOOPS OUTER | | | | |
| 11 | NESTED LOOPS | | | | |
| 12 | NESTED LOOPS | | | | |
| 13 | NESTED LOOPS | | | | |
| 14 | NESTED LOOPS | | | | |
| 15 | TABLE ACCESS BY INDEX ROWID | M_MUNDEA | | | |
|* 16 | INDEX RANGE SCAN | AK_M_MUNDEA_CODE | | | |
| 17 | TABLE ACCESS BY INDEX ROWID | T_DEA | | | |
|* 18 | INDEX UNIQUE SCAN | PK_T_DEA | | | |
| 19 | TABLE ACCESS BY INDEX ROWID | T_ORD | | | |
|* 20 | INDEX UNIQUE SCAN | PK_T_ORD | | | |
| 21 | TABLE ACCESS BY INDEX ROWID | C_DEP | | | |
|* 22 | INDEX UNIQUE SCAN | PK_C_DEP | | | |
| 23 | TABLE ACCESS BY INDEX ROWID | G_CLIBNKATR | | | |
|* 24 | INDEX UNIQUE SCAN | PK_G_CLIBNKATR | | | |
| 25 | TABLE ACCESS BY INDEX ROWID | G_BNK | | | |
|* 26 | INDEX UNIQUE SCAN | PK_G_BNK | | | |
| 27 | TABLE ACCESS BY INDEX ROWID | G_CLI | | | |
|* 28 | INDEX UNIQUE SCAN | PK_G_CLI | | | |
|* 29 | TABLE ACCESS BY INDEX ROWID | T_PROCMEM | | | |
|* 30 | INDEX RANGE SCAN | FK_PROCMEM_ORDERS | | | |
| 31 | TABLE ACCESS BY INDEX ROWID | T_PROCESS | | | |
|* 32 | INDEX UNIQUE SCAN | PK_T_PROCESS | | | |
| 33 | TABLE ACCESS BY INDEX ROWID | T_BOP_STAT_STD | | | |
|* 34 | INDEX UNIQUE SCAN | PK_T_BOP_STAT_STD | | | |
| 35 | TABLE ACCESS BY INDEX ROWID | T_BOP_STAT_LNG | | | |
|* 36 | INDEX UNIQUE SCAN | PK_T_BOP_STAT_LNG | | | |
| 37 | TABLE ACCESS BY INDEX ROWID | T_ARLDEA | | | |
|* 38 | INDEX RANGE SCAN | FK_T_ARLDEA_T_DEAPAYATR | | | |
|* 39 | INDEX RANGE SCAN | PK_G_CLIHST | | | |
|* 40 | TABLE ACCESS BY INDEX ROWID | G_CLIHST | | | |
|* 41 | FILTER | | | | |
|* 42 | CONNECT BY WITH FILTERING (UNIQUE) | | 73728 | 73728 | |
| 43 | TABLE ACCESS BY INDEX ROWID | C_DEP | | | |
|* 44 | INDEX UNIQUE SCAN | PK_C_DEP | | | |
| 45 | NESTED LOOPS | | | | |
| 46 | CONNECT BY PUMP | | | | |
| 47 | TABLE ACCESS BY INDEX ROWID | C_DEP | | | |
|* 48 | INDEX RANGE SCAN | FK_C_DEP_HI | | | |
--------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
15 - SEL$F5BB74E1 / M@SEL$1
16 - SEL$F5BB74E1 / M@SEL$1
17 - SEL$F5BB74E1 / D@SEL$1
18 - SEL$F5BB74E1 / D@SEL$1
19 - SEL$F5BB74E1 / T@SEL$1
20 - SEL$F5BB74E1 / T@SEL$1
21 - SEL$F5BB74E1 / DEP@SEL$1
22 - SEL$F5BB74E1 / DEP@SEL$1
23 - SEL$F5BB74E1 / A@SEL$1
24 - SEL$F5BB74E1 / A@SEL$1
25 - SEL$F5BB74E1 / B@SEL$1
26 - SEL$F5BB74E1 / B@SEL$1
27 - SEL$F5BB74E1 / C@SEL$1
28 - SEL$F5BB74E1 / C@SEL$1
29 - SEL$F5BB74E1 / MEM@SEL$1
30 - SEL$F5BB74E1 / MEM@SEL$1
31 - SEL$F5BB74E1 / P@SEL$1
32 - SEL$F5BB74E1 / P@SEL$1
33 - SEL$F5BB74E1 / S@SEL$2
34 - SEL$F5BB74E1 / S@SEL$2
35 - SEL$F5BB74E1 / L@SEL$2
36 - SEL$F5BB74E1 / L@SEL$2
37 - SEL$F5BB74E1 / AD@SEL$1
38 - SEL$F5BB74E1 / AD@SEL$1
39 - SEL$F5BB74E1 / CH@SEL$1
40 - SEL$F5BB74E1 / CH@SEL$1
41 - SEL$0EEC8FC1
43 - SEL$6 / C_DEP@SEL$6
44 - SEL$6 / C_DEP@SEL$6
45 - SEL$5
47 - SEL$5 / C_DEP@SEL$5
48 - SEL$5 / C_DEP@SEL$5
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter ((TO_NUMBER ("C_FPRM" (' SHOWALLMUNDEA ', "P_IDDEP2" ())) =1 OR IS NOT NULL))
16 - access ("M". "CODE" =:FLT_PAY_CODE)
18 - access ("M". "DEP_ID" = "D". "DEP_ID" AND "M". "ID" = "D". "ID")
20 - access ("T". "DEP_ID" = "D". "DEP_ID" AND "T". "ID" = "D". "ID")
22 - access ("DEP". "ID" = "M". "DEP_ID")
24 - access ("A". "DEP_ID" = "M". "CLI_DEP_ID" AND "A". "ID" = "M". "CLI_ID" AND "A". "NORD" = "M". "NORD")
26 - access ("B". "ID" = "A". "BNK_ID")
28 - access ("C". "DEP_ID" = "D". "CLI_DEP_ID" AND "C". "ID" = "D". "CLI_ID")
29 - filter (TO_NUMBER ("MEM". "MAINFL") =1)
30 - access ("M". "DEP_ID" = "MEM". "DEP_ID" AND "M". "ID" = "MEM". "ORD_ID")
32 - access ("P". "ID" = "MEM". "ID")
34 - access ("S". "ID" = "P". "BOP_ID" AND "S". "NORD" = "P". "NSTAT")
36 - access ("L". "ID" = "S". "ID" AND "L". "NORD" = "S". "NORD" AND "L". "LNG_ID" = "P_LNG" ())
38 - access ("AD". "DEP_ID" = "T". "DEP_ID" AND "AD". "ORD_ID" = "T". "ID")
39 - access ("CH". "DEP_ID" = "C". "DEP_ID" AND "CH". "ID" = "C". "ID" AND
"CH". "FROMDATE" <= "P_OPERDAY" ())
filter ("CH". "FROMDATE" <= "P_OPERDAY" ())
40 - filter ("CH". "TODATE"> = "P_OPERDAY" ())
41 - filter ("ID" =:B1)
42 - access ("ID_HI" =PRIOR NULL)
44 - access ("ID" = "P_IDDEP" ())
48 - access ("ID_HI" = "connect $ _ by $ _ pump $ _ 014". "prior id")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "M". "ID" [NUMBER, 22], "M". "DEP_ID" [NUMBER, 22], "M". "NORD" [NUMBER, 22],
"M". "CODE" [VARCHAR2,10], "M". "CODE_BC" [VARCHAR2,30], "M". "CMSTYPE" [CHARACTER, 1],
"M". "GRP" [VARCHAR2,10], "M". "CMSPAYFL" [CHARACTER, 1], "M". "SVPAYFL" [CHARACTER, 1],
"M". "IMP_ID" [NUMBER, 22], "D". "DEP_ID" [NUMBER, 22], "D". "ID" [NUMBER, 22],
"D". "CLI_DEP_ID" [NUMBER, 22], "D". "CLI_ID" [NUMBER, 22], "D". "ARESTFL" [NUMBER, 22],
"D". "FROMDATE" [DATE, 7], "D". "TODATE" [DATE, 7], "T". "VAL_ID" [NUMBER, 22], "T". "DORD" [DATE, 7],
"T". "CODE" [VARCHAR2,30], "T". "ARCFL" [NUMBER, 22], "DEP". "CODE" [VARCHAR2,30],
"A". "CODE_ACC" [VARCHAR2,30], "B". "CODE" [VARCHAR2,30], "C". "CODE" [VARCHAR2,30],
"P". "ID" [NUMBER, 22], "P". "BOP_ID" [NUMBER, 22], "P". "NSTAT" [NUMBER, 22],
"S". "LONGNAME" [VARCHAR2,1000], "S". "FINISHFL" [CHARACTER, 1], "S". "EDITFL" [CHARACTER, 1],
"S". "COLOR" [NUMBER, 22], "L". "LONGNAME" [VARCHAR2,1000], "AD". "TT_ID" [NUMBER, 22],
"CH". "TAXCODE" [VARCHAR2,30]
2 - "M". "ID" [NUMBER, 22], "M". "DEP_ID" [NUMBER, 22], "M". "NORD" [NUMBER, 22],
"M". "CODE" [VARCHAR2,10], "M". "CODE_BC" [VARCHAR2,30], "M". "CMSTYPE" [CHARACTER, 1],
"M". "GRP" [VARCHAR2,10], "M". "CMSPAYFL" [CHARACTER, 1], "M". "SVPAYFL" [CHARACTER, 1],
"M". "IMP_ID" [NUMBER, 22], "D". "DEP_ID" [NUMBER, 22], "D". "ID" [NUMBER, 22],
"D". "CLI_DEP_ID" [NUMBER, 22], "D". "CLI_ID" [NUMBER, 22], "D". "ARESTFL" [NUMBER, 22],
"D". "FROMDATE" [DATE, 7], "D". "TODATE" [DATE, 7], "T". "VAL_ID" [NUMBER, 22], "T". "DORD" [DATE, 7],
"T". "CODE" [VARCHAR2,30], "T". "ARCFL" [NUMBER, 22], "DEP". "CODE" [VARCHAR2,30],
"A". "CODE_ACC" [VARCHAR2,30], "B". "CODE" [VARCHAR2,30], "C". "CODE" [VARCHAR2,30],
"P". "ID" [NUMBER, 22], "P". "BOP_ID" [NUMBER, 22], "P". "NSTAT" [NUMBER, 22],
"S". "LONGNAME" [VARCHAR2,1000], "S". "FINISHFL" [CHARACTER, 1], "S". "EDITFL" [CHARACTER, 1],
"S". "COLOR" [NUMBER, 22], "L". "LONGNAME" [VARCHAR2,1000], "AD". "TT_ID" [NUMBER, 22],
"CH". "TAXCODE" [VARCHAR2,30]
3 - "M". "ID" [NUMBER, 22], "M". "DEP_ID" [NUMBER, 22], "M". "NORD" [NUMBER, 22],
"M". "CODE" [VARCHAR2,10], "M". "CODE_BC" [VARCHAR2,30], "M". "CMSTYPE" [CHARACTER, 1],
"M". "GRP" [VARCHAR2,10], "M". "CMSPAYFL" [CHARACTER, 1], "M". "SVPAYFL" [CHARACTER, 1],
"M". "IMP_ID" [NUMBER, 22], "D". "DEP_ID" [NUMBER, 22], "D". "ID" [NUMBER, 22],
"D". "CLI_DEP_ID" [NUMBER, 22], "D". "CLI_ID" [NUMBER, 22], "D". "ARESTFL" [NUMBER, 22],
"D". "FROMDATE" [DATE, 7], "D". "TODATE" [DATE, 7], "T". "VAL_ID" [NUMBER, 22], "T". "DORD" [DATE, 7],
"T". "CODE" [VARCHAR2,30], "T". "ARCFL" [NUMBER, 22], "DEP". "CODE" [VARCHAR2,30],
"A". "CODE_ACC" [VARCHAR2,30], "B". "CODE" [VARCHAR2,30], "C". "CODE" [VARCHAR2,30],
"P". "ID" [NUMBER, 22], "P". "BOP_ID" [NUMBER, 22], "P". "NSTAT" [NUMBER, 22],
"S". "LONGNAME" [VARCHAR2,1000], "S". "FINISHFL" [CHARACTER, 1], "S". "EDITFL" [CHARACTER, 1],
"S". "COLOR" [NUMBER, 22], "L". "LONGNAME" [VARCHAR2,1000], "AD". "TT_ID" [NUMBER, 22],
"CH".ROWID [ROWID, 10]
4 - "M". "ID" [NUMBER, 22], "M". "DEP_ID" [NUMBER, 22], "M". "NORD" [NUMBER, 22],
"M". "CODE" [VARCHAR2,10], "M". "CODE_BC" [VARCHAR2,30], "M". "CMSTYPE" [CHARACTER, 1],
"M". "GRP" [VARCHAR2,10], "M". "CMSPAYFL" [CHARACTER, 1], "M". "SVPAYFL" [CHARACTER, 1],
"M". "IMP_ID" [NUMBER, 22], "D". "DEP_ID" [NUMBER, 22], "D". "ID" [NUMBER, 22],
"D". "CLI_DEP_ID" [NUMBER, 22], "D". "CLI_ID" [NUMBER, 22], "D". "ARESTFL" [NUMBER, 22],
"D". "FROMDATE" [DATE, 7], "D". "TODATE" [DATE, 7], "T". "VAL_ID" [NUMBER, 22], "T". "DORD" [DATE, 7],
"T". "CODE" [VARCHAR2,30], "T". "ARCFL" [NUMBER, 22], "DEP". "CODE" [VARCHAR2,30],
"A". "CODE_ACC" [VARCHAR2,30], "B". "CODE" [VARCHAR2,30], "C". "DEP_ID" [NUMBER, 22],
"C". "ID" [NUMBER, 22], "C". "CODE" [VARCHAR2,30], "P". "ID" [NUMBER, 22], "P". "BOP_ID" [NUMBER, 22],
"P". "NSTAT" [NUMBER, 22], "S". "LONGNAME" [VARCHAR2,1000], "S". "FINISHFL" [CHARACTER, 1],
"S". "EDITFL" [CHARACTER, 1], "S". "COLOR" [NUMBER, 22], "L". "LONGNAME" [VARCHAR2,1000],
"AD". "TT_ID" [NUMBER, 22]
5 - "M". "ID" [NUMBER, 22], "M". "DEP_ID" [NUMBER, 22], "M". "NORD" [NUMBER, 22],
"M". "CODE" [VARCHAR2,10], "M". "CODE_BC" [VARCHAR2,30], "M". "CMSTYPE" [CHARACTER, 1],
"M". "GRP" [VARCHAR2,10], "M". "CMSPAYFL" [CHARACTER, 1], "M". "SVPAYFL" [CHARACTER, 1],
"M". "IMP_ID" [NUMBER, 22], "D". "DEP_ID" [NUMBER, 22], "D". "ID" [NUMBER, 22],
"D". "CLI_DEP_ID" [NUMBER, 22], "D". "CLI_ID" [NUMBER, 22], "D". "ARESTFL" [NUMBER, 22],
"D". "FROMDATE" [DATE, 7], "D". "TODATE" [DATE, 7], "T". "DEP_ID" [NUMBER, 22], "T". "ID" [NUMBER, 22],
"T". "VAL_ID" [NUMBER, 22], "T". "DORD" [DATE, 7], "T". "CODE" [VARCHAR2,30], "T". "ARCFL" [NUMBER, 22],
"DEP". "CODE" [VARCHAR2,30], "A". "CODE_ACC" [VARCHAR2,30], "B". "CODE" [VARCHAR2,30],
"C". "DEP_ID" [NUMBER, 22], "C". "ID" [NUMBER, 22], "C". "CODE" [VARCHAR2,30], "P". "ID" [NUMBER, 22],
"P". "BOP_ID" [NUMBER, 22], "P". "NSTAT" [NUMBER, 22], "S". "LONGNAME" [VARCHAR2,1000],
"S". "FINISHFL" [CHARACTER, 1], "S". "EDITFL" [CHARACTER, 1], "S". "COLOR" [NUMBER, 22],
"L". "LONGNAME" [VARCHAR2,1000]
6 - "M". "ID" [NUMBER, 22], "M". "DEP_ID" [NUMBER, 22], "M". "NORD" [NUMBER, 22],
"M". "CODE" [VARCHAR2,10], "M". "CODE_BC" [VARCHAR2,30], "M". "CMSTYPE" [CHARACTER, 1],
"M". "GRP" [VARCHAR2,10], "M". "CMSPAYFL" [CHARACTER, 1], "M". "SVPAYFL" [CHARACTER, 1],
"M". "IMP_ID" [NUMBER, 22], "D". "DEP_ID" [NUMBER, 22], "D". "ID" [NUMBER, 22],
"D". "CLI_DEP_ID" [NUMBER, 22], "D". "CLI_ID" [NUMBER, 22], "D". "ARESTFL" [NUMBER, 22],
"D". "FROMDATE" [DATE, 7], "D". "TODATE" [DATE, 7], "T". "DEP_ID" [NUMBER, 22], "T". "ID" [NUMBER, 22],
"T". "VAL_ID" [NUMBER, 22], "T". "DORD" [DATE, 7], "T". "CODE" [VARCHAR2,30], "T". "ARCFL" [NUMBER, 22],
"DEP". "CODE" [VARCHAR2,30], "A". "CODE_ACC" [VARCHAR2,30], "B". "CODE" [VARCHAR2,30],
"C". "DEP_ID" [NUMBER, 22], "C". "ID" [NUMBER, 22], "C". "CODE" [VARCHAR2,30], "P". "ID" [NUMBER, 22],
"P". "BOP_ID" [NUMBER, 22], "P". "NSTAT" [NUMBER, 22], "S". "ID" [NUMBER, 22], "S". "NORD" [NUMBER, 22],
"S". "LONGNAME" [VARCHAR2,1000], "S". "FINISHFL" [CHARACTER, 1], "S". "EDITFL" [CHARACTER, 1],
"S". "COLOR" [NUMBER, 22]
7 - "M". "ID" [NUMBER, 22], "M". "DEP_ID" [NUMBER, 22], "M". "NORD" [NUMBER, 22],
"M". "CODE" [VARCHAR2,10], "M". "CODE_BC" [VARCHAR2,30], "M". "CMSTYPE" [CHARACTER, 1],
"M". "GRP" [VARCHAR2,10], "M". "CMSPAYFL" [CHARACTER, 1], "M". "SVPAYFL" [CHARACTER, 1],
"M". "IMP_ID" [NUMBER, 22], "D". "DEP_ID" [NUMBER, 22], "D". "ID" [NUMBER, 22],
"D". "CLI_DEP_ID" [NUMBER, 22], "D". "CLI_ID" [NUMBER, 22], "D". "ARESTFL" [NUMBER, 22],
"D". "FROMDATE" [DATE, 7], "D". "TODATE" [DATE, 7], "T". "DEP_ID" [NUMBER, 22], "T". "ID" [NUMBER, 22],
"T". "VAL_ID" [NUMBER, 22], "T". "DORD" [DATE, 7], "T". "CODE" [VARCHAR2,30], "T". "ARCFL" [NUMBER, 22],
"DEP". "CODE" [VARCHAR2,30], "A". "CODE_ACC" [VARCHAR2,30], "B". "CODE" [VARCHAR2,30],
"C". "DEP_ID" [NUMBER, 22], "C". "ID" [NUMBER, 22], "C". "CODE" [VARCHAR2,30], "P". "ID" [NUMBER, 22],
"P". "BOP_ID" [NUMBER, 22], "P". "NSTAT" [NUMBER, 22]
8 - "M". "ID" [NUMBER, 22], "M". "DEP_ID" [NUMBER, 22], "M". "NORD" [NUMBER, 22],
"M". "CODE" [VARCHAR2,10], "M". "CODE_BC" [VARCHAR2,30], "M". "CMSTYPE" [CHARACTER, 1],
"M". "GRP" [VARCHAR2,10], "M". "CMSPAYFL" [CHARACTER, 1], "M". "SVPAYFL" [CHARACTER, 1],
"M". "IMP_ID" [NUMBER, 22], "D". "DEP_ID" [NUMBER, 22], "D". "ID" [NUMBER, 22],
"D". "CLI_DEP_ID" [NUMBER, 22], "D". "CLI_ID" [NUMBER, 22], "D". "ARESTFL" [NUMBER, 22],
"D". "FROMDATE" [DATE, 7], "D". "TODATE" [DATE, 7], "T". "DEP_ID" [NUMBER, 22], "T". "ID" [NUMBER, 22],
"T". "VAL_ID" [NUMBER, 22], "T". "DORD" [DATE, 7], "T". "CODE" [VARCHAR2,30], "T". "ARCFL" [NUMBER, 22],
"DEP". "CODE" [VARCHAR2,30], "A". "CODE_ACC" [VARCHAR2,30], "B". "CODE" [VARCHAR2,30],
"C". "DEP_ID" [NUMBER, 22], "C". "ID" [NUMBER, 22], "C". "CODE" [VARCHAR2,30], "MEM". "ID" [NUMBER, 22]
9 - "M". "ID" [NUMBER, 22], "M". "DEP_ID" [NUMBER, 22], "M". "NORD" [NUMBER, 22],
"M". "CODE" [VARCHAR2,10], "M". "CODE_BC" [VARCHAR2,30], "M". "CMSTYPE" [CHARACTER, 1],
"M". "GRP" [VARCHAR2,10], "M". "CMSPAYFL" [CHARACTER, 1], "M". "SVPAYFL" [CHARACTER, 1],
"M". "IMP_ID" [NUMBER, 22], "D". "DEP_ID" [NUMBER, 22], "D". "ID" [NUMBER, 22],
"D". "CLI_DEP_ID" [NUMBER, 22], "D". "CLI_ID" [NUMBER, 22], "D". "ARESTFL" [NUMBER, 22],
"D". "FROMDATE" [DATE, 7], "D". "TODATE" [DATE, 7], "T". "DEP_ID" [NUMBER, 22], "T". "ID" [NUMBER, 22],
"T". "VAL_ID" [NUMBER, 22], "T". "DORD" [DATE, 7], "T". "CODE" [VARCHAR2,30], "T". "ARCFL" [NUMBER, 22],
"DEP". "CODE" [VARCHAR2,30], "A". "CODE_ACC" [VARCHAR2,30], "B". "CODE" [VARCHAR2,30],
"C". "DEP_ID" [NUMBER, 22], "C". "ID" [NUMBER, 22], "C". "CODE" [VARCHAR2,30]
10 - "M". "ID" [NUMBER, 22], "M". "DEP_ID" [NUMBER, 22], "M". "NORD" [NUMBER, 22],
"M". "CODE" [VARCHAR2,10], "M". "CODE_BC" [VARCHAR2,30], "M". "CMSTYPE" [CHARACTER, 1],
"M". "GRP" [VARCHAR2,10], "M". "CMSPAYFL" [CHARACTER, 1], "M". "SVPAYFL" [CHARACTER, 1],
"M". "IMP_ID" [NUMBER, 22], "D". "DEP_ID" [NUMBER, 22], "D". "ID" [NUMBER, 22],
"D". "CLI_DEP_ID" [NUMBER, 22], "D". "CLI_ID" [NUMBER, 22], "D". "ARESTFL" [NUMBER, 22],
"D". "FROMDATE" [DATE, 7], "D". "TODATE" [DATE, 7], "T". "DEP_ID" [NUMBER, 22], "T". "ID" [NUMBER, 22],
"T". "VAL_ID" [NUMBER, 22], "T". "DORD" [DATE, 7], "T". "CODE" [VARCHAR2,30], "T". "ARCFL" [NUMBER, 22],
"DEP". "CODE" [VARCHAR2,30], "A". "CODE_ACC" [VARCHAR2,30], "B". "CODE" [VARCHAR2,30]
11 - "M". "ID" [NUMBER, 22], "M". "DEP_ID" [NUMBER, 22], "M". "NORD" [NUMBER, 22],
"M". "CODE" [VARCHAR2,10], "M". "CODE_BC" [VARCHAR2,30], "M". "CMSTYPE" [CHARACTER, 1],
"M". "GRP" [VARCHAR2,10], "M". "CMSPAYFL" [CHARACTER, 1], "M". "SVPAYFL" [CHARACTER, 1],
"M". "IMP_ID" [NUMBER, 22], "D". "DEP_ID" [NUMBER, 22], "D". "ID" [NUMBER, 22],
"D". "CLI_DEP_ID" [NUMBER, 22], "D". "CLI_ID" [NUMBER, 22], "D". "ARESTFL" [NUMBER, 22],
"D". "FROMDATE" [DATE, 7], "D". "TODATE" [DATE, 7], "T". "DEP_ID" [NUMBER, 22], "T". "ID" [NUMBER, 22],
"T". "VAL_ID" [NUMBER, 22], "T". "DORD" [DATE, 7], "T". "CODE" [VARCHAR2,30], "T". "ARCFL" [NUMBER, 22],
"DEP". "CODE" [VARCHAR2,30], "A". "BNK_ID" [NUMBER, 22], "A". "CODE_ACC" [VARCHAR2,30]
12 - "M". "ID" [NUMBER, 22], "M". "DEP_ID" [NUMBER, 22], "M". "CLI_ID" [NUMBER, 22],
"M". "CLI_DEP_ID" [NUMBER, 22], "M". "NORD" [NUMBER, 22], "M". "CODE" [VARCHAR2,10],
"M". "CODE_BC" [VARCHAR2,30], "M". "CMSTYPE" [CHARACTER, 1], "M". "GRP" [VARCHAR2,10],
"M". "CMSPAYFL" [CHARACTER, 1], "M". "SVPAYFL" [CHARACTER, 1], "M". "IMP_ID" [NUMBER, 22],
"D". "DEP_ID" [NUMBER, 22], "D". "ID" [NUMBER, 22], "D". "CLI_DEP_ID" [NUMBER, 22],
"D". "CLI_ID" [NUMBER, 22], "D". "ARESTFL" [NUMBER, 22], "D". "FROMDATE" [DATE, 7],
"D". "TODATE" [DATE, 7], "T". "DEP_ID" [NUMBER, 22], "T". "ID" [NUMBER, 22], "T". "VAL_ID" [NUMBER, 22],
"T". "DORD" [DATE, 7], "T". "CODE" [VARCHAR2,30], "T". "ARCFL" [NUMBER, 22], "DEP". "CODE" [VARCHAR2,30]
13 - "M". "ID" [NUMBER, 22], "M". "DEP_ID" [NUMBER, 22], "M". "CLI_ID" [NUMBER, 22],
"M". "CLI_DEP_ID" [NUMBER, 22], "M". "NORD" [NUMBER, 22], "M". "CODE" [VARCHAR2,10],
"M". "CODE_BC" [VARCHAR2,30], "M". "CMSTYPE" [CHARACTER, 1], "M". "GRP" [VARCHAR2,10],
"M". "CMSPAYFL" [CHARACTER, 1], "M". "SVPAYFL" [CHARACTER, 1], "M". "IMP_ID" [NUMBER, 22],
"D". "DEP_ID" [NUMBER, 22], "D". "ID" [NUMBER, 22], "D". "CLI_DEP_ID" [NUMBER, 22],
"D". "CLI_ID" [NUMBER, 22], "D". "ARESTFL" [NUMBER, 22], "D". "FROMDATE" [DATE, 7],
"D". "TODATE" [DATE, 7], "T". "DEP_ID" [NUMBER, 22], "T". "ID" [NUMBER, 22], "T". "VAL_ID" [NUMBER, 22],
"T". "DORD" [DATE, 7], "T". "CODE" [VARCHAR2,30], "T". "ARCFL" [NUMBER, 22]
14 - "M". "ID" [NUMBER, 22], "M". "DEP_ID" [NUMBER, 22], "M". "CLI_ID" [NUMBER, 22],
"M". "CLI_DEP_ID" [NUMBER, 22], "M". "NORD" [NUMBER, 22], "M". "CODE" [VARCHAR2,10],
"M". "CODE_BC" [VARCHAR2,30], "M". "CMSTYPE" [CHARACTER, 1], "M". "GRP" [VARCHAR2,10],
"M". "CMSPAYFL" [CHARACTER, 1], "M". "SVPAYFL" [CHARACTER, 1], "M". "IMP_ID" [NUMBER, 22],
"D". "DEP_ID" [NUMBER, 22], "D". "ID" [NUMBER, 22], "D". "CLI_DEP_ID" [NUMBER, 22],
"D". "CLI_ID" [NUMBER, 22], "D". "ARESTFL" [NUMBER, 22], "D". "FROMDATE" [DATE, 7], "D". "TODATE" [DATE, 7]
15 - "M". "ID" [NUMBER, 22], "M". "DEP_ID" [NUMBER, 22], "M". "CLI_ID" [NUMBER, 22],
"M". "CLI_DEP_ID" [NUMBER, 22], "M". "NORD" [NUMBER, 22], "M". "CODE" [VARCHAR2,10],
"M". "CODE_BC" [VARCHAR2,30], "M". "CMSTYPE" [CHARACTER, 1], "M". "GRP" [VARCHAR2,10],
"M". "CMSPAYFL" [CHARACTER, 1], "M". "SVPAYFL" [CHARACTER, 1], "M". "IMP_ID" [NUMBER, 22]
16 - "M".ROWID [ROWID, 10], "M". "CODE" [VARCHAR2,10], "M". "DEP_ID" [NUMBER, 22]
17 - "D". "DEP_ID" [NUMBER, 22], "D". "ID" [NUMBER, 22], "D". "CLI_DEP_ID" [NUMBER, 22],
"D". "CLI_ID" [NUMBER, 22], "D". "ARESTFL" [NUMBER, 22], "D". "FROMDATE" [DATE, 7], "D". "TODATE" [DATE, 7]
18 - "D".ROWID [ROWID, 10], "D". "DEP_ID" [NUMBER, 22], "D". "ID" [NUMBER, 22]
19 - "T". "DEP_ID" [NUMBER, 22], "T". "ID" [NUMBER, 22], "T". "VAL_ID" [NUMBER, 22],
"T". "DORD" [DATE, 7], "T". "CODE" [VARCHAR2,30], "T". "ARCFL" [NUMBER, 22]
20 - "T".ROWID [ROWID, 10], "T". "DEP_ID" [NUMBER, 22], "T". "ID" [NUMBER, 22]
21 - "DEP". "CODE" [VARCHAR2,30]
22 - "DEP".ROWID [ROWID, 10]
23 - "A". "BNK_ID" [NUMBER, 22], "A". "CODE_ACC" [VARCHAR2,30]
24 - "A".ROWID [ROWID, 10]
25 - "B". "CODE" [VARCHAR2,30]
26 - "B".ROWID [ROWID, 10]
27 - "C". "DEP_ID" [NUMBER, 22], "C". "ID" [NUMBER, 22], "C". "CODE" [VARCHAR2,30]
28 - "C".ROWID [ROWID, 10], "C". "DEP_ID" [NUMBER, 22], "C". "ID" [NUMBER, 22]
29 - "MEM". "ID" [NUMBER, 22]
30 - "MEM".ROWID [ROWID, 10]
31 - "P". "ID" [NUMBER, 22], "P". "BOP_ID" [NUMBER, 22], "P". "NSTAT" [NUMBER, 22]
32 - "P".ROWID [ROWID, 10], "P". "ID" [NUMBER, 22]
33 - "S". "ID" [NUMBER, 22], "S". "NORD" [NUMBER, 22], "S". "LONGNAME" [VARCHAR2,1000],
"S". "FINISHFL" [CHARACTER, 1], "S". "EDITFL" [CHARACTER, 1], "S". "COLOR" [NUMBER, 22]
34 - "S".ROWID [ROWID, 10], "S". "ID" [NUMBER, 22], "S". "NORD" [NUMBER, 22]
35 - "L". "LONGNAME" [VARCHAR2,1000]
36 - "L".ROWID [ROWID, 10]
37 - "AD". "TT_ID" [NUMBER, 22]
38 - "AD".ROWID [ROWID, 10]
39 - "CH".ROWID [ROWID, 10]
40 - "CH". "TAXCODE" [VARCHAR2,30]
41 - "ID" [NUMBER, 22]
42 - "ID_HI" [NUMBER, 22], "ID" [NUMBER, 22], PRIOR NULL [22], LEVEL [4]
43 - "C_DEP".ROWID [ROWID, 10], "ID" [NUMBER, 22], "ID_HI" [NUMBER, 22]
44 - "C_DEP".ROWID [ROWID, 10], "ID" [NUMBER, 22]
45 - "connect $ _ by $ _ pump $ _ 014". "prior id" [NUMBER, 22],
"C_DEP".ROWID [ROWID, 10], "ID" [NUMBER, 22], "ID_HI" [NUMBER, 22]
46 - "connect $ _ by $ _ pump $ _ 014". "prior id" [NUMBER, 22]
47 - "C_DEP".ROWID [ROWID, 10], "ID" [NUMBER, 22], "ID_HI" [NUMBER, 22]
48 - "C_DEP".ROWID [ROWID, 10], "ID_HI" [NUMBER, 22]
Note
-----
- rule based optimizer used (consider using cbo)
- Warning: basic plan statistics not available. These are only collected when:
* hint ' gather_plan_statistics' is used for the statement or
* parameter ' statistics_level ' is set to ' ALL ', at session or system level

7

Re: Request optimization

gister;
Without UNION' long fulfills?

8

Re: Request optimization

Faster but not considerably

9

Re: Request optimization

I want to note still that in select list it is full of calls the user of functions. How fast they work and that in them - too a riddle.
Well and concept quickly/slowly -
IMHO

env wrote:

Without the information about estimated/actual rows, etc. - the plan looks not bad. Until it will be clarified that in it there are any wild readings of millions lines of an index billions times.

+++

10

Re: Request optimization

gister wrote:

Good afternoon! Help to optimize, please, request

It is optimal.

11

Re: Request optimization

...
and t.arcfl = ' 0 '-? number (22)
and rownum=1
/* and m. CODE = ' 2808 '*/)
...
from
...
, T_ORD t
...
where
...
and t.arcfl = ' 0 '-?
and P_OperDay between d. FROMDATE and nvl (d. TODATE, p_maxdate)
and t. ARCFL = 0-?
...

12

Re: Request optimization

Dear Stanislav should learn to ask questions at a forum, and also considering it 2 summer experience over this system could and already the nobility who is engaged in optimization of heavy requests in this system. For example, Maxim of the Item

13

Re: Request optimization

111111222;
Or Ivan .
smile

14

Re: Request optimization

;
It not so is bad, as it seems