USE_CONCAT plan operation after April 2021 Bundle Patch (12.1.0.2) Questions
From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Fri, 27 Aug 2021 09:01:34 -0400
Message-ID: <CAP79kiQxOLEzCtj0pNjTbz3w73Q_g0cPJraqRpv4u14yiL6-Zg_at_mail.gmail.com>
List,
/*+
"CONTRACT_WHOLESALER_ACCOUNT"."WHOLESALER_ACCOUNT_ID"
"RAW_COB_CLAIMS"_at_"SEL$18" "VAD"_at_"SEL$15" "SRCL"@"SEL$10" "BL"@"SEL$13"
"I"_at_"SEL$12")
*/
/*+
"CONTRACT_WHOLESALER_ACCOUNT"."WHOLESALER_ACCOUNT_ID"
"RAW_COB_CLAIMS"_at_"SEL$18" "I"_at_"SEL$12" "VAD"@"SEL$15" "BL"@"SEL$13"
"SRCL"_at_"SEL$10"
"REPLENISHMENT"."REPLENISHED_ON"))
*/
Date: Fri, 27 Aug 2021 09:01:34 -0400
Message-ID: <CAP79kiQxOLEzCtj0pNjTbz3w73Q_g0cPJraqRpv4u14yiL6-Zg_at_mail.gmail.com>
List,
We're on 12.1.0.2 April 2021 Bundle Patch (after patching from Jul 2020 Bundle Patch)
We have a really ugly SQL that I've worked on in the past and managed to get the run time down to 120-150s consistently in the 2020 bundle patch.
After applying the 2021 April bundle patch, I'm getting this USE_CONCAT line in the plan and the execution times are way way off (and I'm not sure if this is the culprit or not but it stood out)
The SQL has a large IN statement and works through a lot of data.
We had a SQL Profile in place that managed plan but after the patch with or without the profile, the SQL execution is terrible.
I also notice there are some additional underscore parameters in the outline for the plan now that weren't there previously.
So my question is, if I want to disable USE_CONCAT through a session parameter (for testing) what parameter would influence the use of USE_CONCAT ? Below are the BEFORE & AFTER Outlines for the plan.
Before 2021 Bundle Patch
/*+
BEGIN_OUTLINE_DATA
FULL(_at_"SEL$4" "POI"_at_"SEL$4")
USE_HASH(_at_"SEL$4" "POI"_at_"SEL$4")
INDEX(_at_"SEL$6" "D"_at_"SEL$6" ("DRUG"."NDC" "DRUG"."PACK_QTY"
"DRUG"."PACK_SIZE"))
USE_NL(_at_"SEL$6" "D"_at_"SEL$6")
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
OPT_PARAM('_optim_peek_user_binds' 'false')
ALL_ROWS
OUTLINE_LEAF(_at_"SEL$19")
OUTLINE_LEAF(_at_"SEL$551CC24A")
OUTLINE_LEAF(_at_"SEL$16")
OUTLINE_LEAF(_at_"SEL$B6A6F6DF")
MERGE(_at_"SEL$6F4644B4")
OUTLINE_LEAF(_at_"SEL$FBFCA56D")
OUTLINE_LEAF(_at_"SEL$085F0B7A")
MERGE(_at_"SEL$15BE40C3")
OUTLINE_LEAF(_at_"SEL$551CC249")
OUTLINE_LEAF(_at_"SEL$632AF070")
MERGE(_at_"SEL$D24588F2")
OUTLINE_LEAF(_at_"SEL$3661D78D")
MERGE(_at_"SEL$21")
MERGE(_at_"SEL$A87E79E7")
OUTLINE_LEAF(_at_"SEL$4")
OUTLINE_LEAF(_at_"SEL$5")
OUTLINE_LEAF(_at_"SEL$6")
OUTLINE_LEAF(_at_"SEL$A0D20652")
MERGE(_at_"SEL$8")
OUTLINE_LEAF(_at_"SEL$9D1717F4")
ANSI_REARCH(_at_"SEL$7")
OUTLINE_LEAF(_at_"SEL$DBA62079")
ANSI_REARCH(_at_"SEL$27")
OUTLINE_LEAF(_at_"SEL$31")
OUTLINE_LEAF(_at_"SET$1")
OUTLINE_LEAF(_at_"SEL$28")
OUTLINE_LEAF(_at_"SEL$3A62EF88")
PUSH_PRED(_at_"SEL$2F5D08D6" "from$_subquery$_003"_at_"SEL$34" 4)
OUTLINE_LEAF(_at_"SEL$2F5D08D6")
MERGE(_at_"SEL$34")
OUTLINE(_at_"SEL$19")
OUTLINE(_at_"SEL$A3ABFE0C")
OUTLINE(_at_"SEL$6F4644B4")
UNNEST(_at_"SEL$18")
OUTLINE(_at_"SEL$63682743")
MERGE(_at_"SEL$9A1971F9")
OUTLINE(_at_"SEL$E7AF8512")
ANSI_REARCH(_at_"SEL$24")
OUTLINE(_at_"SEL$15BE40C3")
ANSI_REARCH(_at_"SEL$23")
OUTLINE(_at_"SEL$145B7DA0")
OUTLINE(_at_"SEL$D24588F2")
MERGE(_at_"SEL$6A52835A")
OUTLINE(_at_"SEL$30")
OUTLINE(_at_"SEL$21")
OUTLINE(_at_"SEL$A87E79E7")
OUTLINE(_at_"SEL$9")
OUTLINE(_at_"SEL$8")
OUTLINE(_at_"SEL$7")
OUTLINE(_at_"SEL$27")
OUTLINE(_at_"SEL$6220C9A1")
MERGE(_at_"SEL$33BBCCBB")
OUTLINE(_at_"SEL$2F5D08D6")
MERGE(_at_"SEL$34")
OUTLINE(_at_"SEL$34F84EA7")
MERGE(_at_"SEL$55666DC4")
OUTLINE(_at_"SEL$34")
OUTLINE(_at_"SEL$8FC7C762")
MERGE(_at_"SEL$33B58238")
OUTLINE(_at_"SEL$18")
OUTLINE(_at_"SEL$29")
OUTLINE(_at_"SEL$9A1971F9")
MERGE(_at_"SEL$65A156B2")
OUTLINE(_at_"SEL$24")
OUTLINE(_at_"SEL$23")
OUTLINE(_at_"SEL$99FC2C6E")
ANSI_REARCH(_at_"SEL$26")
OUTLINE(_at_"SEL$6A52835A")
MERGE(_at_"SEL$22")
OUTLINE(_at_"SEL$37")
OUTLINE(_at_"SEL$33BBCCBB")
MERGE(_at_"SEL$36")
OUTLINE(_at_"SEL$1")
OUTLINE(_at_"SEL$55666DC4")
MERGE(_at_"SEL$9C6E9675")
OUTLINE(_at_"SEL$10E2387E")
ANSI_REARCH(_at_"SEL$17")
OUTLINE(_at_"SEL$33B58238")
MERGE(_at_"SEL$93108F8D")
OUTLINE(_at_"SEL$A4319FDC")
ANSI_REARCH(_at_"SEL$20")
OUTLINE(_at_"SEL$65A156B2")
ANSI_REARCH(_at_"SEL$10")
OUTLINE(_at_"SEL$26")
OUTLINE(_at_"SEL$9F79289E")
ANSI_REARCH(_at_"SEL$25")
OUTLINE(_at_"SEL$22")
OUTLINE(_at_"SEL$35")
OUTLINE(_at_"SEL$36")
OUTLINE(_at_"SEL$2")
OUTLINE(_at_"SEL$9C6E9675")
MERGE(_at_"SEL$470D85D0")
OUTLINE(_at_"SEL$17")
OUTLINE(_at_"SEL$3CE6BBDD")
ANSI_REARCH(_at_"SEL$C6B06173")
OUTLINE(_at_"SEL$93108F8D")
MERGE(_at_"SEL$E87ED40E")
OUTLINE(_at_"SEL$20")
OUTLINE(_at_"SEL$10")
OUTLINE(_at_"SEL$25")
OUTLINE(_at_"SEL$38")
OUTLINE(_at_"SEL$470D85D0")
MERGE(_at_"SEL$3")
MERGE(_at_"SEL$32")
OUTLINE(_at_"SEL$C6B06173")
ANSI_REARCH(_at_"SEL$15")
OUTLINE(_at_"SEL$517421B4")
ANSI_REARCH(_at_"SEL$FC0768F8")
OUTLINE(_at_"SEL$E87ED40E")
MERGE(_at_"SEL$EF9E4006")
OUTLINE(_at_"SEL$33")
OUTLINE(_at_"SEL$3")
OUTLINE(_at_"SEL$32")
OUTLINE(_at_"SEL$15")
OUTLINE(_at_"SEL$FC0768F8")
ANSI_REARCH(_at_"SEL$14")
OUTLINE(_at_"SEL$6F4A64AD")
ANSI_REARCH(_at_"SEL$8ABA6BCC")
OUTLINE(_at_"SEL$EF9E4006")
MERGE(_at_"SEL$11")
OUTLINE(_at_"SEL$14")
OUTLINE(_at_"SEL$8ABA6BCC")
ANSI_REARCH(_at_"SEL$13")
OUTLINE(_at_"SEL$8BB7F254")
ANSI_REARCH(_at_"SEL$12")
OUTLINE(_at_"SEL$11")
OUTLINE(_at_"SEL$13")
OUTLINE(_at_"SEL$12")
NO_ACCESS(_at_"SEL$2F5D08D6" "S"_at_"SEL$3")
INDEX_RS_ASC(_at_"SEL$2F5D08D6" "D"_at_"SEL$3" ("DRUG"."NDC"))
NO_ACCESS(_at_"SEL$2F5D08D6" "from$_subquery$_003"_at_"SEL$34")
LEADING(_at_"SEL$2F5D08D6" "S"_at_"SEL$3" "D"@"SEL$3"
"from$_subquery$_003"_at_"SEL$34")
USE_NL(_at_"SEL$2F5D08D6" "D"_at_"SEL$3")
USE_NL(_at_"SEL$2F5D08D6" "from$_subquery$_003"_at_"SEL$34")
NO_ACCESS(_at_"SEL$28" "T"_at_"SEL$28")
USE_HASH_AGGREGATION(_at_"SEL$28")
INDEX_RS_ASC(_at_"SEL$3A62EF88" "CWA"_at_"SEL$35"
("CONTRACT_WHOLESALER_ACCOUNT"."CONTRACT_ID"
"CONTRACT_WHOLESALER_ACCOUNT"."WHOLESALER_ACCOUNT_ID"
"CONTRACT_WHOLESALER_ACCOUNT"."TYPE"))
BATCH_TABLE_ACCESS_BY_ROWID(_at_"SEL$3A62EF88" "CWA"_at_"SEL$35")
INDEX_RS_ASC(_at_"SEL$3A62EF88" "PRICE_LIST2"_at_"SEL$36"
("PRICE_LIST2"."NDC" "PRICE_LIST2"."ACCOUNT_ID"))
LEADING(_at_"SEL$3A62EF88" "CWA"_at_"SEL$35" "PRICE_LIST2"@"SEL$36")
USE_NL(_at_"SEL$3A62EF88" "PRICE_LIST2"_at_"SEL$36")
NO_ACCESS(_at_"SEL$31" "PO_HOLDS"_at_"SEL$31")
NO_ACCESS(_at_"SEL$3661D78D" "VM_NWVW_0"_at_"SEL$A87E79E7")
NO_ACCESS(_at_"SEL$FBFCA56D" "VM_NWVW_1"_at_"SEL$FBFCA56D")
USE_HASH_AGGREGATION(_at_"SEL$FBFCA56D")
INDEX_RS_ASC(_at_"SEL$B6A6F6DF" "CT"_at_"SEL$11" ("CONTRACT"."CONTRACT_ID"))
INDEX(_at_"SEL$B6A6F6DF" "CL"_at_"SEL$11" ("CLAIM"."CONTRACT_ID"
"CLAIM"."CLAIM_TYPE" "CLAIM"."PROCESSED_ON"))
FULL(_at_"SEL$B6A6F6DF" "CLI"_at_"SEL$14")
INDEX(_at_"SEL$B6A6F6DF" "RAW_COB_CLAIMS"_at_"SEL$18"
("RAW_COB_CLAIMS"."SECONDARY_RAW_CLAIM_ID"))
NO_ACCESS(_at_"SEL$B6A6F6DF" "VAD"_at_"SEL$15")
FULL(_at_"SEL$B6A6F6DF" "SRCL"_at_"SEL$10")
NO_ACCESS(_at_"SEL$B6A6F6DF" "BL"_at_"SEL$13")
INDEX_RS_ASC(_at_"SEL$B6A6F6DF" "I"_at_"SEL$12" ("INVOICE"."CONTRACT_ID"))
BATCH_TABLE_ACCESS_BY_ROWID(_at_"SEL$B6A6F6DF" "I"_at_"SEL$12")
LEADING(_at_"SEL$B6A6F6DF" "CT"_at_"SEL$11" "CL"@"SEL$11" "CLI"@"SEL$14"
"RAW_COB_CLAIMS"_at_"SEL$18" "VAD"_at_"SEL$15" "SRCL"@"SEL$10" "BL"@"SEL$13"
"I"_at_"SEL$12")
USE_NL(_at_"SEL$B6A6F6DF" "CL"_at_"SEL$11")
NLJ_BATCHING(_at_"SEL$B6A6F6DF" "CL"_at_"SEL$11")
USE_HASH(_at_"SEL$B6A6F6DF" "CLI"_at_"SEL$14")
USE_NL(_at_"SEL$B6A6F6DF" "RAW_COB_CLAIMS"_at_"SEL$18")
USE_HASH(_at_"SEL$B6A6F6DF" "VAD"_at_"SEL$15")
USE_HASH(_at_"SEL$B6A6F6DF" "SRCL"_at_"SEL$10")
USE_HASH(_at_"SEL$B6A6F6DF" "BL"_at_"SEL$13")
USE_HASH(_at_"SEL$B6A6F6DF" "I"_at_"SEL$12")
SWAP_JOIN_INPUTS(_at_"SEL$B6A6F6DF" "CLI"_at_"SEL$14")
SWAP_JOIN_INPUTS(_at_"SEL$B6A6F6DF" "VAD"_at_"SEL$15")
SWAP_JOIN_INPUTS(_at_"SEL$B6A6F6DF" "SRCL"_at_"SEL$10")
SWAP_JOIN_INPUTS(_at_"SEL$B6A6F6DF" "BL"_at_"SEL$13")
SWAP_JOIN_INPUTS(_at_"SEL$B6A6F6DF" "I"_at_"SEL$12")
USE_HASH_AGGREGATION(_at_"SEL$B6A6F6DF")
FULL(_at_"SEL$551CC24A" "T1"_at_"SEL$551CC24A")
FULL(_at_"SEL$16" "ACKNOWLEDGED_DISPENSATION"_at_"SEL$16")
USE_HASH_AGGREGATION(_at_"SEL$16")
INDEX(_at_"SEL$632AF070" "R"_at_"SEL$22" ("REPLENISHMENT"."CONTRACT_ID"
"REPLENISHMENT"."NDC" "REPLENISHMENT"."VOIDED_ON"
"REPLENISHMENT"."REPLENISHED_ON"
"REPLENISHMENT"."QUANTITY"
"REPLENISHMENT"."REPLENISHMENT_ID"))
NO_ACCESS(_at_"SEL$632AF070" "BL"_at_"SEL$25")
NO_ACCESS(_at_"SEL$632AF070" "T1"_at_"SEL$22")
LEADING(_at_"SEL$632AF070" "R"_at_"SEL$22" "BL"@"SEL$25" "T1"@"SEL$22")
USE_MERGE(_at_"SEL$632AF070" "BL"_at_"SEL$25")
USE_HASH(_at_"SEL$632AF070" "T1"_at_"SEL$22")
SWAP_JOIN_INPUTS(_at_"SEL$632AF070" "T1"_at_"SEL$22")
USE_HASH_AGGREGATION(_at_"SEL$632AF070")
FULL(_at_"SEL$551CC249" "T1"_at_"SEL$551CC249")
INDEX(_at_"SEL$085F0B7A" "R"_at_"SEL$23" ("REPLENISHMENT"."CONTRACT_ID"
"REPLENISHMENT"."NDC" "REPLENISHMENT"."VOIDED_ON"
"REPLENISHMENT"."REPLENISHED_ON"
"REPLENISHMENT"."QUANTITY"
"REPLENISHMENT"."REPLENISHMENT_ID"))
FULL(_at_"SEL$085F0B7A" "RCL"_at_"SEL$23")
LEADING(_at_"SEL$085F0B7A" "R"_at_"SEL$23" "RCL"@"SEL$23")
USE_HASH(_at_"SEL$085F0B7A" "RCL"_at_"SEL$23")
SWAP_JOIN_INPUTS(_at_"SEL$085F0B7A" "RCL"_at_"SEL$23")
USE_HASH_AGGREGATION(_at_"SEL$085F0B7A")
NO_ACCESS(_at_"SEL$DBA62079" "from$_subquery$_044"_at_"SEL$27")
NO_ACCESS(_at_"SEL$9D1717F4" "from$_subquery$_039"_at_"SEL$7")
NO_ACCESS(_at_"SEL$9D1717F4" "RE"_at_"SEL$7")
LEADING(_at_"SEL$9D1717F4" "from$_subquery$_039"_at_"SEL$7" "RE"@"SEL$7")
USE_HASH(_at_"SEL$9D1717F4" "RE"_at_"SEL$7")
NO_ACCESS(_at_"SEL$6" "from$_subquery$_037"_at_"SEL$6")
LEADING(_at_"SEL$6" "from$_subquery$_037"_at_"SEL$6" "D"@"SEL$6")
INDEX_RS_ASC(_at_"SEL$A0D20652" "C"_at_"SEL$8" ("CONTRACT"."CONTRACT_ID"))
INDEX_RS_ASC(_at_"SEL$A0D20652" "R"_at_"SEL$8"
("REPLENISHMENT"."CONTRACT_ID" "REPLENISHMENT"."VOIDED_ON"))
LEADING(_at_"SEL$A0D20652" "C"_at_"SEL$8" "R"@"SEL$8")
USE_NL(_at_"SEL$A0D20652" "R"_at_"SEL$8")
INDEX_RS_ASC(_at_"SEL$5" "C"_at_"SEL$5" ("CONTRACT"."CONTRACT_ID"))
NO_ACCESS(_at_"SEL$5" "from$_subquery$_035"_at_"SEL$5")
LEADING(_at_"SEL$5" "C"_at_"SEL$5" "from$_subquery$_035"@"SEL$5")
USE_HASH(_at_"SEL$5" "from$_subquery$_035"_at_"SEL$5")
INDEX_RS_ASC(_at_"SEL$4" "PO"_at_"SEL$4" ("PURCHASE_ORDER"."CONTRACT_ID"
"PURCHASE_ORDER"."CREATED_ON"))
LEADING(_at_"SEL$4" "PO"_at_"SEL$4" "POI"@"SEL$4")
INDEX_RS_ASC(_at_"SEL$19" "ORDER_BLACKLIST"_at_"SEL$19"
("ORDER_BLACKLIST"."CONTRACT_ID" "ORDER_BLACKLIST"."NDC"))
BATCH_TABLE_ACCESS_BY_ROWID(_at_"SEL$19" "ORDER_BLACKLIST"_at_"SEL$19")
END_OUTLINE_DATA
*/
After 2021 Bundle Patch
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_null_accepting_semijoin' 'false')
OPT_PARAM('_optimizer_gather_feedback' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
OPT_PARAM('_fix_control' '7452863:0')
ALL_ROWS
OUTLINE_LEAF(_at_"SEL$19")
OUTLINE_LEAF(_at_"SEL$551CC24A")
OUTLINE_LEAF(_at_"SEL$2ACCA6FF")
OUTLINE_LEAF(_at_"SEL$16")
OUTLINE_LEAF(_at_"SEL$DB131040")
MERGE(_at_"SEL$211826FB")
OUTLINE_LEAF(_at_"SEL$FBFCA56D")
OUTLINE_LEAF(_at_"SEL$085F0B7A")
MERGE(_at_"SEL$15BE40C3")
OUTLINE_LEAF(_at_"SEL$551CC249")
MATERIALIZE(_at_"SEL$19")
OUTLINE_LEAF(_at_"SEL$632AF070")
MERGE(_at_"SEL$D24588F2")
OUTLINE_LEAF(_at_"SEL$3661D78D")
MERGE(_at_"SEL$21")
MERGE(_at_"SEL$A87E79E7")
OUTLINE_LEAF(_at_"SEL$A0D20652")
MERGE(_at_"SEL$8")
OUTLINE_LEAF(_at_"SEL$E6D49DC0")
MERGE(_at_"SEL$AF9A6E20")
OUTLINE_LEAF(_at_"SET$1")
OUTLINE_LEAF(_at_"SEL$28")
OUTLINE_LEAF(_at_"SEL$3A62EF88")
PUSH_PRED(_at_"SEL$2F5D08D6" "from$_subquery$_003"_at_"SEL$34" 4)
OUTLINE_LEAF(_at_"SEL$2F5D08D6")
MERGE(_at_"SEL$34")
OUTLINE_LEAF(_at_"SEL$A0D20652_1")
USE_CONCAT(_at_"SEL$A0D20652" 8 OR_PREDICATES(1) PREDICATE_REORDERS((39
36) (40 37) (41 38) (42 39) (43 40) (44 41) (45 42) (46 43) (47 44) (48 45)
(49
46) (50 47) (51 48) (52 49) (53 50) (54 51) (55 52) (56 53) (57 54) (58 55) (59 56) (60 57) (61 58) (62 59) (63 60) (64 61) (65 62) (66 63) (67 64)
(68 65) (69 66) (70 67) (71 68) (72 69) (36 70) (37 71) (38 72)))
OUTLINE_LEAF(_at_"SEL$A0D20652_2")
OUTLINE(_at_"SEL$19")
OUTLINE(_at_"SEL$EA451CA1")
OUTLINE(_at_"SEL$DDCDF9E3")
OUTLINE(_at_"SEL$211826FB")
UNNEST(_at_"SEL$18")
OUTLINE(_at_"SEL$63682743")
MERGE(_at_"SEL$9A1971F9")
OUTLINE(_at_"SEL$E7AF8512")
ANSI_REARCH(_at_"SEL$24")
OUTLINE(_at_"SEL$15BE40C3")
ANSI_REARCH(_at_"SEL$23")
OUTLINE(_at_"SEL$145B7DA0")
OUTLINE(_at_"SEL$D24588F2")
MERGE(_at_"SEL$6A52835A")
OUTLINE(_at_"SEL$30")
OUTLINE(_at_"SEL$21")
OUTLINE(_at_"SEL$A87E79E7")
OUTLINE(_at_"SEL$9")
OUTLINE(_at_"SEL$8")
OUTLINE(_at_"SEL$31")
OUTLINE(_at_"SEL$AF9A6E20")
MERGE(_at_"SEL$8372BB86")
OUTLINE(_at_"SEL$6220C9A1")
MERGE(_at_"SEL$33BBCCBB")
OUTLINE(_at_"SEL$2F5D08D6")
MERGE(_at_"SEL$34")
OUTLINE(_at_"SEL$34F84EA7")
MERGE(_at_"SEL$55666DC4")
OUTLINE(_at_"SEL$34")
OUTLINE(_at_"SEL$A0D20652")
MERGE(_at_"SEL$8")
OUTLINE(_at_"SEL$FC0768F8")
ANSI_REARCH(_at_"SEL$14")
OUTLINE(_at_"SEL$5F56A127")
MERGE(_at_"SEL$AD9A5BFA")
OUTLINE(_at_"SEL$18")
OUTLINE(_at_"SEL$29")
OUTLINE(_at_"SEL$9A1971F9")
MERGE(_at_"SEL$65A156B2")
OUTLINE(_at_"SEL$24")
OUTLINE(_at_"SEL$23")
OUTLINE(_at_"SEL$99FC2C6E")
ANSI_REARCH(_at_"SEL$26")
OUTLINE(_at_"SEL$6A52835A")
MERGE(_at_"SEL$22")
OUTLINE(_at_"SEL$DBA62079")
ANSI_REARCH(_at_"SEL$27")
OUTLINE(_at_"SEL$8372BB86")
MERGE(_at_"SEL$025E71EF")
OUTLINE(_at_"SEL$37")
OUTLINE(_at_"SEL$33BBCCBB")
MERGE(_at_"SEL$36")
OUTLINE(_at_"SEL$1")
OUTLINE(_at_"SEL$55666DC4")
MERGE(_at_"SEL$9C6E9675")
OUTLINE(_at_"SEL$14")
OUTLINE(_at_"SEL$890053D8")
ANSI_REARCH(_at_"SEL$17")
OUTLINE(_at_"SEL$AD9A5BFA")
MERGE(_at_"SEL$E2BB0EA8")
OUTLINE(_at_"SEL$A4319FDC")
ANSI_REARCH(_at_"SEL$20")
OUTLINE(_at_"SEL$65A156B2")
ANSI_REARCH(_at_"SEL$10")
OUTLINE(_at_"SEL$26")
OUTLINE(_at_"SEL$9F79289E")
ANSI_REARCH(_at_"SEL$25")
OUTLINE(_at_"SEL$22")
OUTLINE(_at_"SEL$27")
OUTLINE(_at_"SEL$9D1717F4")
ANSI_REARCH(_at_"SEL$7")
OUTLINE(_at_"SEL$025E71EF")
MERGE(_at_"SEL$9834E3F4")
OUTLINE(_at_"SEL$35")
OUTLINE(_at_"SEL$36")
OUTLINE(_at_"SEL$2")
OUTLINE(_at_"SEL$9C6E9675")
MERGE(_at_"SEL$470D85D0")
OUTLINE(_at_"SEL$17")
OUTLINE(_at_"SEL$A67538A0")
ANSI_REARCH(_at_"SEL$1D32F42C")
OUTLINE(_at_"SEL$E2BB0EA8")
MERGE(_at_"SEL$E87ED40E")
OUTLINE(_at_"SEL$20")
OUTLINE(_at_"SEL$10")
OUTLINE(_at_"SEL$25")
OUTLINE(_at_"SEL$7")
OUTLINE(_at_"SEL$6")
OUTLINE(_at_"SEL$9834E3F4")
MERGE(_at_"SEL$4")
OUTLINE(_at_"SEL$38")
OUTLINE(_at_"SEL$470D85D0")
MERGE(_at_"SEL$3")
MERGE(_at_"SEL$32")
OUTLINE(_at_"SEL$1D32F42C")
ANSI_REARCH(_at_"SEL$15")
OUTLINE(_at_"SEL$60D74DC1")
ANSI_REARCH(_at_"SEL$EA451CA1")
OUTLINE(_at_"SEL$E87ED40E")
MERGE(_at_"SEL$EF9E4006")
OUTLINE(_at_"SEL$5")
OUTLINE(_at_"SEL$4")
OUTLINE(_at_"SEL$33")
OUTLINE(_at_"SEL$3")
OUTLINE(_at_"SEL$32")
OUTLINE(_at_"SEL$15")
OUTLINE(_at_"SEL$6F4A64AD")
ANSI_REARCH(_at_"SEL$8ABA6BCC")
OUTLINE(_at_"SEL$EF9E4006")
MERGE(_at_"SEL$11")
OUTLINE(_at_"SEL$8ABA6BCC")
ANSI_REARCH(_at_"SEL$13")
OUTLINE(_at_"SEL$8BB7F254")
ANSI_REARCH(_at_"SEL$12")
OUTLINE(_at_"SEL$11")
OUTLINE(_at_"SEL$13")
OUTLINE(_at_"SEL$12")
NO_ACCESS(_at_"SEL$2F5D08D6" "S"_at_"SEL$3")
INDEX_RS_ASC(_at_"SEL$2F5D08D6" "D"_at_"SEL$3" ("DRUG"."NDC"))
NO_ACCESS(_at_"SEL$2F5D08D6" "from$_subquery$_003"_at_"SEL$34")
LEADING(_at_"SEL$2F5D08D6" "S"_at_"SEL$3" "D"@"SEL$3"
"from$_subquery$_003"_at_"SEL$34")
USE_NL(_at_"SEL$2F5D08D6" "D"_at_"SEL$3")
USE_NL(_at_"SEL$2F5D08D6" "from$_subquery$_003"_at_"SEL$34")
NO_ACCESS(_at_"SEL$28" "T"_at_"SEL$28")
USE_HASH_AGGREGATION(_at_"SEL$28")
INDEX_RS_ASC(_at_"SEL$3A62EF88" "CWA"_at_"SEL$35"
("CONTRACT_WHOLESALER_ACCOUNT"."CONTRACT_ID"
"CONTRACT_WHOLESALER_ACCOUNT"."WHOLESALER_ACCOUNT_ID"
"CONTRACT_WHOLESALER_ACCOUNT"."TYPE"))
BATCH_TABLE_ACCESS_BY_ROWID(_at_"SEL$3A62EF88" "CWA"_at_"SEL$35")
INDEX(_at_"SEL$3A62EF88" "PRICE_LIST1"_at_"SEL$36" ("PRICE_LIST1"."NDC"
"PRICE_LIST1"."ACCOUNT_ID" "PRICE_LIST1"."PRICE"))
LEADING(_at_"SEL$3A62EF88" "CWA"_at_"SEL$35" "PRICE_LIST1"@"SEL$36")
USE_NL(_at_"SEL$3A62EF88" "PRICE_LIST1"_at_"SEL$36")
INDEX_RS_ASC(_at_"SEL$E6D49DC0" "C"_at_"SEL$5" ("CONTRACT"."CONTRACT_ID"))
INDEX(_at_"SEL$E6D49DC0" "PO"_at_"SEL$4" ("PURCHASE_ORDER"."CONTRACT_ID"
"PURCHASE_ORDER"."CREATED_ON" "PURCHASE_ORDER"."PURCHASE_ORDER_ID"))
INDEX(_at_"SEL$E6D49DC0" "POI"_at_"SEL$4"
("PURCHASE_ORDER_ITEM"."PURCHASE_ORDER_ID"))
NO_ACCESS(_at_"SEL$E6D49DC0" "RE"_at_"SEL$7")
INDEX_FFS(_at_"SEL$E6D49DC0" "D"_at_"SEL$6" ("DRUG"."NDC" "DRUG"."PACK_QTY"
"DRUG"."PACK_SIZE"))
LEADING(_at_"SEL$E6D49DC0" "C"_at_"SEL$5" "PO"@"SEL$4" "POI"@"SEL$4"
"RE"_at_"SEL$7" "D"_at_"SEL$6")
USE_NL(_at_"SEL$E6D49DC0" "PO"_at_"SEL$4")
USE_NL(_at_"SEL$E6D49DC0" "POI"_at_"SEL$4")
NLJ_BATCHING(_at_"SEL$E6D49DC0" "POI"_at_"SEL$4")
USE_HASH(_at_"SEL$E6D49DC0" "RE"_at_"SEL$7")
USE_HASH(_at_"SEL$E6D49DC0" "D"_at_"SEL$6")
PX_JOIN_FILTER(_at_"SEL$E6D49DC0" "RE"_at_"SEL$7")
USE_HASH_AGGREGATION(_at_"SEL$E6D49DC0")
NO_ACCESS(_at_"SEL$3661D78D" "VM_NWVW_0"_at_"SEL$A87E79E7")
USE_HASH_AGGREGATION(_at_"SEL$3661D78D")
NO_ACCESS(_at_"SEL$FBFCA56D" "VM_NWVW_1"_at_"SEL$FBFCA56D")
USE_HASH_AGGREGATION(_at_"SEL$FBFCA56D")
INDEX_RS_ASC(_at_"SEL$DB131040" "CT"_at_"SEL$11" ("CONTRACT"."CONTRACT_ID"))
INDEX_RS_ASC(_at_"SEL$DB131040" "CL"_at_"SEL$11" ("CLAIM"."CONTRACT_ID"
"CLAIM"."CLAIM_TYPE" "CLAIM"."REVERSED_ON"))
BATCH_TABLE_ACCESS_BY_ROWID(_at_"SEL$DB131040" "CL"_at_"SEL$11")
INDEX(_at_"SEL$DB131040" "RAW_COB_CLAIMS"_at_"SEL$18"
("RAW_COB_CLAIMS"."SECONDARY_RAW_CLAIM_ID"))
INDEX_RS_ASC(_at_"SEL$DB131040" "I"_at_"SEL$12" ("INVOICE"."INVOICE_ID"))
NO_ACCESS(_at_"SEL$DB131040" "VAD"_at_"SEL$15")
NO_ACCESS(_at_"SEL$DB131040" "BL"_at_"SEL$13")
FULL(_at_"SEL$DB131040" "SRCL"_at_"SEL$10")
NO_ACCESS(_at_"SEL$DB131040" "VW_LAT_EA451CA1"_at_"SEL$EA451CA1")
LEADING(_at_"SEL$DB131040" "CT"_at_"SEL$11" "CL"@"SEL$11"
"RAW_COB_CLAIMS"_at_"SEL$18" "I"_at_"SEL$12" "VAD"@"SEL$15" "BL"@"SEL$13"
"SRCL"_at_"SEL$10"
"VW_LAT_EA451CA1"_at_"SEL$EA451CA1")
USE_NL(_at_"SEL$DB131040" "CL"_at_"SEL$11")
USE_NL(_at_"SEL$DB131040" "RAW_COB_CLAIMS"_at_"SEL$18")
USE_NL(_at_"SEL$DB131040" "I"_at_"SEL$12")
USE_HASH(_at_"SEL$DB131040" "VAD"_at_"SEL$15")
USE_HASH(_at_"SEL$DB131040" "BL"_at_"SEL$13")
USE_HASH(_at_"SEL$DB131040" "SRCL"_at_"SEL$10")
USE_NL(_at_"SEL$DB131040" "VW_LAT_EA451CA1"_at_"SEL$EA451CA1")
PX_JOIN_FILTER(_at_"SEL$DB131040" "VAD"_at_"SEL$15")
PX_JOIN_FILTER(_at_"SEL$DB131040" "SRCL"_at_"SEL$10")
USE_HASH_AGGREGATION(_at_"SEL$DB131040")
FULL(_at_"SEL$551CC24A" "T1"_at_"SEL$551CC24A")
INDEX(_at_"SEL$2ACCA6FF" "CLI"_at_"SEL$14" ("CLAIM_IGNORE"."CONTRACT_ID"
"CLAIM_IGNORE"."CLAIM_ID"))
INDEX(_at_"SEL$16" "ACKNOWLEDGED_DISPENSATION"_at_"SEL$16"
"ACKNOWLEDGED_DISP_IDX01")
USE_HASH_AGGREGATION(_at_"SEL$16")
NO_ACCESS(_at_"SEL$632AF070" "T1"_at_"SEL$22")
INDEX(_at_"SEL$632AF070" "R"_at_"SEL$22" ("REPLENISHMENT"."CONTRACT_ID"
"REPLENISHMENT"."NDC" "REPLENISHMENT"."VOIDED_ON"
"REPLENISHMENT"."REPLENISHED_ON"
"REPLENISHMENT"."QUANTITY"
"REPLENISHMENT"."REPLENISHMENT_ID"))
NO_ACCESS(_at_"SEL$632AF070" "BL"_at_"SEL$25")
LEADING(_at_"SEL$632AF070" "T1"_at_"SEL$22" "R"@"SEL$22" "BL"@"SEL$25")
USE_HASH(_at_"SEL$632AF070" "R"_at_"SEL$22")
USE_HASH(_at_"SEL$632AF070" "BL"_at_"SEL$25")
USE_HASH_AGGREGATION(_at_"SEL$632AF070")
FULL(_at_"SEL$551CC249" "T1"_at_"SEL$551CC249")
INDEX(_at_"SEL$085F0B7A" "R"_at_"SEL$23" ("REPLENISHMENT"."CONTRACT_ID"
"REPLENISHMENT"."NDC" "REPLENISHMENT"."VOIDED_ON"
"REPLENISHMENT"."REPLENISHED_ON"
"REPLENISHMENT"."QUANTITY"
"REPLENISHMENT"."REPLENISHMENT_ID"))
FULL(_at_"SEL$085F0B7A" "RCL"_at_"SEL$23")
LEADING(_at_"SEL$085F0B7A" "R"_at_"SEL$23" "RCL"@"SEL$23")
USE_HASH(_at_"SEL$085F0B7A" "RCL"_at_"SEL$23")
SWAP_JOIN_INPUTS(_at_"SEL$085F0B7A" "RCL"_at_"SEL$23")
USE_HASH_AGGREGATION(_at_"SEL$085F0B7A")
INDEX_RS_ASC(_at_"SEL$A0D20652_1" "R"_at_"SEL$8"
("REPLENISHMENT"."VOIDED_ON" "REPLENISHMENT"."REPLENISHMENT_ID"))
BATCH_TABLE_ACCESS_BY_ROWID(_at_"SEL$A0D20652_1" "R"_at_"SEL$8")
INDEX(_at_"SEL$A0D20652_1" "C"_at_"SEL$8" ("CONTRACT"."CONTRACT_ID"))
INDEX_RS_ASC(_at_"SEL$A0D20652_2" "C"_at_"SEL$A0D20652_2"
("CONTRACT"."CONTRACT_ID"))
INDEX(_at_"SEL$A0D20652_2" "R"_at_"SEL$A0D20652_2"
("REPLENISHMENT"."CONTRACT_ID" "REPLENISHMENT"."VOIDED_ON"
"REPLENISHMENT"."REPLENISHED_ON"))
LEADING(_at_"SEL$A0D20652_1" "R"_at_"SEL$8" "C"@"SEL$8")
LEADING(_at_"SEL$A0D20652_2" "C"_at_"SEL$A0D20652_2" "R"@"SEL$A0D20652_2")
USE_NL(_at_"SEL$A0D20652_1" "C"_at_"SEL$8")
NLJ_BATCHING(_at_"SEL$A0D20652_1" "C"_at_"SEL$8")
USE_NL(_at_"SEL$A0D20652_2" "R"_at_"SEL$A0D20652_2")
NLJ_BATCHING(_at_"SEL$A0D20652_2" "R"_at_"SEL$A0D20652_2")
USE_HASH_AGGREGATION(_at_"SEL$A0D20652")
FULL(_at_"SEL$19" "ORDER_BLACKLIST"_at_"SEL$19")
END_OUTLINE_DATA
*/
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Aug 27 2021 - 15:01:34 CEST