SR 版本2.3.0
查询一次内存需要分配30多G内存， 不应该需要分配这么大
查询表大小为2.8亿数据，查询过滤后数据为2400W左右。
查询SQL:
SELECT COUNT(*) num FROM (SELECT CITM_SEQ AS A11, split_part(CITM_NAME_L1, ‘$mls$’, 1) AS B11, ENTERPRISECURRENCY666_CITM_TOTAL AS F11, CITM_CODE AS CUST_ORDER_B11, ITEM_TYPE AS CUST_ORDER_D11, (CASE WHEN citm_qty_varchar = ’ ’ THEN ‘–’ ELSE citm_qty_varchar END) AS D11, (COALESCE(split_part(VDRS_NAME, ‘$mls$’, 1), ‘–’)) AS E11 FROM a003_a.ds_detail_items_doub_1 GUI WHERE ((GUI.tenant_id = ‘t00502’ AND 1 = 1) AND GUI.shop_id IN (‘7’, ‘1’, ‘6’, ‘9’, ‘2’, ‘11’, ‘5’, ‘3’, ‘8’, ‘10’, ‘13’, ‘12’, ‘4’)) AND GUI.olet_id IN (‘10’, ‘11’, ‘12’, ‘13’, ‘14’, ‘15’, ‘16’, ‘17’, ‘18’, ‘19’, ‘20’, ‘6’, ‘7’, ‘8’, ‘9’, ‘1’, ‘2’, ‘3’, ‘4’, ‘5’, ‘60’, ‘100’, ‘101’, ‘102’, ‘103’, ‘104’, ‘105’, ‘106’, ‘107’, ‘108’, ‘139’, ‘140’, ‘141’, ‘142’, ‘143’, ‘62’, ‘63’, ‘64’, ‘65’, ‘66’, ‘67’, ‘68’, ‘69’, ‘70’, ‘71’, ‘72’, ‘73’, ‘74’, ‘75’, ‘76’, ‘77’, ‘78’, ‘79’, ‘80’, ‘81’, ‘84’, ‘85’, ‘86’, ‘87’, ‘88’, ‘89’, ‘90’, ‘91’, ‘92’, ‘93’, ‘94’, ‘95’, ‘96’, ‘97’, ‘98’, ‘99’, ‘56’, ‘57’, ‘58’, ‘59’, ‘61’, ‘50’, ‘51’, ‘52’, ‘53’, ‘54’, ‘55’, ‘82’, ‘83’, ‘109’, ‘110’, ‘111’, ‘112’, ‘113’, ‘114’, ‘115’, ‘116’, ‘117’, ‘118’, ‘144’, ‘156’, ‘157’, ‘158’, ‘159’, ‘160’, ‘161’, ‘162’, ‘119’, ‘120’, ‘121’, ‘122’, ‘123’, ‘124’, ‘125’, ‘126’, ‘127’, ‘128’, ‘129’, ‘130’, ‘131’, ‘132’, ‘133’, ‘134’, ‘135’, ‘136’, ‘137’, ‘138’, ‘163’, ‘164’, ‘165’, ‘166’, ‘167’, ‘168’, ‘169’, ‘170’, ‘171’, ‘172’, ‘173’, ‘174’, ‘175’, ‘145’, ‘146’, ‘147’, ‘148’, ‘149’, ‘150’, ‘151’, ‘152’, ‘153’, ‘154’, ‘155’, ‘176’, ‘177’, ‘178’, ‘179’, ‘180’, ‘21’, ‘22’, ‘23’, ‘24’, ‘25’, ‘26’, ‘27’, ‘28’, ‘29’, ‘30’, ‘31’, ‘32’, ‘33’, ‘34’, ‘35’, ‘36’, ‘37’, ‘46’, ‘47’, ‘48’, ‘49’, ‘193’, ‘195’, ‘38’, ‘39’, ‘40’, ‘41’, ‘42’, ‘43’, ‘44’, ‘45’, ‘181’, ‘182’, ‘183’, ‘184’, ‘185’, ‘186’, ‘187’, ‘188’, ‘189’, ‘190’, ‘191’, ‘192’) ORDER BY E11 DESC, A11 ASC, CUST_ORDER_B11 ASC, CUST_ORDER_D11 ASC LIMIT 20000) AS ttt
建表语句:
CREATE TABLE ds_detail_items_doub_1 (
shop_id varchar(65533) NULL COMMENT “”,
olet_id varchar(65533) NULL COMMENT “”,
item_type int(11) NULL COMMENT “”,
chks_id varchar(65533) NULL COMMENT “”,
chks_check_prefix_num varchar(65533) NULL COMMENT “”,
bday_date varchar(65533) NULL COMMENT “”,
cdis_citm_id varchar(65533) NULL COMMENT “”,
citm_code varchar(65533) NULL COMMENT “”,
citm_seq decimal128(20, 4) NULL COMMENT “”,
citm_status varchar(65533) NULL COMMENT “”,
vdrs_name varchar(65533) NULL COMMENT “”,
citm_name_l1 varchar(65533) NULL COMMENT “”,
citm_qty_varchar varchar(65533) NULL COMMENT “”,
citm_qty decimal128(20, 4) NULL COMMENT “”,
enterprisecurrency666_citm_total decimal128(38, 8) NULL COMMENT “”,
propertycurrency666_citm_total decimal128(38, 4) NULL COMMENT “”,
tenant_id varchar(65533) NULL COMMENT “”,
ab_partition int(11) NULL COMMENT “”
) ENGINE=OLAP
DUPLICATE KEY(shop_id, olet_id)
COMMENT “OLAP”
PARTITION BY RANGE(ab_partition)
(PARTITION pcold VALUES [(“0”), (“1”)),
PARTITION phot VALUES [(“2”), (“3”)))
DISTRIBUTED BY HASH(tenant_id, bday_date) BUCKETS 4
PROPERTIES (
“replication_num” = “3”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”,
“enable_persistent_index” = “false”
);
