Home » SQL & PL/SQL » SQL & PL/SQL » QEP and Query Reconstruction Technique (11.2.0.3)
| QEP and Query Reconstruction Technique [message #612842] |
Fri, 25 April 2014 22:07  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Hi all. I am in the throws of creating one of the labs associated with my book that looks like it might get publshed soon, and I have a question for you. I have always accepted the specific limitation I am about to show but it dawned on me that better heads might know a solution to it. Note the following will be provided:
1. a query
2. a QEP (Query Execution Plan) in all its glory obtained with: select * from table(dbms_xplan.display('PLAN_TABLE',NULL,'ADVANCED'))
3. the reconstruction queries that would allow you to incrementally rebuild and test to find performance slowdown
Query Decomposition and Reconstruction is an old method but it works well for me when my head starts to hurt because a SQL is not revealing itself under my interrogations. It is not my primary analysis strategy any more but when the going get tough I often pull it out. So showing the process is quite valuable because it teaches a skill that can aid in SQL Tuning, and gives people a chance to explore the different parts of the QEP to see the information in each section and how it can be used.
My question is that this particular query has three scalar subqueries in the select list. Theae are correlated back to the main query as can be seen by the join inside the scalar. But the QEP shows no indication of which main query column(s) are used to do the join. As a result, some of the reconstructed queries are forced to use only the BIND variable shorthand presented by the QEP. In this examples these are :B1, :B2, :B3. This means those reconstruction queries which have these place holder variables cannot actually be run and one must refer back to the original query to get this information in order to modify the reconstruction queries to be executable. In this examples this is recontruction queries #6,#7,#8. I would have preferred if all info had been available from the QEP.
Compare for example the main query scalar subqueries vs. the reconstructed version using what is available form the QEP.
(SELECT vndrsystmcd
FROM claimref.pfl_relation_code prc
WHERE CAD.rltn_to_emp_cd = prc.pflrltncd) relation_code,
(SELECT vndrsystmcd
FROM claimref.pfl_sub_relation_assctn psra
WHERE CAD.rltn_to_emp_cd = psra.pflrltncd
AND CAD.rltn_attrb_cd = psra.pflsbrltncd) relation_attrib_code,
(SELECT vndrsystmcd
FROM claimref.pfl_reason_code prsnc
WHERE CAD.lv_rsn_cd = prsnc.pflrsncd) vndr_reason_code
------------------------------------------------
, (SELECT "VNDRSYSTMCD" FROM "CLAIMREF"."PFL_REASON_CODE" "PRSNC" WHERE "PRSNC"."PFLRSNCD"=:B1) "VNDRSYSTMCD"--[VARCHAR2,9]
------------------------------------------------
, (SELECT "VNDRSYSTMCD" FROM "CLAIMREF"."PFL_RELATION_CODE" "PRC" WHERE "PRC"."PFLRLTNCD"=:B2) "VNDRSYSTMCD"--[VARCHAR2,9]
------------------------------------------------
, (SELECT "VNDRSYSTMCD" FROM "CLAIMREF"."PFL_SUB_RELATION_ASSCTN" "PSRA" WHERE "PSRA"."PFLSBRLTNCD"=:B3 AND "PSRA"."PFLRLTNCD"=:B4) --"VNDRSYSTMCD"[VARCHAR2,9]
------------------------------------------------
Is there a way to get the query plan to give me this information or is it just a limitation of the 11gR2 Query Plan.
Thanks, Kevin.
SELECT CA.clm_actvty_id,
actvty_typ_nm,
clm_actvty_stts_id,
actvty_dt,
CA.clm_actvty_id,
clm_evnt_id,
CA.xtrnl_systm_id,
xtrnl_systm_nm,
xtrnl_systm_shrt_nm,
spclzd_prcss_flow,
entty_grp_id,
actl_rtrn_to_wrk_dt,
case_id,
case_nm,
asm_ind,
athrzd_end_dt,
clm_stts_prcss_dt,
clm_stts_cd,
clm_stts_dscrptn,
clm_stts_effctv_dt,
dlvry_dt,
curr_prev_row_ind,
dsblty_strt_dt,
ee_sttmnt_dt,
er_sttmnt_dt,
estmtd_rtrn_to_wrk_dt,
xpctd_lv_end_dt,
last_day_wrkd_dt,
frst_full_day_wrk_mssd,
lv_schdl_typ,
lv_schdl_effctv_dt,
physcn_rtrn_to_wrk_dt,
ap_sttmnt_dt,
stts_rsn_cd,
stts_rsn_dscrptn,
prgnss_rtrn_to_wrk_dt,
wrk_schdl_end_dt,
wrk_schdl_effctv_dt,
resume_bnft_dt,
bnft_effctv_dt,
wrk_rltd_ind,
ssnbr,
insrd_id,
emply_id,
cvrg_typ_cd,
clm_stts_dscrptn,
emp_grp_name,
dcmnt_id,
lv_rsn_dscrptn,
rcpnt_frst_nm,
rcpnt_lst_nm,
rcpnt_dob,
rcpnt_bond_dt,
rltn_to_emp_dscrptn,
rltn_attrb_dscrptn,
(SELECT vndrsystmcd
FROM claimref.pfl_relation_code prc
WHERE CAD.rltn_to_emp_cd = prc.pflrltncd) relation_code,
(SELECT vndrsystmcd
FROM claimref.pfl_sub_relation_assctn psra
WHERE CAD.rltn_to_emp_cd = psra.pflrltncd
AND CAD.rltn_attrb_cd = psra.pflsbrltncd) relation_attrib_code,
(SELECT vndrsystmcd
FROM claimref.pfl_reason_code prsnc
WHERE CAD.lv_rsn_cd = prsnc.pflrsncd) vndr_reason_code
FROM claim.claim_activity CA,
claim.claim_activity_details CAD,
external_system ES,
claimref.claim_activity_status CAS,
claimref.claim_activity_type CAT
WHERE CA.clm_evnt_id = :1
AND CA.clm_actvty_id = CAD.clm_actvty_id
AND CA.xtrnl_systm_id = ES.xtrnl_systm_id
AND ( CA.clm_actvty_stts_id = 1
OR CA.clm_actvty_stts_id = 2 )
AND CA.clm_actvty_typ_id = CAT.clm_actvty_typ_cd
AND CA.clm_actvty_stts_id = CAS.clm_actvty_stts_cd
ORDER BY CAT.sort_ordr ASC,
CA.clm_actvty_id ASC
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
Plan hash value: 2025295116
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 339 | 9107 (1)| 00:01:50 |
| 1 | TABLE ACCESS BY INDEX ROWID | PFL_RELATION_CODE | 1 | 6 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PKPFL_RELATION_CODE | 1 | | 0 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | PFL_SUB_RELATION_ASSCTN | 1 | 10 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PKPFL_SUB_RELATION_ASSCTN | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | PFL_REASON_CODE | 1 | 10 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PKPFL_REASON_CODE | 1 | | 0 (0)| 00:00:01 |
| 7 | SORT ORDER BY | | 1 | 339 | 9107 (1)| 00:01:50 |
|* 8 | HASH JOIN | | 1 | 339 | 9106 (1)| 00:01:50 |
| 9 | NESTED LOOPS | | | | | |
| 10 | NESTED LOOPS | | 1 | 113 | 1956 (1)| 00:00:24 |
| 11 | NESTED LOOPS | | 1 | 90 | 1955 (1)| 00:00:24 |
| 12 | NESTED LOOPS | | 1 | 51 | 1954 (1)| 00:00:24 |
|* 13 | TABLE ACCESS STORAGE FULL | CLAIM_ACTIVITY | 1 | 48 | 1954 (1)| 00:00:24 |
|* 14 | INDEX UNIQUE SCAN | PK_CLAIM_ACTIVITY_STATUS | 1 | 3 | 0 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID| CLAIM_ACTIVITY_TYPE | 1 | 39 | 1 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | PK_CLAIM_ACTIVITY_TYPE | 1 | | 0 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PKEXTERNAL_SYSTEM | 1 | | 0 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | EXTERNAL_SYSTEM | 1 | 23 | 1 (0)| 00:00:01 |
| 19 | TABLE ACCESS STORAGE FULL | CLAIM_ACTIVITY_DETAILS | 1763K| 380M| 7145 (1)| 00:01:26 |
--------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2 / PRC@SEL$2
2 - SEL$2 / PRC@SEL$2
3 - SEL$3 / PSRA@SEL$3
4 - SEL$3 / PSRA@SEL$3
5 - SEL$4 / PRSNC@SEL$4
6 - SEL$4 / PRSNC@SEL$4
7 - SEL$1
13 - SEL$1 / CA@SEL$1
14 - SEL$1 / CAS@SEL$1
15 - SEL$1 / CAT@SEL$1
16 - SEL$1 / CAT@SEL$1
17 - SEL$1 / ES@SEL$1
18 - SEL$1 / ES@SEL$1
19 - SEL$1 / CAD@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$2" "PRC"@"SEL$2" ("PFL_RELATION_CODE"."PFLRLTNCD"))
INDEX_RS_ASC(@"SEL$3" "PSRA"@"SEL$3" ("PFL_SUB_RELATION_ASSCTN"."PFLRLTNCD"
"PFL_SUB_RELATION_ASSCTN"."PFLSBRLTNCD"))
INDEX_RS_ASC(@"SEL$4" "PRSNC"@"SEL$4" ("PFL_REASON_CODE"."PFLRSNCD"))
USE_HASH(@"SEL$1" "CAD"@"SEL$1")
NLJ_BATCHING(@"SEL$1" "ES"@"SEL$1")
USE_NL(@"SEL$1" "ES"@"SEL$1")
USE_NL(@"SEL$1" "CAT"@"SEL$1")
USE_NL(@"SEL$1" "CAS"@"SEL$1")
LEADING(@"SEL$1" "CA"@"SEL$1" "CAS"@"SEL$1" "CAT"@"SEL$1" "ES"@"SEL$1" "CAD"@"SEL$1")
FULL(@"SEL$1" "CAD"@"SEL$1")
INDEX(@"SEL$1" "ES"@"SEL$1" ("EXTERNAL_SYSTEM"."XTRNL_SYSTM_ID"))
INDEX_RS_ASC(@"SEL$1" "CAT"@"SEL$1" ("CLAIM_ACTIVITY_TYPE"."CLM_ACTVTY_TYP_CD"))
INDEX(@"SEL$1" "CAS"@"SEL$1" ("CLAIM_ACTIVITY_STATUS"."CLM_ACTVTY_STTS_CD"))
FULL(@"SEL$1" "CA"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$4")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$2")
ALL_ROWS
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PRC"."PFLRLTNCD"=:B1)
4 - access("PSRA"."PFLRLTNCD"=:B1 AND "PSRA"."PFLSBRLTNCD"=:B2)
6 - access("PRSNC"."PFLRSNCD"=:B1)
8 - access("CA"."CLM_ACTVTY_ID"="CAD"."CLM_ACTVTY_ID")
13 - storage("CA"."CLM_EVNT_ID"=:1 AND ("CA"."CLM_ACTVTY_STTS_ID"=1 OR "CA"."CLM_ACTVTY_STTS_ID"=2))
filter("CA"."CLM_EVNT_ID"=:1 AND ("CA"."CLM_ACTVTY_STTS_ID"=1 OR "CA"."CLM_ACTVTY_STTS_ID"=2))
14 - access("CA"."CLM_ACTVTY_STTS_ID"="CAS"."CLM_ACTVTY_STTS_CD")
filter("CAS"."CLM_ACTVTY_STTS_CD"=1 OR "CAS"."CLM_ACTVTY_STTS_CD"=2)
16 - access("CA"."CLM_ACTVTY_TYP_ID"="CAT"."CLM_ACTVTY_TYP_CD")
17 - access("CA"."XTRNL_SYSTM_ID"="ES"."XTRNL_SYSTM_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "PRC".ROWID[ROWID,10], "VNDRSYSTMCD"[VARCHAR2,9]
2 - "PRC".ROWID[ROWID,10]
3 - "PSRA".ROWID[ROWID,10], "VNDRSYSTMCD"[VARCHAR2,9]
4 - "PSRA".ROWID[ROWID,10]
5 - "PRSNC".ROWID[ROWID,10], "VNDRSYSTMCD"[VARCHAR2,9]
6 - "PRSNC".ROWID[ROWID,10]
7 - (#keys=2) "CAT"."SORT_ORDR"[NUMBER,22], "CA"."CLM_ACTVTY_ID"[NUMBER,22], (SELECT
"VNDRSYSTMCD" FROM "CLAIMREF"."PFL_REASON_CODE" "PRSNC" WHERE "PRSNC"."PFLRSNCD"=:B1)[9],
"ACTVTY_TYP_NM"[VARCHAR2,50], "CLM_ACTVTY_STTS_ID"[NUMBER,22], "ACTVTY_DT"[DATE,7],
"CA"."CLM_ACTVTY_ID"[NUMBER,22], "CLM_EVNT_ID"[VARCHAR2,18], "CA"."XTRNL_SYSTM_ID"[NUMBER,22],
"XTRNL_SYSTM_NM"[VARCHAR2,50], "XTRNL_SYSTM_SHRT_NM"[VARCHAR2,20], "SPCLZD_PRCSS_FLOW"[VARCHAR2,30],
"ENTTY_GRP_ID"[VARCHAR2,75], "ACTL_RTRN_TO_WRK_DT"[DATE,7], "CASE_ID"[VARCHAR2,15],
"CASE_NM"[VARCHAR2,60], "ASM_IND"[VARCHAR2,1], "ATHRZD_END_DT"[DATE,7], "CLM_STTS_PRCSS_DT"[DATE,7],
"CLM_STTS_CD"[VARCHAR2,2], "CLM_STTS_DSCRPTN"[VARCHAR2,30], "CLM_STTS_EFFCTV_DT"[DATE,7],
"DLVRY_DT"[DATE,7], "CURR_PREV_ROW_IND"[VARCHAR2,1], "DSBLTY_STRT_DT"[DATE,7],
"EE_STTMNT_DT"[DATE,7], "ER_STTMNT_DT"[DATE,7], "ESTMTD_RTRN_TO_WRK_DT"[DATE,7],
"XPCTD_LV_END_DT"[DATE,7], "LAST_DAY_WRKD_DT"[DATE,7], "FRST_FULL_DAY_WRK_MSSD"[DATE,7],
"LV_SCHDL_TYP"[VARCHAR2,10], "LV_SCHDL_EFFCTV_DT"[DATE,7], "PHYSCN_RTRN_TO_WRK_DT"[DATE,7],
"AP_STTMNT_DT"[DATE,7], "STTS_RSN_CD"[VARCHAR2,2], "STTS_RSN_DSCRPTN"[VARCHAR2,50],
"PRGNSS_RTRN_TO_WRK_DT"[DATE,7], "WRK_SCHDL_END_DT"[DATE,7], "WRK_SCHDL_EFFCTV_DT"[DATE,7],
"RESUME_BNFT_DT"[DATE,7], "BNFT_EFFCTV_DT"[DATE,7], "WRK_RLTD_IND"[VARCHAR2,1], "SSNBR"[VARCHAR2,9],
"INSRD_ID"[VARCHAR2,12], "EMPLY_ID"[VARCHAR2,20], "CVRG_TYP_CD"[VARCHAR2,4],
"CLM_STTS_DSCRPTN"[VARCHAR2,30], "EMP_GRP_NAME"[VARCHAR2,75], "DCMNT_ID"[VARCHAR2,12],
"LV_RSN_DSCRPTN"[VARCHAR2,40], "RCPNT_FRST_NM"[VARCHAR2,30], "RCPNT_LST_NM"[VARCHAR2,30],
"RCPNT_DOB"[DATE,7], "RCPNT_BOND_DT"[DATE,7], "RLTN_TO_EMP_DSCRPTN"[VARCHAR2,90],
"RLTN_ATTRB_DSCRPTN"[VARCHAR2,90], (SELECT "VNDRSYSTMCD" FROM "CLAIMREF"."PFL_RELATION_CODE" "PRC"
WHERE "PRC"."PFLRLTNCD"=:B2)[9], (SELECT "VNDRSYSTMCD" FROM "CLAIMREF"."PFL_SUB_RELATION_ASSCTN"
"PSRA" WHERE "PSRA"."PFLSBRLTNCD"=:B3 AND "PSRA"."PFLRLTNCD"=:B4)[9]
8 - (#keys=1) "CA"."CLM_ACTVTY_ID"[NUMBER,22], "XTRNL_SYSTM_SHRT_NM"[VARCHAR2,20],
"CA"."CLM_EVNT_ID"[VARCHAR2,18], "ACTVTY_DT"[DATE,7], "CA"."CLM_ACTVTY_STTS_ID"[NUMBER,22],
"ENTTY_GRP_ID"[VARCHAR2,75], "CA"."XTRNL_SYSTM_ID"[NUMBER,22], "ACTVTY_TYP_NM"[VARCHAR2,50],
"CAT"."SORT_ORDR"[NUMBER,22], "SPCLZD_PRCSS_FLOW"[VARCHAR2,30], "XTRNL_SYSTM_NM"[VARCHAR2,50],
"RCPNT_BOND_DT"[DATE,7], "CURR_PREV_ROW_IND"[VARCHAR2,1], "INSRD_ID"[VARCHAR2,12],
"SSNBR"[VARCHAR2,9], "EMPLY_ID"[VARCHAR2,20], "CASE_ID"[VARCHAR2,15], "CASE_NM"[VARCHAR2,60],
"CVRG_TYP_CD"[VARCHAR2,4], "CLM_STTS_CD"[VARCHAR2,2], "CLM_STTS_DSCRPTN"[VARCHAR2,30],
"CLM_STTS_EFFCTV_DT"[DATE,7], "ATHRZD_END_DT"[DATE,7], "BNFT_EFFCTV_DT"[DATE,7],
"CLM_STTS_PRCSS_DT"[DATE,7], "ASM_IND"[VARCHAR2,1], "DSBLTY_STRT_DT"[DATE,7],
"LAST_DAY_WRKD_DT"[DATE,7], "FRST_FULL_DAY_WRK_MSSD"[DATE,7], "XPCTD_LV_END_DT"[DATE,7],
"DLVRY_DT"[DATE,7], "STTS_RSN_CD"[VARCHAR2,2], "STTS_RSN_DSCRPTN"[VARCHAR2,50],
"ACTL_RTRN_TO_WRK_DT"[DATE,7], "ESTMTD_RTRN_TO_WRK_DT"[DATE,7], "PHYSCN_RTRN_TO_WRK_DT"[DATE,7],
"PRGNSS_RTRN_TO_WRK_DT"[DATE,7], "RESUME_BNFT_DT"[DATE,7], "AP_STTMNT_DT"[DATE,7],
"EE_STTMNT_DT"[DATE,7], "ER_STTMNT_DT"[DATE,7], "WRK_RLTD_IND"[VARCHAR2,1],
"LV_SCHDL_TYP"[VARCHAR2,10], "LV_SCHDL_EFFCTV_DT"[DATE,7], "WRK_SCHDL_EFFCTV_DT"[DATE,7],
"WRK_SCHDL_END_DT"[DATE,7], "EMP_GRP_NAME"[VARCHAR2,75], "DCMNT_ID"[VARCHAR2,12],
"CAD"."LV_RSN_CD"[VARCHAR2,4], "LV_RSN_DSCRPTN"[VARCHAR2,40], "CAD"."RLTN_TO_EMP_CD"[VARCHAR2,4],
"RLTN_TO_EMP_DSCRPTN"[VARCHAR2,90], "CAD"."RLTN_ATTRB_CD"[VARCHAR2,4],
"RLTN_ATTRB_DSCRPTN"[VARCHAR2,90], "RCPNT_LST_NM"[VARCHAR2,30], "RCPNT_FRST_NM"[VARCHAR2,30],
"RCPNT_DOB"[DATE,7]
9 - (#keys=0) "CA"."CLM_ACTVTY_ID"[NUMBER,22], "CA"."CLM_EVNT_ID"[VARCHAR2,18],
"ACTVTY_DT"[DATE,7], "CA"."CLM_ACTVTY_STTS_ID"[NUMBER,22], "ENTTY_GRP_ID"[VARCHAR2,75],
"CA"."XTRNL_SYSTM_ID"[NUMBER,22], "ACTVTY_TYP_NM"[VARCHAR2,50], "CAT"."SORT_ORDR"[NUMBER,22],
"SPCLZD_PRCSS_FLOW"[VARCHAR2,30], "XTRNL_SYSTM_NM"[VARCHAR2,50], "XTRNL_SYSTM_SHRT_NM"[VARCHAR2,20]
10 - (#keys=0) "CA"."CLM_ACTVTY_ID"[NUMBER,22], "CA"."CLM_EVNT_ID"[VARCHAR2,18],
"ACTVTY_DT"[DATE,7], "CA"."CLM_ACTVTY_STTS_ID"[NUMBER,22], "ENTTY_GRP_ID"[VARCHAR2,75],
"CA"."XTRNL_SYSTM_ID"[NUMBER,22], "ACTVTY_TYP_NM"[VARCHAR2,50], "CAT"."SORT_ORDR"[NUMBER,22],
"SPCLZD_PRCSS_FLOW"[VARCHAR2,30], "ES".ROWID[ROWID,10]
11 - (#keys=0) "CA"."CLM_ACTVTY_ID"[NUMBER,22], "CA"."CLM_EVNT_ID"[VARCHAR2,18],
"ACTVTY_DT"[DATE,7], "CA"."CLM_ACTVTY_STTS_ID"[NUMBER,22], "ENTTY_GRP_ID"[VARCHAR2,75],
"CA"."XTRNL_SYSTM_ID"[NUMBER,22], "ACTVTY_TYP_NM"[VARCHAR2,50], "CAT"."SORT_ORDR"[NUMBER,22],
"SPCLZD_PRCSS_FLOW"[VARCHAR2,30]
12 - (#keys=0) "CA"."CLM_ACTVTY_ID"[NUMBER,22], "CA"."CLM_EVNT_ID"[VARCHAR2,18],
"ACTVTY_DT"[DATE,7], "CA"."CLM_ACTVTY_STTS_ID"[NUMBER,22], "CA"."CLM_ACTVTY_TYP_ID"[NUMBER,22],
"ENTTY_GRP_ID"[VARCHAR2,75], "CA"."XTRNL_SYSTM_ID"[NUMBER,22]
13 - "CA"."CLM_ACTVTY_ID"[NUMBER,22], "CA"."CLM_EVNT_ID"[VARCHAR2,18], "ACTVTY_DT"[DATE,7],
"CA"."CLM_ACTVTY_STTS_ID"[NUMBER,22], "CA"."CLM_ACTVTY_TYP_ID"[NUMBER,22],
"ENTTY_GRP_ID"[VARCHAR2,75], "CA"."XTRNL_SYSTM_ID"[NUMBER,22]
15 - "ACTVTY_TYP_NM"[VARCHAR2,50], "CAT"."SORT_ORDR"[NUMBER,22], "SPCLZD_PRCSS_FLOW"[VARCHAR2,30]
16 - "CAT".ROWID[ROWID,10]
17 - "ES".ROWID[ROWID,10]
18 - "XTRNL_SYSTM_NM"[VARCHAR2,50], "XTRNL_SYSTM_SHRT_NM"[VARCHAR2,20]
19 - "CAD"."CLM_ACTVTY_ID"[NUMBER,22], "CURR_PREV_ROW_IND"[VARCHAR2,1], "INSRD_ID"[VARCHAR2,12],
"SSNBR"[VARCHAR2,9], "EMPLY_ID"[VARCHAR2,20], "CASE_ID"[VARCHAR2,15], "CASE_NM"[VARCHAR2,60],
"CVRG_TYP_CD"[VARCHAR2,4], "CLM_STTS_CD"[VARCHAR2,2], "CLM_STTS_DSCRPTN"[VARCHAR2,30],
"CLM_STTS_EFFCTV_DT"[DATE,7], "ATHRZD_END_DT"[DATE,7], "BNFT_EFFCTV_DT"[DATE,7],
"CLM_STTS_PRCSS_DT"[DATE,7], "ASM_IND"[VARCHAR2,1], "DSBLTY_STRT_DT"[DATE,7],
"LAST_DAY_WRKD_DT"[DATE,7], "FRST_FULL_DAY_WRK_MSSD"[DATE,7], "XPCTD_LV_END_DT"[DATE,7],
"DLVRY_DT"[DATE,7], "STTS_RSN_CD"[VARCHAR2,2], "STTS_RSN_DSCRPTN"[VARCHAR2,50],
"ACTL_RTRN_TO_WRK_DT"[DATE,7], "ESTMTD_RTRN_TO_WRK_DT"[DATE,7], "PHYSCN_RTRN_TO_WRK_DT"[DATE,7],
"PRGNSS_RTRN_TO_WRK_DT"[DATE,7], "RESUME_BNFT_DT"[DATE,7], "AP_STTMNT_DT"[DATE,7],
"EE_STTMNT_DT"[DATE,7], "ER_STTMNT_DT"[DATE,7], "WRK_RLTD_IND"[VARCHAR2,1],
"LV_SCHDL_TYP"[VARCHAR2,10], "LV_SCHDL_EFFCTV_DT"[DATE,7], "WRK_SCHDL_EFFCTV_DT"[DATE,7],
"WRK_SCHDL_END_DT"[DATE,7], "EMP_GRP_NAME"[VARCHAR2,75], "DCMNT_ID"[VARCHAR2,12],
"CAD"."LV_RSN_CD"[VARCHAR2,4], "LV_RSN_DSCRPTN"[VARCHAR2,40], "CAD"."RLTN_TO_EMP_CD"[VARCHAR2,4],
"RLTN_TO_EMP_DSCRPTN"[VARCHAR2,90], "CAD"."RLTN_ATTRB_CD"[VARCHAR2,4],
"RLTN_ATTRB_DSCRPTN"[VARCHAR2,90], "RCPNT_LST_NM"[VARCHAR2,30], "RCPNT_FRST_NM"[VARCHAR2,30],
"RCPNT_DOB"[DATE,7], "RCPNT_BOND_DT"[DATE,7]
179 rows selected.
RECONSTRUCTION QUERIES
--
-- #1
--
select
-----------------------------------------------
-- claim_activity
"CA"."CLM_ACTVTY_ID"--[NUMBER,22]
, "CA"."CLM_EVNT_ID"--[VARCHAR2,18]
, "ACTVTY_DT"--[DATE,7]
, "CA"."CLM_ACTVTY_STTS_ID"--[NUMBER,22]
, "CA"."CLM_ACTVTY_TYP_ID"--[NUMBER,22]
, "ENTTY_GRP_ID"--[VARCHAR2,75]
, "CA"."XTRNL_SYSTM_ID"--[NUMBER,22]
-----------------------------------------------
from
claim_activity
where 1 = 1
--
-- claim.CLAIM_ACTIVITY CA
--
and ("CA"."CLM_EVNT_ID"=:1 AND ("CA"."CLM_ACTVTY_STTS_ID"=1 OR "CA"."CLM_ACTVTY_STTS_ID"=2))
/
--
-- #2
--
select
-----------------------------------------------
-- claim_activity
"CA"."CLM_ACTVTY_ID"--[NUMBER,22]
, "CA"."CLM_EVNT_ID"--[VARCHAR2,18]
, "ACTVTY_DT"--[DATE,7]
, "CA"."CLM_ACTVTY_STTS_ID"--[NUMBER,22]
, "CA"."CLM_ACTVTY_TYP_ID"--[NUMBER,22]
, "ENTTY_GRP_ID"--[VARCHAR2,75]
, "CA"."XTRNL_SYSTM_ID"--[NUMBER,22]
-----------------------------------------------
-- claim_activity_status
-----------------------------------------------
from
claim.CLAIM_ACTIVITY CA
, claim.CLAIM_ACTIVITY_STATUS CAS
where 1 = 1
--
-- claim_activity
--
and ("CA"."CLM_EVNT_ID"=:1 AND ("CA"."CLM_ACTVTY_STTS_ID"=1 OR "CA"."CLM_ACTVTY_STTS_ID"=2))
--
-- claim_activity_status
--
and ("CA"."CLM_ACTVTY_STTS_ID"="CAS"."CLM_ACTVTY_STTS_CD")
and ("CAS"."CLM_ACTVTY_STTS_CD"=1 OR "CAS"."CLM_ACTVTY_STTS_CD"=2)
/
--
-- #3
--
select
------------------------------------------------
-- claim_activity
--
"CA"."CLM_ACTVTY_ID"--[NUMBER,22]
, "CA"."CLM_EVNT_ID"--[VARCHAR2,18]
, "ACTVTY_DT"--[DATE,7]
, "CA"."CLM_ACTVTY_STTS_ID"--[NUMBER,22]
, "CA"."CLM_ACTVTY_TYP_ID"--[NUMBER,22]
, "ENTTY_GRP_ID"--[VARCHAR2,75]
, "CA"."XTRNL_SYSTM_ID"--[NUMBER,22]
-----------------------------------------------
-- claim_activity_status
------------------------------------------------
-- claim_activity_type
, "ACTVTY_TYP_NM"--[VARCHAR2,50]
, "CAT"."SORT_ORDR"--[NUMBER,22]
, "SPCLZD_PRCSS_FLOW"--[VARCHAR2,30]
------------------------------------------------
from
claim.CLAIM_ACTIVITY CA
, claim.CLAIM_ACTIVITY_STATUS CAS
, claim.CLAIM_ACTIVITY_TYPE CAT
where 1 = 1
--
-- claim_activity
--
and ("CA"."CLM_EVNT_ID"=:1 AND ("CA"."CLM_ACTVTY_STTS_ID"=1 OR "CA"."CLM_ACTVTY_STTS_ID"=2))
--
-- claim_activity_status
--
and ("CA"."CLM_ACTVTY_STTS_ID"="CAS"."CLM_ACTVTY_STTS_CD")
and ("CAS"."CLM_ACTVTY_STTS_CD"=1 OR "CAS"."CLM_ACTVTY_STTS_CD"=2)
--
-- claim_activity_type
--
and ("CA"."CLM_ACTVTY_TYP_ID"="CAT"."CLM_ACTVTY_TYP_CD")
/
--
-- #4
--
select
------------------------------------------------
-- claim_activity
--
"CA"."CLM_ACTVTY_ID"--[NUMBER,22]
, "CA"."CLM_EVNT_ID"--[VARCHAR2,18]
, "ACTVTY_DT"--[DATE,7]
, "CA"."CLM_ACTVTY_STTS_ID"--[NUMBER,22]
, "CA"."CLM_ACTVTY_TYP_ID"--[NUMBER,22]
, "ENTTY_GRP_ID"--[VARCHAR2,75]
, "CA"."XTRNL_SYSTM_ID"--[NUMBER,22]
-----------------------------------------------
-- claim_activity_status
------------------------------------------------
-- claim_activity_type
, "ACTVTY_TYP_NM"--[VARCHAR2,50]
, "CAT"."SORT_ORDR"--[NUMBER,22]
, "SPCLZD_PRCSS_FLOW"--[VARCHAR2,30]
------------------------------------------------
-- external_system
, "XTRNL_SYSTM_NM"--[VARCHAR2,50]
, "XTRNL_SYSTM_SHRT_NM"--[VARCHAR2,20]
------------------------------------------------
from
claim.CLAIM_ACTIVITY CA
, claim.CLAIM_ACTIVITY_STATUS CAS
, claim.CLAIM_ACTIVITY_TYPE CAT
, claim.EXTERNAL_SYSTEM ES
where 1 = 1
--
-- claim_activity
--
and ("CA"."CLM_EVNT_ID"=:1 AND ("CA"."CLM_ACTVTY_STTS_ID"=1 OR "CA"."CLM_ACTVTY_STTS_ID"=2))
--
-- claim_activity_status
--
and ("CA"."CLM_ACTVTY_STTS_ID"="CAS"."CLM_ACTVTY_STTS_CD")
and ("CAS"."CLM_ACTVTY_STTS_CD"=1 OR "CAS"."CLM_ACTVTY_STTS_CD"=2)
--
-- claim_activity_type
--
and ("CA"."CLM_ACTVTY_TYP_ID"="CAT"."CLM_ACTVTY_TYP_CD")
--
-- external_system
--
and ("CA"."XTRNL_SYSTM_ID"="ES"."XTRNL_SYSTM_ID")
/
--
-- #5
--
select
------------------------------------------------
-- claim_activity
--
"CA"."CLM_ACTVTY_ID"--[NUMBER,22]
, "CA"."CLM_EVNT_ID"--[VARCHAR2,18]
, "ACTVTY_DT"--[DATE,7]
, "CA"."CLM_ACTVTY_STTS_ID"--[NUMBER,22]
, "CA"."CLM_ACTVTY_TYP_ID"--[NUMBER,22]
, "ENTTY_GRP_ID"--[VARCHAR2,75]
, "CA"."XTRNL_SYSTM_ID"--[NUMBER,22]
-----------------------------------------------
-- claim_activity_status
------------------------------------------------
-- claim_activity_type
, "ACTVTY_TYP_NM"--[VARCHAR2,50]
, "CAT"."SORT_ORDR"--[NUMBER,22]
, "SPCLZD_PRCSS_FLOW"--[VARCHAR2,30]
------------------------------------------------
-- external_system
, "XTRNL_SYSTM_NM"--[VARCHAR2,50]
, "XTRNL_SYSTM_SHRT_NM"--[VARCHAR2,20]
------------------------------------------------
-- claim_activity_details
, "CAD"."CLM_ACTVTY_ID"--[NUMBER,22]
, "CURR_PREV_ROW_IND"--[VARCHAR2,1]
, "INSRD_ID"--[VARCHAR2,12]
, "SSNBR"--[VARCHAR2,9]
, "EMPLY_ID"--[VARCHAR2,20]
, "CASE_ID"--[VARCHAR2,15]
, "CASE_NM"--[VARCHAR2,60]
, "CVRG_TYP_CD"--[VARCHAR2,4]
, "CLM_STTS_CD"--[VARCHAR2,2]
, "CLM_STTS_DSCRPTN"--[VARCHAR2,30]
, "CLM_STTS_EFFCTV_DT"--[DATE,7]
, "ATHRZD_END_DT"--[DATE,7]
, "BNFT_EFFCTV_DT"--[DATE,7]
, "CLM_STTS_PRCSS_DT"--[DATE,7]
, "ASM_IND"--[VARCHAR2,1]
, "DSBLTY_STRT_DT"--[DATE,7]
, "LAST_DAY_WRKD_DT"--[DATE,7]
, "FRST_FULL_DAY_WRK_MSSD"--[DATE,7]
, "XPCTD_LV_END_DT"--[DATE,7]
, "DLVRY_DT"--[DATE,7]
, "STTS_RSN_CD"--[VARCHAR2,2]
, "STTS_RSN_DSCRPTN"--[VARCHAR2,50]
, "ACTL_RTRN_TO_WRK_DT"--[DATE,7]
, "ESTMTD_RTRN_TO_WRK_DT"--[DATE,7]
, "PHYSCN_RTRN_TO_WRK_DT"--[DATE,7]
, "PRGNSS_RTRN_TO_WRK_DT"--[DATE,7]
, "RESUME_BNFT_DT"--[DATE,7]
, "AP_STTMNT_DT"--[DATE,7]
, "EE_STTMNT_DT"--[DATE,7]
, "ER_STTMNT_DT"--[DATE,7]
, "WRK_RLTD_IND"--[VARCHAR2,1]
, "LV_SCHDL_TYP"--[VARCHAR2,10]
, "LV_SCHDL_EFFCTV_DT"--[DATE,7]
, "WRK_SCHDL_EFFCTV_DT"--[DATE,7]
, "WRK_SCHDL_END_DT"--[DATE,7]
, "EMP_GRP_NAME"--[VARCHAR2,75]
, "DCMNT_ID"--[VARCHAR2,12]
, "CAD"."LV_RSN_CD"--[VARCHAR2,4]
, "LV_RSN_DSCRPTN"--[VARCHAR2,40]
, "CAD"."RLTN_TO_EMP_CD"--[VARCHAR2,4]
, "RLTN_TO_EMP_DSCRPTN"--[VARCHAR2,90]
, "CAD"."RLTN_ATTRB_CD"--[VARCHAR2,4]
, "RLTN_ATTRB_DSCRPTN"--[VARCHAR2,90]
, "RCPNT_LST_NM"--[VARCHAR2,30]
, "RCPNT_FRST_NM"--[VARCHAR2,30]
, "RCPNT_DOB"--[DATE,7]
, "RCPNT_BOND_DT"--[DATE,7]
------------------------------------------------
from
claim.CLAIM_ACTIVITY CA
, claim.CLAIM_ACTIVITY_STATUS CAS
, claim.CLAIM_ACTIVITY_TYPE CAT
, claim.EXTERNAL_SYSTEM ES
, claim.CLAIM_ACTIVITY_DETAILS CAD
where 1 = 1
--
-- claim_activity
--
and ("CA"."CLM_EVNT_ID"=:1 AND ("CA"."CLM_ACTVTY_STTS_ID"=1 OR "CA"."CLM_ACTVTY_STTS_ID"=2))
--
-- claim_activity_status
--
and ("CA"."CLM_ACTVTY_STTS_ID"="CAS"."CLM_ACTVTY_STTS_CD")
and ("CAS"."CLM_ACTVTY_STTS_CD"=1 OR "CAS"."CLM_ACTVTY_STTS_CD"=2)
--
-- claim_activity_type
--
and ("CA"."CLM_ACTVTY_TYP_ID"="CAT"."CLM_ACTVTY_TYP_CD")
--
-- external_system
--
and ("CA"."XTRNL_SYSTM_ID"="ES"."XTRNL_SYSTM_ID")
--
-- claim_activity_details
and ("CA"."CLM_ACTVTY_ID"="CAD"."CLM_ACTVTY_ID")
/
--
-- #6
--
select
------------------------------------------------
-- claim_activity
--
"CA"."CLM_ACTVTY_ID"--[NUMBER,22]
, "CA"."CLM_EVNT_ID"--[VARCHAR2,18]
, "ACTVTY_DT"--[DATE,7]
, "CA"."CLM_ACTVTY_STTS_ID"--[NUMBER,22]
, "CA"."CLM_ACTVTY_TYP_ID"--[NUMBER,22]
, "ENTTY_GRP_ID"--[VARCHAR2,75]
, "CA"."XTRNL_SYSTM_ID"--[NUMBER,22]
-----------------------------------------------
-- claim_activity_status
------------------------------------------------
-- claim_activity_type
, "ACTVTY_TYP_NM"--[VARCHAR2,50]
, "CAT"."SORT_ORDR"--[NUMBER,22]
, "SPCLZD_PRCSS_FLOW"--[VARCHAR2,30]
------------------------------------------------
-- external_system
, "XTRNL_SYSTM_NM"--[VARCHAR2,50]
, "XTRNL_SYSTM_SHRT_NM"--[VARCHAR2,20]
------------------------------------------------
-- claim_activity_details
, "CAD"."CLM_ACTVTY_ID"--[NUMBER,22]
, "CURR_PREV_ROW_IND"--[VARCHAR2,1]
, "INSRD_ID"--[VARCHAR2,12]
, "SSNBR"--[VARCHAR2,9]
, "EMPLY_ID"--[VARCHAR2,20]
, "CASE_ID"--[VARCHAR2,15]
, "CASE_NM"--[VARCHAR2,60]
, "CVRG_TYP_CD"--[VARCHAR2,4]
, "CLM_STTS_CD"--[VARCHAR2,2]
, "CLM_STTS_DSCRPTN"--[VARCHAR2,30]
, "CLM_STTS_EFFCTV_DT"--[DATE,7]
, "ATHRZD_END_DT"--[DATE,7]
, "BNFT_EFFCTV_DT"--[DATE,7]
, "CLM_STTS_PRCSS_DT"--[DATE,7]
, "ASM_IND"--[VARCHAR2,1]
, "DSBLTY_STRT_DT"--[DATE,7]
, "LAST_DAY_WRKD_DT"--[DATE,7]
, "FRST_FULL_DAY_WRK_MSSD"--[DATE,7]
, "XPCTD_LV_END_DT"--[DATE,7]
, "DLVRY_DT"--[DATE,7]
, "STTS_RSN_CD"--[VARCHAR2,2]
, "STTS_RSN_DSCRPTN"--[VARCHAR2,50]
, "ACTL_RTRN_TO_WRK_DT"--[DATE,7]
, "ESTMTD_RTRN_TO_WRK_DT"--[DATE,7]
, "PHYSCN_RTRN_TO_WRK_DT"--[DATE,7]
, "PRGNSS_RTRN_TO_WRK_DT"--[DATE,7]
, "RESUME_BNFT_DT"--[DATE,7]
, "AP_STTMNT_DT"--[DATE,7]
, "EE_STTMNT_DT"--[DATE,7]
, "ER_STTMNT_DT"--[DATE,7]
, "WRK_RLTD_IND"--[VARCHAR2,1]
, "LV_SCHDL_TYP"--[VARCHAR2,10]
, "LV_SCHDL_EFFCTV_DT"--[DATE,7]
, "WRK_SCHDL_EFFCTV_DT"--[DATE,7]
, "WRK_SCHDL_END_DT"--[DATE,7]
, "EMP_GRP_NAME"--[VARCHAR2,75]
, "DCMNT_ID"--[VARCHAR2,12]
, "CAD"."LV_RSN_CD"--[VARCHAR2,4]
, "LV_RSN_DSCRPTN"--[VARCHAR2,40]
, "CAD"."RLTN_TO_EMP_CD"--[VARCHAR2,4]
, "RLTN_TO_EMP_DSCRPTN"--[VARCHAR2,90]
, "CAD"."RLTN_ATTRB_CD"--[VARCHAR2,4]
, "RLTN_ATTRB_DSCRPTN"--[VARCHAR2,90]
, "RCPNT_LST_NM"--[VARCHAR2,30]
, "RCPNT_FRST_NM"--[VARCHAR2,30]
, "RCPNT_DOB"--[DATE,7]
, "RCPNT_BOND_DT"--[DATE,7]
------------------------------------------------
, (SELECT "VNDRSYSTMCD" FROM "CLAIMREF"."PFL_REASON_CODE" "PRSNC" WHERE "PRSNC"."PFLRSNCD"=:B1) "VNDRSYSTMCD"--[VARCHAR2,9]
------------------------------------------------
from
claim.CLAIM_ACTIVITY CA
, claim.CLAIM_ACTIVITY_STATUS CAS
, claim.CLAIM_ACTIVITY_TYPE CAT
, claim.EXTERNAL_SYSTEM ES
, claim.CLAIM_ACTIVITY_DETAILS CAD
where 1 = 1
--
-- claim_activity
--
and ("CA"."CLM_EVNT_ID"=:1 AND ("CA"."CLM_ACTVTY_STTS_ID"=1 OR "CA"."CLM_ACTVTY_STTS_ID"=2))
--
-- claim_activity_status
--
and ("CA"."CLM_ACTVTY_STTS_ID"="CAS"."CLM_ACTVTY_STTS_CD")
and ("CAS"."CLM_ACTVTY_STTS_CD"=1 OR "CAS"."CLM_ACTVTY_STTS_CD"=2)
--
-- claim_activity_type
--
and ("CA"."CLM_ACTVTY_TYP_ID"="CAT"."CLM_ACTVTY_TYP_CD")
--
-- external_system
--
and ("CA"."XTRNL_SYSTM_ID"="ES"."XTRNL_SYSTM_ID")
--
-- claim_activity_details
and ("CA"."CLM_ACTVTY_ID"="CAD"."CLM_ACTVTY_ID")
/
--
-- #7
--
select
------------------------------------------------
-- claim_activity
--
"CA"."CLM_ACTVTY_ID"--[NUMBER,22]
, "CA"."CLM_EVNT_ID"--[VARCHAR2,18]
, "ACTVTY_DT"--[DATE,7]
, "CA"."CLM_ACTVTY_STTS_ID"--[NUMBER,22]
, "CA"."CLM_ACTVTY_TYP_ID"--[NUMBER,22]
, "ENTTY_GRP_ID"--[VARCHAR2,75]
, "CA"."XTRNL_SYSTM_ID"--[NUMBER,22]
-----------------------------------------------
-- claim_activity_status
------------------------------------------------
-- claim_activity_type
, "ACTVTY_TYP_NM"--[VARCHAR2,50]
, "CAT"."SORT_ORDR"--[NUMBER,22]
, "SPCLZD_PRCSS_FLOW"--[VARCHAR2,30]
------------------------------------------------
-- external_system
, "XTRNL_SYSTM_NM"--[VARCHAR2,50]
, "XTRNL_SYSTM_SHRT_NM"--[VARCHAR2,20]
------------------------------------------------
-- claim_activity_details
, "CAD"."CLM_ACTVTY_ID"--[NUMBER,22]
, "CURR_PREV_ROW_IND"--[VARCHAR2,1]
, "INSRD_ID"--[VARCHAR2,12]
, "SSNBR"--[VARCHAR2,9]
, "EMPLY_ID"--[VARCHAR2,20]
, "CASE_ID"--[VARCHAR2,15]
, "CASE_NM"--[VARCHAR2,60]
, "CVRG_TYP_CD"--[VARCHAR2,4]
, "CLM_STTS_CD"--[VARCHAR2,2]
, "CLM_STTS_DSCRPTN"--[VARCHAR2,30]
, "CLM_STTS_EFFCTV_DT"--[DATE,7]
, "ATHRZD_END_DT"--[DATE,7]
, "BNFT_EFFCTV_DT"--[DATE,7]
, "CLM_STTS_PRCSS_DT"--[DATE,7]
, "ASM_IND"--[VARCHAR2,1]
, "DSBLTY_STRT_DT"--[DATE,7]
, "LAST_DAY_WRKD_DT"--[DATE,7]
, "FRST_FULL_DAY_WRK_MSSD"--[DATE,7]
, "XPCTD_LV_END_DT"--[DATE,7]
, "DLVRY_DT"--[DATE,7]
, "STTS_RSN_CD"--[VARCHAR2,2]
, "STTS_RSN_DSCRPTN"--[VARCHAR2,50]
, "ACTL_RTRN_TO_WRK_DT"--[DATE,7]
, "ESTMTD_RTRN_TO_WRK_DT"--[DATE,7]
, "PHYSCN_RTRN_TO_WRK_DT"--[DATE,7]
, "PRGNSS_RTRN_TO_WRK_DT"--[DATE,7]
, "RESUME_BNFT_DT"--[DATE,7]
, "AP_STTMNT_DT"--[DATE,7]
, "EE_STTMNT_DT"--[DATE,7]
, "ER_STTMNT_DT"--[DATE,7]
, "WRK_RLTD_IND"--[VARCHAR2,1]
, "LV_SCHDL_TYP"--[VARCHAR2,10]
, "LV_SCHDL_EFFCTV_DT"--[DATE,7]
, "WRK_SCHDL_EFFCTV_DT"--[DATE,7]
, "WRK_SCHDL_END_DT"--[DATE,7]
, "EMP_GRP_NAME"--[VARCHAR2,75]
, "DCMNT_ID"--[VARCHAR2,12]
, "CAD"."LV_RSN_CD"--[VARCHAR2,4]
, "LV_RSN_DSCRPTN"--[VARCHAR2,40]
, "CAD"."RLTN_TO_EMP_CD"--[VARCHAR2,4]
, "RLTN_TO_EMP_DSCRPTN"--[VARCHAR2,90]
, "CAD"."RLTN_ATTRB_CD"--[VARCHAR2,4]
, "RLTN_ATTRB_DSCRPTN"--[VARCHAR2,90]
, "RCPNT_LST_NM"--[VARCHAR2,30]
, "RCPNT_FRST_NM"--[VARCHAR2,30]
, "RCPNT_DOB"--[DATE,7]
, "RCPNT_BOND_DT"--[DATE,7]
------------------------------------------------
, (SELECT "VNDRSYSTMCD" FROM "CLAIMREF"."PFL_REASON_CODE" "PRSNC" WHERE "PRSNC"."PFLRSNCD"=:B1) "VNDRSYSTMCD"--[VARCHAR2,9]
------------------------------------------------
, (SELECT "VNDRSYSTMCD" FROM "CLAIMREF"."PFL_RELATION_CODE" "PRC" WHERE "PRC"."PFLRLTNCD"=:B2) "VNDRSYSTMCD"--[VARCHAR2,9]
------------------------------------------------
from
claim.CLAIM_ACTIVITY CA
, claim.CLAIM_ACTIVITY_STATUS CAS
, claim.CLAIM_ACTIVITY_TYPE CAT
, claim.EXTERNAL_SYSTEM ES
, claim.CLAIM_ACTIVITY_DETAILS CAD
where 1 = 1
--
-- claim_activity
--
and ("CA"."CLM_EVNT_ID"=:1 AND ("CA"."CLM_ACTVTY_STTS_ID"=1 OR "CA"."CLM_ACTVTY_STTS_ID"=2))
--
-- claim_activity_status
--
and ("CA"."CLM_ACTVTY_STTS_ID"="CAS"."CLM_ACTVTY_STTS_CD")
and ("CAS"."CLM_ACTVTY_STTS_CD"=1 OR "CAS"."CLM_ACTVTY_STTS_CD"=2)
--
-- claim_activity_type
--
and ("CA"."CLM_ACTVTY_TYP_ID"="CAT"."CLM_ACTVTY_TYP_CD")
--
-- external_system
--
and ("CA"."XTRNL_SYSTM_ID"="ES"."XTRNL_SYSTM_ID")
--
-- claim_activity_details
and ("CA"."CLM_ACTVTY_ID"="CAD"."CLM_ACTVTY_ID")
/
--
-- #8
--
select
------------------------------------------------
-- claim_activity
--
"CA"."CLM_ACTVTY_ID"--[NUMBER,22]
, "CA"."CLM_EVNT_ID"--[VARCHAR2,18]
, "ACTVTY_DT"--[DATE,7]
, "CA"."CLM_ACTVTY_STTS_ID"--[NUMBER,22]
, "CA"."CLM_ACTVTY_TYP_ID"--[NUMBER,22]
, "ENTTY_GRP_ID"--[VARCHAR2,75]
, "CA"."XTRNL_SYSTM_ID"--[NUMBER,22]
-----------------------------------------------
-- claim_activity_status
------------------------------------------------
-- claim_activity_type
, "ACTVTY_TYP_NM"--[VARCHAR2,50]
, "CAT"."SORT_ORDR"--[NUMBER,22]
, "SPCLZD_PRCSS_FLOW"--[VARCHAR2,30]
------------------------------------------------
-- external_system
, "XTRNL_SYSTM_NM"--[VARCHAR2,50]
, "XTRNL_SYSTM_SHRT_NM"--[VARCHAR2,20]
------------------------------------------------
-- claim_activity_details
, "CAD"."CLM_ACTVTY_ID"--[NUMBER,22]
, "CURR_PREV_ROW_IND"--[VARCHAR2,1]
, "INSRD_ID"--[VARCHAR2,12]
, "SSNBR"--[VARCHAR2,9]
, "EMPLY_ID"--[VARCHAR2,20]
, "CASE_ID"--[VARCHAR2,15]
, "CASE_NM"--[VARCHAR2,60]
, "CVRG_TYP_CD"--[VARCHAR2,4]
, "CLM_STTS_CD"--[VARCHAR2,2]
, "CLM_STTS_DSCRPTN"--[VARCHAR2,30]
, "CLM_STTS_EFFCTV_DT"--[DATE,7]
, "ATHRZD_END_DT"--[DATE,7]
, "BNFT_EFFCTV_DT"--[DATE,7]
, "CLM_STTS_PRCSS_DT"--[DATE,7]
, "ASM_IND"--[VARCHAR2,1]
, "DSBLTY_STRT_DT"--[DATE,7]
, "LAST_DAY_WRKD_DT"--[DATE,7]
, "FRST_FULL_DAY_WRK_MSSD"--[DATE,7]
, "XPCTD_LV_END_DT"--[DATE,7]
, "DLVRY_DT"--[DATE,7]
, "STTS_RSN_CD"--[VARCHAR2,2]
, "STTS_RSN_DSCRPTN"--[VARCHAR2,50]
, "ACTL_RTRN_TO_WRK_DT"--[DATE,7]
, "ESTMTD_RTRN_TO_WRK_DT"--[DATE,7]
, "PHYSCN_RTRN_TO_WRK_DT"--[DATE,7]
, "PRGNSS_RTRN_TO_WRK_DT"--[DATE,7]
, "RESUME_BNFT_DT"--[DATE,7]
, "AP_STTMNT_DT"--[DATE,7]
, "EE_STTMNT_DT"--[DATE,7]
, "ER_STTMNT_DT"--[DATE,7]
, "WRK_RLTD_IND"--[VARCHAR2,1]
, "LV_SCHDL_TYP"--[VARCHAR2,10]
, "LV_SCHDL_EFFCTV_DT"--[DATE,7]
, "WRK_SCHDL_EFFCTV_DT"--[DATE,7]
, "WRK_SCHDL_END_DT"--[DATE,7]
, "EMP_GRP_NAME"--[VARCHAR2,75]
, "DCMNT_ID"--[VARCHAR2,12]
, "CAD"."LV_RSN_CD"--[VARCHAR2,4]
, "LV_RSN_DSCRPTN"--[VARCHAR2,40]
, "CAD"."RLTN_TO_EMP_CD"--[VARCHAR2,4]
, "RLTN_TO_EMP_DSCRPTN"--[VARCHAR2,90]
, "CAD"."RLTN_ATTRB_CD"--[VARCHAR2,4]
, "RLTN_ATTRB_DSCRPTN"--[VARCHAR2,90]
, "RCPNT_LST_NM"--[VARCHAR2,30]
, "RCPNT_FRST_NM"--[VARCHAR2,30]
, "RCPNT_DOB"--[DATE,7]
, "RCPNT_BOND_DT"--[DATE,7]
------------------------------------------------
, (SELECT "VNDRSYSTMCD" FROM "CLAIMREF"."PFL_REASON_CODE" "PRSNC" WHERE "PRSNC"."PFLRSNCD"=:B1) "VNDRSYSTMCD"--[VARCHAR2,9]
------------------------------------------------
, (SELECT "VNDRSYSTMCD" FROM "CLAIMREF"."PFL_RELATION_CODE" "PRC" WHERE "PRC"."PFLRLTNCD"=:B2) "VNDRSYSTMCD"--[VARCHAR2,9]
------------------------------------------------
, (SELECT "VNDRSYSTMCD" FROM "CLAIMREF"."PFL_SUB_RELATION_ASSCTN" "PSRA" WHERE "PSRA"."PFLSBRLTNCD"=:B3 AND "PSRA"."PFLRLTNCD"=:B4) --"VNDRSYSTMCD"[VARCHAR2,9]
------------------------------------------------
from
claim.CLAIM_ACTIVITY CA
, claim.CLAIM_ACTIVITY_STATUS CAS
, claim.CLAIM_ACTIVITY_TYPE CAT
, claim.EXTERNAL_SYSTEM ES
, claim.CLAIM_ACTIVITY_DETAILS CAD
where 1 = 1
--
-- claim_activity
--
and ("CA"."CLM_EVNT_ID"=:1 AND ("CA"."CLM_ACTVTY_STTS_ID"=1 OR "CA"."CLM_ACTVTY_STTS_ID"=2))
--
-- claim_activity_status
--
and ("CA"."CLM_ACTVTY_STTS_ID"="CAS"."CLM_ACTVTY_STTS_CD")
and ("CAS"."CLM_ACTVTY_STTS_CD"=1 OR "CAS"."CLM_ACTVTY_STTS_CD"=2)
--
-- claim_activity_type
--
and ("CA"."CLM_ACTVTY_TYP_ID"="CAT"."CLM_ACTVTY_TYP_CD")
--
-- external_system
--
and ("CA"."XTRNL_SYSTM_ID"="ES"."XTRNL_SYSTM_ID")
--
-- claim_activity_details
and ("CA"."CLM_ACTVTY_ID"="CAD"."CLM_ACTVTY_ID")
/
[Updated on: Fri, 25 April 2014 22:15] Report message to a moderator |
|
|
|
| Re: QEP and Query Reconstruction Technique [message #612849 is a reply to message #612842] |
Sat, 26 April 2014 05:52  |
John Watson
Messages: 8995
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I've done a few experiments, using simpler examples that project a scalar subquery, and I get your result: I can find no way to get the the filter (or access) displayed as anything other than a bind variable. That is what is in the ACCESS_PREDICATES and/or FILTER_PREDICATES columns of the plan_table, so I don't think there is any other information available. Very annoying. Anyone else have any ideas?
As an aside, I see that you are using release 11.2.0.3. Release 12.1 can unnest the subquery in some circumstances, and re-write it as an outer join (which I assume is what you are working towards in this exercise?) See this:
orclz> explain plan for select dname,(select avg(sal) from emp where emp.deptno=dept.deptno) from dept;
Explained.
orclz> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 2653503239
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 156 | 14 (58)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 4 | 156 | 14 (58)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 52 | 3 (0)| 00:00:01 |
| 3 | VIEW | VW_SSQ_1 | 3 | 78 | 8 (63)| 00:00:01 |
| 4 | HASH GROUP BY | | 3 | 21 | 8 (63)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP | 11 | 77 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ITEM_1"(+)="DEPT"."DEPTNO")
17 rows selected.
orclz>
then if I go back to the 11g optimizer, it runs as a correlation:
orclz> alter session set optimizer_features_enable='11.2.0.3';
Session altered.
orclz> explain plan for select dname,(select avg(sal) from emp where emp.deptno=dept.deptno) from dept;
Explained.
orclz> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
Plan hash value: 2018188441
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 52 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL| EMP | 4 | 28 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 4 | 52 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMP"."DEPTNO"=:B1)
15 rows selected.
orclz>
I can hint it, though:
orclz>
orclz> show parameter features_e
NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
optimizer_features_enable string 11.2.0.3
orclz>
orclz> explain plan for select dname,(select /*+ unnest */ avg(sal) from emp where emp.deptno=dept.deptno) from dept;
Explained.
orclz> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
Plan hash value: 2834279049
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 156 | 7 (29)| 00:00:01 |
| 1 | MERGE JOIN OUTER | | 4 | 156 | 7 (29)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 3 | 78 | 5 (40)| 00:00:01 |
| 5 | VIEW | VW_SSQ_1 | 3 | 78 | 4 (25)| 00:00:01 |
| 6 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 11 | 77 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("ITEM_1"(+)="DEPT"."DEPTNO")
filter("ITEM_1"(+)="DEPT"."DEPTNO")
20 rows selected.
orclz>
|
|
|
|
| Re: QEP and Query Reconstruction Technique [message #612864 is a reply to message #612849] |
Sat, 26 April 2014 10:01 |
|
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Thank you for taking the time to rewiew this John. And thanks for the validation. I figured this information was not avaialable but I wanted someone smarter than me to say so.
As to the lab, I was not attempting to demonstrate any particular optimization (though as you point out, the query certainly has plenty of material to work with). My goal with this lab is to have people take a real hard look at the QEP and what it contains, and to tie this back to what they learn from chapters 1,2 from my book. What better way to do that than to have them rebuild the original query one piece at a time through reconstruction, using ONLY information they can extract from the QEP. Through this lab they will look at all parts of the QEP and merge information obtained from each. The reconstruction queries they produce would also have value if they were in a situation where they needed to actually tune the query.
So as a lab it is a pretty good one. It allows the student to push through a process with specific skills learned and applied, the end result being a potentially useful product (reconstruction queries). The lab is mostly about learning how to extract information from a QEP, and reinforcing various concepts from chapters 1,2 like DRIVING TABLE, JOIN ORDER, and RECONSTRUCTION QUERIES to name a few.
As to my larger example, what peeked my interest in it was that it had extra "NON-CONFORMING" material that could be talked about in a class setting if there was time avaiable, and it presented this other neat quirk too. The ORDER BY at the end of the query changes what shows up in the PROJECTION section of the QEP.
With the ORDER BY we get this, NOTICE that this contains the subquery text from the scalars.
7 - (#keys=2) "CAT"."SORT_ORDR"[NUMBER,22], "CA"."CLM_ACTVTY_ID"[NUMBER,22], (SELECT
"VNDRSYSTMCD" FROM "CLAIMREF"."PFL_REASON_CODE" "PRSNC" WHERE "PRSNC"."PFLRSNCD"=:B1)[9],
"ACTVTY_TYP_NM"[VARCHAR2,50], "CLM_ACTVTY_STTS_ID"[NUMBER,22], "ACTVTY_DT"[DATE,7],
"CA"."CLM_ACTVTY_ID"[NUMBER,22], "CLM_EVNT_ID"[VARCHAR2,18], "CA"."XTRNL_SYSTM_ID"[NUMBER,22],
"XTRNL_SYSTM_NM"[VARCHAR2,50], "XTRNL_SYSTM_SHRT_NM"[VARCHAR2,20], "SPCLZD_PRCSS_FLOW"[VARCHAR2,30],
"ENTTY_GRP_ID"[VARCHAR2,75], "ACTL_RTRN_TO_WRK_DT"[DATE,7], "CASE_ID"[VARCHAR2,15],
"CASE_NM"[VARCHAR2,60], "ASM_IND"[VARCHAR2,1], "ATHRZD_END_DT"[DATE,7], "CLM_STTS_PRCSS_DT"[DATE,7],
"CLM_STTS_CD"[VARCHAR2,2], "CLM_STTS_DSCRPTN"[VARCHAR2,30], "CLM_STTS_EFFCTV_DT"[DATE,7],
"DLVRY_DT"[DATE,7], "CURR_PREV_ROW_IND"[VARCHAR2,1], "DSBLTY_STRT_DT"[DATE,7],
"EE_STTMNT_DT"[DATE,7], "ER_STTMNT_DT"[DATE,7], "ESTMTD_RTRN_TO_WRK_DT"[DATE,7],
"XPCTD_LV_END_DT"[DATE,7], "LAST_DAY_WRKD_DT"[DATE,7], "FRST_FULL_DAY_WRK_MSSD"[DATE,7],
"LV_SCHDL_TYP"[VARCHAR2,10], "LV_SCHDL_EFFCTV_DT"[DATE,7], "PHYSCN_RTRN_TO_WRK_DT"[DATE,7],
"AP_STTMNT_DT"[DATE,7], "STTS_RSN_CD"[VARCHAR2,2], "STTS_RSN_DSCRPTN"[VARCHAR2,50],
"PRGNSS_RTRN_TO_WRK_DT"[DATE,7], "WRK_SCHDL_END_DT"[DATE,7], "WRK_SCHDL_EFFCTV_DT"[DATE,7],
"RESUME_BNFT_DT"[DATE,7], "BNFT_EFFCTV_DT"[DATE,7], "WRK_RLTD_IND"[VARCHAR2,1], "SSNBR"[VARCHAR2,9],
"INSRD_ID"[VARCHAR2,12], "EMPLY_ID"[VARCHAR2,20], "CVRG_TYP_CD"[VARCHAR2,4],
"CLM_STTS_DSCRPTN"[VARCHAR2,30], "EMP_GRP_NAME"[VARCHAR2,75], "DCMNT_ID"[VARCHAR2,12],
"LV_RSN_DSCRPTN"[VARCHAR2,40], "RCPNT_FRST_NM"[VARCHAR2,30], "RCPNT_LST_NM"[VARCHAR2,30],
"RCPNT_DOB"[DATE,7], "RCPNT_BOND_DT"[DATE,7], "RLTN_TO_EMP_DSCRPTN"[VARCHAR2,90],
"RLTN_ATTRB_DSCRPTN"[VARCHAR2,90], (SELECT "VNDRSYSTMCD" FROM "CLAIMREF"."PFL_RELATION_CODE" "PRC"
WHERE "PRC"."PFLRLTNCD"=:B2)[9], (SELECT "VNDRSYSTMCD" FROM "CLAIMREF"."PFL_SUB_RELATION_ASSCTN"
"PSRA" WHERE "PSRA"."PFLSBRLTNCD"=:B3 AND "PSRA"."PFLRLTNCD"=:B4)[9]
Without the ORDER BY we get this, NOTICE that this variation has lost the scalars. Why would not having ORDER BY do this?
7 - (#keys=1) "CA"."CLM_ACTVTY_ID"[NUMBER,22], "XTRNL_SYSTM_SHRT_NM"[VARCHAR2,20],
"CA"."CLM_EVNT_ID"[VARCHAR2,18], "ACTVTY_DT"[DATE,7], "CA"."CLM_ACTVTY_STTS_ID"[NUMBER,22],
"ENTTY_GRP_ID"[VARCHAR2,75], "CA"."XTRNL_SYSTM_ID"[NUMBER,22], "ACTVTY_TYP_NM"[VARCHAR2,50],
"SPCLZD_PRCSS_FLOW"[VARCHAR2,30], "XTRNL_SYSTM_NM"[VARCHAR2,50], "RCPNT_BOND_DT"[DATE,7],
"CURR_PREV_ROW_IND"[VARCHAR2,1], "INSRD_ID"[VARCHAR2,12], "SSNBR"[VARCHAR2,9],
"EMPLY_ID"[VARCHAR2,20], "CASE_ID"[VARCHAR2,15], "CASE_NM"[VARCHAR2,60], "CVRG_TYP_CD"[VARCHAR2,4],
"CLM_STTS_CD"[VARCHAR2,2], "CLM_STTS_DSCRPTN"[VARCHAR2,30], "CLM_STTS_EFFCTV_DT"[DATE,7],
"ATHRZD_END_DT"[DATE,7], "BNFT_EFFCTV_DT"[DATE,7], "CLM_STTS_PRCSS_DT"[DATE,7],
"ASM_IND"[VARCHAR2,1], "DSBLTY_STRT_DT"[DATE,7], "LAST_DAY_WRKD_DT"[DATE,7],
"FRST_FULL_DAY_WRK_MSSD"[DATE,7], "XPCTD_LV_END_DT"[DATE,7], "DLVRY_DT"[DATE,7],
"STTS_RSN_CD"[VARCHAR2,2], "STTS_RSN_DSCRPTN"[VARCHAR2,50], "ACTL_RTRN_TO_WRK_DT"[DATE,7],
"ESTMTD_RTRN_TO_WRK_DT"[DATE,7], "PHYSCN_RTRN_TO_WRK_DT"[DATE,7], "PRGNSS_RTRN_TO_WRK_DT"[DATE,7],
"RESUME_BNFT_DT"[DATE,7], "AP_STTMNT_DT"[DATE,7], "EE_STTMNT_DT"[DATE,7], "ER_STTMNT_DT"[DATE,7],
"WRK_RLTD_IND"[VARCHAR2,1], "LV_SCHDL_TYP"[VARCHAR2,10], "LV_SCHDL_EFFCTV_DT"[DATE,7],
"WRK_SCHDL_EFFCTV_DT"[DATE,7], "WRK_SCHDL_END_DT"[DATE,7], "EMP_GRP_NAME"[VARCHAR2,75],
"DCMNT_ID"[VARCHAR2,12], "CAD"."LV_RSN_CD"[VARCHAR2,4], "LV_RSN_DSCRPTN"[VARCHAR2,40],
"CAD"."RLTN_TO_EMP_CD"[VARCHAR2,4], "RLTN_TO_EMP_DSCRPTN"[VARCHAR2,90],
"CAD"."RLTN_ATTRB_CD"[VARCHAR2,4], "RLTN_ATTRB_DSCRPTN"[VARCHAR2,90], "RCPNT_LST_NM"[VARCHAR2,30],
"RCPNT_FRST_NM"[VARCHAR2,30], "RCPNT_DOB"[DATE,7]
What is minssing is the scalar subqueryies in the projection section. So why would Oracle show these when there is an ORDER BY in the query, not show them when the ORDER BY is removed? I am guessing this is nothing more than a "bug" in the QEP and NOT a clue that the query execution actually processed these pieces differently.
Thanks again John. I can move forward now. Kevin Meade
Kevin
|
|
|
|
| Re: QEP and Query Reconstruction Technique [message #612924 is a reply to message #612864] |
Sun, 27 April 2014 21:36 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Hi Kevin,
I think you are comparing two different things in your latest post. It's not that the 2nd version does not have the scalar sub-queries, they were aslo missing from the first version - you are just comparing 2 different lines - the line with scalars is attached to the SORT step and the line without is attached to the HASH JOIN.
Go back to your original plan and you will notice both are present in the same plan - the SORT has the scalars and the HASH JOIN does not.
This makes sense; the HASH JOIN is performed BEFORE the scalars (so they are not present), but the SORT is performed AFTER (so they are present).
Ross Leishman
|
|
|
|
| Re: QEP and Query Reconstruction Technique [message #612930 is a reply to message #612924] |
Mon, 28 April 2014 01:22 |
John Watson
Messages: 8995
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Perhaps this takes us back to the basics of the SQL execution cycle:
First, row selection;
Second, aggregation;
Third, column projection;
Fourth, ordering.
Projecting a subquery appears to confuse this. Perhaps using phrases such as the "select list" rather than the "projected column list" doesn't help when explaining, many beginners do think that it is columns that are selected, not rows. I get this in class quite frequently.
I also strongly believe that using Oracle's join syntax, where join conditions and filters are mixed up in the predicate, can add to confusion. ANSI syntax is much clearer in breaking down the row selection stage: a join clause that joins tables, a predicate that filters rows. A bit more twenty first century, too.
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Jan 13 02:21:55 CST 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
