版本 doris-2.1.6-rc04-653e315ba5
建表语句
CREATE TABLE `dsp_mdm_org_bak_07` (
`CODE` varchar(50) NOT NULL COMMENT '',
`LEVELS` decimal(3,0) NULL COMMENT '',
`CREATE_TIME` datetime NULL COMMENT '',
`USED` char(1) NULL COMMENT '',
`MODIFY_TIME` datetime NULL COMMENT '',
`VERSION_NO` bigint NULL COMMENT '',
`STATUS` varchar(10) NULL COMMENT '',
`DISPLAYORDER` text NULL COMMENT '',
`DISPLAY` char(1) NULL COMMENT '',
`ISSTOPSEND` varchar(10) NULL COMMENT '',
`dsc_update_time` datetime NULL COMMENT ''
) ENGINE=OLAP
UNIQUE KEY(`CODE`)
COMMENT '金刚: 组织结构'
DISTRIBUTED BY HASH(`CODE`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"compression" = "LZ4",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);
数据样例
INSERT INTO dsp_mdm_org_bak_07 (CODE,LEVELS,CREATE_TIME,USED,MODIFY_TIME,VERSION_NO,STATUS,DISPLAYORDER,DISPLAY,ISSTOPSEND,dsc_update_time) VALUES
('512005',5,NULL,'N','2016-01-20 17:30:36.0',1630893026018,'INVALID','999','Y','N','2021-09-06 09:50:26.0'),
('85e4c63775b942849a317ed95530c11b',6,'2020-03-02 15:03:58.0','Y','2022-03-11 10:04:32.0',1646964272759,'VALID','999','Y','N','2022-03-11 10:04:33.0'),
('8a814fb2506f469b01506f95f0714c33',6,'2015-10-16 15:38:28.0','Y','2021-09-03 13:25:41.0',1630646741194,'VALID','999','Y','N','2021-09-03 13:25:41.0'),
('8a814fb2506f469b0150702683ac3444',6,'2015-10-16 18:16:23.0','Y','2020-09-11 15:05:41.0',1599807941000,'VALID','999','Y','N','2020-09-11 15:05:41.0'),
('8a814fb25097880a0150a7e73347418b',6,'2015-10-27 14:05:58.0','Y','2019-10-18 12:10:13.0',1447650061555,'VALID','999','Y','N','2015-11-16 13:01:02.0'),
('8a814fb34eb80049014eb88bfa611396',6,'2015-07-23 09:34:18.0','N','2020-09-14 15:59:01.0',1600070341182,'VALID','999','Y','N','2020-09-14 15:59:01.0'),
('8a814fb35012530a015012ba99c50b3d',6,'2015-09-28 14:53:50.0','Y','2022-11-29 19:24:00.0',1669888444735,'VALID','999','Y','Y','2022-12-01 17:54:05.0'),
('8a814fb3536ea667015373d564a24949',6,'2016-03-14 14:34:38.0','Y','2021-09-06 08:55:58.0',1710922233223,'VALID','999','Y','N','2024-03-20 16:10:33.0'),
('8a8165576091bbce0160967af63d26c7',5,'2017-12-27 13:38:49.0','N','2019-09-29 16:15:15.0',1569744915798,'INVALID','999','Y','N','2019-09-29 16:15:16.0'),
('791005',6,NULL,'N','2016-01-13 11:07:11.0',1594110973768,'VALID','999','Y','N','2020-07-07 16:36:14.0'),
('8a8142c337f26914013836dbbf7f2ff6',5,'2012-06-29 14:09:27.0','Y','2022-11-29 19:24:00.0',1669888443983,'VALID','999','Y','Y','2022-12-01 17:54:04.0'),
('8a8142c458b4ba300158dc8e858d5573',7,'2016-12-08 11:51:17.0','Y','2024-08-07 16:48:04.0',1723020484469,'VALID','999','Y','N','2024-08-07 16:48:04.0'),
('8a814fb250b164ec0150b81f87413835',6,'2015-10-30 17:41:25.0','Y','2020-12-09 10:35:25.0',1731920823940,'VALID','999','Y','N','2024-11-18 17:07:04.0'),
('8a814fb2535e808f01537d5857580a17',6,'2016-03-16 10:54:14.0','Y','2022-11-29 19:24:00.0',1669888449042,'VALID','999','Y','Y','2022-12-01 17:54:09.0'),
('8a814fb35072d03e0150750de4920ac9',6,'2015-10-17 17:07:36.0','Y','2021-09-03 14:23:18.0',1630650198239,'VALID','999','Y','N','2021-09-03 14:23:18.0'),
('8a814fb353ca422d0153caad00e53c4b',6,'2016-03-31 11:17:28.0','Y','2022-11-29 19:24:00.0',1669888450391,'VALID','999','Y','Y','2022-12-01 17:54:10.0'),
('8a8165575ca0d496015ce249933f21e3',6,'2017-06-26 10:44:51.0','N','2019-01-23 14:49:37.0',1514355176689,'INVALID','999','Y','N','2017-12-27 14:12:57.0'),
('8a81655760b4dcf40160c4383e6a1395',6,'2018-01-05 10:48:28.0','Y','2022-11-29 19:24:00.0',1669888449127,'VALID','999','Y','Y','2022-12-01 17:54:09.0'),
('8a8165585bccf73b015be5dab49a561d',6,'2017-05-08 10:19:27.0','Y','2023-05-24 10:00:00.0',1684893600073,'VALID','999','Y','Y','2023-05-24 10:00:00.0'),
('730010',6,NULL,'N','2020-09-14 15:58:00.0',1600070280599,'VALID','999','Y','N','2020-09-14 15:58:01.0'),
('8a8142c45620056a015628b04a923049',6,'2016-07-27 03:30:57.0','Y','2022-11-29 19:24:00.0',1669888442292,'VALID','999','Y','Y','2022-12-01 17:54:02.0'),
('8a8142c457f49ca90157f648414c06d3',6,'2016-10-24 18:41:52.0','Y','2021-09-03 13:24:31.0',1630646671254,'VALID','999','Y','N','2021-09-03 13:24:31.0'),
('8a814fb2506f469b01506fb0d09264d3',6,'2015-10-16 16:07:50.0','Y','2020-09-11 15:05:41.0',1599807941000,'VALID','999','Y','N','2020-09-11 15:05:41.0'),
('8a814fb252edc71c0152eeaf44ff4f4e',6,'2016-02-17 18:03:29.0','Y','2021-09-06 09:09:43.0',1710922233223,'VALID','999','Y','N','2024-03-20 16:10:33.0'),
('8a8165575ae4377e015aef604bad5f39',5,'2017-03-21 13:39:09.0','Y','2021-12-29 17:31:17.0',1640770277407,'VALID','999','Y','N','2021-12-29 17:31:17.0'),
('0b8b83e094a2486d8a5b039a7812f8a4',6,'2020-12-04 17:34:04.0','Y','2022-11-29 19:24:00.0',1669888451119,'VALID','999','Y','Y','2022-12-01 17:54:11.0'),
('17db2ad0f99944d28b6a2854c428cd64',6,'2020-03-20 20:24:09.0','Y','2022-11-29 19:24:00.0',1669888451329,'VALID','999','Y','Y','2022-12-01 17:54:11.0'),
('8a814fb2449b7ffc0144ce57ec1733c8',6,'2014-03-17 12:41:12.0','Y','2022-11-29 19:24:00.0',1669888444104,'VALID','999','Y','Y','2022-12-01 17:54:04.0'),
('8a814fb2504ba11c015064efb8bc2a17',6,'2015-10-14 14:00:43.0','Y','2020-09-14 15:59:01.0',1600070341182,'VALID','999','Y','N','2020-09-14 15:59:01.0'),
('8a814fb2539ce6830153a3120b5329ca',6,'2016-03-23 18:43:02.0','Y','2016-01-13 11:07:11.0',1594110973771,'VALID','999','Y','N','2020-07-07 16:36:14.0'),
('8a814fb2556d57d40155760c0a3055d3',6,'2016-06-22 10:59:08.0','N','2016-01-13 11:07:11.0',1594110973768,'VALID','999','Y','N','2020-07-07 16:36:14.0'),
('8a814fb35072d03e015074f318a965bc',6,'2015-10-17 16:38:20.0','Y','2022-03-11 10:07:13.0',1646964433012,'VALID','999','Y','N','2022-03-11 10:07:13.0'),
('557003',6,NULL,'Y','2015-10-10 13:45:30.0',1594111185829,'VALID','999','Y','N','2020-07-07 16:39:46.0'),
('5a085feac43143fb84f417ce0f519473',6,'2021-11-23 14:45:35.0','Y','2021-11-23 15:04:01.0',1637651041706,'VALID','999','Y','N','2021-11-23 15:04:02.0'),
('8a8142c43e4f9e08013e6814552d4599',6,'2013-05-03 09:49:23.0','Y','2021-09-03 13:25:41.0',1630646741194,'VALID','999','Y','N','2021-09-03 13:25:41.0'),
('8a814fb24ffe5bb00150178f783c4c60',6,'2015-09-29 13:24:49.0','Y','2015-10-10 13:45:30.0',1594111185829,'VALID','999','Y','N','2020-07-07 16:39:46.0'),
('8a814fb2557c185b01558140c4554b85',6,'2016-06-24 15:12:33.0','Y','2021-09-03 13:25:41.0',1630646741193,'VALID','999','Y','N','2021-09-03 13:25:41.0'),
('8a814fb3508e536f0150a794eb3c572e',6,'2015-10-27 12:36:06.0','Y','2015-10-10 13:45:30.0',1594111185830,'VALID','999','Y','N','2020-07-07 16:39:46.0'),
('8a814fb351adc2fe0151f1f5ce256718',6,'2015-12-30 16:16:36.0','Y','2020-09-11 15:15:41.0',1599808541089,'VALID','999','Y','N','2020-09-11 15:15:41.0'),
('8a814fb353f196260153f460aae6628b',6,'2016-04-08 13:38:09.0','Y','2016-04-08 13:38:09.0',1460093905798,'VALID','999','Y','N','2016-04-08 13:38:26.0'),
('1f65bc1fcee1498b9557719ace3e2c44',5,'2020-04-01 16:45:47.0','Y','2022-11-29 19:24:00.0',1669888451297,'VALID','999','Y','Y','2022-12-01 17:54:11.0'),
('451010',5,NULL,'Y','2020-02-25 09:23:54.0',1582593834080,'VALID','999','Y','N','2020-02-25 09:23:54.0'),
('816004',6,NULL,'Y','2022-11-29 19:24:00.0',1669888443268,'VALID','999','Y','Y','2022-12-01 17:54:03.0'),
('8a8142c33b930d48013b933118c40348',6,'2012-12-13 15:36:06.0','Y','2021-09-03 13:12:39.0',1630645959290,'VALID','999','Y','N','2021-09-03 13:12:39.0'),
('8a814fb2504ba11c015064468084493f',6,'2015-10-14 10:55:53.0','Y','2022-11-29 19:24:00.0',1669888445098,'VALID','999','Y','Y','2022-12-01 17:54:05.0'),
('8a814fb25952d16f0159686f800c55e0',6,'2017-01-04 15:44:14.0','Y','2022-03-11 09:57:17.0',1646963837306,'VALID','999','Y','N','2022-03-11 09:57:17.0'),
('8a814fb35072d03e01507f3b263710d3',6,'2015-10-19 16:33:14.0','Y','2022-11-29 19:24:00.0',1669888448031,'VALID','999','Y','Y','2022-12-01 17:54:08.0'),
('8a814fb3507f76870150845e4c754f45',6,'2015-10-20 16:29:43.0','Y','2022-11-29 19:24:00.0',1669888448220,'VALID','999','Y','Y','2022-12-01 17:54:08.0'),
('8a814fb3508e536f0150a788e84a5445',6,'2015-10-27 12:22:58.0','Y','2015-10-10 13:45:30.0',1594111185830,'VALID','999','Y','N','2020-07-07 16:39:46.0'),
('8a814fb3536ea6670153745aea160a64',6,'2016-03-14 17:00:28.0','Y','2022-11-29 19:24:00.0',1669888448773,'VALID','999','Y','Y','2022-12-01 17:54:09.0'),
('8a8165585afa3a8d015afe3720fa7b46',5,'2017-03-24 10:48:30.0','N','2019-04-25 15:29:27.0',1527570882922,'INVALID','999','Y','N','2018-05-29 13:14:43.0'),
('8a8165595c9b50eb015ca012d2c24813',6,'2017-06-13 14:10:07.0','Y','2020-09-14 15:58:38.0',1600070318409,'VALID','999','Y','N','2020-09-14 15:58:38.0'),
('8a8165595e9841e7015ec1fff86c29e6',6,'2017-09-27 14:22:11.0','Y','2015-10-10 13:45:30.0',1594111185831,'VALID','999','Y','N','2020-07-07 16:39:46.0'),
('8a81655960b4dcf40160b599cb2337b5',6,'2018-01-02 14:40:43.0','Y','2015-10-10 13:45:30.0',1594111185829,'VALID','999','Y','N','2020-07-07 16:39:46.0'),
('8a814fb2506f469b01506fad05cb617f',6,'2015-10-16 16:03:41.0','N','2020-09-11 15:05:41.0',1599807941000,'VALID','999','Y','N','2020-09-11 15:05:41.0'),
('8a814fb2507f769b0150846eb1eb002e',6,'2015-10-20 16:47:38.0','Y','2022-11-29 19:24:00.0',1669888448233,'VALID','999','Y','Y','2022-12-01 17:54:08.0'),
('8a814fb25317a19601531cdefddd69a9',6,'2016-02-26 17:18:09.0','Y','2021-12-01 09:56:30.0',1722330701840,'VALID','999','Y','N','2024-07-30 17:11:42.0'),
('8a814fb3508e536f0150985a21ab1c77',6,'2015-10-24 13:37:35.0','N','2020-08-27 09:36:37.0',1598492197599,'VALID','999','Y','N','2020-08-27 09:36:38.0'),
('8a814fb3536ea6670153737f585326cf',6,'2016-03-14 13:00:38.0','N','2021-09-06 08:55:58.0',1710922233223,'VALID','999','Y','N','2024-03-20 16:10:33.0'),
('8a814fb3536ea667015373ccbb064454',6,'2016-03-14 14:25:10.0','Y','2021-09-06 08:55:58.0',1710922233223,'VALID','999','Y','N','2024-03-20 16:10:33.0'),
('8a814fb353adbdd70153b5be3daa3864',6,'2016-03-27 09:44:17.0','Y','2020-09-11 15:05:53.0',1599807953988,'VALID','999','Y','N','2020-09-11 15:05:54.0'),
('8a814fb36a05fae8016a0629379e2a24',6,'2019-04-10 15:32:05.0','Y','2021-12-22 10:50:48.0',1640141448647,'VALID','999','Y','N','2021-12-22 10:50:49.0'),
('4aa545fad30449dd84622c9ea19360ac',6,'2020-12-04 17:34:04.0','Y','2022-11-29 19:24:00.0',1669888451483,'VALID','999','Y','Y','2022-12-01 17:54:11.0'),
('8a8142c337f269140137fd2681305584',5,'2012-06-18 09:13:08.0','Y','2020-09-11 15:05:40.0',1599807940999,'VALID','999','Y','N','2020-09-11 15:05:41.0'),
('8a814fb2506a9f4401506ad31a0346fd',6,'2015-10-15 17:27:11.0','Y','2022-11-29 19:24:00.0',1669888444848,'VALID','999','Y','Y','2022-12-01 17:54:05.0'),
('8a814fb25097880a0150a3382c3125d9',6,'2015-10-26 16:16:18.0','Y','2022-11-29 19:24:00.0',1669888448680,'VALID','999','Y','Y','2022-12-01 17:54:09.0'),
('8a814fb25097880a0150a7e03e083ea1',6,'2015-10-27 13:58:22.0','Y','2015-11-19 16:52:12.0',1447923133297,'VALID','999','Y','N','2015-11-19 16:52:13.0'),
('8a814fb250acea880150adaade842d52',6,'2015-10-28 16:57:47.0','Y','2021-09-03 14:23:18.0',1630650198239,'VALID','999','Y','N','2021-09-03 14:23:18.0'),
('8a814fb253491acb01535a198f943290',6,'2016-03-09 14:38:57.0','Y','2020-09-11 15:06:05.0',1599807965469,'VALID','999','Y','N','2020-09-11 15:06:05.0'),
('8a814fb258b4b8d10158e23a268f6199',6,'2016-12-09 14:16:51.0','Y','2022-11-29 19:24:00.0',1669888442621,'VALID','999','Y','Y','2022-12-01 17:54:03.0'),
('8a814fb35072d03e015074504a825b88',6,'2015-10-17 13:40:30.0','Y','2022-11-29 19:24:00.0',1669888446967,'VALID','999','Y','Y','2022-12-01 17:54:07.0'),
('8a814fb353bd89560153c6311e5d3824',6,'2016-03-30 14:23:41.0','Y','2017-05-25 14:16:56.0',1495693019548,'VALID','999','Y','N','2017-05-25 14:17:00.0'),
('8a8165575a4adb8c015a730ad2a501b0',5,'2017-02-25 10:12:53.0','Y','2016-01-13 11:07:28.0',1594111501331,'VALID','999','Y','N','2020-07-07 16:45:01.0'),
('8a81655760b4dcf40160c437a93812af',6,'2018-01-05 10:47:50.0','Y','2022-11-29 19:24:00.0',1669888449216,'VALID','999','Y','Y','2022-12-01 17:54:09.0'),
('dc57047073554c14a3a3219a4cb6878b',5,'2020-04-01 16:45:36.0','Y','2020-08-05 10:11:17.0',1596593477271,'VALID','999','Y','N','2020-08-05 10:11:17.0'),
('8a8142c3429793c901429816aa930ab5',5,'2013-11-27 13:44:51.0','Y','2019-12-26 14:42:59.0',1577342579091,'VALID','999','Y','N','2019-12-26 14:42:59.0'),
('8a814fb24912139e0149123dafd5071b',6,'2014-10-15 13:17:52.0','N','2015-11-05 10:25:43.0',1630889984927,'INVALID','999','Y','N','2021-09-06 08:59:45.0'),
('8a814fb25072d040015074ba09bb171f',5,'2015-10-17 15:36:00.0','Y','2020-03-11 18:01:09.0',1583920869681,'VALID','999','Y','N','2020-03-11 18:01:10.0'),
('8a814fb25ffb3ee90160001d1dd1005e',6,'2017-11-28 08:53:16.0','Y','2021-12-15 17:29:40.0',1639560580850,'VALID','999','Y','N','2021-12-15 17:29:41.0'),
('8a814fb26ac35a28016ade68f0f94e0c',5,'2019-05-22 15:19:40.0','N','2019-05-24 14:55:03.0',1558680903873,'INVALID','999','Y','N','2019-05-24 14:55:04.0'),
('8a814fb34e4f1226014e4fa5e31f009a',6,'2015-07-03 00:42:28.0','Y','2019-04-01 15:18:10.0',1554103090301,'VALID','999','Y','N','2019-04-01 15:18:10.0'),
('8a814fb351adc2fe0151c26dabb528dc',6,'2015-12-21 10:45:46.0','Y','2022-03-11 10:07:13.0',1646964433155,'VALID','999','Y','N','2022-03-11 10:07:13.0'),
('8a814fb3577f4d0601578865512f42e7',6,'2016-10-03 10:35:23.0','Y','2020-09-11 15:05:41.0',1599807941001,'VALID','999','Y','N','2020-09-11 15:05:41.0'),
('8a8165575a4adb8c015a72fbb15e782e',4,'2017-02-25 09:56:21.0','Y','2021-09-03 13:12:39.0',1722330672224,'VALID','999','Y','N','2024-07-30 17:11:12.0'),
('8a81655760b4dcf40160c02dfc694936',6,'2018-01-04 15:58:47.0','Y','2022-11-29 19:24:00.0',1669888449074,'VALID','999','Y','Y','2022-12-01 17:54:09.0'),
('8a8165586879a07e0168987d55895616',5,'2019-01-29 15:22:57.0','N','2019-07-24 16:27:11.0',1563956831885,'INVALID','999','Y','N','2019-07-24 16:27:12.0'),
('d357cc5011d045a89e69a5f5246277ab',5,'2020-03-09 02:48:11.0','Y','2020-03-09 02:50:38.0',1583981438038,'VALID','999.0','Y','N',NULL),
('dd58176d39254cff9421b7e67208b345',6,'2020-04-02 08:37:48.0','Y','2021-12-20 09:55:55.0',1639965355210,'VALID','999','Y','N','2021-12-20 09:55:55.0'),
('469001',5,NULL,'Y','2020-02-25 09:55:51.0',1582595751630,'VALID','999','Y','N','2020-02-25 09:55:52.0'),
('832e6276bd0949b29f14bcf7fd5b7fcf',5,'2024-03-06 10:11:48.0','Y','2024-03-06 10:23:26.0',1709691806241,'VALID','999','Y','N','2024-03-06 10:23:26.0'),
('8a8142c45819d1cd01581e19e98e2889',6,'2016-11-01 12:16:04.0','Y','2020-03-15 19:30:50.0',1584271850524,'VALID','999','Y','N','2020-03-15 19:30:51.0'),
('8a814fb24e43a4c6014e47b659850d9d',5,'2015-07-01 11:43:29.0','Y','2022-11-29 19:24:00.0',1669888444529,'VALID','999','Y','Y','2022-12-01 17:54:05.0'),
('8a814fb2506f469b01506fbb15026d81',6,'2015-10-16 16:19:03.0','Y','2020-09-11 15:05:41.0',1599807941000,'VALID','999','Y','N','2020-09-11 15:05:41.0'),
('8a814fb25638c610015672e0ed2b5cc4',6,'2016-08-10 13:15:58.0','Y','2021-12-16 13:48:41.0',1639633721539,'VALID','999','Y','N','2021-12-16 13:48:42.0'),
('8a814fb265c9c0dd0165d0b906363a67',4,'2018-09-13 10:18:31.0','N','2019-07-31 16:02:57.0',1564560177843,'INVALID','999','Y','N','2019-07-31 16:02:58.0'),
('8a814fb35072d03e0150750f23040b63',6,'2015-10-17 17:08:57.0','Y','2021-09-03 14:23:18.0',1630650198239,'VALID','999','Y','N','2021-09-03 14:23:18.0'),
('8a81655862852e0801628f381c600ce9',6,'2018-04-04 13:54:01.0','Y','2022-11-29 19:24:00.0',1669888449160,'VALID','999','Y','Y','2022-12-01 17:54:09.0'),
('8a81655965c9bead0165cb463e002c82',6,'2018-09-12 08:55:02.0','Y','2021-09-06 09:27:18.0',1710922233228,'VALID','999','Y','N','2024-03-20 16:10:33.0'),
('8a8165596b92a23e016bb11e095523eb',7,'2019-07-02 13:17:44.0','Y','2024-08-07 16:48:04.0',1723020484470,'VALID','999','Y','N','2024-08-07 16:48:04.0'),
('b6c6a832841b4e4b9216e591e49d8ab7',6,'2020-03-18 13:49:37.0','Y','2020-03-18 13:49:37.0',1584510578739,'VALID','999','Y','N','2020-03-18 13:49:39.0');
explain语句
-- 关注code的大小写和ddl中的是否一致， 以及是否分桶裁剪。
explain
select * from dsp_mdm_org_bak_07 where CODE='512090' and STATUS='VALID' AND USED='Y';
explain
select * from dsp_mdm_org_bak_07 where code='512090' and STATUS='VALID' AND USED='Y';
