达梦数据库学习笔记之 — huge 列存表
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 达梦数据库学习笔记之 — huge 列存表
达梦数据库提供了基于列存的huge table，在olap方应该是有一些优势的，这里进行简单测试。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 |
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到底是如何存储的。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
[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个文件。
我们继续来查看一下文件中的数据存放格式：
|
1 2 3 4 5 6 |
[dmdba@mogdb enmotech]$ strings ./hts_test/SCH150994945/TAB1469/COL0000_0000000000.dta |more BENCHMARKSQLCTISYSSYSSYSAUDITORSYSDBASYSSSOBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQL BENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLBENCHMARKSQLCTISYSSYSSYSSYSSYSSYSSYSSYSSYSS YSSYSSYSSYSSYSSYSS。。。。。。 |
可以看到，列存表的机制非常之简单，就是将一个表的每个列单独存放为一个文件，以COL000x进行命名，然后每个文件中都只有存在该列的数据。任何一个文件丢失，都将导致表数据不完整，无法访问。
Leave a Reply
You must be logged in to post a comment.