============================================================ |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| ------------------------------------------------------------ |0 |HASH GROUP BY | |93769 |646713 | |1 |└─HASH RIGHT OUTER JOIN | |93769 |600022 | |2 | ├─TABLE FULL SCAN |sspa|44120 |1997 | |3 | └─HASH RIGHT OUTER JOIN | |93735 |577994 | |4 | ├─HASH RIGHT OUTER JOIN | |30022 |7291 | |5 | │ ├─TABLE FULL SCAN |su |3281 |174 | |6 | │ └─TABLE FULL SCAN |ssac|30022 |3390 | |7 | └─TABLE FULL SCAN |pom |91250 |552542 | ============================================================ Outputs & filters: ------------------------------------- 0 - output([date_format(pom.order_time_cn(0x7f33aa036b60), '%Y-%m-%d')(0x7f33aa038450)], [sspa.id(0x7f33aa029be0)], [ssac.id(0x7f33aa039aa0)], [su.id(0x7f33aa02ec80)]), filter(nil), rowset=256 group([date_format(pom.order_time_cn(0x7f33aa036b60), '%Y-%m-%d')(0x7f33aa038450)], [pom.store_acct_id(0x7f33aa0298b0)], [pom.shipping_country_code(0x7f33aa03b780)], [pom.logis_type_id(0x7f33aa03be00)]), agg_func(nil) 1 - output([pom.store_acct_id(0x7f33aa0298b0)], [pom.shipping_country_code(0x7f33aa03b780)], [pom.logis_type_id(0x7f33aa03be00)], [sspa.id(0x7f33aa029be0)], [ssac.id(0x7f33aa039aa0)], [su.id(0x7f33aa02ec80)], [pom.order_time_cn(0x7f33aa036b60)]), filter(nil), rowset=256 equal_conds([pom.store_acct_id(0x7f33aa0298b0) = sspa.id(0x7f33aa029be0)(0x7f33aa029120)]), other_conds(nil) 2 - output([sspa.id(0x7f33aa029be0)]), filter(nil), rowset=256 access([sspa.id(0x7f33aa029be0)]), partitions(p0) is_index_back=false, is_global_index=false, range_key([sspa.id(0x7f33aa029be0)]), range(MIN ; MAX)always true 3 - output([pom.store_acct_id(0x7f33aa0298b0)], [pom.shipping_country_code(0x7f33aa03b780)], [pom.logis_type_id(0x7f33aa03be00)], [ssac.id(0x7f33aa039aa0)], [su.id(0x7f33aa02ec80)], [pom.order_time_cn(0x7f33aa036b60)]), filter(nil), rowset=256 equal_conds([pom.store_acct_id(0x7f33aa0298b0) = ssac.store_acct_id(0x7f33aa02d230)(0x7f33aa02ca90)]), other_conds(nil) 4 - output([ssac.id(0x7f33aa039aa0)], [su.id(0x7f33aa02ec80)], [ssac.store_acct_id(0x7f33aa02d230)]), filter(nil), rowset=256 equal_conds([ssac.leader_id(0x7f33aa02e950) = su.id(0x7f33aa02ec80)(0x7f33aa02e1c0)]), other_conds(nil) 5 - output([su.id(0x7f33aa02ec80)]), filter(nil), rowset=256 access([su.id(0x7f33aa02ec80)]), partitions(p0) is_index_back=false, is_global_index=false, range_key([su.id(0x7f33aa02ec80)]), range(MIN ; MAX)always true 6 - output([ssac.id(0x7f33aa039aa0)], [ssac.store_acct_id(0x7f33aa02d230)], [ssac.leader_id(0x7f33aa02e950)]), filter(nil), rowset=256 access([ssac.id(0x7f33aa039aa0)], [ssac.store_acct_id(0x7f33aa02d230)], [ssac.leader_id(0x7f33aa02e950)]), partitions(p0) is_index_back=false, is_global_index=false, range_key([ssac.id(0x7f33aa039aa0)]), range(MIN ; MAX)always true 7 - output([pom.store_acct_id(0x7f33aa0298b0)], [pom.order_time_cn(0x7f33aa036b60)], [pom.shipping_country_code(0x7f33aa03b780)], [pom.logis_type_id(0x7f33aa03be00)]), filter([(T_OP_BTW, pom.order_time_cn(0x7f33aa036b60), cast(date_add(cur_date()(0x7f33aa0322e0), cast(-10, VARCHAR(1048576))(0x7f33aa0348c0), 4)(0x7f33aa031b60), DATETIME(-1, -1))(0x7f33aa0377a0), date_add(current_timestamp()(0x7f33aa033890), cast(0, VARCHAR(1048576))(0x7f33aa0355d0), 4)(0x7f33aa033110))(0x7f33aa0361a0)], [(T_OP_NOT_IN, pom.process_status(0x7f33aa030fe0), (501, 503, 504, 500)(0x7f33aa03f6a0))(0x7f33aa02f250)]), rowset=256 access([pom.store_acct_id(0x7f33aa0298b0)], [pom.process_status(0x7f33aa030fe0)], [pom.order_time_cn(0x7f33aa036b60)], [pom.shipping_country_code(0x7f33aa03b780)], [pom.logis_type_id(0x7f33aa03be00)]), partitions(p0) is_index_back=false, is_global_index=false, filter_before_indexback[false,false], range_key([pom.id(0x7f33aa03d190)]), range(MIN ; MAX)always true Used Hint: ------------------------------------- /*+ */ Qb name trace: ------------------------------------- stmt_id:0, stmt_type:T_EXPLAIN stmt_id:1, SEL$1 Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA USE_HASH_AGGREGATION(@"SEL$1") LEADING(@"SEL$1" ("trade_online"."sspa"@"SEL$1" (("trade_online"."su"@"SEL$1" "trade_online"."ssac"@"SEL$1") "trade_online"."pom"@"SEL$1"))) USE_HASH(@"SEL$1" ("trade_online"."su"@"SEL$1" "trade_online"."ssac"@"SEL$1" "trade_online"."pom"@"SEL$1")) FULL(@"SEL$1" "sspa"@"SEL$1") USE_HASH(@"SEL$1" "trade_online"."pom"@"SEL$1") USE_HASH(@"SEL$1" "trade_online"."ssac"@"SEL$1") FULL(@"SEL$1" "su"@"SEL$1") FULL(@"SEL$1" "ssac"@"SEL$1") FULL(@"SEL$1" "pom"@"SEL$1") OPTIMIZER_FEATURES_ENABLE('4.2.2.0') END_OUTLINE_DATA */ Optimization Info: ------------------------------------- sspa: table_rows:44104 physical_range_rows:50331 logical_range_rows:44120 index_back_rows:0 output_rows:44120 table_dop:1 dop_method:Table DOP avaiable_index_name:[idx_plat_code_store_acct, index_plat_status, idx_paypal_email1, idx_allroot_alias_name, idx_register_email, idx_store_acct, idx_email, idx_paypal_email2, idx_paypal_email3, sys_sales_plat_acct] pruned_index_name:[idx_plat_code_store_acct, index_plat_status, idx_paypal_email1, idx_allroot_alias_name, idx_register_email, idx_store_acct, idx_email, idx_paypal_email2, idx_paypal_email3] stats version:1721055455139827 dynamic sampling level:0 estimation method:[OPTIMIZER STATISTICS, STORAGE] su: table_rows:3279 physical_range_rows:4216 logical_range_rows:3281 index_back_rows:0 output_rows:3281 table_dop:1 dop_method:Table DOP avaiable_index_name:[idx_uniq_login_name, idx_user_name, idx_login_name, sys_user] pruned_index_name:[idx_uniq_login_name, idx_user_name, idx_login_name] stats version:1721055456358451 dynamic sampling level:0 estimation method:[OPTIMIZER STATISTICS, STORAGE] ssac: table_rows:29993 physical_range_rows:41454 logical_range_rows:30022 index_back_rows:0 output_rows:30022 table_dop:1 dop_method:Table DOP avaiable_index_name:[idx_store_acct_id, sys_sales_acct_config] stats version:1720105758557930 dynamic sampling level:0 estimation method:[OPTIMIZER STATISTICS, STORAGE] pom: table_rows:6301215 physical_range_rows:7043838 logical_range_rows:6220814 index_back_rows:0 output_rows:97881 table_dop:1 dop_method:Table DOP avaiable_index_name:[index_tracking_no, idx_plat_code_order_time, idx_remain_time_plat_code, idx_store_acct_id_order_time_cn, idx_process_status, idx_buyer, idx_store_acct_id, idx_shipping_time, idx_modify_time, idx_order_time, idx_logis_agent_tracking_no, idx_plat_transaction_id, idx_plat_order_id, plat_order_main] pruned_index_name:[index_tracking_no, idx_plat_code_order_time, idx_remain_time_plat_code, idx_buyer, idx_shipping_time, idx_modify_time, idx_logis_agent_tracking_no, idx_plat_transaction_id, idx_plat_order_id] stats version:1721206781415100 dynamic sampling level:0 estimation method:[OPTIMIZER STATISTICS, STORAGE] Plan Type: LOCAL Note: Degree of Parallelisim is 1 because of table property