条件不同，执行效率差别巨大，如何解决

OceanBase 技术问题
#1

【 使用环境 】测试环境
【 OB or 其他组件 】ob4218
【 使用版本 】4218
【问题描述】同一条sql，条件不同，走的计划相差很大导致代价很大。
explain SELECT O.crdt_contract_no, O.customer_no, O.customer_nm, O.prod_no, O.occr_type_cd, O.busi_ccy_cd, O.contract_amt, O.contract_bal, O.overdue_amt, O.fix_rate, O.exe_rate, O.start_dt, O.mat_dt, O.bfchg_major_gatmhd_cd, O.risk_classify_cd, O.optr_no, O.hndl_org_no, O.hndl_dt, O.afrnwl_contract_epday, CI.dcmtp_cd, CI.document_no FROM contract_bscinf O INNER JOIN cust_info CI ON O.customer_no = CI.customer_no INNER JOIN prd_catalog PC ON O.prod_no = PC.prod_no AND PC.onbs_ofbs_ind = ‘01’ WHERE O.CONTRACT_STATUS IN (‘320’, ‘380’) AND O.optr_no = ‘3954’ AND O.hndl_org_no = ‘0011’;
±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ===================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| --------------------------------------------------------------------- |
| |0 |PX COORDINATOR | |428822 |11265472 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10002|428822 |9727850 | |
| |2 | └─HASH JOIN | |428822 |6267169 | |
| |3 | ├─EXCHANGE IN DISTR | |428822 |4814813 | |
| |4 | │ └─EXCHANGE OUT DISTR (PKEY)|:EX10001|428822 |3471786 | |
| |5 | │ └─HASH JOIN | |428822 |449252 | |
| |6 | │ ├─EXCHANGE IN DISTR | |178 |253 | |
| |7 | │ │ └─EXCHANGE OUT DISTR |:EX10000|178 |186 | |
| |8 | │ │ └─TABLE FULL SCAN |PC |178 |34 | |
| |9 | │ └─TABLE FULL SCAN |O |428822 |310437 | |
| |10| └─PX PARTITION ITERATOR | |3269544 |148602 | |
| |11| └─TABLE FULL SCAN |CI(tt) |3269544 |148602 | |
| ===================================================================== |

Outputs & filters:
0 - output([INTERNAL_FUNCTION(O.CRDT_CONTRACT_NO, O.CUSTOMER_NO, O.CUSTOMER_NM, O.PROD_NO, O.OCCR_TYPE_CD, O.BUSI_CCY_CD, O.CONTRACT_AMT, O.CONTRACT_BAL,
O.OVERDUE_AMT, O.FIX_RATE, O.EXE_RATE, O.START_DT, O.MAT_DT, O.BFCHG_MAJOR_GATMHD_CD, O.RISK_CLASSIFY_CD, O.OPTR_NO, O.HNDL_ORG_NO, O.HNDL_DT, O.AFRNWL_CONTRACT_EPDAY,
CI.DCMTP_CD, CI.DOCUMENT_NO)]), filter(nil), rowset=256
1 - output([INTERNAL_FUNCTION(O.CRDT_CONTRACT_NO, O.CUSTOMER_NO, O.CUSTOMER_NM, O.PROD_NO, O.OCCR_TYPE_CD, O.BUSI_CCY_CD, O.CONTRACT_AMT, O.CONTRACT_BAL,
O.OVERDUE_AMT, O.FIX_RATE, O.EXE_RATE, O.START_DT, O.MAT_DT, O.BFCHG_MAJOR_GATMHD_CD, O.RISK_CLASSIFY_CD, O.OPTR_NO, O.HNDL_ORG_NO, O.HNDL_DT, O.AFRNWL_CONTRACT_EPDAY,
CI.DCMTP_CD, CI.DOCUMENT_NO)]), filter(nil), rowset=256
dop=1
2 - output([O.CUSTOMER_NO], [O.PROD_NO], [O.CRDT_CONTRACT_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD], [O.CONTRACT_AMT],
[O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD], [O.HNDL_DT],
[O.AFRNWL_CONTRACT_EPDAY], [CI.DCMTP_CD], [CI.DOCUMENT_NO]), filter(nil), rowset=256
equal_conds([O.CUSTOMER_NO = CI.CUSTOMER_NO]), other_conds(nil)
3 - output([O.CUSTOMER_NO], [O.PROD_NO], [O.CRDT_CONTRACT_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD], [O.CONTRACT_AMT],
[O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD], [O.HNDL_DT],
[O.AFRNWL_CONTRACT_EPDAY]), filter(nil), rowset=256
4 - output([O.CUSTOMER_NO], [O.PROD_NO], [O.CRDT_CONTRACT_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD], [O.CONTRACT_AMT],
[O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD], [O.HNDL_DT],
[O.AFRNWL_CONTRACT_EPDAY]), filter(nil), rowset=256
(#keys=1, [O.CUSTOMER_NO]), is_single, dop=1
5 - output([O.CUSTOMER_NO], [O.PROD_NO], [O.CRDT_CONTRACT_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD], [O.CONTRACT_AMT],
[O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD], [O.HNDL_DT],
[O.AFRNWL_CONTRACT_EPDAY]), filter(nil), rowset=256
equal_conds([O.PROD_NO = PC.PROD_NO]), other_conds(nil)
6 - output([PC.PROD_NO]), filter(nil), rowset=256
7 - output([PC.PROD_NO]), filter(nil), rowset=256
is_single, dop=1
8 - output([PC.PROD_NO]), filter([PC.ONBS_OFBS_IND = cast(‘01’, VARCHAR(1048576))]), rowset=256
access([PC.PROD_NO], [PC.ONBS_OFBS_IND]), partitions(p0)
is_index_back=false, is_global_index=false, filter_before_indexback[false],
range_key([PC.PROD_NO]), range(MIN ; MAX)always true
9 - output([O.CRDT_CONTRACT_NO], [O.CUSTOMER_NO], [O.PROD_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD], [O.CONTRACT_AMT],
[O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD], [O.HNDL_DT],
[O.AFRNWL_CONTRACT_EPDAY]), filter([O.CONTRACT_STATUS IN (cast(‘320’, VARCHAR(1048576)), cast(‘380’, VARCHAR(1048576)))], [O.HNDL_ORG_NO = cast(‘0011’,
VARCHAR(1048576))], [O.OPTR_NO = cast(‘3954’, VARCHAR(1048576))]), rowset=256
access([O.CRDT_CONTRACT_NO], [O.CUSTOMER_NO], [O.PROD_NO], [O.CONTRACT_STATUS], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD],
[O.CONTRACT_AMT], [O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD],
[O.HNDL_DT], [O.AFRNWL_CONTRACT_EPDAY]), partitions(p0)
is_index_back=false, is_global_index=false, filter_before_indexback[false,false,false],
range_key([O.CRDT_CONTRACT_NO]), range(MIN ; MAX)always true
10 - output([CI.CUSTOMER_NO], [CI.DCMTP_CD], [CI.DOCUMENT_NO]), filter(nil), rowset=256
affinitize, force partition granule
11 - output([CI.CUSTOMER_NO], [CI.DCMTP_CD], [CI.DOCUMENT_NO]), filter(nil), rowset=256
access([CI.CUSTOMER_NO], [CI.DCMTP_CD], [CI.DOCUMENT_NO]), partitions(p[0-255])
is_index_back=false, is_global_index=false,
range_key([CI.CUSTOMER_NO], [CI.DCMTP_CD], [CI.DOCUMENT_NO]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true

±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
62 rows in set (0.021 sec)

MySQL [sjcscredit]> explain SELECT O.crdt_contract_no,O.customer_no,O.customer_nm,O.prod_no,O.occr_type_cd,O.busi_ccy_cd,O.contract_amt,O.contract_bal,O.overdue_amt,O.fix_rate,O.exe_rate,O.start_dt,O.mat_dt,O.bfchg_major_gatmhd_cd,O.risk_classify_cd,O.optr_no,O.hndl_org_no,O.hndl_dt,O.afrnwl_contract_epday,CI.dcmtp_cd,CI.document_no FROM contract_bscinf O,cust_info CI WHERE O.customer_no=CI.customer_no AND EXISTS ( SELECT 1 FROM prd_catalog PC WHERE O.prod_no=PC.prod_no AND PC.onbs_ofbs_ind=‘01’) AND O.CONTRACT_STATUS IN (‘320’,‘380’) AND O.optr_no=‘2523’ AND O.hndl_org_no=‘1378’
→ ;
±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =========================================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------------------------------------- |
| |0 |PX COORDINATOR | |1 |707 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10001 |1 |707 | |
| |2 | └─NESTED-LOOP JOIN | |1 |707 | |
| |3 | ├─EXCHANGE IN DISTR | |1 |686 | |
| |4 | │ └─EXCHANGE OUT DISTR (PKEY)|:EX10000 |1 |686 | |
| |5 | │ └─NESTED-LOOP JOIN | |1 |686 | |
| |6 | │ ├─TABLE RANGE SCAN |O(contract_bscinf_OPTR_NO_IDX)|1 |668 | |
| |7 | │ └─DISTRIBUTED TABLE GET|PC |1 |18 | |
| |8 | └─PX PARTITION ITERATOR | |1 |5126 | |
| |9 | └─TABLE GET |CI |1 |5126 | |
| =========================================================================================== |

Outputs & filters:
0 - output([INTERNAL_FUNCTION(O.CRDT_CONTRACT_NO, O.CUSTOMER_NO, O.CUSTOMER_NM, O.PROD_NO, O.OCCR_TYPE_CD, O.BUSI_CCY_CD, O.CONTRACT_AMT, O.CONTRACT_BAL,
O.OVERDUE_AMT, O.FIX_RATE, O.EXE_RATE, O.START_DT, O.MAT_DT, O.BFCHG_MAJOR_GATMHD_CD, O.RISK_CLASSIFY_CD, O.OPTR_NO, O.HNDL_ORG_NO, O.HNDL_DT, O.AFRNWL_CONTRACT_EPDAY,
CI.DCMTP_CD, CI.DOCUMENT_NO)]), filter(nil), rowset=16
1 - output([INTERNAL_FUNCTION(O.CRDT_CONTRACT_NO, O.CUSTOMER_NO, O.CUSTOMER_NM, O.PROD_NO, O.OCCR_TYPE_CD, O.BUSI_CCY_CD, O.CONTRACT_AMT, O.CONTRACT_BAL,
O.OVERDUE_AMT, O.FIX_RATE, O.EXE_RATE, O.START_DT, O.MAT_DT, O.BFCHG_MAJOR_GATMHD_CD, O.RISK_CLASSIFY_CD, O.OPTR_NO, O.HNDL_ORG_NO, O.HNDL_DT, O.AFRNWL_CONTRACT_EPDAY,
CI.DCMTP_CD, CI.DOCUMENT_NO)]), filter(nil), rowset=16
dop=1
2 - output([O.CUSTOMER_NO], [O.PROD_NO], [O.CRDT_CONTRACT_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD], [O.CONTRACT_AMT],
[O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD], [O.HNDL_DT],
[O.AFRNWL_CONTRACT_EPDAY], [CI.DCMTP_CD], [CI.DOCUMENT_NO]), filter(nil), rowset=16
conds(nil), nl_params_([O.CUSTOMER_NO(:0)]), use_batch=false
3 - output([PARTITION_ID], [O.CUSTOMER_NO], [O.PROD_NO], [O.CRDT_CONTRACT_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD],
[O.CONTRACT_AMT], [O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD],
[O.HNDL_DT], [O.AFRNWL_CONTRACT_EPDAY]), filter(nil), rowset=16
4 - output([PARTITION_ID], [O.CUSTOMER_NO], [O.PROD_NO], [O.CRDT_CONTRACT_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD],
[O.CONTRACT_AMT], [O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD],
[O.HNDL_DT], [O.AFRNWL_CONTRACT_EPDAY]), filter(nil), rowset=16
(#keys=1, [O.CUSTOMER_NO]), is_single, dop=1
5 - output([O.CUSTOMER_NO], [O.PROD_NO], [O.CRDT_CONTRACT_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD], [O.CONTRACT_AMT],
[O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD], [O.HNDL_DT],
[O.AFRNWL_CONTRACT_EPDAY]), filter(nil), rowset=16
conds(nil), nl_params_([O.PROD_NO(:2)]), use_batch=true
6 - output([O.CRDT_CONTRACT_NO], [O.CUSTOMER_NO], [O.PROD_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD], [O.CONTRACT_AMT],
[O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD], [O.HNDL_DT],
[O.AFRNWL_CONTRACT_EPDAY]), filter([O.HNDL_ORG_NO = cast(‘1378’, VARCHAR(1048576))], [O.CONTRACT_STATUS IN (cast(‘320’, VARCHAR(1048576)), cast(‘380’, VARCHAR(1048576)))]), rowset=16
access([O.CRDT_CONTRACT_NO], [O.CUSTOMER_NO], [O.PROD_NO], [O.CONTRACT_STATUS], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD],
[O.CONTRACT_AMT], [O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD],
[O.HNDL_DT], [O.AFRNWL_CONTRACT_EPDAY]), partitions(p0)
is_index_back=true, is_global_index=false, filter_before_indexback[false,false],
range_key([O.OPTR_NO], [O.CRDT_CONTRACT_NO]), range(2523,MIN ; 2523,MAX),
range_cond([O.OPTR_NO = cast(‘2523’, VARCHAR(1048576))])
7 - output(nil), filter([PC.ONBS_OFBS_IND = cast(‘01’, VARCHAR(1048576))]), rowset=16
access([GROUP_ID], [PC.ONBS_OFBS_IND]), partitions(p0)
is_index_back=false, is_global_index=false, filter_before_indexback[false],
range_key([PC.PROD_NO]), range(MIN ; MAX),
range_cond([:2 = PC.PROD_NO])
8 - output([CI.DCMTP_CD], [CI.DOCUMENT_NO]), filter(nil), rowset=16
affinitize, force partition granule
9 - output([CI.DCMTP_CD], [CI.DOCUMENT_NO]), filter(nil), rowset=16
access([CI.DCMTP_CD], [CI.DOCUMENT_NO]), partitions(p[0-255])
is_index_back=false, is_global_index=false,
range_key([CI.CUSTOMER_NO]), range(MIN ; MAX),
range_cond([:0 = CI.CUSTOMER_NO])

±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
59 rows in set (0.021 sec)

1 个赞
#3

1、用obdiag收集信息

收集SQL性能问题信息

obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-hxx -Pxx -uxx -pxx -Dxx’, trace_id=‘xx’}”
obdiag安装
https://www.oceanbase.com/docs/common-obdiag-cn-1000000001491140
2、 获取 sql plan monitor 信息，全过程可以参看如下操作。
1.登录sys租户设置 sql_plan_monitor 参数

确认 sql_plan_monitor 已经打开

show parameters like ‘enable_sql_audit’;

如果 enable_sql_audit = False 则将其打开

alter system enable_sql_audit = true;

  1. 登录业务租户，获取 sql 的执行计划
    EXPLAIN EXTENDED sql语句; --保存在文本里

  2. 设置临时 trace 获取
    SET ob_enable_show_trace=‘ON’;

  3. 再次执行需要采集的 sql 语句

  4. 获取上一步执行的 sql 的 trace_id 信息
    select last_trace_id();

  5. 临时关闭 plan monitor 数据，防止信息被覆盖
    alter system enable_sql_audit = false;

  6. 获取 plan monitor 的 sql ，将 xxxxx 替换为第 6 步返回的 trace_id ，以获取每个算子的吐行信息
    select plan_line_id, plan_operation, sum(output_rows), sum(STARTS) rescan, min(first_refresh_time) open_time, max(last_refresh_time) close_time, min(first_change_time) first_row_time, max(last_change_time) last_row_eof_time, count(1) from oceanbase.gv$sql_plan_monitor where trace_id = ‘xxxxx’ group by plan_line_id, plan_operation order by plan_line_id;

8 恢复 sql_audit 参数
alter system enable_sql_audit = true;

–获取以上信息后，最好也提供对应 trace_id 信息的 observer.log 日志协助

#4

sql.txt (11.5 KB)
html.zip (102.3 KB)
这是sql和obdlg的图

#5

YB4214083F06-000627B98ECCC111-0-0.log (122.9 KB)

#6

SELECT /*+monitor */O.crdt_contract_no, O.customer_no, O.customer_nm, O.prod_no, O.occr_type_cd, O.busi_ccy_cd, O.contract_amt, O.contract_bal, O.overdue_amt, O.fix_rate, O.exe_rate, O.start_dt, O.mat_dt, O.bfchg_major_gatmhd_cd, O.risk_classify_cd, O.optr_no, O.hndl_org_no, O.hndl_dt, O.afrnwl_contract_epday, CI.dcmtp_cd, CI.document_no FROM contract_bscinf O INNER JOIN cust_info CI ON O.customer_no = CI.customer_no INNER JOIN prd_catalog PC ON O.prod_no = PC.prod_no AND PC.onbs_ofbs_ind = ‘01’ WHERE O.CONTRACT_STATUS IN (‘320’, ‘380’) AND O.optr_no = ‘3954’ AND O.hndl_org_no = ‘0011’;

SELECT /*+monitor */ O.crdt_contract_no,O.customer_no,O.customer_nm,O.prod_no,O.occr_type_cd,O.busi_ccy_cd,O.contract_amt,O.contract_bal,O.overdue_amt,O.fix_rate,O.exe_rate,O.start_dt,O.mat_dt,O.bfchg_major_gatmhd_cd,O.risk_classify_cd,O.optr_no,O.hndl_org_no,O.hndl_dt,O.afrnwl_contract_epday,CI.dcmtp_cd,CI.document_no FROM contract_bscinf O,cust_info CI WHERE O.customer_no=CI.customer_no AND EXISTS ( SELECT 1 FROM prd_catalog PC WHERE O.prod_no=PC.prod_no AND PC.onbs_ofbs_ind=‘01’) AND O.CONTRACT_STATUS IN (‘320’,‘380’) AND O.optr_no=‘2523’ AND O.hndl_org_no=‘1378’;

两个语句都加一下 hint /*+monitor */ 执行以后 重新用obdiag收集 用下面这个收集重新发一下 上面发的有问题

收集SQL性能问题信息

obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-hxx -Pxx -uxx -pxx -Dxx’, trace_id=‘xx’}”

#7

看着执行计划 应该是数据根据过滤条件 有数据倾斜 走的算子不一样
你可以根据过滤条件查一下数据

#8

确实这两条count（*）的数量相差很大

#9

那就是了数据的倾斜比较大 导致的属于正常 状态值如果分布不均的话 就会出现这种问题 建议过滤大的数据别走索引 会有回表 回表+索引的消耗 比走全表消耗还要高

#10

emmm我调整字符序后不知道为啥执行计划就变了 table get 从之前的bin变成了general

explain SELECT O.crdt_contract_no, O.customer_no, O.customer_nm, O.prod_no, O.occr_type_cd, O.busi_ccy_cd, O.contract_amt, O.contract_bal, O.overdue_amt, O.fix_rate, O.exe_rate, O.start_dt, O.mat_dt, O.bfchg_major_gatmhd_cd, O.risk_classify_cd, O.optr_no, O.hndl_org_no, O.hndl_dt, O.afrnwl_contract_epday, CI.dcmtp_cd, CI.document_no FROM contract_bscinf O INNER JOIN cust_info CI ON O.customer_no = CI.customer_no INNER JOIN prd_catalog PC ON O.prod_no = PC.prod_no AND PC.onbs_ofbs_ind = ‘01’ WHERE O.CONTRACT_STATUS IN (‘320’, ‘380’) AND O.optr_no = ‘3954’ AND O.hndl_org_no = ‘0011’;
±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ============================================================================ |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ---------------------------------------------------------------------------- |
| |0 |PX COORDINATOR | |15713 |886073 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10001|15713 |809826 | |
| |2 | └─NESTED-LOOP JOIN | |15713 |638201 | |
| |3 | ├─EXCHANGE IN DISTR | |15872 |308294 | |
| |4 | │ └─EXCHANGE OUT DISTR (PKEY) |:EX10000|15872 |240580 | |
| |5 | │ └─NESTED-LOOP JOIN | |15872 |88168 | |
| |6 | │ ├─TABLE FULL SCAN |PC |5 |29 | |
| |7 | │ └─DISTRIBUTED TABLE RANGE SCAN|O(tmp) |3816 |21092 | |
| |8 | └─PX PARTITION ITERATOR | |1 |5126 | |
| |9 | └─TABLE GET |CI |1 |5126 | |
| ============================================================================ |

Outputs & filters:
0 - output([INTERNAL_FUNCTION(O.CRDT_CONTRACT_NO, O.CUSTOMER_NO, O.CUSTOMER_NM, O.PROD_NO, O.OCCR_TYPE_CD, O.BUSI_CCY_CD, O.CONTRACT_AMT, O.CONTRACT_BAL,
O.OVERDUE_AMT, O.FIX_RATE, O.EXE_RATE, O.START_DT, O.MAT_DT, O.BFCHG_MAJOR_GATMHD_CD, O.RISK_CLASSIFY_CD, O.OPTR_NO, O.HNDL_ORG_NO, O.HNDL_DT, O.AFRNWL_CONTRACT_EPDAY,
CI.DCMTP_CD, CI.DOCUMENT_NO)]), filter(nil), rowset=256
1 - output([INTERNAL_FUNCTION(O.CRDT_CONTRACT_NO, O.CUSTOMER_NO, O.CUSTOMER_NM, O.PROD_NO, O.OCCR_TYPE_CD, O.BUSI_CCY_CD, O.CONTRACT_AMT, O.CONTRACT_BAL,
O.OVERDUE_AMT, O.FIX_RATE, O.EXE_RATE, O.START_DT, O.MAT_DT, O.BFCHG_MAJOR_GATMHD_CD, O.RISK_CLASSIFY_CD, O.OPTR_NO, O.HNDL_ORG_NO, O.HNDL_DT, O.AFRNWL_CONTRACT_EPDAY,
CI.DCMTP_CD, CI.DOCUMENT_NO)]), filter(nil), rowset=256
dop=1
2 - output([O.CUSTOMER_NO], [O.CRDT_CONTRACT_NO], [O.PROD_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD], [O.CONTRACT_AMT],
[O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD], [O.HNDL_DT],
[O.AFRNWL_CONTRACT_EPDAY], [CI.DCMTP_CD], [CI.DOCUMENT_NO]), filter(nil), rowset=256
conds(nil), nl_params_([O.CUSTOMER_NO(:0)]), use_batch=false
3 - output([PARTITION_ID], [O.CUSTOMER_NO], [O.CRDT_CONTRACT_NO], [O.PROD_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD],
[O.CONTRACT_AMT], [O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD],
[O.HNDL_DT], [O.AFRNWL_CONTRACT_EPDAY]), filter(nil), rowset=256
4 - output([PARTITION_ID], [O.CUSTOMER_NO], [O.CRDT_CONTRACT_NO], [O.PROD_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD],
[O.CONTRACT_AMT], [O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD],
[O.HNDL_DT], [O.AFRNWL_CONTRACT_EPDAY]), filter(nil), rowset=256
(#keys=1, [O.CUSTOMER_NO]), is_single, dop=1
5 - output([O.CUSTOMER_NO], [O.CRDT_CONTRACT_NO], [O.PROD_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD], [O.CONTRACT_AMT],
[O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD], [O.HNDL_DT],
[O.AFRNWL_CONTRACT_EPDAY]), filter(nil), rowset=256
conds(nil), nl_params_([PC.PROD_NO(:3)]), use_batch=true
6 - output([PC.PROD_NO]), filter([PC.ONBS_OFBS_IND = ‘01’]), rowset=256
access([PC.PROD_NO], [PC.ONBS_OFBS_IND]), partitions(p0)
is_index_back=false, is_global_index=false, filter_before_indexback[false],
range_key([PC.PROD_NO]), range(MIN ; MAX)always true
7 - output([O.CRDT_CONTRACT_NO], [O.CUSTOMER_NO], [O.PROD_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD], [O.CONTRACT_AMT],
[O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD], [O.HNDL_DT],
[O.AFRNWL_CONTRACT_EPDAY]), filter(nil), rowset=256
access([GROUP_ID], [O.CRDT_CONTRACT_NO], [O.CUSTOMER_NO], [O.PROD_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD],
[O.CONTRACT_AMT], [O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD],
[O.HNDL_DT], [O.AFRNWL_CONTRACT_EPDAY]), partitions(p0)
is_index_back=true, is_global_index=false,
range_key([O.OPTR_NO], [O.HNDL_ORG_NO], [O.CONTRACT_STATUS], [O.PROD_NO], [O.CRDT_CONTRACT_NO]), range(MIN ; MAX),
range_cond([O.CONTRACT_STATUS IN (‘320’, ‘380’)], [O.OPTR_NO = ‘3954’], [O.HNDL_ORG_NO = ‘0011’], [O.PROD_NO = :3])
8 - output([CI.DCMTP_CD], [CI.DOCUMENT_NO]), filter(nil), rowset=256
affinitize, force partition granule
9 - output([CI.DCMTP_CD], [CI.DOCUMENT_NO]), filter(nil), rowset=256
access([CI.DCMTP_CD], [CI.DOCUMENT_NO]), partitions(p[0-255])
is_index_back=false, is_global_index=false,
range_key([CI.CUSTOMER_NO]), range(MIN ; MAX),
range_cond([:0 = CI.CUSTOMER_NO])

±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
58 rows in set (0.02 sec)

#11

是所有的表的字符序都改了么？ 字符序影响字符串数据的排序顺序以及字符串搜索和比较操作的行为

#12

这三张表给改了

#13

尽量库的字符序保持一致 要不然表关联的时候 会出现字符序不一致 索引会失效的情况