Home » RDBMS Server » Performance Tuning » Tuning (Oracle, 10g, Windows Server 2003)
| Tuning [message #453979] |
Mon, 03 May 2010 05:21  |
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
|
Senior Member |
|
|
Hi,
Please help or give tips to tune following query
SQL_FULLTEXT
--------------------------------------------------------------------------------
SELECT CLIENTID,CAS,CUSTOMER_NAME,UCN,OG,CLIENT_NXT_RVW_DT,CLIENT_LST_RVW_DT,CRE
DIT_FLAG,
FAMILY_NAME,PRIMARY_CRED_EXEC,SUPERVISORY_CRED_EXEC,CORP_BANKER,ADMINISTRATOR,FA
MILY_NXT_RVW_DT,D_COMMENTS,D_REVIEWSTATUS,D_COMMITTEDDEAL,
D_CORPBANKER,D_RESPONSIBLEOFCR,D_MAINT_LEVEL,D_CREDITRISKWATCH,D_RULES,D_REVIEWT
EMPLATE,D_GROUPNAME,D_GROUPNO,D_ASSOCAITE,D_ANALYST,
D_PBCCFIRMTYPE,D_PBCCOG,D_PBCMCAPITALNNOP,D_PBCMCAPITALNOP,D_PBCMMARGINNOP,D_PBC
FMARGIN,D_PBCFCAPITALDATE,D_PBCFCAPITAL,
D_PBATTRADETYPE,D_PBATBULLION,D_PBATCURRENCIES,D_PBNETNETBULLION,D_PBNETNETFX,D_
PBNETBULLION,D_PBNETFX,D_PRIMEBROKERAGE,D_SELECTUCN,
D_RVWTMPTOTHER,row_number
From (Select CLIENTID,CAS,CUSTOMER_NAME,UCN,OG,CLIENT_NXT_RVW_DT,CLIENT_LST_RVW_
DT,CREDIT_FLAG,
FAMILY_NAME,PRIMARY_CRED_EXEC,SUPERVISORY_CRED_EXEC,CASE WHEN CORP_BANKER IS NUL
L THEN NULL WHEN CORP_BANKER IS NOT NULL THEN CASE WHEN D_RESPONSIBLEOFCR IS NOT
NULL THEN D_RESPONSIBLEOFCR ELSE CORP_BANKER END ELSE CORP_BANKER END CORP_BANK
ER,
ADMINISTRATOR,FAMILY_NXT_RVW_DT,D_COMMENTS,D_REVIEWSTATUS,D_COMMITTEDDEAL,D_CORP
BANKER,D_RESPONSIBLEOFCR,D_MAINT_LEVEL,
D_CREDITRISKWATCH,D_RULES,D_REVIEWTEMPLATE,D_GROUPNAME,D_GROUPNO,D_ASSOCAITE,D_A
NALYST,D_PBCCFIRMTYPE,D_PBCCOG,D_PBCMCAPITALNNOP,
D_PBCMCAPITALNOP,D_PBCMMARGINNOP,D_PBCFMARGIN,D_PBCFCAPITALDATE,D_PBCFCAPITAL,D_
PBATTRADETYPE,D_PBATBULLION,D_PBATCURRENCIES,
D_PBNETNETBULLION,D_PBNETNETFX,D_PBNETBULLION,D_PBNETFX,D_PRIMEBROKERAGE,D_SELEC
TUCN,D_RVWTMPTOTHER,Rownum row_number
From (SELECT /*+ FIRST_ROWS(10)*/
C.OID AS CLIENTID,
C.CUSTOMER_ID AS CAS,
N.NAME AS CUSTOMER_NAME,
U.CLIENT_UCN AS UCN,
G.OBG AS OG,
To_Char(G.NXT_RVW_DT,'DD/MM/YYYY') AS CLIENT_NXT_RVW_DT,
To_Char(G.LST_RVW_DT,'DD/MM/YYYY') AS CLIENT_LST_RVW_DT,
R.CCF_CD AS CREDIT_FLAG,
(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL
WHEN C.OID = P.ULT_PAR_OID THEN N.NAME
WHEN C.OID <> P.ULT_PAR_OID THEN (SELECT NAME
FROM REFDBO.V_CIT_CLIENT_NAME
WHERE CLT_OID = P.ULT_PAR_OID
AND SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT) END)
FAMILY_NAME,
(SELECT Max(Decode(CODE_VALUE,'C1',Substr(ID_EMAIL_INTERNAL,1,Instr(
ID_EMAIL_INTERNAL,'/')-1)))
FROM REFDBO.V_TEAM_PATH_V3
WHERE CODE_VALUE IN ( 'C1','C3'
,'CORP BKER','CRD ADM' )
AND SYSDATE BETWEEN CT_ASOF_D
T AND CT_UNTIL_DT
AND SYSDATE BETWEEN CV_ASOF_D
T AND CV_UNTIL_DT
AND SYSDATE BETWEEN HR_ASOF_D
T AND HR_UNTIL_DT
AND SYSDATE BETWEEN TM_ASOF_D
T AND TM_UNTIL_DT
AND SYSDATE BETWEEN TMB_ASOF_
DT AND TMB_UNTIL_DT
AND CLT_OID=C.OID
) PRIMARY_CRED_EXEC,
(SELECT Max(Decode(CODE_VALUE,'C3',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMA
IL_INTERNAL,'/')-1)))
FROM REFDBO.V_TEAM_PATH_V3
WHERE CODE_VALUE IN ( 'C1','C3'
,'CORP BKER','CRD ADM' )
AND SYSDATE BETWEEN CT_ASOF_D
T AND CT_UNTIL_DT
AND SYSDATE BETWEEN CV_ASOF_D
T AND CV_UNTIL_DT
AND SYSDATE BETWEEN HR_ASOF_D
T AND HR_UNTIL_DT
AND SYSDATE BETWEEN TM_ASOF_D
T AND TM_UNTIL_DT
AND SYSDATE BETWEEN TMB_ASOF_
DT AND TMB_UNTIL_DT
AND CLT_OID=C.OID
) SUPERVISORY_CRED_EXEC ,
(SELECT Max(Decode(CODE_VALUE,'CORP BKER',Substr(ID_EMAIL_INTERNAL,1,Instr
(ID_EMAIL_INTERNAL,'/')-1)))
FROM REFDBO.V_TEAM_PATH_V3
WHERE CODE_VALUE IN ( 'C1','C3'
,'CORP BKER','CRD ADM' )
AND SYSDATE BETWEEN CT_ASOF_D
T AND CT_UNTIL_DT
AND SYSDATE BETWEEN CV_ASOF_D
T AND CV_UNTIL_DT
AND SYSDATE BETWEEN HR_ASOF_D
T AND HR_UNTIL_DT
AND SYSDATE BETWEEN TM_ASOF_D
T AND TM_UNTIL_DT
AND SYSDATE BETWEEN TMB_ASOF_
DT AND TMB_UNTIL_DT
AND CLT_OID=C.OID
) CORP_BANKER,
(SELECT Max(Decode(CODE_VALUE,'CRD ADM',Substr(ID_EMAIL_INTERNAL,1,Instr(I
D_EMAIL_INTERNAL,'/')-1)))
FROM REFDBO.V_TEAM_PATH_V3
WHERE CODE_VALUE IN ( 'C1','C3'
,'CORP BKER','CRD ADM' )
AND SYSDATE BETWEEN CT_ASOF_D
T AND CT_UNTIL_DT
AND SYSDATE BETWEEN CV_ASOF_D
T AND CV_UNTIL_DT
AND SYSDATE BETWEEN HR_ASOF_D
T AND HR_UNTIL_DT
AND SYSDATE BETWEEN TM_ASOF_D
T AND TM_UNTIL_DT
AND SYSDATE BETWEEN TMB_ASOF_
DT AND TMB_UNTIL_DT
AND CLT_OID=C.OID
) ADMINISTRATOR,
(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL
WHEN C.OID = P.ULT_PAR_OID THEN To_Char(G.NXT_RVW_DT,'DD/MM/YYYY')
WHEN C.OID <> P.ULT_PAR_OID THEN (SELECT To_Char(NXT_RVW_DT,'DD/MM/YYYY')
FROM REFDBO.V_INT_GRADES
WHERE CLT_OID = P.ULT_PAR_OID
AND SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT) END)
FAMILY_NXT_RVW_DT,
D.COMMENTS AS D_COM
MENTS,
D.REVIEWSTATUS AS D_REVIEWSTATUS,
D.COMMITTEDDEAL AS D_COMMITTEDDEAL,
D.CORPBANKER AS D_CORPBANKER,
D.RESPONSIBLEOFCR AS D_RES
PONSIBLEOFCR,
D.MAINT_LEVEL AS D_MAINT_LEVEL,
D.CREDITRISKWATCH AS D_CREDITRISKWATCH,
D.RULES AS D_RULES,
D.REVIEWTEMPLATE AS D_REVIEWTEMPLATE,
D.GROUPNAME AS D_GROUPNAME,
D.GROUPNO AS D_GROUPNO,
D.ASSOCAITE AS D_ASSOCAITE,
D.ANALYST AS D_ANALYST,
D.PBCCFIRMTYPE AS D_PBCCFIRMTYPE,
D.PBCCOG AS D_PBCCOG,
D.PBCMCAPITALNNOP AS D_PBCMCAPITALNNOP,
D.PBCMCAPITALNOP AS D_PBCMCAPITALNOP,
D.PBCMMARGINNOP AS D_PBCMMARGINNOP,
D.PBCFMARGIN AS D_PBCFMARGIN,
D.PBCFCAPITALDATE AS D_PBCFCAPITALDATE,
D.PBCFCAPITAL AS D_PBCFCAPITAL,
D.PBATTRADETYPE AS D_PBATTRADETYPE,
D.PBATBULLION AS D_PBATBULLION,
D.PBATCURRENCIES AS D_PBATCURRENCIES,
D.PBNETNETBULLION AS D_PBNETNETBULLION,
D.PBNETNETFX AS D_PBNETNETFX,
D.PBNETBULLION AS D_PBNETBULLION,
D.PBNETFX AS D_PBNETFX,
D.PRIMEBROKERAGE AS D_PRIMEBROKERAGE,
D.SELECTUCN AS D_SELECTUCN,
D.RVWTMPTOTHER AS D_RVWTMPTOTHER
FROM REFDBO.V_CREDIT_GCR CG,
REFDBO.V_GCR_REFERENCE GR,
REFDBO.V_CIT_CLIENT_UCN U,
REFDBO.V_INT_GRADES G,
REFDBO.V_CREDIT_FLAG R,
REFDBO.V_UNIQUE_ULT_PAR_ALL P,
REFDBO.V_CLIENT C,
REFDBO.V_CIT_CLIENT_NAME N,
GINGER.CLIENT_DATA_EXTENDED D
WHERE SUBSTR(R.CCF_CD , 1 , 1 ) = 'C'
AND C.OID = N.CLT_OID
AND N.CLT_OID = U.CLIENT_OID
AND U.CLIENT_OID = G.CLT_OID
AND G.CLT_OID = P.CHILD_CLT_OID
AND CG.GCR NOT IN (
SELECT BUS_DEV_UNIT_ID
FROM REFDBO.V_BC_BDU_CAU
WHERE BUS_DEV_UNIT_ID = CG.GCR
AND CR_ADM_UNIT_NO IN ( 39 , 62 , 704 , 705 , 711 )
AND SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT )
AND R.CLT_OID = CG.CLT_OID
AND CG.CLT_OID = D.OID(+)
AND CG.GCR = GR.ID_GCR
AND CG.GCR_CD = 'PRI'
AND SYSDATE BETWEEN GR.CIT_EFF_ASOF_DT AND GR.CIT_EFF_UNTIL_DT
AND SYSDATE BETWEEN CG.CIT_EFF_ASOF_DT AND CG.CIT_EFF_UNTIL_DT
AND SYSDATE BETWEEN C.CIT_EFF_ASOF_DT AND C.CIT_EFF_UNTIL_DT
AND SYSDATE BETWEEN N.CIT_EFF_ASOF_DT AND N.CIT_EFF_UNTIL_DT
AND SYSDATE BETWEEN U.CIT_EFF_ASOF_DT AND U.CIT_EFF_UNTIL_DT
AND SYSDATE BETWEEN G.CIT_EFF_ASOF_DT AND G.CIT_EFF_UNTIL_DT
AND SYSDATE BETWEEN P.CIT_EFF_ASOF_DT AND P.CIT_EFF_UNTIL_DT
AND SYSDATE BETWEEN R.CIT_EFF_ASOF_DT AND R.CIT_EFF_UNTIL_DT
AND R.CCF_CD IN ('C','C1','C2','C3','CC','CR','CS','CY') ORDER B
Y CUSTOMER_NAME)) Where row_number Between 300*(1-1)+1 And 300*1
Explain plan is shown as follows
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 272d1a6kxvy37, child number 0
-------------------------------------
SELECT CLIENTID,CAS,CUSTOMER_NAME,UCN,OG,CLIENT_NXT_RVW_DT,CLIENT_LST_RVW_DT,CREDIT_FLAG,
FAMILY_NAME,PRIMARY_CRED_EXEC,SUPERVISORY_CRED_EXEC,CORP_BANKER,ADMINISTRATOR,FAMILY_NXT_RVW_DT,D_CO
EDDEAL, D_CORPBANKER,D_RESPONSIBLEOFCR,D_MAINT_LEVEL,D_CREDITRISKWATCH,D_RULES,D_REVIEWTEMPLATE,D_GR
_ANALYST, D_PBCCFIRMTYPE,D_PBCCOG,D_PBCMCAPITALNNOP,D_PBCMCAPITALNOP,D_PBCMMARGINNOP,D_PBCFMARGIN,D_
D_PBATTRADETYPE,D_PBATBULLION,D_PBATCURRENCIES,D_PBNETNETBULLION,D_PBNETNETFX,D_PBNETBULLION,D_PBNET
, D_RVWTMPTOTHER,row_number From (Select CLIENTID,CAS,CUSTOMER_NAME,UCN,OG,CLIENT_NXT_RVW_DT,CLIENT_
FAMILY_NAME,PRIMARY_CRED_EXEC,SUPERVISORY_CRED_EXEC,CASE WHEN CORP_BANKER IS NULL THEN NULL WHEN COR
WHEN D_RESPONSIBLEOFCR IS NOT NULL THEN D_RESPONSIBLEOFCR ELSE CORP_BANKER END ELSE CORP_BANKER END
Plan hash value: 416451301
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 62665 (100)| | | |
| 1 | PARTITION RANGE ITERATOR | | 1 | 47 | | 4 (0)| 00:00:01 | KEY | 2 |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID | CIT_CLIENT_NAME | 1 | 47 | | 4 (0)| 00:00:01
|* 3 | INDEX RANGE SCAN | CIT_CLIENT_NAME_I3 | 1 | | | 3 (0)| 00:00:01 | KEY | 2 |
| 4 | SORT AGGREGATE | | 1 | 194 | | | | | |
|* 5 | TABLE ACCESS BY LOCAL INDEX ROWID | WORKFORCE_JPMC_V3 | 1 | 76 | | 3 (0)| 00:00:
| 6 | NESTED LOOPS | | 1 | 194 | | 16 (0)| 00:00:01 | | |
| 7 | NESTED LOOPS | | 1 | 118 | | 13 (0)| 00:00:01 | | |
| 8 | NESTED LOOPS | | 1 | 90 | | 11 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS | | 1 | 53 | | 8 (0)| 00:00:01 | | |
| 10 | PARTITION RANGE ITERATOR | | 1 | 30 | | 5 (0)| 00:00:01 | KEY | 2 |
|* 11 | TABLE ACCESS BY LOCAL INDEX ROWID| CLIENT_TEAM | 1 | 30 | | 5 (0)| 00:00:01 | K
|* 12 | INDEX RANGE SCAN | CLIENT_TEAM_I3 | 2 | | | 3 (0)| 00:00:01 | KEY | 2 |
| 13 | PARTITION RANGE ITERATOR | | 1 | 23 | | 3 (0)| 00:00:01 | KEY | 2 |
|* 14 | TABLE ACCESS BY LOCAL INDEX ROWID| TEAM | 1 | 23 | | 3 (0)| 00:00:01 | KEY | 2
|* 15 | INDEX RANGE SCAN | TEAM_I1 | 1 | | | 2 (0)| 00:00:01 | KEY | 2 |
| 16 | PARTITION RANGE ITERATOR | | 1 | 37 | | 3 (0)| 00:00:01 | KEY | 2 |
|* 17 | TABLE ACCESS BY LOCAL INDEX ROWID | TEAM_MEMBER | 1 | 37 | | 3 (0)| 00:00:0
|* 18 | INDEX RANGE SCAN | TEAM_MEMBER_I3 | 1 | | | 2 (0)| 00:00:01 | KEY | 2 |
|* 19 | TABLE ACCESS BY INDEX ROWID | CODE_VALUE | 1 | 28 | | 2 (0)| 00:00:01 | |
|* 20 | INDEX RANGE SCAN | CODE_VALUE_I1 | 1 | | | 1 (0)| 00:00:01 | | |
| 21 | PARTITION RANGE ITERATOR | | 1 | | | 2 (0)| 00:00:01 | KEY | 2 |
|* 22 | INDEX RANGE SCAN | WORKFORCE_JPMC_V3_I1 | 1 | | | 2 (0)| 00:00:01 | KEY |
| 23 | SORT AGGREGATE | | 1 | 194 | | | | | |
|* 24 | TABLE ACCESS BY LOCAL INDEX ROWID | WORKFORCE_JPMC_V3 | 1 | 76 | | 3 (0)| 00:00:
| 25 | NESTED LOOPS | | 1 | 194 | | 16 (0)| 00:00:01 | | |
| 26 | NESTED LOOPS | | 1 | 118 | | 13 (0)| 00:00:01 | | |
| 27 | NESTED LOOPS | | 1 | 90 | | 11 (0)| 00:00:01 | | |
| 28 | NESTED LOOPS | | 1 | 53 | | 8 (0)| 00:00:01 | | |
| 29 | PARTITION RANGE ITERATOR | | 1 | 30 | | 5 (0)| 00:00:01 | KEY | 2 |
|* 30 | TABLE ACCESS BY LOCAL INDEX ROWID| CLIENT_TEAM | 1 | 30 | | 5 (0)| 00:00:01 | K
|* 31 | INDEX RANGE SCAN | CLIENT_TEAM_I3 | 2 | | | 3 (0)| 00:00:01 | KEY | 2 |
| 32 | PARTITION RANGE ITERATOR | | 1 | 23 | | 3 (0)| 00:00:01 | KEY | 2 |
|* 33 | TABLE ACCESS BY LOCAL INDEX ROWID| TEAM | 1 | 23 | | 3 (0)| 00:00:01 | KEY | 2
|* 34 | INDEX RANGE SCAN | TEAM_I1 | 1 | | | 2 (0)| 00:00:01 | KEY | 2 |
| 35 | PARTITION RANGE ITERATOR | | 1 | 37 | | 3 (0)| 00:00:01 | KEY | 2 |
|* 36 | TABLE ACCESS BY LOCAL INDEX ROWID | TEAM_MEMBER | 1 | 37 | | 3 (0)| 00:00:0
|* 37 | INDEX RANGE SCAN | TEAM_MEMBER_I3 | 1 | | | 2 (0)| 00:00:01 | KEY | 2 |
|* 38 | TABLE ACCESS BY INDEX ROWID | CODE_VALUE | 1 | 28 | | 2 (0)| 00:00:01 | |
|* 39 | INDEX RANGE SCAN | CODE_VALUE_I1 | 1 | | | 1 (0)| 00:00:01 | | |
| 40 | PARTITION RANGE ITERATOR | | 1 | | | 2 (0)| 00:00:01 | KEY | 2 |
|* 41 | INDEX RANGE SCAN | WORKFORCE_JPMC_V3_I1 | 1 | | | 2 (0)| 00:00:01 | KEY |
| 42 | SORT AGGREGATE | | 1 | 194 | | | | | |
|* 43 | TABLE ACCESS BY LOCAL INDEX ROWID | WORKFORCE_JPMC_V3 | 1 | 76 | | 3 (0)| 00:00:
| 44 | NESTED LOOPS | | 1 | 194 | | 16 (0)| 00:00:01 | | |
| 45 | NESTED LOOPS | | 1 | 118 | | 13 (0)| 00:00:01 | | |
| 46 | NESTED LOOPS | | 1 | 90 | | 11 (0)| 00:00:01 | | |
| 47 | NESTED LOOPS | | 1 | 53 | | 8 (0)| 00:00:01 | | |
| 48 | PARTITION RANGE ITERATOR | | 1 | 30 | | 5 (0)| 00:00:01 | KEY | 2 |
|* 49 | TABLE ACCESS BY LOCAL INDEX ROWID| CLIENT_TEAM | 1 | 30 | | 5 (0)| 00:00:01 | K
|* 50 | INDEX RANGE SCAN | CLIENT_TEAM_I3 | 2 | | | 3 (0)| 00:00:01 | KEY | 2 |
| 51 | PARTITION RANGE ITERATOR | | 1 | 23 | | 3 (0)| 00:00:01 | KEY | 2 |
|* 52 | TABLE ACCESS BY LOCAL INDEX ROWID| TEAM | 1 | 23 | | 3 (0)| 00:00:01 | KEY | 2
|* 53 | INDEX RANGE SCAN | TEAM_I1 | 1 | | | 2 (0)| 00:00:01 | KEY | 2 |
| 54 | PARTITION RANGE ITERATOR | | 1 | 37 | | 3 (0)| 00:00:01 | KEY | 2 |
|* 55 | TABLE ACCESS BY LOCAL INDEX ROWID | TEAM_MEMBER | 1 | 37 | | 3 (0)| 00:00:0
|* 56 | INDEX RANGE SCAN | TEAM_MEMBER_I3 | 1 | | | 2 (0)| 00:00:01 | KEY | 2 |
|* 57 | TABLE ACCESS BY INDEX ROWID | CODE_VALUE | 1 | 28 | | 2 (0)| 00:00:01 | |
|* 58 | INDEX RANGE SCAN | CODE_VALUE_I1 | 1 | | | 1 (0)| 00:00:01 | | |
| 59 | PARTITION RANGE ITERATOR | | 1 | | | 2 (0)| 00:00:01 | KEY | 2 |
|* 60 | INDEX RANGE SCAN | WORKFORCE_JPMC_V3_I1 | 1 | | | 2 (0)| 00:00:01 | KEY |
| 61 | SORT AGGREGATE | | 1 | 194 | | | | | |
|* 62 | TABLE ACCESS BY LOCAL INDEX ROWID | WORKFORCE_JPMC_V3 | 1 | 76 | | 3 (0)| 00:00:
| 63 | NESTED LOOPS | | 1 | 194 | | 16 (0)| 00:00:01 | | |
| 64 | NESTED LOOPS | | 1 | 118 | | 13 (0)| 00:00:01 | | |
| 65 | NESTED LOOPS | | 1 | 90 | | 11 (0)| 00:00:01 | | |
| 66 | NESTED LOOPS | | 1 | 53 | | 8 (0)| 00:00:01 | | |
| 67 | PARTITION RANGE ITERATOR | | 1 | 30 | | 5 (0)| 00:00:01 | KEY | 2 |
|* 68 | TABLE ACCESS BY LOCAL INDEX ROWID| CLIENT_TEAM | 1 | 30 | | 5 (0)| 00:00:01 | K
|* 69 | INDEX RANGE SCAN | CLIENT_TEAM_I3 | 2 | | | 3 (0)| 00:00:01 | KEY | 2 |
| 70 | PARTITION RANGE ITERATOR | | 1 | 23 | | 3 (0)| 00:00:01 | KEY | 2 |
|* 71 | TABLE ACCESS BY LOCAL INDEX ROWID| TEAM | 1 | 23 | | 3 (0)| 00:00:01 | KEY | 2
|* 72 | INDEX RANGE SCAN | TEAM_I1 | 1 | | | 2 (0)| 00:00:01 | KEY | 2 |
| 73 | PARTITION RANGE ITERATOR | | 1 | 37 | | 3 (0)| 00:00:01 | KEY | 2 |
|* 74 | TABLE ACCESS BY LOCAL INDEX ROWID | TEAM_MEMBER | 1 | 37 | | 3 (0)| 00:00:0
|* 75 | INDEX RANGE SCAN | TEAM_MEMBER_I3 | 1 | | | 2 (0)| 00:00:01 | KEY | 2 |
|* 76 | TABLE ACCESS BY INDEX ROWID | CODE_VALUE | 1 | 28 | | 2 (0)| 00:00:01 | |
|* 77 | INDEX RANGE SCAN | CODE_VALUE_I1 | 1 | | | 1 (0)| 00:00:01 | | |
| 78 | PARTITION RANGE ITERATOR | | 1 | | | 2 (0)| 00:00:01 | KEY | 2 |
|* 79 | INDEX RANGE SCAN | WORKFORCE_JPMC_V3_I1 | 1 | | | 2 (0)| 00:00:01 | KEY |
|* 80 | TABLE ACCESS BY INDEX ROWID | INT_GRADES | 1 | 29 | | 5 (0)| 00:00:01 | | |
|* 81 | INDEX RANGE SCAN | INT_GRADES_PK | 2 | | | 3 (0)| 00:00:01 | | |
|* 82 | VIEW | | 294K| 898M| | 62665 (12)| 00:04:36 | | |
| 83 | COUNT | | | | | | | | |
| 84 | VIEW | | 294K| 873M| | 62665 (12)| 00:04:36 | | |
| 85 | SORT ORDER BY | | 294K| 97M| 208M| 62665 (12)| 00:04:36 | | |
|* 86 | HASH JOIN | | 294K| 97M| 91M| 44677 (17)| 00:03:17 | | |
|* 87 | HASH JOIN | | 291K| 87M| 2160K| 38372 (16)| 00:02:49 | | |
| 88 | PARTITION RANGE ITERATOR | | 51431 | 1556K| | 542 (26)| 00:00:03 | KEY
|* 89 | TABLE ACCESS FULL | CIT_RPT_ULT_ALL_CREDIT | 51431 | 1556K| | 542 (26)| 00:00:03 |
|* 90 | HASH JOIN | | 2878K| 779M| 41M| 29837 (20)| 00:02:12 | | |
|* 91 | TABLE ACCESS FULL | INT_GRADES | 915K| 31M| | 828 (31)| 00:00:04 | | |
|* 92 | HASH JOIN | | 2321K| 549M| 45M| 22906 (23)| 00:01:41 | | |
| 93 | PARTITION LIST SINGLE | | 832K| 35M| | 912 (29)| 00:00:05 | KEY | KEY
|* 94 | TABLE ACCESS FULL | CIT_CLIENT_ALIAS | 832K| 35M| | 912 (29)| 00:00:05 | 1
| 95 | NESTED LOOPS | | 2062K| 399M| | 17355 (28)| 00:01:17 | | |
|* 96 | HASH JOIN RIGHT ANTI | | 17 | 2652 | | 830 (23)| 00:00:04 | | |
|* 97 | TABLE ACCESS FULL | BC_BDU_CAU | 44 | 1276 | | 11 (19)| 00:00:01 | |
|* 98 | HASH JOIN | | 762 | 96774 | | 818 (23)| 00:00:04 | | |
| 99 | NESTED LOOPS OUTER | | 539 | 54978 | | 808 (23)| 00:00:04 | | |
|*100 | HASH JOIN | | 530 | 33920 | | 792 (23)| 00:00:04 | | |
| 101 | PARTITION RANGE ITERATOR | | 529 | 21160 | | 570 (19)| 00:00:03 | KEY |
|*102 | TABLE ACCESS FULL | CREDIT_GCR | 529 | 21160 | | 570 (19)| 00:00:03 | K
|*103 | TABLE ACCESS FULL | CIT_CREDIT_FLAG | 4312 | 101K| | 221 (32)| 00:00:01 |
| 104 | TABLE ACCESS BY INDEX ROWID | CLIENT_DATA_EXTENDED | 1 | 38 | | 1 (0)| 00:00:
|*105 | INDEX UNIQUE SCAN | SYS_C002611930 | 1 | | | 0 (0)| | | |
|*106 | TABLE ACCESS FULL | GES_GCR_REFERENCE | 15717 | 383K| | 10 (30)| 00:00:01 |
| 107 | PARTITION RANGE ITERATOR | | 121K| 5588K| | 972 (29)| 00:00:05 | KEY | 2 |
|*108 | TABLE ACCESS FULL | CIT_CLIENT_NAME | 121K| 5588K| | 972 (29)| 00:00:05 |
| 109 | PARTITION RANGE ITERATOR | | 2435K| 78M| | 4287 (27)| 00:00:19 | KEY |
|*110 | TABLE ACCESS FULL | CLIENT | 2435K| 78M| | 4287 (27)| 00:00:19 | KEY |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CIT_EFF_ASOF_DT"<=SYSDATE@!)
3 - access("CLT_OID"=:B1 AND "CIT_EFF_UNTIL_DT">=SYSDATE@! AND "CIT_EFF_UNTIL_DT" IS NOT NULL)
5 - filter("CIT_EFF_ASOF_DT"<=SYSDATE@!)
11 - filter(("CT"."CIT_EFF_ASOF_DT"<=SYSDATE@! AND "CT"."CIT_EFF_UNTIL_DT">=SYSDATE@!))
12 - access("CT"."CLT_OID"=:B1)
14 - filter("TM"."CIT_EFF_ASOF_DT"<=SYSDATE@!)
15 - access("CT"."TEM_OID"="TM"."OID" AND "TM"."CIT_EFF_UNTIL_DT">=SYSDATE@! AND "TM"."CIT_EFF_UNT
17 - filter((INTERNAL_FUNCTION("TMB"."TMR_CD") AND "TMB"."CIT_EFF_ASOF_DT"<=SYSDATE@! AND "TMB"."E
"TMB"."CIT_EFF_UNTIL_DT">=SYSDATE@!))
18 - access("TM"."OID"="TMB"."TEM_OID")
19 - filter("CV"."CIT_EFF_UNTIL_DT">=SYSDATE@!)
20 - access("CV"."CODE_VALUE"="TMB"."TMR_CD" AND "CV"."CT"='TMR' AND "CV"."CIT_EFF_ASOF_DT"<=SYSDA
filter(("CV"."CODE_VALUE"='C1' OR "CV"."CODE_VALUE"='C3' OR "CV"."CODE_VALUE"='CORP BKER' OR
22 - access("TMB"."EMPL_ID"="ID_STANDARD" AND "CIT_EFF_UNTIL_DT">=SYSDATE@! AND "CIT_EFF_UNTIL_DT"
24 - filter("CIT_EFF_ASOF_DT"<=SYSDATE@!)
30 - filter(("CT"."CIT_EFF_ASOF_DT"<=SYSDATE@! AND "CT"."CIT_EFF_UNTIL_DT">=SYSDATE@!))
31 - access("CT"."CLT_OID"=:B1)
33 - filter("TM"."CIT_EFF_ASOF_DT"<=SYSDATE@!)
34 - access("CT"."TEM_OID"="TM"."OID" AND "TM"."CIT_EFF_UNTIL_DT">=SYSDATE@! AND "TM"."CIT_EFF_UNT
36 - filter((INTERNAL_FUNCTION("TMB"."TMR_CD") AND "TMB"."CIT_EFF_ASOF_DT"<=SYSDATE@! AND "TMB"."E
"TMB"."CIT_EFF_UNTIL_DT">=SYSDATE@!))
37 - access("TM"."OID"="TMB"."TEM_OID")
38 - filter("CV"."CIT_EFF_UNTIL_DT">=SYSDATE@!)
39 - access("CV"."CODE_VALUE"="TMB"."TMR_CD" AND "CV"."CT"='TMR' AND "CV"."CIT_EFF_ASOF_DT"<=SYSDA
filter(("CV"."CODE_VALUE"='C1' OR "CV"."CODE_VALUE"='C3' OR "CV"."CODE_VALUE"='CORP BKER' OR
41 - access("TMB"."EMPL_ID"="ID_STANDARD" AND "CIT_EFF_UNTIL_DT">=SYSDATE@! AND "CIT_EFF_UNTIL_DT"
43 - filter("CIT_EFF_ASOF_DT"<=SYSDATE@!)
49 - filter(("CT"."CIT_EFF_ASOF_DT"<=SYSDATE@! AND "CT"."CIT_EFF_UNTIL_DT">=SYSDATE@!))
50 - access("CT"."CLT_OID"=:B1)
52 - filter("TM"."CIT_EFF_ASOF_DT"<=SYSDATE@!)
53 - access("CT"."TEM_OID"="TM"."OID" AND "TM"."CIT_EFF_UNTIL_DT">=SYSDATE@! AND "TM"."CIT_EFF_UNT
55 - filter((INTERNAL_FUNCTION("TMB"."TMR_CD") AND "TMB"."CIT_EFF_ASOF_DT"<=SYSDATE@! AND "TMB"."E
"TMB"."CIT_EFF_UNTIL_DT">=SYSDATE@!))
56 - access("TM"."OID"="TMB"."TEM_OID")
57 - filter("CV"."CIT_EFF_UNTIL_DT">=SYSDATE@!)
58 - access("CV"."CODE_VALUE"="TMB"."TMR_CD" AND "CV"."CT"='TMR' AND "CV"."CIT_EFF_ASOF_DT"<=SYSDA
filter(("CV"."CODE_VALUE"='C1' OR "CV"."CODE_VALUE"='C3' OR "CV"."CODE_VALUE"='CORP BKER' OR
60 - access("TMB"."EMPL_ID"="ID_STANDARD" AND "CIT_EFF_UNTIL_DT">=SYSDATE@! AND "CIT_EFF_UNTIL_DT"
62 - filter("CIT_EFF_ASOF_DT"<=SYSDATE@!)
68 - filter(("CT"."CIT_EFF_ASOF_DT"<=SYSDATE@! AND "CT"."CIT_EFF_UNTIL_DT">=SYSDATE@!))
69 - access("CT"."CLT_OID"=:B1)
71 - filter("TM"."CIT_EFF_ASOF_DT"<=SYSDATE@!)
72 - access("CT"."TEM_OID"="TM"."OID" AND "TM"."CIT_EFF_UNTIL_DT">=SYSDATE@! AND "TM"."CIT_EFF_UNT
74 - filter((INTERNAL_FUNCTION("TMB"."TMR_CD") AND "TMB"."CIT_EFF_ASOF_DT"<=SYSDATE@! AND "TMB"."E
"TMB"."CIT_EFF_UNTIL_DT">=SYSDATE@!))
75 - access("TM"."OID"="TMB"."TEM_OID")
76 - filter("CV"."CIT_EFF_UNTIL_DT">=SYSDATE@!)
77 - access("CV"."CODE_VALUE"="TMB"."TMR_CD" AND "CV"."CT"='TMR' AND "CV"."CIT_EFF_ASOF_DT"<=SYSDA
filter(("CV"."CODE_VALUE"='C1' OR "CV"."CODE_VALUE"='C3' OR "CV"."CODE_VALUE"='CORP BKER' OR
79 - access("TMB"."EMPL_ID"="ID_STANDARD" AND "CIT_EFF_UNTIL_DT">=SYSDATE@! AND "CIT_EFF_UNTIL_DT"
80 - filter("CIT_EFF_UNTIL_DT">=SYSDATE@!)
81 - access("CLT_OID"=:B1 AND "CIT_EFF_ASOF_DT"<=SYSDATE@!)
82 - filter(("ROW_NUMBER">=1 AND "ROW_NUMBER"<=300))
86 - access("OID"="CLT_OID")
87 - access("CLT_OID"="CHILD_CLT_OID")
89 - filter(("CIT_EFF_ASOF_DT"<=SYSDATE@! AND "CIT_EFF_UNTIL_DT">=SYSDATE@!))
90 - access("CLT_OID"="CLT_OID")
91 - filter(("CIT_EFF_UNTIL_DT">=SYSDATE@! AND "CIT_EFF_ASOF_DT"<=SYSDATE@!))
92 - access("CLT_OID"="CLT_OID")
94 - filter((("CAS_CD"<>'CLO' OR "CAS_CD" IS NULL) AND "CIT_EFF_UNTIL_DT">=SYSDATE@! AND "CIT_EFF_
96 - access("BUS_DEV_UNIT_ID"="GCR")
97 - filter((INTERNAL_FUNCTION("CR_ADM_UNIT_NO") AND "CIT_EFF_UNTIL_DT">=SYSDATE@! AND "CIT_EFF_AS
98 - access("GCR"="ID_GCR")
100 - access("CLT_OID"="CLT_OID")
102 - filter(("GCR_CD"='PRI' AND "CIT_EFF_ASOF_DT"<=SYSDATE@! AND "CIT_EFF_UNTIL_DT">=SYSDATE@!))
103 - filter((SUBSTR("CCF_CD",1,1)='C' AND "CIT_EFF_UNTIL_DT">=SYSDATE@! AND INTERNAL_FUNCTION("CCF
"CIT_EFF_ASOF_DT"<=SYSDATE@!))
105 - access("CLT_OID"="D"."OID")
106 - filter(("CIT_EFF_ASOF_DT"<=SYSDATE@! AND "CIT_EFF_UNTIL_DT">=SYSDATE@!))
108 - filter(("CIT_EFF_ASOF_DT"<=SYSDATE@! AND "CIT_EFF_UNTIL_DT">=SYSDATE@!))
110 - filter(("CIT_EFF_UNTIL_DT">=SYSDATE@! AND "CIT_EFF_ASOF_DT"<=SYSDATE@!))
204 rows selected.
Regards,
Ritesh
|
|
|
|
|
|
| Re: Tuning [message #453997 is a reply to message #453985] |
Mon, 03 May 2010 06:42  |
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
|
Senior Member |
|
|
Removed empty lines in query
SELECT CLIENTID,CAS,CUSTOMER_NAME,UCN,OG,CLIENT_NXT_RVW_DT,CLIENT_LST_RVW_DT,CREDIT_FLAG,
FAMILY_NAME,PRIMARY_CRED_EXEC,SUPERVISORY_CRED_EXEC,CORP_BANKER,ADMINISTRATOR,FAMILY_NXT_RVW_DT,D_COMMENTS,
D_REVIEWSTATUS,D_COMMITTEDDEAL,D_CORPBANKER,D_RESPONSIBLEOFCR,D_MAINT_LEVEL,D_CREDITRISKWATCH,D_RULES,
D_REVIEWTEMPLATE,D_GROUPNAME,D_GROUPNO,D_ASSOCAITE,D_ANALYST,D_PBCCFIRMTYPE,D_PBCCOG,D_PBCMCAPITALNNOP,
D_PBCMCAPITALNOP,D_PBCMMARGINNOP,D_PBCFMARGIN,D_PBCFCAPITALDATE,D_PBCFCAPITAL,D_PBATTRADETYPE,D_PBATBULLION,
D_PBATCURRENCIES,D_PBNETNETBULLION,D_PBNETNETFX,D_PBNETBULLION,D_PBNETFX,D_PRIMEBROKERAGE,D_SELECTUCN,
D_RVWTMPTOTHER,row_number
From (Select CLIENTID,CAS,CUSTOMER_NAME,UCN,OG,CLIENT_NXT_RVW_DT,CLIENT_LST_RVW_DT,CREDIT_FLAG,
FAMILY_NAME,PRIMARY_CRED_EXEC,SUPERVISORY_CRED_EXEC,CASE WHEN CORP_BANKER IS NULL THEN NULL WHEN
CORP_BANKER IS NOT NULL THEN CASE WHEN D_RESPONSIBLEOFCR IS NOT NULL THEN D_RESPONSIBLEOFCR ELSE CORP_BANKER END
ELSE CORP_BANKER END CORP_BANKER,
ADMINISTRATOR,FAMILY_NXT_RVW_DT,D_COMMENTS,D_REVIEWSTATUS,D_COMMITTEDDEAL,D_CORPBANKER,
D_RESPONSIBLEOFCR,D_MAINT_LEVEL,D_CREDITRISKWATCH,D_RULES,D_REVIEWTEMPLATE,D_GROUPNAME,D_GROUPNO,D_ASSOCAITE,
D_ANALYST,D_PBCCFIRMTYPE,D_PBCCOG,D_PBCMCAPITALNNOP,D_PBCMCAPITALNOP,D_PBCMMARGINNOP,D_PBCFMARGIN,D_PBCFCAPITALDATE,
D_PBCFCAPITAL,D_PBATTRADETYPE,D_PBATBULLION,D_PBATCURRENCIES,D_PBNETNETBULLION,D_PBNETNETFX,D_PBNETBULLION,
D_PBNETFX,D_PRIMEBROKERAGE,D_SELECTUCN,D_RVWTMPTOTHER,Rownum row_number
From (SELECT /*+ FIRST_ROWS(10)*/
C.OID AS CLIENTID,
C.CUSTOMER_ID AS CAS,
N.NAME AS CUSTOMER_NAME,
U.CLIENT_UCN AS UCN,
G.OBG AS OG,
To_Char(G.NXT_RVW_DT,'DD/MM/YYYY') AS CLIENT_NXT_RVW_DT,
To_Char(G.LST_RVW_DT,'DD/MM/YYYY') AS CLIENT_LST_RVW_DT,
R.CCF_CD AS CREDIT_FLAG,
(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL
WHEN C.OID = P.ULT_PAR_OID THEN N.NAME
WHEN C.OID <> P.ULT_PAR_OID THEN (SELECT NAME
FROM REFDBO.V_CIT_CLIENT_NAME
WHERE CLT_OID = P.ULT_PAR_OID
AND SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT) END) FAMILY_NAME,
(SELECT Max(Decode(CODE_VALUE,'C1',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1)))
FROM REFDBO.V_TEAM_PATH_V3
WHERE CODE_VALUE IN ( 'C1','C3','CORP BKER','CRD ADM' )
AND SYSDATE BETWEEN CT_ASOF_DT AND CT_UNTIL_DT
AND SYSDATE BETWEEN CV_ASOF_DT AND CV_UNTIL_DT
AND SYSDATE BETWEEN HR_ASOF_DT AND HR_UNTIL_DT
AND SYSDATE BETWEEN TM_ASOF_DT AND TM_UNTIL_DT
AND SYSDATE BETWEEN TMB_ASOF_DT AND TMB_UNTIL_DT
AND CLT_OID=C.OID) PRIMARY_CRED_EXEC,
(SELECT Max(Decode(CODE_VALUE,'C3',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1)))
FROM REFDBO.V_TEAM_PATH_V3
WHERE CODE_VALUE IN ( 'C1','C3','CORP BKER','CRD ADM' )
AND SYSDATE BETWEEN CT_ASOF_DT AND CT_UNTIL_DT
AND SYSDATE BETWEEN CV_ASOF_DT AND CV_UNTIL_DT
AND SYSDATE BETWEEN HR_ASOF_DT AND HR_UNTIL_DT
AND SYSDATE BETWEEN TM_ASOF_DT AND TM_UNTIL_DT
AND SYSDATE BETWEEN TMB_ASOF_DT AND TMB_UNTIL_DT
AND CLT_OID=C.OID
) SUPERVISORY_CRED_EXEC ,
(SELECT Max(Decode(CODE_VALUE,'CORP BKER',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1)))
FROM REFDBO.V_TEAM_PATH_V3
WHERE CODE_VALUE IN ( 'C1','C3','CORP BKER','CRD ADM' )
AND SYSDATE BETWEEN CT_ASOF_DT AND CT_UNTIL_DT
AND SYSDATE BETWEEN CV_ASOF_DT AND CV_UNTIL_DT
AND SYSDATE BETWEEN HR_ASOF_DT AND HR_UNTIL_DT
AND SYSDATE BETWEEN TM_ASOF_DT AND TM_UNTIL_DT
AND SYSDATE BETWEEN TMB_ASOF_DT AND TMB_UNTIL_DT
AND CLT_OID=C.OID
) CORP_BANKER,
(SELECT Max(Decode(CODE_VALUE,'CRD ADM',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1)))
FROM REFDBO.V_TEAM_PATH_V3
WHERE CODE_VALUE IN ( 'C1','C3','CORP BKER','CRD ADM' )
AND SYSDATE BETWEEN CT_ASOF_DT AND CT_UNTIL_DT
AND SYSDATE BETWEEN CV_ASOF_DT AND CV_UNTIL_DT
AND SYSDATE BETWEEN HR_ASOF_DT AND HR_UNTIL_DT
AND SYSDATE BETWEEN TM_ASOF_DT AND TM_UNTIL_DT
AND SYSDATE BETWEEN TMB_ASOF_DT AND TMB_UNTIL_DT
AND CLT_OID=C.OID
) ADMINISTRATOR,
(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL
WHEN C.OID = P.ULT_PAR_OID THEN To_Char(G.NXT_RVW_DT,'DD/MM/YYYY')
WHEN C.OID <> P.ULT_PAR_OID THEN (SELECT To_Char(NXT_RVW_DT,'DD/MM/YYYY')
FROM REFDBO.V_INT_GRADES
WHERE CLT_OID = P.ULT_PAR_OID
AND SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT) END) FAMILY_NXT_RVW_DT,
D.COMMENTS AS D_COMMENTS,
D.REVIEWSTATUS AS D_REVIEWSTATUS,
D.COMMITTEDDEAL AS D_COMMITTEDDEAL,
D.CORPBANKER AS D_CORPBANKER,
D.RESPONSIBLEOFCR AS D_RESPONSIBLEOFCR,
D.MAINT_LEVEL AS D_MAINT_LEVEL,
D.CREDITRISKWATCH AS D_CREDITRISKWATCH,
D.RULES AS D_RULES,
D.REVIEWTEMPLATE AS D_REVIEWTEMPLATE,
D.GROUPNAME AS D_GROUPNAME,
D.GROUPNO AS D_GROUPNO,
D.ASSOCAITE AS D_ASSOCAITE,
D.ANALYST AS D_ANALYST,
D.PBCCFIRMTYPE AS D_PBCCFIRMTYPE,
D.PBCCOG AS D_PBCCOG,
D.PBCMCAPITALNNOP AS D_PBCMCAPITALNNOP,
D.PBCMCAPITALNOP AS D_PBCMCAPITALNOP,
D.PBCMMARGINNOP AS D_PBCMMARGINNOP,
D.PBCFMARGIN AS D_PBCFMARGIN,
D.PBCFCAPITALDATE AS D_PBCFCAPITALDATE,
D.PBCFCAPITAL AS D_PBCFCAPITAL,
D.PBATTRADETYPE AS D_PBATTRADETYPE,
D.PBATBULLION AS D_PBATBULLION,
D.PBATCURRENCIES AS D_PBATCURRENCIES,
D.PBNETNETBULLION AS D_PBNETNETBULLION,
D.PBNETNETFX AS D_PBNETNETFX,
D.PBNETBULLION AS D_PBNETBULLION,
D.PBNETFX AS D_PBNETFX,
D.PRIMEBROKERAGE AS D_PRIMEBROKERAGE,
D.SELECTUCN AS D_SELECTUCN,
D.RVWTMPTOTHER AS D_RVWTMPTOTHER
FROM REFDBO.V_CREDIT_GCR CG,
REFDBO.V_GCR_REFERENCE GR,
REFDBO.V_CIT_CLIENT_UCN U,
REFDBO.V_INT_GRADES G,
REFDBO.V_CREDIT_FLAG R,
REFDBO.V_UNIQUE_ULT_PAR_ALL P,
REFDBO.V_CLIENT C,
REFDBO.V_CIT_CLIENT_NAME N,
GINGER.CLIENT_DATA_EXTENDED D
WHERE SUBSTR(R.CCF_CD , 1 , 1 ) = 'C'
AND C.OID = N.CLT_OID
AND N.CLT_OID = U.CLIENT_OID
AND U.CLIENT_OID = G.CLT_OID
AND G.CLT_OID = P.CHILD_CLT_OID
AND CG.GCR NOT IN (
SELECT BUS_DEV_UNIT_ID
FROM REFDBO.V_BC_BDU_CAU
WHERE BUS_DEV_UNIT_ID = CG.GCR
AND CR_ADM_UNIT_NO IN ( 39 , 62 , 704 , 705 , 711 )
AND SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT )
AND R.CLT_OID = CG.CLT_OID
AND CG.CLT_OID = D.OID(+)
AND CG.GCR = GR.ID_GCR
AND CG.GCR_CD = 'PRI'
AND SYSDATE BETWEEN GR.CIT_EFF_ASOF_DT AND GR.CIT_EFF_UNTIL_DT
AND SYSDATE BETWEEN CG.CIT_EFF_ASOF_DT AND CG.CIT_EFF_UNTIL_DT
AND SYSDATE BETWEEN C.CIT_EFF_ASOF_DT AND C.CIT_EFF_UNTIL_DT
AND SYSDATE BETWEEN N.CIT_EFF_ASOF_DT AND N.CIT_EFF_UNTIL_DT
AND SYSDATE BETWEEN U.CIT_EFF_ASOF_DT AND U.CIT_EFF_UNTIL_DT
AND SYSDATE BETWEEN G.CIT_EFF_ASOF_DT AND G.CIT_EFF_UNTIL_DT
AND SYSDATE BETWEEN P.CIT_EFF_ASOF_DT AND P.CIT_EFF_UNTIL_DT
AND SYSDATE BETWEEN R.CIT_EFF_ASOF_DT AND R.CIT_EFF_UNTIL_DT
AND R.CCF_CD IN ('C','C1','C2','C3','CC','CR','CS','CY')
ORDER BY CUSTOMER_NAME)) Where row_number Between 300*(1-1)+1 And 300*1;
Regards
Ritesh
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 07:54:03 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
