mysql> SELECT count(1) from -> (select AX.OC_CARR ,AX.OC_FLT_NBR ,BX.RTE_IND_NEW,BX.LEG_SEQ ,BX.DPT_AIRPT ,BX.ARRV_AIRPT ,AX.RTE -> FROM ( -> SELECT A.OC_CARR ,A.OC_FLT_NBR ,A.RTE_IND_NEW ,A.LEG_SEQ ,A.DPT_AIRPT ,A.ARRV_AIRPT -> ,GROUP_CONCAT( A.V ORDER BY A.LEG_SEQ SEPARATOR '-') AS RTE -> FROM DEV_TMP.RTE_TEMP_2 A -> GROUP BY A.OC_CARR ,A.OC_FLT_NBR ,A.RTE_IND_NEW -> )AX -> LEFT JOIN -> (SELECT * -> ,MAX(LEG_SEQ) OVER(PARTITION BY OC_CARR ,OC_FLT_NBR ,RTE_IND_NEW ORDER BY LEG_SEQ DESC) IDMAX -> FROM DEV_TMP.RTE_TEMP_2 -> )BX -> ON AX.OC_CARR=BX.OC_CARR -> AND AX.OC_FLT_NBR=BX.OC_FLT_NBR -> AND AX.RTE_IND_NEW=BX.RTE_IND_NEW -> WHERE BX.LEG_SEQ=BX.IDMAX ) -> ; +----------+ | count(1) | +----------+ | 5521470 | +----------+ 1 row in set (1 min 41.11 sec) +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ================================================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | -------------------------------------------------------------------------------------------------- | | |0 |SCALAR GROUP BY | |1 |5215278 | | | |1 |└─PX COORDINATOR | |1 |5215278 | | | |2 | └─EXCHANGE OUT DISTR |:EX10000 |1 |5215278 | | | |3 | └─MERGE GROUP BY | |1 |5215278 | | | |4 | └─NESTED-LOOP JOIN | |1 |5215278 | | | |5 | ├─SUBPLAN SCAN |BX |1 |5205667 | | | |6 | │ └─PX PARTITION ITERATOR | |6018477 |5138710 | | | |7 | │ └─WINDOW FUNCTION | |6018477 |5138710 | | | |8 | │ └─TABLE FULL SCAN |rte_temp_2(index_name,Reverse)|6018477 |360230 | | | |9 | └─SUBPLAN SCAN |AX |1 |9610 | | | |10| └─DISTRIBUTED TABLE RANGE SCAN|A(index_name) |1 |9610 | | | ================================================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([T_FUN_COUNT_SUM(T_FUN_COUNT(*)(0x7f59fc674490))(0x7f6805e756b0)]), filter(nil), rowset=256 | | group(nil), agg_func([T_FUN_COUNT_SUM(T_FUN_COUNT(*)(0x7f59fc674490))(0x7f6805e756b0)]) | | 1 - output([T_FUN_COUNT(*)(0x7f59fc674490)]), filter(nil), rowset=256 | | 2 - output([T_FUN_COUNT(*)(0x7f59fc674490)]), filter(nil), rowset=256 | | dop=1 | | 3 - output([T_FUN_COUNT(*)(0x7f59fc674490)]), filter(nil), rowset=256 | | group(nil), agg_func([T_FUN_COUNT(*)(0x7f59fc674490)]) | | 4 - output(nil), filter(nil), rowset=256 | | conds(nil), nl_params_([BX.RTE_IND_NEW(0x7f59fc6704c0)(:3)], [BX.OC_CARR(0x7f59fc66da60)(:4)], [BX.OC_FLT_NBR(0x7f59fc66ef90)(:5)]), use_batch=false | | 5 - output([BX.OC_CARR(0x7f59fc66da60)], [BX.OC_FLT_NBR(0x7f59fc66ef90)], [BX.RTE_IND_NEW(0x7f59fc6704c0)]), filter([BX.LEG_SEQ(0x7f59fc671710) = BX.IDMAX(0x7f59fc6719f0)(0x7f59fc671010)]), rowset=256 | | access([BX.OC_CARR(0x7f59fc66da60)], [BX.OC_FLT_NBR(0x7f59fc66ef90)], [BX.RTE_IND_NEW(0x7f59fc6704c0)], [BX.LEG_SEQ(0x7f59fc671710)], [BX.IDMAX(0x7f59fc6719f0)]) | | 6 - output([rte_temp_2.OC_CARR(0x7f59fc665540)], [rte_temp_2.OC_FLT_NBR(0x7f59fc665810)], [rte_temp_2.RTE_IND_NEW(0x7f59fc665df0)], [rte_temp_2.LEG_SEQ(0x7f59fc6660c0)], | | [T_FUN_MAX(rte_temp_2.LEG_SEQ(0x7f59fc6660c0))(0x7f59fc667300)(0x7f59fc666ee0)]), filter(nil), rowset=256 | | partition wise, force partition granule | | 7 - output([rte_temp_2.OC_CARR(0x7f59fc665540)], [rte_temp_2.OC_FLT_NBR(0x7f59fc665810)], [rte_temp_2.RTE_IND_NEW(0x7f59fc665df0)], [rte_temp_2.LEG_SEQ(0x7f59fc6660c0)], | | [T_FUN_MAX(rte_temp_2.LEG_SEQ(0x7f59fc6660c0))(0x7f59fc667300)(0x7f59fc666ee0)]), filter(nil), rowset=256 | | win_expr(T_FUN_MAX(rte_temp_2.LEG_SEQ(0x7f59fc6660c0))(0x7f59fc667300)(0x7f59fc666ee0)), partition_by([rte_temp_2.OC_CARR(0x7f59fc665540)], [rte_temp_2.OC_FLT_NBR(0x7f59fc665810)], | | [rte_temp_2.RTE_IND_NEW(0x7f59fc665df0)]), order_by([rte_temp_2.LEG_SEQ(0x7f59fc6660c0), DESC]), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(CURRENT | | ROW) | | 8 - output([rte_temp_2.OC_CARR(0x7f59fc665540)], [rte_temp_2.OC_FLT_NBR(0x7f59fc665810)], [rte_temp_2.RTE_IND_NEW(0x7f59fc665df0)], [rte_temp_2.LEG_SEQ(0x7f59fc6660c0)]), filter(nil), rowset=256 | | access([rte_temp_2.OC_CARR(0x7f59fc665540)], [rte_temp_2.OC_FLT_NBR(0x7f59fc665810)], [rte_temp_2.RTE_IND_NEW(0x7f59fc665df0)], [rte_temp_2.LEG_SEQ(0x7f59fc6660c0)]), partitions(p[0-479]) | | is_index_back=false, is_global_index=false, | | range_key([rte_temp_2.OC_CARR(0x7f59fc665540)], [rte_temp_2.OC_FLT_NBR(0x7f59fc665810)], [rte_temp_2.RTE_IND_NEW(0x7f59fc665df0)], [rte_temp_2.LEG_SEQ(0x7f59fc6660c0)], | | [rte_temp_2.__pk_increment(0x7f59fc675870)]), range(MIN,MIN,MIN,MIN,MIN ; MAX,MAX,MAX,MAX,MAX)always true | | 9 - output(nil), filter(nil), rowset=256 | | access(nil) | | 10 - output([A.OC_CARR(0x7f59fc64c210)], [A.OC_FLT_NBR(0x7f59fc64c4e0)], [A.RTE_IND_NEW(0x7f59fc64d120)]), filter(nil), rowset=256 | | access([A.OC_CARR(0x7f59fc64c210)], [A.OC_FLT_NBR(0x7f59fc64c4e0)], [A.RTE_IND_NEW(0x7f59fc64d120)]), partitions(p[0-479]) | | limit(1), offset(nil), is_index_back=false, is_global_index=false, | | range_key([A.OC_CARR(0x7f59fc64c210)], [A.OC_FLT_NBR(0x7f59fc64c4e0)], [A.RTE_IND_NEW(0x7f59fc64d120)], [A.LEG_SEQ(0x7f6bd1dca2d0)], [A.__pk_increment(0x7f59fc675420)]), | | range(MIN,MIN,MIN,MIN,MIN ; MAX,MAX,MAX,MAX,MAX)always true, | | range_cond([A.RTE_IND_NEW(0x7f59fc64d120) = :3(0x7f6bd1cefe20)], [A.OC_CARR(0x7f59fc64c210) = :4(0x7f6bd1cf0650)], [A.OC_FLT_NBR(0x7f59fc64c4e0) = | | :5(0x7f6bd1cf0e80)]) | | Used Hint: | | ------------------------------------- | | /*+ | | | | */ | | Qb name trace: | | ------------------------------------- | | stmt_id:0, stmt_type:T_EXPLAIN | | stmt_id:1, SEL$1 > SEL$EA493D33 > SEL$0EE93CA6 > SEL$D3A5388B | | stmt_id:2, SEL$2 > SEL$89B3808A | | stmt_id:3, SEL$3 > SEL$3E84DA65 > SEL$50E3642C | | stmt_id:4, SEL$4 > SEL$3656C1FA | | Outline Data: | | ------------------------------------- | | /*+ | | BEGIN_OUTLINE_DATA | | GBY_PUSHDOWN(@"SEL$D3A5388B") | | LEADING(@"SEL$D3A5388B" ("BX"@"SEL$2" "AX"@"SEL$2")) | | USE_NL(@"SEL$D3A5388B" "AX"@"SEL$2") | | PQ_DISTRIBUTE(@"SEL$D3A5388B" "AX"@"SEL$2" NONE ALL) | | PQ_DISTRIBUTE_WINDOW(@"SEL$3656C1FA" (0) NONE) | | INDEX(@"SEL$3656C1FA" "dev_tmp"."rte_temp_2"@"SEL$4" "index_name") | | INDEX(@"SEL$50E3642C" "A"@"SEL$3" "index_name") | | USE_DAS(@"SEL$50E3642C" "A"@"SEL$3") | | SIMPLIFY_GROUP_BY(@"SEL$1") | | PROJECT_PRUNE(@"SEL$2") | | PROJECT_PRUNE(@"SEL$3") | | PROJECT_PRUNE(@"SEL$4") | | MERGE(@"SEL$89B3808A" > "SEL$EA493D33") | | OUTER_TO_INNER(@"SEL$0EE93CA6") | | SIMPLIFY_GROUP_BY(@"SEL$3E84DA65") | | OPTIMIZER_FEATURES_ENABLE('4.0.0.0') | | END_OUTLINE_DATA | | */ | | Optimization Info: | | ------------------------------------- | | rte_temp_2: | | table_rows:6018477 | | physical_range_rows:6018477 | | logical_range_rows:6018477 | | index_back_rows:0 | | output_rows:6018477 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[index_name, rte_temp_2] | | pruned_index_name:[rte_temp_2] | | stats version:1696912418744268 | | dynamic sampling level:0 | | A: | | table_rows:6018477 | | physical_range_rows:1 | | logical_range_rows:1 | | index_back_rows:0 | | output_rows:1 | | table_dop:1 | | dop_method:DAS DOP | | avaiable_index_name:[index_name, rte_temp_2] | | pruned_index_name:[rte_temp_2] | | stats version:1696912418744268 | | dynamic sampling level:0 | | Plan Type: | | DISTRIBUTED | | Note: | | Degree of Parallelisim is 1 because of table property | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 114 rows in set (0.02 sec) YB420AA02224-00060758381FA034-0-0 mysql> mysql> * from oceanbase.gv$ob_sql_audit where trace_id='YB420AA02224-0006075830CFA032-0-0' and SVR_IP='10.160.34.36' and svr_port=2882 and sqlselect * from gv$ob_processlist where state='active'; +--------------+----------+----------+------------+-------------+--------------------+-----------+--------+---------+------+------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------+-----------+-----------+------------+----------------------------------+-----------+-----------+------------+-----------------------------------+-----------------+--------+--------+-------------+ | SVR_IP | SVR_PORT | SQL_PORT | ID | USER | HOST | DB | TENANT | COMMAND | TIME | TOTAL_TIME | STATE | INFO | PROXY_SESSID | MASTER_SESSID | USER_CLIENT_IP | USER_HOST | RETRY_CNT | RETRY_INFO | SQL_ID | TRANS_ID | THREAD_ID | SSL_CIPHER | TRACE_ID | TRANS_STATE | ACTION | MODULE | CLIENT_INFO | +--------------+----------+----------+------------+-------------+--------------------+-----------+--------+---------+------+------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------+-----------+-----------+------------+----------------------------------+-----------+-----------+------------+-----------------------------------+-----------------+--------+--------+-------------+ | 10.160.34.36 | 2882 | 2881 | 3221666838 | root | 10.160.34.36:21494 | oceanbase | tnt_nh | Query | 4 | 4 | ACTIVE | SELECT count(1) from (select AX.OC_CARR ,AX.OC_FLT_NBR ,BX.RTE_IND_NEW,BX.LEG_SEQ ,BX.DPT_AIRPT ,BX.ARRV_AIRPT ,AX.RTE FROM ( SELECT A.OC_CARR ,A.OC_FLT_NBR ,A.RTE_IND_NEW ,A.LEG_SEQ ,A.DPT_AIRPT ,A.ARRV_AIRPT ,GROUP_CONCAT( A.V ORDER BY A.LEG_SEQ SEPARATOR '-') AS RTE FROM DEV_TMP.RTE_TEMP_2 A GROUP BY A.OC_CARR ,A.OC_FLT_NBR ,A.RTE_IND_NEW )AX LEFT JOIN (SELECT * ,MAX(LEG_SEQ) OVER(PARTITION BY OC_CARR ,OC_FLT_NBR ,RTE_IND_NEW ORDER BY LEG_SEQ DESC) IDMAX FROM DEV_TMP.RTE_TEMP_2 )BX ON AX.OC_CARR=BX.OC_CARR AND AX.OC_FLT_NBR=BX.OC_FLT_NBR AND AX.RTE_IND_NEW=BX.RTE_IND_NEW WHERE BX.LEG_SEQ=BX.IDMAX ) | 765649474856092372 | NULL | 10.160.34.35 | % | 1 | -4023 | 7C6A755ECC4E724F3F96192B634F6B4C | 0 | 88622 | NULL | YB420AA02224-00060758283FA03E-0-0 | | | | | +--------------+----------+----------+------------+-------------+--------------------+-----------+--------+---------+------+------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------+-----------+-----------+------------+----------------------------------+-----------+-----------+------------+-----------------------------------+-----------------+--------+--------+-------------+ mysql> select * from oceanbase.gv$ob_sql_audit where trace_id='YB420AA02224-00060758283FA03E-0-0' and SVR_IP='10.160.34.36' and svr_port=2882 and sql_id='7C6A755ECC4E724F3F96192B634F6B4C' and ELAPSED_TIME >10000000; +--------------+----------+------------+-------------+-----------------------------------+------------+--------------+-------------+-----------+-------------+---------------------+---------+-----------+------------+----------------+--------+-----------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+-------------+---------------+----------+-------+--------+--------+-----------+------------------------+---------+-----------------+--------+------+--------+------+-------+---------------+-------------+-------------+--------------------+-----------------+-----------------------+-------------+-----------+-----------+--------------+-----------------+-------------+------------------+--------------+----------+---------------+------------+-------------+---------------+--------------+-----------------------+-----------------------+-------------------+---------------+---------------+------------------------+-----------------+------------+-----------+------------+-------------------+-------------------------+------------------------+---------------------+----------------------+----------------------+-----------------------+---------------------+-------------------+---------------------------------+---------------------+-----------------------+-------------------+------------+--------------------+-------------------+------------------+-------+---------------------+--------------+-----------------------+---------------+----------------------+--------------------+--------------+-----------+---------------+---------------------+------------------+ | SVR_IP | SVR_PORT | REQUEST_ID | SQL_EXEC_ID | TRACE_ID | SID | CLIENT_IP | CLIENT_PORT | TENANT_ID | TENANT_NAME | EFFECTIVE_TENANT_ID | USER_ID | USER_NAME | USER_GROUP | USER_CLIENT_IP | DB_ID | DB_NAME | SQL_ID | QUERY_SQL | PLAN_ID | AFFECTED_ROWS | RETURN_ROWS | PARTITION_CNT | RET_CODE | QC_ID | DFO_ID | SQC_ID | WORKER_ID | EVENT | P1TEXT | P1 | P2TEXT | P2 | P3TEXT | P3 | LEVEL | WAIT_CLASS_ID | WAIT_CLASS# | WAIT_CLASS | STATE | WAIT_TIME_MICRO | TOTAL_WAIT_TIME_MICRO | TOTAL_WAITS | RPC_COUNT | PLAN_TYPE | IS_INNER_SQL | IS_EXECUTOR_RPC | IS_HIT_PLAN | REQUEST_TIME | ELAPSED_TIME | NET_TIME | NET_WAIT_TIME | QUEUE_TIME | DECODE_TIME | GET_PLAN_TIME | EXECUTE_TIME | APPLICATION_WAIT_TIME | CONCURRENCY_WAIT_TIME | USER_IO_WAIT_TIME | SCHEDULE_TIME | ROW_CACHE_HIT | BLOOM_FILTER_CACHE_HIT | BLOCK_CACHE_HIT | DISK_READS | RETRY_CNT | TABLE_SCAN | CONSISTENCY_LEVEL | MEMSTORE_READ_ROW_COUNT | SSSTORE_READ_ROW_COUNT | DATA_BLOCK_READ_CNT | DATA_BLOCK_CACHE_HIT | INDEX_BLOCK_READ_CNT | INDEX_BLOCK_CACHE_HIT | BLOCKSCAN_BLOCK_CNT | BLOCKSCAN_ROW_CNT | PUSHDOWN_STORAGE_FILTER_ROW_CNT | REQUEST_MEMORY_USED | EXPECTED_WORKER_COUNT | USED_WORKER_COUNT | SCHED_INFO | FUSE_ROW_CACHE_HIT | PS_CLIENT_STMT_ID | PS_INNER_STMT_ID | TX_ID | SNAPSHOT_VERSION | REQUEST_TYPE | IS_BATCHED_MULTI_STMT | OB_TRACE_INFO | PLAN_HASH | LOCK_FOR_READ_TIME | PARAMS_VALUE | RULE_NAME | PARTITION_HIT | TX_INTERNAL_ROUTING | TX_STATE_VERSION | +--------------+----------+------------+-------------+-----------------------------------+------------+--------------+-------------+-----------+-------------+---------------------+---------+-----------+------------+----------------+--------+-----------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+-------------+---------------+----------+-------+--------+--------+-----------+------------------------+---------+-----------------+--------+------+--------+------+-------+---------------+-------------+-------------+--------------------+-----------------+-----------------------+-------------+-----------+-----------+--------------+-----------------+-------------+------------------+--------------+----------+---------------+------------+-------------+---------------+--------------+-----------------------+-----------------------+-------------------+---------------+---------------+------------------------+-----------------+------------+-----------+------------+-------------------+-------------------------+------------------------+---------------------+----------------------+----------------------+-----------------------+---------------------+-------------------+---------------------------------+---------------------+-----------------------+-------------------+------------+--------------------+-------------------+------------------+-------+---------------------+--------------+-----------------------+---------------+----------------------+--------------------+--------------+-----------+---------------+---------------------+------------------+ | 10.160.34.36 | 2882 | 75429927 | 96351136 | YB420AA02224-00060758283FA03E-0-0 | 3221691424 | 10.160.34.36 | 21494 | 1002 | | 1002 | 200001 | | 0 | 10.160.34.35 | 201001 | | 7C6A755ECC4E724F3F96192B634F6B4C | | 4411 | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | | | 0 | | 0 | | 0 | 0 | 100 | 0 | OTHER | MAX_WAIT TIME ZERO | 0 | 0 | 0 | 0 | 3 | 0 | 1 | 1 | 1697003638082399 | 96913142 | 0 | 0 | 0 | 0 | 0 | 96913142 | 0 | 0 | 0 | 0 | 0 | 0 | 5508580 | 0 | 0 | 0 | -1 | 0 | 5752807 | 2754987 | 2754987 | 5507186 | 2753593 | 217 | 1312230 | 0 | 5023832 | 0 | 0 | NULL | 0 | -1 | -1 | 0 | 0 | 4 | 0 | NULL | 0 | 0 | | | 1 | 0 | 0 | | 10.160.34.36 | 2882 | 75429997 | 96317505 | YB420AA02224-00060758283FA03E-0-0 | 3221666838 | 10.160.34.36 | 21494 | 1002 | tnt_nh | 1002 | 200001 | root | 100 | 10.160.34.35 | 201001 | oceanbase | 7C6A755ECC4E724F3F96192B634F6B4C | SELECT count(1) from (select AX.OC_CARR ,AX.OC_FLT_NBR ,BX.RTE_IND_NEW,BX.LEG_SEQ ,BX.DPT_AIRPT ,BX.ARRV_AIRPT ,AX.RTE FROM ( SELECT A.OC_CARR ,A.OC_FLT_NBR ,A.RTE_IND_NEW ,A.LEG_SEQ ,A.DPT_AIRPT ,A.ARRV_AIRPT ,GROUP_CONCAT( A.V ORDER BY A.LEG_SEQ SEPARATOR '-') AS RTE FROM DEV_TMP.RTE_TEMP_2 A GROUP BY A.OC_CARR ,A.OC_FLT_NBR ,A.RTE_IND_NEW )AX LEFT JOIN (SELECT * ,MAX(LEG_SEQ) OVER(PARTITION BY OC_CARR ,OC_FLT_NBR ,RTE_IND_NEW ORDER BY LEG_SEQ DESC) IDMAX FROM DEV_TMP.RTE_TEMP_2 )BX ON AX.OC_CARR=BX.OC_CARR AND AX.OC_FLT_NBR=BX.OC_FLT_NBR AND AX.RTE_IND_NEW=BX.RTE_IND_NEW WHERE BX.LEG_SEQ=BX.IDMAX ) | 4411 | 0 | 1 | 480 | 0 | 0 | 0 | 0 | 0 | px loop condition wait | address | 140154120264832 | | 0 | | 0 | 0 | 104 | 4 | CONCURRENCY | WAITED SHORT TIME | 4455 | 97645562 | 93255 | 0 | 3 | 0 | 0 | 1 | 1697003638073145 | 97702427 | 0 | 3 | 150 | 2 | 5536 | 97696556 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2526924 | 0 | 0 | NULL | 0 | -1 | -1 | 0 | 1697003638079028352 | 2 | 0 | NULL | 15528409201006426418 | 0 | | | 1 | 0 | 0 | +--------------+----------+------------+-------------+-----------------------------------+------------+--------------+-------------+-----------+-------------+---------------------+---------+-----------+------------+----------------+--------+-----------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+-------------+---------------+----------+-------+--------+--------+-----------+------------------------+---------+-----------------+--------+------+--------+------+-------+---------------+-------------+-------------+--------------------+-----------------+-----------------------+-------------+-----------+-----------+--------------+-----------------+-------------+------------------+--------------+----------+---------------+------------+-------------+---------------+--------------+-----------------------+-----------------------+-------------------+---------------+---------------+------------------------+-----------------+------------+-----------+------------+-------------------+-------------------------+------------------------+---------------------+----------------------+----------------------+-----------------------+---------------------+-------------------+---------------------------------+---------------------+-----------------------+-------------------+------------+--------------------+-------------------+------------------+-------+---------------------+--------------+-----------------------+---------------+----------------------+--------------------+--------------+-----------+---------------+---------------------+------------------+ 2 rows in set (33.50 sec) mysql> select svr_ip,plan_depth,plan_line_id,operator,name,rows,cost,property from gv$ob_plan_cache_plan_explain where tenant_id=1002 and svr_ip='10.160.34.36' and svr_port=2882 and plan_id=4411 ; +--------------+------------+--------------+----------------------------+------------------------+---------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | svr_ip | plan_depth | plan_line_id | operator | name | rows | cost | property | +--------------+------------+--------------+----------------------------+------------------------+---------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 10.160.34.36 | 0 | 0 | PHY_SCALAR_AGGREGATE | NULL | 1 | 5215277 | NULL | | 10.160.34.36 | 1 | 1 | PHY_PX_FIFO_COORD | NULL | 1 | 5215277 | NULL | | 10.160.34.36 | 2 | 2 | PHY_PX_REDUCE_TRANSMIT | NULL | 1 | 5215277 | NULL | | 10.160.34.36 | 3 | 3 | PHY_MERGE_GROUP_BY | NULL | 1 | 5215277 | NULL | | 10.160.34.36 | 4 | 4 | PHY_NESTED_LOOP_JOIN | NULL | 1 | 5215277 | NULL | | 10.160.34.36 | 5 | 5 | PHY_SUBPLAN_SCAN | NULL | 1 | 5205666 | NULL | | 10.160.34.36 | 6 | 6 | PHY_GRANULE_ITERATOR | NULL | 6018477 | 5138709 | NULL | | 10.160.34.36 | 7 | 7 | PHY_WINDOW_FUNCTION | NULL | 6018477 | 5138709 | NULL | | 10.160.34.36 | 8 | 8 | PHY_TABLE_SCAN | rte_temp_2(index_name) | 6018477 | 360229 | table_rows:6018477, physical_range_rows:6018477, logical_range_rows:6018477, index_back_rows:0, output_rows:6018477, avaiable_index_name[index_name,rte_temp_2], pruned_index_name[rte_temp_2] | | 10.160.34.36 | 5 | 9 | PHY_SUBPLAN_SCAN | NULL | 1 | 9609 | NULL | | 10.160.34.36 | 6 | 10 | PHY_TABLE_SCAN | A(index_name) | 1 | 9609 | table_rows:6018477, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, avaiable_index_name[index_name,rte_temp_2], pruned_index_name[rte_temp_2] | +--------------+------------+--------------+----------------------------+------------------------+---------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 11 rows in set (0.00 sec) mysql> show trace; +----------------------------------------------+----------------------------+---------------+ | Operation | StartTime | ElapseTime | +----------------------------------------------+----------------------------+---------------+ | ob_proxy | 2023-10-11 14:07:15.365841 | 188099.088 ms | | ├── ob_proxy_partition_location_lookup | 2023-10-11 14:07:15.365911 | 0.140 ms | | ├── ob_proxy_server_process_req | 2023-10-11 14:07:15.366139 | 188098.117 ms | | └── com_query_process | 2023-10-11 14:07:15.369671 | 188094.752 ms | | └── mpquery_single_stmt | 2023-10-11 14:07:15.369702 | 188094.706 ms | | └── sql_execute | 2023-10-11 14:07:15.372260 | 188091.975 ms | | ├── response_result | 2023-10-11 14:07:15.372410 | 188091.500 ms | | │ └── px_schedule | 2023-10-11 14:10:23.463749 | 0.001 ms | | └── close | 2023-10-11 14:10:23.463925 | 0.111 ms | | └── end_transaction | 2023-10-11 14:10:23.464011 | 0.006 ms | +----------------------------------------------+----------------------------+---------------+ 3.X版本执行计划 ==================================================================== |ID|OPERATOR |NAME |EST. ROWS|COST | -------------------------------------------------------------------- |0 |PX COORDINATOR | |1 |8061987| |1 | EXCHANGE OUT DISTR |:EX10001 |1 |8061987| |2 | INSERT | |1 |8061987| |3 | EXCHANGE IN DISTR | |1 |8061987| |4 | EXCHANGE OUT DISTR (PKEY HASH)|:EX10000 |1 |8061987| |5 | SUBPLAN SCAN |VIEW1 |1 |8061987| |6 | PX PARTITION ITERATOR | |1 |8061987| |7 | MERGE GROUP BY | |1 |8061987| |8 | SORT | |1 |8061987| |9 | NESTED-LOOP JOIN | |1 |8061986| |10| SUBPLAN SCAN |BX |1 |8061960| |11| WINDOW FUNCTION | |7429440 |7855745| |12| SORT | |7429440 |3910002| |13| TABLE SCAN |rte_temp_2|7429440 |2873743| |14| TABLE SCAN |A |1 |25 | ==================================================================== Outputs & filters: ------------------------------------- 0 - output(nil), filter(nil), rowset=256 1 - output(nil), filter(nil), rowset=256, dop=32 2 - output(nil), filter(nil), columns([{rte_tmp: ({rte_tmp: (rte_tmp.OC_CARR(0xffb71e750000), rte_tmp.OC_FLT_NBR(0xffb71e750300), rte_tmp.RTE_IND_NEW(0xffb71e750600), rte_tmp.__pk_increment(0xffb71e842030), rte_tmp.LEG_SEQ(0xffb71e750960), rte_tmp.DPT_AIRPT(0xffb71e750c60), rte_tmp.ARRV_AIRPT(0xffb71e750f60), rte_tmp.RTE(0xffb71e751260))})}]), partitions(p[0-99]), conv_exprs([column_conv(CHAR,utf8mb4_general_ci,length:2,NULL,VIEW1.OC_CARR(0xffb71e83e460))(0xffb71e842360)], [column_conv(CHAR,utf8mb4_general_ci,length:4,NULL,VIEW1.OC_FLT_NBR(0xffb71e83e760))(0xffb71e8499c0)], [column_conv(INT,PS:(11,0),NULL,VIEW1.RTE_IND_NEW(0xffb71e83ea60))(0xffb71e851020)], [column_conv(BIGINT UNSIGNED,PS:(-1,-1),NOT NULL,nextval(1)(0xffb71e858650))(0xffb71e859170)], [column_conv(INT,PS:(11,0),NULL,VIEW1.LEG_SEQ(0xffb71e83ed60))(0xffb71e8607f0)], [column_conv(CHAR,utf8mb4_general_ci,length:3,NULL,VIEW1.DPT_AIRPT(0xffb71e83f060))(0xffb71e867e50)], [column_conv(CHAR,utf8mb4_general_ci,length:3,NULL,VIEW1.ARRV_AIRPT(0xffb71e83f360))(0xffb71e86f4b0)], [column_conv(VARCHAR,utf8mb4_general_ci,length:200,NULL,VIEW1.RTE(0xffb71e83f660))(0xffb71e876b10)]) 3 - output([column_conv(CHAR,utf8mb4_general_ci,length:2,NULL,VIEW1.OC_CARR(0xffb71e83e460))(0xffb71e842360)], [column_conv(CHAR,utf8mb4_general_ci,length:4,NULL,VIEW1.OC_FLT_NBR(0xffb71e83e760))(0xffb71e8499c0)], [column_conv(INT,PS:(11,0),NULL,VIEW1.RTE_IND_NEW(0xffb71e83ea60))(0xffb71e851020)], [column_conv(BIGINT UNSIGNED,PS:(-1,-1),NOT NULL,nextval(1)(0xffb71e858650))(0xffb71e859170)], [column_conv(INT,PS:(11,0),NULL,VIEW1.LEG_SEQ(0xffb71e83ed60))(0xffb71e8607f0)], [column_conv(CHAR,utf8mb4_general_ci,length:3,NULL,VIEW1.DPT_AIRPT(0xffb71e83f060))(0xffb71e867e50)], [column_conv(CHAR,utf8mb4_general_ci,length:3,NULL,VIEW1.ARRV_AIRPT(0xffb71e83f360))(0xffb71e86f4b0)], [column_conv(VARCHAR,utf8mb4_general_ci,length:200,NULL,VIEW1.RTE(0xffb71e83f660))(0xffb71e876b10)], [PARTITION_ID(0xffc60be0b350)]), filter(nil), rowset=256 4 - (#keys=3, [column_conv(CHAR,utf8mb4_general_ci,length:2,NULL,VIEW1.OC_CARR(0xffb71e83e460))(0xffb71e842360), CHAR, 2], [column_conv(CHAR,utf8mb4_general_ci,length:4,NULL,VIEW1.OC_FLT_NBR(0xffb71e83e760))(0xffb71e8499c0), CHAR, 4], [column_conv(INT,PS:(11,0),NULL,VIEW1.RTE_IND_NEW(0xffb71e83ea60))(0xffb71e851020)]), output([column_conv(CHAR,utf8mb4_general_ci,length:2,NULL,VIEW1.OC_CARR(0xffb71e83e460))(0xffb71e842360)], [column_conv(CHAR,utf8mb4_general_ci,length:4,NULL,VIEW1.OC_FLT_NBR(0xffb71e83e760))(0xffb71e8499c0)], [column_conv(INT,PS:(11,0),NULL,VIEW1.RTE_IND_NEW(0xffb71e83ea60))(0xffb71e851020)], [column_conv(BIGINT UNSIGNED,PS:(-1,-1),NOT NULL,nextval(1)(0xffb71e858650))(0xffb71e859170)], [column_conv(INT,PS:(11,0),NULL,VIEW1.LEG_SEQ(0xffb71e83ed60))(0xffb71e8607f0)], [column_conv(CHAR,utf8mb4_general_ci,length:3,NULL,VIEW1.DPT_AIRPT(0xffb71e83f060))(0xffb71e867e50)], [column_conv(CHAR,utf8mb4_general_ci,length:3,NULL,VIEW1.ARRV_AIRPT(0xffb71e83f360))(0xffb71e86f4b0)], [column_conv(VARCHAR,utf8mb4_general_ci,length:200,NULL,VIEW1.RTE(0xffb71e83f660))(0xffb71e876b10)], [PARTITION_ID(0xffc60be0b350)]), filter(nil), rowset=256, dop=32 5 - output([column_conv(CHAR,utf8mb4_general_ci,length:2,NULL,VIEW1.OC_CARR(0xffb71e83e460))(0xffb71e842360)], [column_conv(CHAR,utf8mb4_general_ci,length:4,NULL,VIEW1.OC_FLT_NBR(0xffb71e83e760))(0xffb71e8499c0)], [column_conv(INT,PS:(11,0),NULL,VIEW1.RTE_IND_NEW(0xffb71e83ea60))(0xffb71e851020)], [column_conv(BIGINT UNSIGNED,PS:(-1,-1),NOT NULL,nextval(1)(0xffb71e858650))(0xffb71e859170)], [column_conv(INT,PS:(11,0),NULL,VIEW1.LEG_SEQ(0xffb71e83ed60))(0xffb71e8607f0)], [column_conv(CHAR,utf8mb4_general_ci,length:3,NULL,VIEW1.DPT_AIRPT(0xffb71e83f060))(0xffb71e867e50)], [column_conv(CHAR,utf8mb4_general_ci,length:3,NULL,VIEW1.ARRV_AIRPT(0xffb71e83f360))(0xffb71e86f4b0)], [column_conv(VARCHAR,utf8mb4_general_ci,length:200,NULL,VIEW1.RTE(0xffb71e83f660))(0xffb71e876b10)]), filter(nil), rowset=256, access([VIEW1.OC_CARR(0xffb71e83e460)], [VIEW1.OC_FLT_NBR(0xffb71e83e760)], [VIEW1.RTE_IND_NEW(0xffb71e83ea60)], [VIEW1.LEG_SEQ(0xffb71e83ed60)], [VIEW1.DPT_AIRPT(0xffb71e83f060)], [VIEW1.ARRV_AIRPT(0xffb71e83f360)], [VIEW1.RTE(0xffb71e83f660)]) 6 - output([A.OC_CARR(0xff4a424448a0)], [A.OC_FLT_NBR(0xff4a42444bd0)], [BX.RTE_IND_NEW(0xff4a42443580)], [BX.LEG_SEQ(0xff4a424438b0)], [BX.DPT_AIRPT(0xff4a42443be0)], [BX.ARRV_AIRPT(0xff4a42443f10)], [T_FUN_GROUP_CONCAT(A.V(0xff4a42445560)) order_items(A.LEG_SEQ(0xff4a42445230)) separator_param_expr('-')(0xff4a42448300)]), filter(nil), rowset=256, partition wise, force partition granule. 7 - output([A.OC_CARR(0xff4a424448a0)], [A.OC_FLT_NBR(0xff4a42444bd0)], [BX.RTE_IND_NEW(0xff4a42443580)], [BX.LEG_SEQ(0xff4a424438b0)], [BX.DPT_AIRPT(0xff4a42443be0)], [BX.ARRV_AIRPT(0xff4a42443f10)], [T_FUN_GROUP_CONCAT(A.V(0xff4a42445560)) order_items(A.LEG_SEQ(0xff4a42445230)) separator_param_expr('-')(0xff4a42448300)]), filter(nil), rowset=256, group([BX.OC_CARR(0xff4a42442f20)], [BX.OC_FLT_NBR(0xff4a42443250)], [BX.rte_temp_2.__pk_increment(0xff4a42444570)]), agg_func([T_FUN_GROUP_CONCAT(A.V(0xff4a42445560)) order_items(A.LEG_SEQ(0xff4a42445230)) separator_param_expr('-')(0xff4a42448300)]) 8 - output([A.OC_CARR(0xff4a424448a0)], [A.OC_FLT_NBR(0xff4a42444bd0)], [BX.RTE_IND_NEW(0xff4a42443580)], [BX.LEG_SEQ(0xff4a424438b0)], [BX.DPT_AIRPT(0xff4a42443be0)], [BX.ARRV_AIRPT(0xff4a42443f10)], [A.V(0xff4a42445560)], [A.LEG_SEQ(0xff4a42445230)], [BX.OC_CARR(0xff4a42442f20)], [BX.OC_FLT_NBR(0xff4a42443250)], [BX.rte_temp_2.__pk_increment(0xff4a42444570)]), filter(nil), rowset=256, sort_keys([BX.OC_CARR(0xff4a42442f20), ASC], [BX.OC_FLT_NBR(0xff4a42443250), ASC], [BX.rte_temp_2.__pk_increment(0xff4a42444570), ASC]), prefix_pos(2) 9 - output([A.OC_CARR(0xff4a424448a0)], [A.OC_FLT_NBR(0xff4a42444bd0)], [BX.RTE_IND_NEW(0xff4a42443580)], [BX.LEG_SEQ(0xff4a424438b0)], [BX.DPT_AIRPT(0xff4a42443be0)], [BX.ARRV_AIRPT(0xff4a42443f10)], [A.V(0xff4a42445560)], [A.LEG_SEQ(0xff4a42445230)], [BX.OC_CARR(0xff4a42442f20)], [BX.OC_FLT_NBR(0xff4a42443250)], [BX.rte_temp_2.__pk_increment(0xff4a42444570)]), filter(nil), rowset=256, conds(nil), nl_params_([BX.RTE_IND_NEW(0xff4a42443580)], [BX.OC_CARR(0xff4a42442f20)], [BX.OC_FLT_NBR(0xff4a42443250)]), batch_join=false 10 - output([BX.OC_CARR(0xff4a42442f20)], [BX.OC_FLT_NBR(0xff4a42443250)], [BX.RTE_IND_NEW(0xff4a42443580)], [BX.LEG_SEQ(0xff4a424438b0)], [BX.DPT_AIRPT(0xff4a42443be0)], [BX.ARRV_AIRPT(0xff4a42443f10)], [BX.rte_temp_2.__pk_increment(0xff4a42444570)]), filter([BX.LEG_SEQ(0xff4a424438b0) = BX.IDMAX(0xff4a42444240)(0xff4a42445890)]), rowset=256, access([BX.OC_CARR(0xff4a42442f20)], [BX.OC_FLT_NBR(0xff4a42443250)], [BX.RTE_IND_NEW(0xff4a42443580)], [BX.LEG_SEQ(0xff4a424438b0)], [BX.DPT_AIRPT(0xff4a42443be0)], [BX.ARRV_AIRPT(0xff4a42443f10)], [BX.IDMAX(0xff4a42444240)], [BX.rte_temp_2.__pk_increment(0xff4a42444570)]) 11 - output([rte_temp_2.OC_CARR(0xff4a424ba0e0)], [rte_temp_2.OC_FLT_NBR(0xff4a424ba410)], [rte_temp_2.RTE_IND_NEW(0xff4a424ba740)], [rte_temp_2.LEG_SEQ(0xff4a424baa70)], [rte_temp_2.DPT_AIRPT(0xff4a424bada0)], [rte_temp_2.ARRV_AIRPT(0xff4a424bb0d0)], [T_FUN_MAX(rte_temp_2.LEG_SEQ(0xff4a424baa70))(0xff4a424bc3d0)(0xff4a424bbf60)], [rte_temp_2.__pk_increment(0xff4a424bb400)]), filter(nil), rowset=256, win_expr(T_FUN_MAX(rte_temp_2.LEG_SEQ(0xff4a424baa70))(0xff4a424bc3d0)(0xff4a424bbf60)), partition_by([rte_temp_2.OC_CARR(0xff4a424ba0e0)], [rte_temp_2.OC_FLT_NBR(0xff4a424ba410)], [rte_temp_2.RTE_IND_NEW(0xff4a424ba740)]), order_by([rte_temp_2.LEG_SEQ(0xff4a424baa70), DESC]), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(CURRENT ROW) 12 - output([rte_temp_2.OC_CARR(0xff4a424ba0e0)], [rte_temp_2.OC_FLT_NBR(0xff4a424ba410)], [rte_temp_2.RTE_IND_NEW(0xff4a424ba740)], [rte_temp_2.LEG_SEQ(0xff4a424baa70)], [rte_temp_2.DPT_AIRPT(0xff4a424bada0)], [rte_temp_2.ARRV_AIRPT(0xff4a424bb0d0)], [rte_temp_2.__pk_increment(0xff4a424bb400)]), filter(nil), rowset=256, sort_keys([rte_temp_2.OC_CARR(0xff4a424ba0e0), ASC], [rte_temp_2.OC_FLT_NBR(0xff4a424ba410), ASC], [rte_temp_2.RTE_IND_NEW(0xff4a424ba740), ASC], [rte_temp_2.LEG_SEQ(0xff4a424baa70), DESC]), prefix_pos(2) 13 - output([rte_temp_2.OC_CARR(0xff4a424ba0e0)], [rte_temp_2.OC_FLT_NBR(0xff4a424ba410)], [rte_temp_2.RTE_IND_NEW(0xff4a424ba740)], [rte_temp_2.LEG_SEQ(0xff4a424baa70)], [rte_temp_2.DPT_AIRPT(0xff4a424bada0)], [rte_temp_2.ARRV_AIRPT(0xff4a424bb0d0)], [rte_temp_2.__pk_increment(0xff4a424bb400)]), filter(nil), rowset=256, access([rte_temp_2.OC_CARR(0xff4a424ba0e0)], [rte_temp_2.OC_FLT_NBR(0xff4a424ba410)], [rte_temp_2.RTE_IND_NEW(0xff4a424ba740)], [rte_temp_2.LEG_SEQ(0xff4a424baa70)], [rte_temp_2.DPT_AIRPT(0xff4a424bada0)], [rte_temp_2.ARRV_AIRPT(0xff4a424bb0d0)], [rte_temp_2.__pk_increment(0xff4a424bb400)]), partitions(p[0-479]), is_index_back=false, range_key([rte_temp_2.OC_CARR(0xff4a424ba0e0)], [rte_temp_2.OC_FLT_NBR(0xff4a424ba410)], [rte_temp_2.__pk_increment(0xff4a424bb400)]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true 14 - output([A.OC_CARR(0xff4a424448a0)], [A.OC_FLT_NBR(0xff4a42444bd0)], [A.LEG_SEQ(0xff4a42445230)], [A.V(0xff4a42445560)]), filter([A.RTE_IND_NEW(0xff4a42444f00) = ?(0xffc60bd3c0d0)]), rowset=256, access([A.OC_CARR(0xff4a424448a0)], [A.OC_FLT_NBR(0xff4a42444bd0)], [A.RTE_IND_NEW(0xff4a42444f00)], [A.LEG_SEQ(0xff4a42445230)], [A.V(0xff4a42445560)]), partitions(p[0-479]), is_index_back=false, filter_before_indexback[false], range_key([A.OC_CARR(0xff4a424448a0)], [A.OC_FLT_NBR(0xff4a42444bd0)], [A.__pk_increment(0xffc60bd1dd50)]), range(MIN ; MAX), range_cond([A.OC_CARR(0xff4a424448a0) = ?(0xffc60bd3cc40)], [A.OC_FLT_NBR(0xff4a42444bd0) = ?(0xffc60bd3f7c0)]) Used Hint: ------------------------------------- /*+ PARALLEL(32) ENABLE_PARALLEL_DML */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA NO_USE_HASH_AGGREGATION(@"SEL$1") LEADING(@"SEL$1" ("BX"@"SEL$1" "dev_tmp.A"@"SEL$1" )) USE_NL(@"SEL$1" ("dev_tmp.A"@"SEL$1" )) PQ_DISTRIBUTE(@"SEL$1" ("dev_tmp.A"@"SEL$1" ) NONE NONE) NO_USE_NL_MATERIALIZATION(@"SEL$1" ("dev_tmp.A"@"SEL$1" )) FULL(@"SEL$3" "dev_tmp.rte_temp_2"@"SEL$3") FULL(@"SEL$1" "dev_tmp.A"@"SEL$1") PARALLEL(32) ENABLE_PARALLEL_DML END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- rte_temp_2:table_rows:5907633, physical_range_rows:7429440, logical_range_rows:7429440, index_back_rows:0, output_rows:7429440, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[index_name,rte_temp_2], estimation info[table_id:1103909674354648, (table_type:1, version:0-1696788010174666-1696788010174666, logical_rc:15478, physical_rc:15478), (table_type:7, version:1696788000206953-1696788000206953-1696788030181438, logical_rc:0, physical_rc:0), (table_type:7, version:1696788030181438-1696790489630943-1696792195506091, logical_rc:0, physical_rc:0), (table_type:7, version:1696792195506091-1696793695740325-1696795485157614, logical_rc:0, physical_rc:0), (table_type:7, version:1696795485157614-1696812142982522-1696813905063702, logical_rc:0, physical_rc:0), (table_type:7, version:1696813905063702-1696815273581714-1696817063833273, logical_rc:0, physical_rc:0), (table_type:7, version:1696817063833273-1696819275965191-1696821063864857, logical_rc:0, physical_rc:0), (table_type:5, version:1696817063833273-1696819275965191-1696821063864857, logical_rc:0, physical_rc:0), (table_type:0, version:1696821063864857-1696821063864857-1696822048533064, logical_rc:0, physical_rc:0), (table_type:0, version:1696822048533064-1696822048533064-9223372036854775807, logical_rc:0, physical_rc:0)] A:table_rows:5907633, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:0, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[index_name,rte_temp_2], estimation info[table_id:1103909674354648, (table_type:1, version:0-1696788010174666-1696788010174666, logical_rc:15478, physical_rc:15478), (table_type:7, version:1696788000206953-1696788000206953-1696788030181438, logical_rc:0, physical_rc:0), (table_type:7, version:1696788030181438-1696790489630943-1696792195506091, logical_rc:0, physical_rc:0), (table_type:7, version:1696792195506091-1696793695740325-1696795485157614, logical_rc:0, physical_rc:0), (table_type:7, version:1696795485157614-1696812142982522-1696813905063702, logical_rc:0, physical_rc:0), (table_type:7, version:1696813905063702-1696815273581714-1696817063833273, logical_rc:0, physical_rc:0), (table_type:7, version:1696817063833273-1696819275965191-1696821063864857, logical_rc:0, physical_rc:0), (table_type:5, version:1696817063833273-1696819275965191-1696821063864857, logical_rc:0, physical_rc:0), (table_type:0, version:1696821063864857-1696821063864857-1696822048533064, logical_rc:0, physical_rc:0), (table_type:0, version:1696822048533064-1696822048533064-9223372036854775807, logical_rc:0, physical_rc:0)] Parameters: ------------------------------------- Note: ------------------------------------- Degree of Parallelism is 32 because of hint