本篇内容主要讲解“Oracle与PostgreSQL中PageSize对数据存储有哪些影响”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“Oracle与PostgreSQL中PageSize对数据存储有哪些影响”吧!

Oracle数据库，Block Size设定为8K

TEST-cndb@pc15.infogov>showparameterdb_block_sizeNAMETYPEVALUE-----------------------------------------------------------------------------db_block_sizeinteger8192--为方便分析，创建一个只有128K的表空间TEST-cndb@pc15.infogov>createtablespacetbs_tmpdatafile'/data/oradata/testtbs/tbs_tmp01.dbf'size128Kautoextendoff;Tablespacecreated.--创建一张表，存储在tbs_tmp表空间，一行的大小>8KTEST-cndb@pc15.infogov>droptablet2purge;Tabledropped.TEST-cndb@pc15.infogov>TEST-cndb@pc15.infogov>createtablet2(idint,c1char(2000),c2char(2000),c3char(2000),c4char(2000),c5char(2000))tablespacetbs_tmp;Tablecreated.TEST-cndb@pc15.infogov>insertintot2values(1,rpad('1',2000,'1'),rpad('2',2000,'2'),rpad('3',2000,'3'),rpad('4',2000,'4'),rpad('5',2000,'5'));1rowcreated.TEST-cndb@pc15.infogov>commit;Commitcomplete.--执行checkpoint，flush数据到磁盘TEST-cndb@pc15.infogov>altersystemcheckpoint;Systemaltered.

在一行数据大于Block Size时，Oracle使用行链接的方式实现跨块存储。

TEST-cndb@pc15.infogov>droptableCHAINED_ROWSpurge;Tabledropped.TEST-cndb@pc15.infogov>createtableCHAINED_ROWS(2owner_namevarchar2(30),3table_namevarchar2(30),4cluster_namevarchar2(30),5partition_namevarchar2(30),6subpartition_namevarchar2(30),7head_rowidrowid,8analyze_timestampdate9);Tablecreated.TEST-cndb@pc15.infogov>analyzetablet2listchainedrows;Tableanalyzed.TEST-cndb@pc15.infogov>selecttable_name,head_rowidfromCHAINED_ROWS;TABLE_NAMEHEAD_ROWID------------------------------------------------T2AAAZroAB9AAAAAPAAA二、PG

PG的默认Block Size为8K，可以在编译安装时修改，不作任何调整，创建一张预期行占用空间可能 > 8K的数据表，插入测试数据：

testdb=#droptableifexistst2;n_filepath('t2');DROPTABLEtestdb=#createtablet2(idint,c1char(4000),c2char(4000),c3char(4000));CREATETABLEtestdb=#testdb=#\d+t2Table"public.t2"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description--------+-----------------+-----------+----------+---------+----------+--------------+-------------id|integer||||plain||c1|character(4000)||||extended||c2|character(4000)||||extended||c3|character(4000)||||extended||testdb=#testdb=#insertintot2values(1,'11','12','13');INSERT01testdb=#insertintot2values(2,rpad('2',4000,'2'),rpad('3',4000,'3'),rpad('4',4000,'4'));INSERT01testdb=#testdb=#checkpoint;CHECKPOINTtestdb=#selectpg_relation_filepath('t2');pg_relation_filepath----------------------base/16477/26700(1row)[xdb@localhostutf8db]$hexdump-C$PGDATA/base/16477/267000000000001000000d0d826db000000002000681e|......&......h.|000000100020042000000000309f9e01689e8801|......0...h...|0000002000000000000000000000000000000000|................|*00001e600000000000000000079c180000000000|................|00001e7000000000000000000200040002081800|................|00001e8002000000e2000000a00f0000fe320f01|.............2..|00001e90ff0f01ff0f01ff0f01ff0f01ff0f01ff|................|00001ea00f01ffff0f01ff0f01ff0f01ff0f01ff|................|00001eb00f01ff0f01ff0f01ff0f019fe2000000|................|00001ec0a00f0000fe330f01ff0f01ff0f01ff0f|.....3..........|00001ed001ff0f01ff0f01ff0f01ffff0f01ff0f|................|00001ee001ff0f01ff0f01ff0f01ff0f01ff0f01|................|00001ef0ff0f019fe2000000a00f0000fe340f01|.............4..|00001f00ff0f01ff0f01ff0f01ff0f01ff0f01ff|................|00001f100f01ffff0f01ff0f01ff0f01ff0f01ff|................|00001f200f01ff0f01ff0f01ff0f019f00000000|................|00001f30069c1800000000000000000000000000|................|00001f40010004000208180001000000ee000000|................|00001f50a00f0000f83131200f01ff0f01ff0f01|.....11........|00001f60ff0f01ff0f01ffff0f01ff0f01ff0f01|................|00001f70ff0f01ff0f01ff0f01ff0f01ff0f01ff|................|00001f80030f01ff0f019d00ee000000a00f0000|................|00001f90f83132200f01ff0f01ff0f01ff0f01ff|.12............|00001fa00f01ffff0f01ff0f01ff0f01ff0f01ff|................|00001fb00f01ff0f01ff0f01ff0f01ff030f01ff|................|00001fc00f019d00ee000000a00f0000f8313320|.............13|00001fd00f01ff0f01ff0f01ff0f01ff0f01ffff|................|00001fe00f01ff0f01ff0f01ff0f01ff0f01ff0f|................|00001ff001ff0f01ff0f01ff030f01ff0f019d00|................|00002000

可以看到，虽然行数据>8K，但在PG中，这些数据都存储在一个block中（显然使用了压缩），00000000~00002000为一个block 8K。

实际上，在PG中，PG使用称为TOAST (The Oversized-Attribute Storage Technique)的技术：

If any of the columns of a table are TOAST-able, the table will have an associated TOAST table, whose OID is stored in the table's pg_class.reltoastrelid entry. On-disk TOASTed values are kept in the TOAST table, as described in more detail below.

对于数据表的列，有四种存储选项：

PLAIN prevents either compression or out-of-line storage; furthermore it disables use of single-byte headers for varlena types. This is the only possible strategy for columns of non-TOAST-able data types.
EXTENDED allows both compression and out-of-line storage. This is the default for most TOAST-able data types. Compression will be attempted first, then out-of-line storage if the row is still too big.
EXTERNAL allows out-of-line storage but not compression. Use of EXTERNAL will make substring operations on wide text and bytea columns faster (at the penalty of increased storage space) because these operations are optimized to fetch only the required parts of the out-of-line value when it is not compressed.
MAIN allows compression but not out-of-line storage. (Actually, out-of-line storage will still be performed for such columns, but only as a last resort when there is no other way to make the row small enough to fit on a page.)

默认选项为EXTENDED。
我们不妨尝试PLAIN和EXTERNAL这两种选项：
PLAIN

--PLAINtestdb=#droptableifexistst3;11','12','13');insertintot3values(2,rpad('2',4000,'2'),rpad('3',4000,'3'),rpad('4',4000,'4'));DROPTABLEtestdb=#createtablet3(idint,c1char(4000),c2char(4000),c3char(4000));CREATETABLEtestdb=#testdb=#altertablet3alterc1setstorageplain;ALTERTABLEtestdb=#altertablet3alterc2setstorageplain;ALTERTABLEtestdb=#altertablet3alterc3setstorageplain;ALTERTABLEtestdb=#testdb=#\d+t3Table"public.t3"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description--------+-----------------+-----------+----------+---------+---------+--------------+-------------id|integer||||plain||c1|character(4000)||||plain||c2|character(4000)||||plain||c3|character(4000)||||plain||testdb=#testdb=#insertintot3values(1,'11','12','13');2018-07-2811:32:07.561CST[1576]ERROR:rowistoobig:size12040,maximumsize81602018-07-2811:32:07.561CST[1576]STATEMENT:insertintot3values(1,'11','12','13');ERROR:rowistoobig:size12040,maximumsize8160testdb=#insertintot3values(2,rpad('2',4000,'2'),rpad('3',4000,'3'),rpad('4',4000,'4'));2018-07-2811:32:08.687CST[1576]ERROR:rowistoobig:size12040,maximumsize81602018-07-2811:32:08.687CST[1576]STATEMENT:insertintot3values(2,rpad('2',4000,'2'),rpad('3',4000,'3'),rpad('4',4000,'4'));ERROR:rowistoobig:size12040,maximumsize8160--如果使用PLAIN选项，数据行限制为8160（32Bytes用于头部信息、ItemId和Specialspace等）

EXTERNAL

testdb=#--EXTERNAL选项，RowSize>8Ktestdb=#droptableifexistst4;TERNAL;insertintot4values(1,'11','12','13');insertintot4values(2,rpad('2',4000,'2'),rpad('3',4000,'3'),rpad('4',4000,'4'));DROPTABLEtestdb=#createtablet4(idint,c1char(4000),c2char(4000),c3char(4000));CREATETABLEtestdb=#testdb=#altertablet4alterc1setstorageEXTERNAL;ALTERTABLEtestdb=#altertablet4alterc2setstorageEXTERNAL;ALTERTABLEtestdb=#altertablet4alterc3setstorageEXTERNAL;ALTERTABLEtestdb=#testdb=#insertintot4values(1,'11','12','13');INSERT01testdb=#insertintot4values(2,rpad('2',4000,'2'),rpad('3',4000,'3'),rpad('4',4000,'4'));INSERT01testdb=#testdb=#checkpoint;CHECKPOINTtestdb=#testdb=#selectpg_relation_filepath('t4');pg_relation_filepath----------------------base/16477/26712(1row)[xdb@localhostutf8db]$hexdump-C$PGDATA/base/16477/26712000000000100000030572bdb000000002000501f|....0W+......P.|000000100020042000000000a89fa400509fa400|..........P...|0000002000000000000000000000000000000000|................|*00001f50159c1800000000000000000000000000|................|00001f600200040006081800020000000112a40f|................|00001f700000a00f0000616800005b6800000112|......ah..[h....|00001f80a40f0000a00f0000626800005b680000|........bh..[h..|00001f900112a40f0000a00f0000636800005b68|..........ch..[h|00001fa00000000000000000149c180000000000|................|00001fb000000000000000000100040006081800|................|00001fc0010000000112a40f0000a00f00005e68|..............^h|00001fd000005b6800000112a40f0000a00f0000|..[h............|00001fe05f6800005b6800000112a40f0000a00f|_h..[h..........|00001ff00000606800005b680000000000000000|..`h..[h........|00002000--在数据表的block没有存储用户数据，实际的存储数据文件使用以下SQL获得：testdb=#selectreltoastrelidfrompg_classwhererelname='t4';reltoastrelid---------------26715(1row)[xdb@localhostutf8db]$hexdump-C$PGDATA/base/16477/26715000000000100000008082bdb0000000028000008|......+.....(...|0000001000200420000000001098e00f2090e00f|.............|00000020f08f58000088e00f0000000000000000|..X.............|0000003000000000000000000000000000000000|................|*00000800149c1800000000000000000000000000|................|0000081004000300020818005f68000000000000|........_h......|00000820401f0000313220202020202020202020|@...12|0000083020202020202020202020202020202020||*00000ff0149c1800000000000000000000000000|................|0000100003000300020818005e68000002000000|........^h......|0000101030000000202020202020202000000000|0.......|00001020149c1800000000000000000000000000|................|0000103002000300020818005e68000001000000|........^h......|00001040401f0000202020202020202020202020|@...|0000105020202020202020202020202020202020||*00001810149c1800000000000000000000000000|................|0000182001000300020818005e68000000000000|........^h......|00001830401f0000313120202020202020202020|@...11|0000184020202020202020202020202020202020||*000020000100000030222bdb000000002c00d007|....0"+.....,...|0000201000200420000000001098e00fe0975800|............X.|00002020f08fe00f0088e00fd087580000000000|..........X.....|0000203000000000000000000000000000000000|................|*000027d0149c1800000000000000000000000100|................|000027e005000300020818006068000002000000|........`h......|000027f030000000202020202020202000000000|0.......|00002800149c1800000000000000000000000100|................|0000281004000300020818006068000001000000|........`h......|00002820401f0000202020202020202020202020|@...|0000283020202020202020202020202020202020||*00002ff0149c1800000000000000000000000100|................|0000300003000300020818006068000000000000|........`h......|00003010401f0000313320202020202020202020|@...13|0000302020202020202020202020202020202020||*000037e0149c1800000000000000000000000100|................|000037f002000300020818005f68000002000000|........_h......|0000380030000000202020202020202000000000|0.......|00003810149c1800000000000000000000000100|................|0000382001000300020818005f68000001000000|........_h......|00003830401f0000202020202020202020202020|@...|0000384020202020202020202020202020202020||*0000400001000000503c2bdb0000000028000008|....P<+.....(...|0000401000200420000000001098e00f2090e00f|.............|00004020f08f58000088e00f0000000000000000|..X.............|0000403000000000000000000000000000000000|................|*00004800159c1800000000000000000000000200|................|0000481004000300020818006268000000000000|........bh......|00004820401f0000333333333333333333333333|@...333333333333|0000483033333333333333333333333333333333|3333333333333333|*00004ff0159c1800000000000000000000000200|................|0000500003000300020818006168000002000000|........ah......|0000501030000000323232323232323200000000|0...22222222....|00005020159c1800000000000000000000000200|................|0000503002000300020818006168000001000000|........ah......|00005040401f0000323232323232323232323232|@...222222222222|0000505032323232323232323232323232323232|2222222222222222|*00005810159c1800000000000000000000000200|................|0000582001000300020818006168000000000000|........ah......|00005830401f0000323232323232323232323232|@...222222222222|0000584032323232323232323232323232323232|2222222222222222|*000060000100000078562bdb000000002c00d007|....xV+.....,...|0000601000200420000000001098e00fe0975800|............X.|00006020f08fe00f0088e00fd087580000000000|..........X.....|0000603000000000000000000000000000000000|................|*000067d0159c1800000000000000000000000300|................|000067e005000300020818006368000002000000|........ch......|000067f030000000343434343434343400000000|0...44444444....|00006800159c1800000000000000000000000300|................|0000681004000300020818006368000001000000|........ch......|00006820401f0000343434343434343434343434|@...444444444444|0000683034343434343434343434343434343434|4444444444444444|*00006ff0159c1800000000000000000000000300|................|0000700003000300020818006368000000000000|........ch......|00007010401f0000343434343434343434343434|@...444444444444|0000702034343434343434343434343434343434|4444444444444444|*000077e0159c1800000000000000000000000300|................|000077f002000300020818006268000002000000|........bh......|0000780030000000333333333333333300000000|0...33333333....|00007810159c1800000000000000000000000300|................|0000782001000300020818006268000001000000|........bh......|00007830401f0000333333333333333333333333|@...333333333333|0000784033333333333333333333333333333333|3333333333333333|*00008000[xdb@localhostutf8db]$

到此，相信大家对“Oracle与PostgreSQL中PageSize对数据存储有哪些影响”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！