达梦数据库提供了基于列存的huge table，在olap方应该是有一些优势的，这里进行简单测试。
SQL> CREATE HUGE TABLESPACE HTS_TEST PATH '/opt/dm/dmdbms/data/enmotech/hts_test' ;
executed successfully
used time: 25.213(ms). Execute id is 312.
SQL>
SQL> create table enmotech0828_1 as select * from dba_objects;
executed successfully
used time: 50.499(ms). Execute id is 316.
SQL> insert into enmotech0828_1 select * from enmotech0828_1;
affect rows 1713
used time: 15.260(ms). Execute id is 323.
SQL> /
affect rows 3426
used time: 21.881(ms). Execute id is 324.
SQL> /
affect rows 6852
used time: 32.102(ms). Execute id is 325.
SQL> /
affect rows 13704
used time: 68.793(ms). Execute id is 326.
SQL> /
affect rows 27408
used time: 120.657(ms). Execute id is 327.
SQL> /
affect rows 54816
used time: 212.995(ms). Execute id is 328.
SQL> /
affect rows 109632
used time: 438.037(ms). Execute id is 329.
SQL> commit;
executed successfully
used time: 2.774(ms). Execute id is 330.
SQL> create HUGE table enmotech0828_2 storage (on "HTS_TEST" ) as select * from enmotech0828_1;
executed successfully
used time: 315.134(ms). Execute id is 332.
SQL>
SQL> select count(object_id) from enmotech0828_1 where OBJECT_ID=268436903;
LINEID COUNT(OBJECT_ID)
---------- --------------------
1 128
used time: 26.345(ms). Execute id is 340.
SQL> select count(object_id) from enmotech0828_2 where OBJECT_ID=268436903;
LINEID COUNT(OBJECT_ID)
---------- --------------------
1 128
used time: 10.522(ms). Execute id is 339.
SQL> select id,name,cache,type$,TOTAL_SIZE,MAX_SIZE,COPY_NUM,SIZE_MODE from v$tablespace;
LINEID ID NAME CACHE TYPE$ TOTAL_SIZE MAX_SIZE COPY_NUM SIZE_MODE
---------- ----------- ------------ ------ ----------- -------------------- -------------------- ----------- ---------
1 0 SYSTEM 1 3072 0 NULL NULL
2 1 ROLL 1 59264 0 NULL NULL
3 3 TEMP 2 131072 2621440 NULL NULL
4 4 MAIN 1 552960 0 NULL NULL
5 5 ENMOTECH NORMAL 1 546432 0 NULL NULL
6 6 TEST_RECOVER NORMAL 1 16384 0 NULL NULL
6 rows got
used time: 2.021(ms). Execute id is 343.
SQL> select * from V$HUGE_TABLESPACE ;
LINEID ID NAME PATHNAME DIR_NUM COPY_NUM SIZE_MODE
---------- ----------- -------- ---------------------------------- ----------- ----------- ---------
1 128 HMAIN /opt/dm/dmdbms/data/enmotech/HMAIN 1 NULL NULL
2 129 HTS_TEST /opt/dm/data/enmoetch/hts_test 1 NULL NULL
used time: 0.975(ms). Execute id is 345.
SQL> select * from V$HUGE_TABLESPACE_PATH;
LINEID ID PATHNAME
---------- ----------- ----------------------------------
1 128 /opt/dm/dmdbms/data/enmotech/HMAIN
2 129 /opt/dm/data/enmoetch/hts_test
used time: 0.862(ms). Execute id is 347.
SQL> set lineshow off
SQL> call SP_TABLEDEF('SYSDBA','ENMOTECH0828_2');
COLUMN_VALUE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE HUGE TABLE "SYSDBA"."ENMOTECH0828_2" ( "OWNER" VARCHAR(128), "OBJECT_NAME" VARCHAR(128), "SUBOBJECT_NAME" VARCHAR(1), "OBJECT_ID" DEC, "DATA_OBJECT_ID" DEC, "OBJECT_TYPE" VARCHAR(15), "CREATED" TIMESTAMP(6), "LAST_DDL_TIME" TIMESTAMP(6), "TIMESTAMP" TIMESTAMP(6), "STATUS" VARCHAR(7), "TEMPORARY" VARCHAR(1), "GENERATED" VARCHAR(1), "SECONDARY" VARCHAR(1), "NAMESPACE" DEC, "EDITION_NAME" VARCHAR(1)) STORAGE(STAT ASYNCHRONOUS, WITH DELTA, SECTION(65536), FILESIZE(64), ON "HTS
_TEST") LOG ALL ;
used time: 0.526(ms). Execute id is 363.
SQL> select a.rowid,a.object_id,a.owner,a.object_name from enmotech0828_1 a where rownum=1;
ROWID OBJECT_ID OWNER OBJECT_NAME
-------------------- --------- ------------ ------------
281078273 150995945 BENCHMARKSQL BENCHMARKSQL
used time: 2.311(ms). Execute id is 364.
SQL> select a.rowid,a.object_id,a.owner,a.object_name from enmotech0828_2 a where rownum=1;
ROWID OBJECT_ID OWNER OBJECT_NAME
-------------------- --------- ------------ ------------
1 150995945 BENCHMARKSQL BENCHMARKSQL
used time: 7.408(ms). Execute id is 365.
SQL> select owner,object_name,object_type,object_id from dba_objects
2 where object_name like '%ENMOTECH0828_2%';
OWNER OBJECT_NAME OBJECT_TYPE OBJECT_ID
------ ------------------- ----------- ---------
SYSDBA ENMOTECH0828_2 TABLE 1469
SYSDBA ENMOTECH0828_2$AUX TABLE 1470
SYSDBA ENMOTECH0828_2$DAUX TABLE 1472
SYSDBA ENMOTECH0828_2$RAUX TABLE 1471
SYSDBA ENMOTECH0828_2$UAUX TABLE 1473
used time: 31.618(ms). Execute id is 366.
SQL> set pagesize 100
SQL> l
1* select COLID,SEC_ID,FILE_ID,OFFSET,ACOUNT,CPR_FLAG,MAX_VAL,MIN_VAL from ENMOTECH0828_2$AUX;
SQL> /
COLID SEC_ID FILE_ID OFFSET ACOUNT CPR_FLAG MAX_VAL MIN_VAL
----------- ----------- ----------- -------------------- ----------- -------- ------------------ --------------------------------------
0 0 0 4096 65536 N 0x53595353534F 0x42454E43484D41524B53514C
0 1 0 540672 65536 N 0x53595353534F 0x42454E43484D41524B53514C
0 2 0 1077248 65536 N 0x53595353534F 0x42454E43484D41524B53514C
0 3 -1 0 22656 N NULL NULL
1 0 0 4096 65536 N 0x58435352 0x2323484953544F4752414D535F5441424C45
1 1 0 1310720 65536 N 0x58435352 0x2323484953544F4752414D535F5441424C45
1 2 0 2617344 65536 N 0x58435352 0x2323484953544F4752414D535F5441424C45
1 3 -1 0 22656 N NULL NULL
2 0 0 4096 65536 N NULL NULL
2 1 0 270336 65536 N NULL NULL
2 2 0 536576 65536 N NULL NULL
2 3 -1 0 22656 N NULL NULL
3 0 0 4096 65536 Y 0xC5063641521D 0x80
3 1 0 724992 65536 Y 0xC5063641521D 0x80
3 2 0 1445888 65536 Y 0xC5063641521D 0x80
3 3 -1 0 22656 N NULL NULL
4 0 0 4096 65536 Y NULL NULL
4 1 0 16384 65536 Y NULL NULL
4 2 0 28672 65536 Y NULL NULL
4 3 -1 0 22656 N NULL NULL
5 0 0 4096 65536 N 0x56494557 0x434C415353
5 1 0 679936 65536 N 0x56494557 0x434C415353
5 2 0 1355776 65536 N 0x56494557 0x434C415353
5 3 -1 0 22656 N NULL NULL
6 0 0 4096 65536 N 0xE507E47507D3B403 0xE587D2C291E1DA0D
6 1 0 798720 65536 N 0xE507E47507D3B403 0xE587D2C291E1DA0D
6 2 0 1593344 65536 N 0xE507E47507D3B403 0xE587D2C291E1DA0D
6 3 -1 0 22656 N NULL NULL
7 0 0 4096 65536 N 0xE507E47507010000 0xE587D2D191010000
7 1 0 798720 65536 N 0xE507E47507010000 0xE587D2D191010000
7 2 0 1593344 65536 N 0xE507E47507010000 0xE587D2D191010000
7 3 -1 0 22656 N NULL NULL
8 0 0 4096 65536 N 0xE507E47507010000 0xE587D2D191010000
8 1 0 798720 65536 N 0xE507E47507010000 0xE587D2D191010000
8 2 0 1593344 65536 N 0xE507E47507010000 0xE587D2D191010000
8 3 -1 0 22656 N NULL NULL
9 0 0 4096 65536 N 0x56414C4944 0x56414C4944
9 1 0 598016 65536 N 0x56414C4944 0x56414C4944
9 2 0 1191936 65536 N 0x56414C4944 0x56414C4944
9 3 -1 0 22656 N NULL NULL
10 0 0 4096 65536 N 0x59 0x4E
10 1 0 335872 65536 N 0x59 0x4E
10 2 0 667648 65536 N 0x59 0x4E
10 3 -1 0 22656 N NULL NULL
11 0 0 4096 65536 N 0x59 0x4E
11 1 0 311296 65536 N 0x59 0x4E
11 2 0 618496 65536 N 0x59 0x4E
11 3 -1 0 22656 N NULL NULL
12 0 0 4096 65536 N NULL NULL
12 1 0 270336 65536 N NULL NULL
12 2 0 536576 65536 N NULL NULL
12 3 -1 0 22656 N NULL NULL
13 0 0 4096 65536 Y NULL NULL
13 1 0 16384 65536 Y NULL NULL
13 2 0 28672 65536 Y NULL NULL
13 3 -1 0 22656 N NULL NULL
14 0 0 4096 65536 N NULL NULL
14 1 0 270336 65536 N NULL NULL
14 2 0 536576 65536 N NULL NULL
14 3 -1 0 22656 N NULL NULL
60 rows got
可以看到达梦这里的huge表功能，还提供了相关是视图。我们继续来观察huge table到底是如何存储的。
[dmdba@mogdb enmotech]$ ls -ltr hts_test/SCH150994945/TAB1469/
total 983040
-rw-r--r--. 1 dmdba dinstall 67108864 Aug 28 07:18 COL0000_0000000000.dta
-rw-r--r--. 1 dmdba dinstall 67108864 Aug 28 07:18 COL0001_0000000000.dta
-rw-r--r--. 1 dmdba dinstall 67108864 Aug 28 07:18 COL0002_0000000000.dta
-rw-r--r--. 1 dmdba dinstall 67108864 Aug 28 07:18 COL0003_0000000000.dta
-rw-r--r--. 1 dmdba dinstall 67108864 Aug 28 07:18 COL0004_0000000000.dta
-rw-r--r--. 1 dmdba dinstall 67108864 Aug 28 07:18 COL0005_0000000000.dta
-rw-r--r--. 1 dmdba dinstall 67108864 Aug 28 07:18 COL0006_0000000000.dta
-rw-r--r--. 1 dmdba dinstall 67108864 Aug 28 07:18 COL0007_0000000000.dta
-rw-r--r--. 1 dmdba dinstall 67108864 Aug 28 07:18 COL0008_0000000000.dta
-rw-r--r--. 1 dmdba dinstall 67108864 Aug 28 07:18 COL0009_0000000000.dta
-rw-r--r--. 1 dmdba dinstall 67108864 Aug 28 07:18 COL0010_0000000000.dta
-rw-r--r--. 1 dmdba dinstall 67108864 Aug 28 07:18 COL0011_0000000000.dta
-rw-r--r--. 1 dmdba dinstall 67108864 Aug 28 07:18 COL0012_0000000000.dta
-rw-r--r--. 1 dmdba dinstall 67108864 Aug 28 07:18 COL0013_0000000000.dta
-rw-r--r--. 1 dmdba dinstall 67108864 Aug 28 07:18 COL0014_0000000000.dta
可以看到；对于huge 列存表，达梦数据库的做法是在huge tablespace下面，以的object_id为名称；
然后将表的每一列数据单独存放到一个文件中，如上：这里我的测试表有15个列；因此该表下面存放了15个文件。
我们继续来查看一下文件中的数据存放格式：
[dmdba@mogdb enmotech]$ strings ./hts_test/SCH150994945/TAB1469/COL0000_0000000000.dta |more
BENCHMARKSQLCTISYSSYSSYSAUDITORSYSDBASYSSSOBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQL
BENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLCTISYSSYSSYSSYSSYSSYSSYSSYSSYSS
YSSYSSYSSYSSYSSYSS。。。。。。
可以看到，列存表的机制非常之简单，就是将一个表的每个列单独存放为一个文件，以COL000x进行命名，然后每个文件中都只有存在该列的数据。任何一个文件丢失，都将导致表数据不完整，无法访问。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
