obclient(rd_read@woqu)[cprint]> explain extended SELECT -> b.set_name, -> c.station_name, -> ( SELECT nick_name FROM t_cust_reg f WHERE f.cust_id = a.cust_id LIMIT 1 ) nick_name, -> e.mobile, -> d.code_info, -> a.create_time, -> a.finish_time, -> a.state, -> a.err_msg, -> a.pay_time -> FROM -> t_cust_ops a, -> t_set b, -> t_station c, -> t_fault_code d, -> t_cust e -> WHERE -> a.set_id = b.set_id -> AND a.station_id = c.station_id -> AND a.fault_code = d.CODE -> AND a.cust_id = e.cust_id -> ORDER BY create_time DESC LIMIT 10,10; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | =================================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ----------------------------------------------------------------------------------- | | |0 |SUBPLAN FILTER | |10 |706074 | | | |1 |├─LIMIT | |10 |705895 | | | |2 |│ └─TOP-N SORT | |20 |705895 | | | |3 |│ └─HASH JOIN | |46370 |704063 | | | |4 |│ ├─PX COORDINATOR | |335 |617 | | | |5 |│ │ └─EXCHANGE OUT DISTR |:EX10000 |335 |432 | | | |6 |│ │ └─TABLE FULL SCAN |b |335 |18 | | | |7 |│ └─HASH JOIN | |46093 |698077 | | | |8 |│ ├─TABLE FULL SCAN |d |19 |126 | | | |9 |│ └─HASH JOIN | |46093 |683056 | | | |10|│ ├─PX COORDINATOR | |2201 |5403 | | | |11|│ │ └─EXCHANGE OUT DISTR |:EX20000 |2201 |4315 | | | |12|│ │ └─TABLE FULL SCAN |c |2201 |1873 | | | |13|│ └─HASH JOIN | |46093 |671852 | | | |14|│ ├─PX COORDINATOR | |47919 |225662 | | | |15|│ │ └─EXCHANGE OUT DISTR|:EX30000 |47919 |158488 | | | |16|│ │ └─TABLE FULL SCAN |a |47919 |7397 | | | |17|│ └─TABLE FULL SCAN |e |3739414 |111158 | | | |18|└─TABLE RANGE SCAN |f(idx_cust_nick_name)|1 |18 | | | =================================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([b.set_name(0x7fa47162f380)], [c.station_name(0x7fa47162f6d0)], [subquery(1)(0x7fa47162f870)], [e.mobile(0x7fa471665440)], [d.code_info(0x7fa471665790)], | | [a.create_time(0x7fa471665ae0)], [a.finish_time(0x7fa471665e30)], [a.state(0x7fa471666180)], [a.err_msg(0x7fa4716664d0)], [a.pay_time(0x7fa471666820)]), filter(nil), rowset=256 | | exec_params_([a.cust_id(0x7fa47162ecd0)(:0(0x7fa471664a90))]), onetime_exprs_(nil), init_plan_idxs_(nil), use_batch=false | | 1 - output([a.cust_id(0x7fa47162ecd0)], [b.set_name(0x7fa47162f380)], [c.station_name(0x7fa47162f6d0)], [e.mobile(0x7fa471665440)], [d.code_info(0x7fa471665790)], | | [a.create_time(0x7fa471665ae0)], [a.finish_time(0x7fa471665e30)], [a.state(0x7fa471666180)], [a.err_msg(0x7fa4716664d0)], [a.pay_time(0x7fa471666820)]), filter(nil), rowset=256 | | limit(10(0x7fa471668bf0)), offset(10(0x7fa471668a90)) | | 2 - output([a.cust_id(0x7fa47162ecd0)], [b.set_name(0x7fa47162f380)], [c.station_name(0x7fa47162f6d0)], [e.mobile(0x7fa471665440)], [d.code_info(0x7fa471665790)], | | [a.create_time(0x7fa471665ae0)], [a.finish_time(0x7fa471665e30)], [a.state(0x7fa471666180)], [a.err_msg(0x7fa4716664d0)], [a.pay_time(0x7fa471666820)]), filter(nil), rowset=256 | | sort_keys([a.create_time(0x7fa471665ae0), DESC]), topn(10(0x7fa471668bf0) + 10(0x7fa471668a90)(0x7fa36d5af6c0)) | | 3 - output([a.cust_id(0x7fa47162ecd0)], [b.set_name(0x7fa47162f380)], [c.station_name(0x7fa47162f6d0)], [e.mobile(0x7fa471665440)], [d.code_info(0x7fa471665790)], | | [a.create_time(0x7fa471665ae0)], [a.finish_time(0x7fa471665e30)], [a.state(0x7fa471666180)], [a.err_msg(0x7fa4716664d0)], [a.pay_time(0x7fa471666820)]), filter(nil), rowset=256 | | equal_conds([a.set_id(0x7fa47162d0b0) = b.set_id(0x7fa47162d240)(0x7fa47162cf40)]), other_conds(nil) | | 4 - output([b.set_name(0x7fa47162f380)], [b.set_id(0x7fa47162d240)]), filter(nil), rowset=256 | | 5 - output([b.set_name(0x7fa47162f380)], [b.set_id(0x7fa47162d240)]), filter(nil), rowset=256 | | is_single, dop=1 | | 6 - output([b.set_id(0x7fa47162d240)], [b.set_name(0x7fa47162f380)]), filter(nil), rowset=256 | | access([b.set_id(0x7fa47162d240)], [b.set_name(0x7fa47162f380)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([b.set_id(0x7fa47162d240)]), range(MIN ; MAX)always true | | 7 - output([a.cust_id(0x7fa47162ecd0)], [c.station_name(0x7fa47162f6d0)], [e.mobile(0x7fa471665440)], [d.code_info(0x7fa471665790)], [a.create_time(0x7fa471665ae0)], | | [a.finish_time(0x7fa471665e30)], [a.state(0x7fa471666180)], [a.err_msg(0x7fa4716664d0)], [a.pay_time(0x7fa471666820)], [a.set_id(0x7fa47162d0b0)]), filter(nil), rowset=256 | | equal_conds([a.fault_code(0x7fa47162e370) = d.code(0x7fa47162e500)(0x7fa47162e200)]), other_conds(nil) | | 8 - output([d.code(0x7fa47162e500)], [d.code_info(0x7fa471665790)]), filter(nil), rowset=256 | | access([d.code(0x7fa47162e500)], [d.code_info(0x7fa471665790)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([d.fault_code_id(0x7fa471669da0)]), range(MIN ; MAX)always true | | 9 - output([a.cust_id(0x7fa47162ecd0)], [c.station_name(0x7fa47162f6d0)], [e.mobile(0x7fa471665440)], [a.create_time(0x7fa471665ae0)], [a.finish_time(0x7fa471665e30)], | | [a.state(0x7fa471666180)], [a.err_msg(0x7fa4716664d0)], [a.pay_time(0x7fa471666820)], [a.set_id(0x7fa47162d0b0)], [a.fault_code(0x7fa47162e370)]), filter(nil), rowset=256 | | equal_conds([a.station_id(0x7fa47162da10) = c.station_id(0x7fa47162dba0)(0x7fa47162d8a0)]), other_conds(nil) | | 10 - output([c.station_name(0x7fa47162f6d0)], [c.station_id(0x7fa47162dba0)]), filter(nil), rowset=256 | | 11 - output([c.station_name(0x7fa47162f6d0)], [c.station_id(0x7fa47162dba0)]), filter(nil), rowset=256 | | is_single, dop=1 | | 12 - output([c.station_id(0x7fa47162dba0)], [c.station_name(0x7fa47162f6d0)]), filter(nil), rowset=256 | | access([c.station_id(0x7fa47162dba0)], [c.station_name(0x7fa47162f6d0)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([c.station_id(0x7fa47162dba0)]), range(MIN ; MAX)always true | | 13 - output([a.cust_id(0x7fa47162ecd0)], [e.mobile(0x7fa471665440)], [a.create_time(0x7fa471665ae0)], [a.finish_time(0x7fa471665e30)], [a.state(0x7fa471666180)], | | [a.err_msg(0x7fa4716664d0)], [a.pay_time(0x7fa471666820)], [a.set_id(0x7fa47162d0b0)], [a.station_id(0x7fa47162da10)], [a.fault_code(0x7fa47162e370)]), filter(nil), rowset=256 | | equal_conds([a.cust_id(0x7fa47162ecd0) = e.cust_id(0x7fa47162ee60)(0x7fa47162eb60)]), other_conds(nil) | | 14 - output([a.cust_id(0x7fa47162ecd0)], [a.create_time(0x7fa471665ae0)], [a.finish_time(0x7fa471665e30)], [a.state(0x7fa471666180)], [a.err_msg(0x7fa4716664d0)], | | [a.pay_time(0x7fa471666820)], [a.set_id(0x7fa47162d0b0)], [a.station_id(0x7fa47162da10)], [a.fault_code(0x7fa47162e370)]), filter(nil), rowset=256 | | 15 - output([a.cust_id(0x7fa47162ecd0)], [a.create_time(0x7fa471665ae0)], [a.finish_time(0x7fa471665e30)], [a.state(0x7fa471666180)], [a.err_msg(0x7fa4716664d0)], | | [a.pay_time(0x7fa471666820)], [a.set_id(0x7fa47162d0b0)], [a.station_id(0x7fa47162da10)], [a.fault_code(0x7fa47162e370)]), filter(nil), rowset=256 | | is_single, dop=1 | | 16 - output([a.set_id(0x7fa47162d0b0)], [a.station_id(0x7fa47162da10)], [a.fault_code(0x7fa47162e370)], [a.cust_id(0x7fa47162ecd0)], [a.create_time(0x7fa471665ae0)], | | [a.finish_time(0x7fa471665e30)], [a.state(0x7fa471666180)], [a.err_msg(0x7fa4716664d0)], [a.pay_time(0x7fa471666820)]), filter(nil), rowset=256 | | access([a.set_id(0x7fa47162d0b0)], [a.station_id(0x7fa47162da10)], [a.fault_code(0x7fa47162e370)], [a.cust_id(0x7fa47162ecd0)], [a.create_time(0x7fa471665ae0)], | | [a.finish_time(0x7fa471665e30)], [a.state(0x7fa471666180)], [a.err_msg(0x7fa4716664d0)], [a.pay_time(0x7fa471666820)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([a.cust_ops_id(0x7fa471669c10)]), range(MIN ; MAX)always true | | 17 - output([e.cust_id(0x7fa47162ee60)], [e.mobile(0x7fa471665440)]), filter(nil), rowset=256 | | access([e.cust_id(0x7fa47162ee60)], [e.mobile(0x7fa471665440)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([e.cust_id(0x7fa47162ee60)]), range(MIN ; MAX)always true | | 18 - output([f.nick_name(0x7fa471664f30)]), filter(nil), rowset=256 | | access([f.nick_name(0x7fa471664f30)]), partitions(p0) | | limit(1(0x7fa4716650d0)), offset(nil), is_index_back=false, is_global_index=false, | | range_key([f.cust_id(0x7fa471664900)], [f.nick_name(0x7fa471664f30)], [f.reg_id(0x7fa471669a00)]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true, | | range_cond([f.cust_id(0x7fa471664900) = :0(0x7fa471664a90)(0x7fa471664790)]), is_fast_range = true | | Used Hint: | | ------------------------------------- | | /*+ | | | | */ | | Qb name trace: | | ------------------------------------- | | stmt_id:0, stmt_type:T_EXPLAIN | | stmt_id:1, SEL$1 | | stmt_id:2, SEL$2 | | Outline Data: | | ------------------------------------- | | /*+ | | BEGIN_OUTLINE_DATA | | LEADING(@"SEL$1" ("b"@"SEL$1" ("d"@"SEL$1" ("c"@"SEL$1" ("a"@"SEL$1" "e"@"SEL$1"))))) | | USE_HASH(@"SEL$1" ("e"@"SEL$1" "d"@"SEL$1" "c"@"SEL$1" "a"@"SEL$1")) | | PQ_DISTRIBUTE(@"SEL$1" ("e"@"SEL$1" "d"@"SEL$1" "c"@"SEL$1" "a"@"SEL$1") LOCAL LOCAL) | | FULL(@"SEL$1" "b"@"SEL$1") | | USE_HASH(@"SEL$1" ("e"@"SEL$1" "c"@"SEL$1" "a"@"SEL$1")) | | FULL(@"SEL$1" "d"@"SEL$1") | | USE_HASH(@"SEL$1" ("e"@"SEL$1" "a"@"SEL$1")) | | PQ_DISTRIBUTE(@"SEL$1" ("e"@"SEL$1" "a"@"SEL$1") LOCAL LOCAL) | | FULL(@"SEL$1" "c"@"SEL$1") | | USE_HASH(@"SEL$1" "e"@"SEL$1") | | PQ_DISTRIBUTE(@"SEL$1" "e"@"SEL$1" LOCAL LOCAL) | | FULL(@"SEL$1" "a"@"SEL$1") | | FULL(@"SEL$1" "e"@"SEL$1") | | INDEX(@"SEL$2" "f"@"SEL$2" "idx_cust_nick_name") | | OPTIMIZER_FEATURES_ENABLE('4.3.5.3') | | END_OUTLINE_DATA | | */ | | Optimization Info: | | ------------------------------------- | | b: | | table_rows:333 | | physical_range_rows:347 | | logical_range_rows:335 | | index_back_rows:0 | | output_rows:335 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[idx_1, idx_real_merchant_id, idx_total_set_id, t_set] | | pruned_index_name:[idx_1, idx_real_merchant_id, idx_total_set_id] | | stats info:[version=2025-09-14 22:01:33.278220, is_locked=0, is_expired=0] | | dynamic sampling level:0 | | estimation method:[OPTIMIZER STATISTICS, STORAGE] | | d: | | table_rows:19 | | physical_range_rows:19 | | logical_range_rows:19 | | index_back_rows:0 | | output_rows:19 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[idx_fault_code_class, idx_code, t_fault_code] | | pruned_index_name:[idx_fault_code_class] | | stats info:[version=2025-08-14 18:33:12.358954, is_locked=0, is_expired=0] | | dynamic sampling level:0 | | estimation method:[OPTIMIZER STATISTICS, STORAGE] | | c: | | table_rows:2201 | | physical_range_rows:42656 | | logical_range_rows:2201 | | index_back_rows:0 | | output_rows:2201 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[idx_set_id, t_station] | | pruned_index_name:[idx_set_id] | | stats info:[version=2025-09-16 18:44:01.865394, is_locked=0, is_expired=0] | | dynamic sampling level:0 | | estimation method:[OPTIMIZER STATISTICS, STORAGE] | | a: | | table_rows:47916 | | physical_range_rows:47919 | | logical_range_rows:47919 | | index_back_rows:0 | | output_rows:47919 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[idx_create_time, idx_set_id, idx_station_id, idx_fault_code, idx_cust_id, t_cust_ops] | | stats info:[version=2025-08-14 18:31:52.074437, is_locked=0, is_expired=0] | | dynamic sampling level:0 | | estimation method:[OPTIMIZER STATISTICS, STORAGE] | | e: | | table_rows:3724650 | | physical_range_rows:3809884 | | logical_range_rows:3739414 | | index_back_rows:0 | | output_rows:3739414 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[idx_2, idx_set_id, udx_moblie_henant, t_cust] | | pruned_index_name:[idx_2, idx_set_id, udx_moblie_henant] | | stats info:[version=2025-09-15 22:00:51.206181, is_locked=0, is_expired=0] | | dynamic sampling level:0 | | estimation method:[OPTIMIZER STATISTICS, STORAGE] | | f: | | table_rows:3752556 | | physical_range_rows:1 | | logical_range_rows:1 | | index_back_rows:0 | | output_rows:1 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[udx_1, idx_4, udx_2, udx_3, idx_cust_nick_name, t_cust_reg] | | pruned_index_name:[udx_1, idx_4, udx_2, udx_3, t_cust_reg] | | stats info:[version=2025-08-14 18:32:09.007206, is_locked=0, is_expired=0] | | dynamic sampling level:0 | | estimation method:[OPTIMIZER STATISTICS, STORAGE] | | Plan Type: | | DISTRIBUTED | | Parameters: | | :0 => 1 | | :1 => 10 | | :2 => 10 | | Note: | | Degree of Parallelisim is 1 because of table property | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 202 rows in set (0.022 sec) obclient(rd_read@woqu)[cprint]>