【 使用环境 】生产环境 or 测试环境
测试环境
【 OB or 其他组件 】
OB
【 使用版本 】
4.2.1.2
【问题描述】清晰明确描述问题
问题1：同一个查询sql,我查分区表比非分区表还慢。
问题2：我加了索引查询sql，反而比不加索引还慢。
表结构：
CREATE TABLE dm_outpatient_medical_reduce1 (
outpatient_medical_id bigint(20) NOT NULL,
full_date date NOT NULL,
org_id bigint(20) DEFAULT NULL,
org_name varchar(100) DEFAULT NULL,
dept_id bigint(20) DEFAULT NULL,
dept_name varchar(50) DEFAULT NULL,
data_src_code varchar(60) DEFAULT NULL,
medical_nums bigint(20) DEFAULT NULL,
etl_date datetime(6) DEFAULT NULL,
sex_code varchar(10) DEFAULT NULL,
diagnosis_code varchar(200) DEFAULT NULL,
effective_flag varchar(100) DEFAULT NULL,
region_id varchar(30) DEFAULT NULL,
PRIMARY KEY (outpatient_medical_id),
KEY dm_outpatient_medical_reduce1_diagnosis_code (diagnosis_code) BLOCK_SIZE 16384 LOCAL,
KEY dm_outpatient_medical_reduce1_sex_code (sex_code) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 4 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
CREATE TABLE dim_scd_organization (
org_id int(11) NOT NULL,
org_source_key varchar(50) DEFAULT NULL,
org_code varchar(200) DEFAULT NULL,
org_name varchar(100) DEFAULT NULL,
province_code varchar(40) DEFAULT NULL,
province_name varchar(100) DEFAULT NULL,
city_code varchar(40) DEFAULT NULL,
city_name varchar(100) DEFAULT NULL,
district_code varchar(40) DEFAULT NULL,
district_name varchar(100) DEFAULT NULL,
street_code varchar(40) DEFAULT NULL,
street_name varchar(100) DEFAULT NULL,
org_type_code varchar(40) DEFAULT NULL,
org_type_name varchar(100) DEFAULT NULL,
mlevel_code varchar(40) DEFAULT NULL,
mlevel_name varchar(100) DEFAULT NULL,
org_sname varchar(70) DEFAULT NULL,
manage_type_code varchar(30) DEFAULT NULL,
manage_type_name varchar(60) DEFAULT NULL,
approved_type_code varchar(30) DEFAULT NULL,
approved_type_name varchar(60) DEFAULT NULL,
primary_ins_type_code varchar(30) DEFAULT NULL,
primary_ins_type_name varchar(60) DEFAULT NULL,
parent_org_index varchar(36) DEFAULT NULL,
parent_org_code varchar(36) DEFAULT NULL,
parent_org_name varchar(70) DEFAULT NULL,
sponsor_type varchar(30) DEFAULT NULL,
relation_code varchar(30) DEFAULT NULL,
is_teaching varchar(30) DEFAULT NULL,
zone_coords varchar(100) DEFAULT NULL,
center_coords varchar(100) DEFAULT NULL,
org_addr_detail varchar(100) DEFAULT NULL,
org_postcode varchar(50) DEFAULT NULL,
org_fax varchar(20) DEFAULT NULL,
org_tel varchar(20) DEFAULT NULL,
org_email varchar(70) DEFAULT NULL,
onduty_tel varchar(20) DEFAULT NULL,
complaint_tel varchar(20) DEFAULT NULL,
org_url varchar(200) DEFAULT NULL,
org_introduction varchar(200) DEFAULT NULL,
establish_date date DEFAULT NULL,
plan_bed_num int(11) DEFAULT NULL,
hos_code varchar(255) DEFAULT NULL,
org_name_abbr varchar(255) DEFAULT NULL,
pinyin varchar(255) DEFAULT NULL,
shoupin varchar(255) DEFAULT NULL,
wubi varchar(255) DEFAULT NULL,
higher_org_source_key varchar(36) DEFAULT NULL,
higher_org_name varchar(100) DEFAULT NULL,
status int(11) DEFAULT NULL,
sort int(11) DEFAULT NULL,
delete_flag int(11) DEFAULT NULL,
create_time datetime(6) DEFAULT NULL,
update_time datetime(6) DEFAULT NULL,
begin_date date DEFAULT NULL,
end_date date NOT NULL,
data_source_code varchar(10) NOT NULL,
etl_date datetime(6) DEFAULT NULL,
org_level int(11) DEFAULT NULL,
is_aux_org varchar(2) DEFAULT NULL,
is_outcall_org varchar(2) DEFAULT NULL,
auxorg_busi_type_code varchar(2) DEFAULT NULL,
auxorg_busi_type_name varchar(32) DEFAULT NULL,
PRIMARY KEY (org_id, end_date, data_source_code),
KEY idx_dim_scd_organization_district_code (district_code) BLOCK_SIZE 16384 LOCAL,
KEY idx_dim_scd_organization_end_date (end_date) BLOCK_SIZE 16384 LOCAL,
KEY idx_dim_scd_organization_org_source_key (org_source_key) BLOCK_SIZE 16384 LOCAL,
KEY idx_dim_scd_organization_orgid (org_id) BLOCK_SIZE 16384 LOCAL,
KEY idx_dim_scd_organization_province_code (province_code) BLOCK_SIZE 16384 LOCAL,
KEY idx_dim_scd_organization_province_name (province_name) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 4 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
数据量：
dm.dm_outpatient_medical_reduce1 20351889
dw.dim_scd_organization 82055
查询的语句：
select
COALESCE(sum(dm_outpatient_medical.medical_nums), 0) as value_bq
from dm.dm_outpatient_medical_reduce1 as dm_outpatient_medical
left join dw.dim_scd_organization as dim_scd_organization_province_code
ON dim_scd_organization_province_code.org_id=dm_outpatient_medical.org_id
and dim_scd_organization_province_code.end_date = ‘2099-12-31’
WHERE 1=1 and dm_outpatient_medical.full_date between ‘2023-01-01’ and ‘2023-10-29’
and dim_scd_organization_province_code.province_code = ‘330000’
and dm_outpatient_medical.data_src_code = ‘2’
and dm_outpatient_medical.effective_flag = ‘1’
查询执行计划：
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
|0 |SCALAR GROUP BY | |1 |7799044 |
|1 |└─HASH JOIN | |5130 |7798951 |
|2 | ├─TABLE RANGE SCAN |dim_scd_organization_province_code(idx_dim_scd_organization_province_code)|16 |286 |
|3 | └─PX COORDINATOR | |2322412 |7586609 |
|4 | └─EXCHANGE OUT DISTR|:EX10000 |2322412 |5780075 |
|5 | └─TABLE FULL SCAN |dm_outpatient_medical |2322412 |1720185 |
Outputs & filters:
0 - output([coalesce(T_FUN_SUM(dm_outpatient_medical.medical_nums), cast(0, DECIMAL(20, 0)))]), filter(nil), rowset=256
group(nil), agg_func([T_FUN_SUM(dm_outpatient_medical.medical_nums)])
1 - output([dm_outpatient_medical.medical_nums]), filter(nil), rowset=256
equal_conds([dim_scd_organization_province_code.org_id = dm_outpatient_medical.org_id]), other_conds(nil)
2 - output([dim_scd_organization_province_code.org_id]), filter([cast(dim_scd_organization_province_code.end_date, DATETIME(-1, -1)) = INTERNAL_FUNCTION(‘2099-12-31’,
110, 17)]), rowset=256
access([dim_scd_organization_province_code.org_id], [dim_scd_organization_province_code.end_date]), partitions(p0)
is_index_back=false, is_global_index=false, filter_before_indexback[false],
range_key([dim_scd_organization_province_code.province_code], [dim_scd_organization_province_code.org_id], [dim_scd_organization_province_code.end_date],
[dim_scd_organization_province_code.data_source_code]), range(330000,MIN,MIN,MIN ; 330000,MAX,MAX,MAX),
range_cond([dim_scd_organization_province_code.province_code = ‘330000’])
3 - output([dm_outpatient_medical.org_id], [dm_outpatient_medical.medical_nums]), filter(nil), rowset=256
4 - output([dm_outpatient_medical.org_id], [dm_outpatient_medical.medical_nums]), filter(nil), rowset=256
is_single, dop=1
5 - output([dm_outpatient_medical.org_id], [dm_outpatient_medical.medical_nums]), filter([cast(dm_outpatient_medical.full_date, DATETIME(-1, -1)) >= INTERNAL_FUNCTION(‘2023-01-01’,
114, 17)], [dm_outpatient_medical.data_src_code = ‘2’], [dm_outpatient_medical.effective_flag = ‘1’], [cast(dm_outpatient_medical.full_date, DATETIME(-1,
-1)) <= INTERNAL_FUNCTION(‘2023-10-29’, 112, 17)]), rowset=256
access([dm_outpatient_medical.org_id], [dm_outpatient_medical.full_date], [dm_outpatient_medical.data_src_code], [dm_outpatient_medical.effective_flag],
[dm_outpatient_medical.medical_nums]), partitions(p0)
is_index_back=false, is_global_index=false, filter_before_indexback[false,false,false,false],
range_key([dm_outpatient_medical.outpatient_medical_id]), range(MIN ; MAX)always true
【复现路径】问题出现前后相关操作
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：