+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ================================================================================= | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | --------------------------------------------------------------------------------- | | |0 |TOP-N SORT | |1 |1321813 | | | |1 |└─SUBPLAN SCAN |VIEW1 |1 |1321813 | | | |2 | └─WINDOW FUNCTION | |13416 |1321664 | | | |3 | └─PARTITION SORT | |13416 |1305047 | | | |4 | └─HASH JOIN | |13416 |1296199 | | | |5 | ├─TABLE FULL SCAN |region |1 |5 | | | |6 | └─HASH JOIN | |67076 |1290168 | | | |7 | ├─TABLE FULL SCAN |nation |25 |6 | | | |8 | └─PX COORDINATOR | |67076 |1282477 | | | |9 | └─EXCHANGE OUT DISTR |:EX10001|67076 |1095866 | | | |10| └─HASH JOIN | |67076 |675916 | | | |11| ├─EXCHANGE IN DISTR | |67076 |629524 | | | |12| │ └─EXCHANGE OUT DISTR (PKEY)|:EX10000|67076 |562129 | | | |13| │ └─PX PARTITION ITERATOR | |67076 |410605 | | | |14| │ └─NESTED-LOOP JOIN | |67076 |410605 | | | |15| │ ├─TABLE FULL SCAN |part |16230 |111933 | | | |16| │ └─TABLE RANGE SCAN |partsupp|5 |36 | | | |17| └─PX PARTITION ITERATOR | |100000 |12738 | | | |18| └─TABLE FULL SCAN |supplier|100000 |12738 | | | ================================================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([VIEW1.supplier.s_acctbal(0x7f1c59ab7dc0)], [VIEW1.supplier.s_name(0x7f1c59ab80b0)], [VIEW1.nation.n_name(0x7f1c59ab92a0)], [VIEW1.part.p_partkey(0x7f1c59acc430)], | | [VIEW1.part.p_mfgr(0x7f1c59accd00)], [VIEW1.supplier.s_address(0x7f1c59ab83b0)], [VIEW1.supplier.s_phone(0x7f1c59ab86b0)], [VIEW1.supplier.s_comment(0x7f1c59ab89b0)]), filter(nil), rowset=256 | | sort_keys([VIEW1.supplier.s_acctbal(0x7f1c59ab7dc0), DESC], [VIEW1.nation.n_name(0x7f1c59ab92a0), ASC], [VIEW1.supplier.s_name(0x7f1c59ab80b0), ASC], | | [VIEW1.part.p_partkey(0x7f1c59acc430), ASC]), topn(100) | | 1 - output([VIEW1.supplier.s_acctbal(0x7f1c59ab7dc0)], [VIEW1.supplier.s_name(0x7f1c59ab80b0)], [VIEW1.supplier.s_address(0x7f1c59ab83b0)], [VIEW1.supplier.s_phone(0x7f1c59ab86b0)], | | [VIEW1.supplier.s_comment(0x7f1c59ab89b0)], [VIEW1.nation.n_name(0x7f1c59ab92a0)], [VIEW1.part.p_partkey(0x7f1c59acc430)], [VIEW1.part.p_mfgr(0x7f1c59accd00)]), filter([VIEW1.partsupp.ps_supplycost(0x | | 7f1c59ab74c0) = VIEW1.min(ps_supplycost)(0x7f1c59a1b7f0)(0x7f1c59a203a0)]), rowset=256 | | access([VIEW1.min(ps_supplycost)(0x7f1c59a1b7f0)], [VIEW1.partsupp.ps_supplycost(0x7f1c59ab74c0)], [VIEW1.supplier.s_acctbal(0x7f1c59ab7dc0)], [VIEW1.supplier.s_name(0x7f1c59ab80b0)], | | [VIEW1.supplier.s_address(0x7f1c59ab83b0)], [VIEW1.supplier.s_phone(0x7f1c59ab86b0)], [VIEW1.supplier.s_comment(0x7f1c59ab89b0)], [VIEW1.nation.n_name(0x7f1c59ab92a0)], | | [VIEW1.part.p_partkey(0x7f1c59acc430)], [VIEW1.part.p_mfgr(0x7f1c59accd00)]) | | 2 - output([T_FUN_MIN(partsupp.ps_supplycost(0x7f1c59aaf750))(0x7f1c59ab2fd0)(0x7f1c59aba6c0)], [partsupp.ps_supplycost(0x7f1c59aaf750)], [supplier.s_acctbal(0x7f1c59a184c0)], | | [supplier.s_name(0x7f1c59a187a0)], [supplier.s_address(0x7f1c59a19040)], [supplier.s_phone(0x7f1c59a1b230)], [supplier.s_comment(0x7f1c59a1b510)], [nation.n_name(0x7f1c59a18a80)], | | [part.p_partkey(0x7f1c59a16240)], [part.p_mfgr(0x7f1c59a18d60)]), filter(nil), rowset=256 | | win_expr(T_FUN_MIN(partsupp.ps_supplycost(0x7f1c59aaf750))(0x7f1c59ab2fd0)(0x7f1c59aba6c0)), partition_by([partsupp.ps_partkey(0x7f1c59aae050)]), | | order_by(nil), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED FOLLOWING) | | 3 - output([partsupp.ps_supplycost(0x7f1c59aaf750)], [supplier.s_acctbal(0x7f1c59a184c0)], [supplier.s_name(0x7f1c59a187a0)], [supplier.s_address(0x7f1c59a19040)], | | [supplier.s_phone(0x7f1c59a1b230)], [supplier.s_comment(0x7f1c59a1b510)], [nation.n_name(0x7f1c59a18a80)], [part.p_partkey(0x7f1c59a16240)], [part.p_mfgr(0x7f1c59a18d60)], | | [partsupp.ps_partkey(0x7f1c59aae050)]), filter(nil), rowset=256 | | sort_keys([HASH(partsupp.ps_partkey(0x7f1c59aae050))(0x7f1b6b2e5920), ASC], [partsupp.ps_partkey(0x7f1c59aae050), ASC]) | | 4 - output([partsupp.ps_supplycost(0x7f1c59aaf750)], [supplier.s_acctbal(0x7f1c59a184c0)], [supplier.s_name(0x7f1c59a187a0)], [supplier.s_address(0x7f1c59a19040)], | | [supplier.s_phone(0x7f1c59a1b230)], [supplier.s_comment(0x7f1c59a1b510)], [nation.n_name(0x7f1c59a18a80)], [part.p_partkey(0x7f1c59a16240)], [part.p_mfgr(0x7f1c59a18d60)], | | [partsupp.ps_partkey(0x7f1c59aae050)]), filter(nil), rowset=256 | | equal_conds([nation.n_regionkey(0x7f1c59aaf190) = region.r_regionkey(0x7f1c59aaebd0)(0x7f1c59ab0b50)]), other_conds(nil) | | 5 - output([region.r_regionkey(0x7f1c59aaebd0)]), filter([region.r_name(0x7f1c59aaf470) = 'MIDDLE EAST'(0x7f1c59ab13e0)]), rowset=256 | | access([region.r_regionkey(0x7f1c59aaebd0)], [region.r_name(0x7f1c59aaf470)]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([region.r_regionkey(0x7f1c59aaebd0)]), range(MIN ; MAX)always true | | 6 - output([partsupp.ps_supplycost(0x7f1c59aaf750)], [supplier.s_acctbal(0x7f1c59a184c0)], [supplier.s_name(0x7f1c59a187a0)], [supplier.s_address(0x7f1c59a19040)], | | [supplier.s_phone(0x7f1c59a1b230)], [supplier.s_comment(0x7f1c59a1b510)], [nation.n_name(0x7f1c59a18a80)], [part.p_partkey(0x7f1c59a16240)], [part.p_mfgr(0x7f1c59a18d60)], | | [partsupp.ps_partkey(0x7f1c59aae050)], [nation.n_regionkey(0x7f1c59aaf190)]), filter(nil), rowset=256 | | equal_conds([supplier.s_nationkey(0x7f1c59aaeeb0) = nation.n_nationkey(0x7f1c59aae8f0)(0x7f1c59ab02c0)]), other_conds(nil) | | 7 - output([nation.n_nationkey(0x7f1c59aae8f0)], [nation.n_regionkey(0x7f1c59aaf190)], [nation.n_name(0x7f1c59a18a80)]), filter(nil), rowset=256 | | access([nation.n_nationkey(0x7f1c59aae8f0)], [nation.n_regionkey(0x7f1c59aaf190)], [nation.n_name(0x7f1c59a18a80)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([nation.n_nationkey(0x7f1c59aae8f0)]), range(MIN ; MAX)always true | | 8 - output([partsupp.ps_supplycost(0x7f1c59aaf750)], [supplier.s_acctbal(0x7f1c59a184c0)], [supplier.s_name(0x7f1c59a187a0)], [supplier.s_address(0x7f1c59a19040)], | | [supplier.s_phone(0x7f1c59a1b230)], [supplier.s_comment(0x7f1c59a1b510)], [part.p_partkey(0x7f1c59a16240)], [part.p_mfgr(0x7f1c59a18d60)], [partsupp.ps_partkey(0x7f1c59aae050)], | | [supplier.s_nationkey(0x7f1c59aaeeb0)]), filter(nil), rowset=256 | | 9 - output([partsupp.ps_supplycost(0x7f1c59aaf750)], [supplier.s_acctbal(0x7f1c59a184c0)], [supplier.s_name(0x7f1c59a187a0)], [supplier.s_address(0x7f1c59a19040)], | | [supplier.s_phone(0x7f1c59a1b230)], [supplier.s_comment(0x7f1c59a1b510)], [part.p_partkey(0x7f1c59a16240)], [part.p_mfgr(0x7f1c59a18d60)], [partsupp.ps_partkey(0x7f1c59aae050)], | | [supplier.s_nationkey(0x7f1c59aaeeb0)]), filter(nil), rowset=256 | | dop=1 | | 10 - output([partsupp.ps_supplycost(0x7f1c59aaf750)], [supplier.s_acctbal(0x7f1c59a184c0)], [supplier.s_name(0x7f1c59a187a0)], [supplier.s_address(0x7f1c59a19040)], | | [supplier.s_phone(0x7f1c59a1b230)], [supplier.s_comment(0x7f1c59a1b510)], [part.p_partkey(0x7f1c59a16240)], [part.p_mfgr(0x7f1c59a18d60)], [partsupp.ps_partkey(0x7f1c59aae050)], | | [supplier.s_nationkey(0x7f1c59aaeeb0)]), filter(nil), rowset=256 | | equal_conds([supplier.s_suppkey(0x7f1c59aae610) = partsupp.ps_suppkey(0x7f1c59aae330)(0x7f1c59aafa30)]), other_conds(nil) | | 11 - output([partsupp.ps_supplycost(0x7f1c59aaf750)], [part.p_partkey(0x7f1c59a16240)], [part.p_mfgr(0x7f1c59a18d60)], [partsupp.ps_partkey(0x7f1c59aae050)], | | [partsupp.ps_suppkey(0x7f1c59aae330)]), filter(nil), rowset=256 | | 12 - output([partsupp.ps_supplycost(0x7f1c59aaf750)], [part.p_partkey(0x7f1c59a16240)], [part.p_mfgr(0x7f1c59a18d60)], [partsupp.ps_partkey(0x7f1c59aae050)], | | [partsupp.ps_suppkey(0x7f1c59aae330)]), filter(nil), rowset=256 | | (#keys=1, [partsupp.ps_suppkey(0x7f1c59aae330)]), dop=1 | | 13 - output([partsupp.ps_supplycost(0x7f1c59aaf750)], [part.p_partkey(0x7f1c59a16240)], [part.p_mfgr(0x7f1c59a18d60)], [partsupp.ps_partkey(0x7f1c59aae050)], | | [partsupp.ps_suppkey(0x7f1c59aae330)]), filter(nil), rowset=256 | | partition wise, force partition granule | | 14 - output([partsupp.ps_supplycost(0x7f1c59aaf750)], [part.p_partkey(0x7f1c59a16240)], [part.p_mfgr(0x7f1c59a18d60)], [partsupp.ps_partkey(0x7f1c59aae050)], | | [partsupp.ps_suppkey(0x7f1c59aae330)]), filter(nil), rowset=256 | | conds(nil), nl_params_([part.p_partkey(0x7f1c59a16240)(:3)]), use_batch=true | | 15 - output([part.p_partkey(0x7f1c59a16240)], [part.p_mfgr(0x7f1c59a18d60)]), filter([part.p_size(0x7f1c59a17380) = 6(0x7f1c59a1ced0)], [(T_OP_LIKE, part.p_type(0x7f1c59a17660), | | '%TIN', '\\')(0x7f1c59a1d9e0)]), rowset=256 | | access([part.p_partkey(0x7f1c59a16240)], [part.p_size(0x7f1c59a17380)], [part.p_type(0x7f1c59a17660)], [part.p_mfgr(0x7f1c59a18d60)]), partitions(p[0-1]) | | is_index_back=false, is_global_index=false, filter_before_indexback[false,false], | | range_key([part.p_partkey(0x7f1c59a16240)]), range(MIN ; MAX)always true | | 16 - output([partsupp.ps_partkey(0x7f1c59aae050)], [partsupp.ps_suppkey(0x7f1c59aae330)], [partsupp.ps_supplycost(0x7f1c59aaf750)]), filter(nil), rowset=256 | | access([GROUP_ID(0x7f1b6b3d9e70)], [partsupp.ps_partkey(0x7f1c59aae050)], [partsupp.ps_suppkey(0x7f1c59aae330)], [partsupp.ps_supplycost(0x7f1c59aaf750)]), partitions(p[0-1]) | | is_index_back=false, is_global_index=false, | | range_key([partsupp.ps_partkey(0x7f1c59aae050)], [partsupp.ps_suppkey(0x7f1c59aae330)]), range(MIN ; MAX), | | range_cond([partsupp.ps_partkey(0x7f1c59aae050) = :3(0x7f1d57e99960)]) | | 17 - output([supplier.s_suppkey(0x7f1c59aae610)], [supplier.s_nationkey(0x7f1c59aaeeb0)], [supplier.s_acctbal(0x7f1c59a184c0)], [supplier.s_name(0x7f1c59a187a0)], | | [supplier.s_address(0x7f1c59a19040)], [supplier.s_phone(0x7f1c59a1b230)], [supplier.s_comment(0x7f1c59a1b510)]), filter(nil), rowset=256 | | affinitize, force partition granule | | 18 - output([supplier.s_suppkey(0x7f1c59aae610)], [supplier.s_nationkey(0x7f1c59aaeeb0)], [supplier.s_acctbal(0x7f1c59a184c0)], [supplier.s_name(0x7f1c59a187a0)], | | [supplier.s_address(0x7f1c59a19040)], [supplier.s_phone(0x7f1c59a1b230)], [supplier.s_comment(0x7f1c59a1b510)]), filter(nil), rowset=256 | | access([supplier.s_suppkey(0x7f1c59aae610)], [supplier.s_nationkey(0x7f1c59aaeeb0)], [supplier.s_acctbal(0x7f1c59a184c0)], [supplier.s_name(0x7f1c59a187a0)], | | [supplier.s_address(0x7f1c59a19040)], [supplier.s_phone(0x7f1c59a1b230)], [supplier.s_comment(0x7f1c59a1b510)]), partitions(p[0-1]) | | is_index_back=false, is_global_index=false, | | range_key([supplier.s_suppkey(0x7f1c59aae610)]), range(MIN ; MAX)always true | | Used Hint: | | ------------------------------------- | | /*+ | | | | */ | | Qb name trace: | | ------------------------------------- | | stmt_id:0, stmt_type:T_EXPLAIN | | stmt_id:1, SEL$1 > SEL$D91D0E3F > SEL$39F0CD55 > SEL$A3C4E0DF | | stmt_id:2, SEL$2 > SEL$ED434F45 > SEL$8FEA16DB > SEL$194E002F | | Outline Data: | | ------------------------------------- | | /*+ | | BEGIN_OUTLINE_DATA | | PQ_DISTRIBUTE_WINDOW(@"SEL$194E002F" (0) NONE PARTITION_SORT) | | LEADING(@"SEL$194E002F" ("tpch10"."region"@"SEL$2" ("tpch10"."nation"@"SEL$2" (("tpch10"."part"@"SEL$1" "tpch10"."partsupp"@"SEL$2") "tpch10"."supplier"@"SEL$2")))) | | USE_HASH(@"SEL$194E002F" ("tpch10"."supplier"@"SEL$2" "tpch10"."partsupp"@"SEL$2" "tpch10"."part"@"SEL$1" "tpch10"."nation"@"SEL$2")) | | FULL(@"SEL$194E002F" "region"@"SEL$2") | | USE_HASH(@"SEL$194E002F" ("tpch10"."supplier"@"SEL$2" "tpch10"."partsupp"@"SEL$2" "tpch10"."part"@"SEL$1")) | | PQ_DISTRIBUTE(@"SEL$194E002F" ("tpch10"."supplier"@"SEL$2" "tpch10"."partsupp"@"SEL$2" "tpch10"."part"@"SEL$1") LOCAL LOCAL) | | FULL(@"SEL$194E002F" "nation"@"SEL$2") | | USE_HASH(@"SEL$194E002F" "tpch10"."supplier"@"SEL$2") | | PQ_DISTRIBUTE(@"SEL$194E002F" "tpch10"."supplier"@"SEL$2" PARTITION NONE) | | USE_NL(@"SEL$194E002F" "tpch10"."partsupp"@"SEL$2") | | PQ_DISTRIBUTE(@"SEL$194E002F" "tpch10"."partsupp"@"SEL$2" NONE NONE) | | FULL(@"SEL$194E002F" "tpch10"."part"@"SEL$1") | | FULL(@"SEL$194E002F" "partsupp"@"SEL$2") | | FULL(@"SEL$194E002F" "supplier"@"SEL$2") | | AGGR_FIRST_UNNEST(@"SEL$2") | | WIN_MAGIC(@"SEL$D91D0E3F" "VIEW1"@"SEL$1" ("tpch10"."partsupp"@"SEL$1" "tpch10"."supplier"@"SEL$1" "tpch10"."nation"@"SEL$1" "tpch10"."region"@"SEL$1")) | | PRED_DEDUCE(@"SEL$39F0CD55") | | PRED_DEDUCE(@"SEL$2") | | SIMPLIFY_EXPR(@"SEL$ED434F45") | | PROJECT_PRUNE(@"SEL$8FEA16DB") | | OPTIMIZER_FEATURES_ENABLE('4.0.0.0') | | END_OUTLINE_DATA | | */ | | Optimization Info: | | ------------------------------------- | | region: | | table_rows:5 | | physical_range_rows:5 | | logical_range_rows:5 | | index_back_rows:0 | | output_rows:1 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[idx_9, region] | | pruned_index_name:[idx_9] | | stats version:1723039207834421 | | dynamic sampling level:0 | | nation: | | table_rows:25 | | physical_range_rows:25 | | logical_range_rows:25 | | index_back_rows:0 | | output_rows:25 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[idx_0, idx_4, idx_7, nation] | | pruned_index_name:[idx_0, idx_4] | | stats version:1723039207877494 | | dynamic sampling level:0 | | part: | | table_rows:2000000 | | physical_range_rows:2000000 | | logical_range_rows:2000000 | | index_back_rows:0 | | output_rows:16229 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[idx_2, idx_5, part] | | pruned_index_name:[idx_2, idx_5] | | stats version:1723039207773933 | | dynamic sampling level:1 | | partsupp: | | table_rows:8000000 | | physical_range_rows:4 | | logical_range_rows:4 | | index_back_rows:0 | | output_rows:4 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[idx_3, idx_8, partsupp] | | pruned_index_name:[idx_3, idx_8] | | stats version:1723039234126164 | | dynamic sampling level:0 | | supplier: | | table_rows:100000 | | physical_range_rows:100000 | | logical_range_rows:100000 | | index_back_rows:0 | | output_rows:100000 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[idx_1, idx_6, supplier] | | pruned_index_name:[idx_6] | | stats version:1723039208340302 | | dynamic sampling level:0 | | Plan Type: | | DISTRIBUTED | | Note: | | Degree of Parallelisim is 1 because of table property | | Expr Constraints: | | 1 result is TRUE | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 206 rows in set (0.172 sec)