ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 表最多能有1000列还是255列?

表最多能有1000列还是255列?

原创 Linux操作系统 作者:warehouse 时间:2007-10-08 00:00:00 0 删除 编辑

通过测试发现最多可以有1000列,每一row pieve最多255。


SQL> create table tt(id int);
表已创建。

SQL
> edit

已写入 file afiedt
.buf

1
declare

2 v_string varchar2(100);

3 begin

4
for i in 1..1000 loop

5 v_string
:= 'alter table tt add col'||i||' int' ;

6 execute immediate v_string;

7 end loop ;

8* end;
SQL> /

declare

*
第 1 行出现错误:
ORA-01792: 表或视图中的最大列数为 1000

ORA
-06512: 在 line 6



SQL
>
--===================

Row Format and Size

Oracle stores each row of a database table containing data
for less than 256 columns as one or more row pieces. If an entire row can be inserted into a single data block, then Oracle stores the row as one row piece. However, if all of a row's data cannot be inserted into a single data block or if an update to an existing row causes the row to outgrow its data block, then Oracle stores the row using multiple row pieces. A data block usually contains only one row piece for each row. When Oracle must store a row in more than one row piece, it is chained across multiple blocks.



When a table has more than 255 columns, rows that have data after the 255th column are likely to be chained within the same block. This is called intra-block chaining. A chained row'
s pieces are chained together using the rowids of the pieces. With intra-block chaining, users receive all the data in the same block. If the row fits in the block, users do not see an effect in I/O performance, because no extra I/O operation is required to retrieve the rest of the row.



--======================================================
接着上面的测试再做个测试看看

SQL
> insert into tt(id) values(1)

2 ;


已创建 1 行。



SQL
> commit;


提交完成。



SQL
> edit

已写入 file afiedt
.buf



1
declare

2 v_string varchar2(100);

3 begin

4
for i in 1..999 loop

5 v_string
:= 'update tt set col'||i||'='||i ;

6 execute immediate v_string;

7 commit;

8 end loop ;

9* end;
SQL> /


SQL> analyze table tt compute statistics;


表已分析。



SQL
> select blocks,chaiN_cnt from user_tables where table_name='TT';



BLOCKS CHAIN_CNT
---------- ----------

4 0



SQL
> select id , rowid from tt;



ID ROWID
---------- ------------------

1 AAADD5AAEAAAAAUAAA



SQL
> alter system dump datafile 4 block 20;


系统已更改。
--===============================================================
dump:
Start dump data blocks tsn: 8 file#: 4 minblk 20 maxblk 20
buffer tsn: 8 rdba: 0x01000014 (4/20)
scn: 0x0000.001ae24e seq: 0x01 flg: 0x04 tail: 0xe24e0601

frmt
: 0x02 chkval: 0x641b type: 0x06=trans data

Hex dump of block
: st=0, typ_found=1

Dump of memory from 0x06DB2200 to 0x06DB4200
..................................................
6DB41F0 3503C203 3603C203 3703C203 E24E0601 [...5...6...7..N.]
Block header dump: 0x01000014

Object id on Block
? Y

seg
/obj: 0x30f9 csc: 0x00.1ae24e itc: 3 flg: E typ: 1 - DATA

brn
: 0 bdba: 0x1000011 ver: 0x01 opc: 0

inc
: 0 exflg: 0



Itl Xid Uba Flag Lck Scn
/Fsc

0x01 0x0007.022.00000106 0x00000000.0000.00 C
--- 0 scn 0x0000.001ae231

0x02 0x0000.000.00000000 0x00000000.0000.00
---- 0 fsc 0x0000.00000000

0x03 0x0000.000.00000000 0x00000000.0000.00
---- 0 fsc 0x0000.00000000



data_block_dump
,data header at 0x6db227c
===============
tsiz: 0x1f80

hsiz
: 0x1a

pbl
: 0x06db227c

bdba
: 0x01000014

76543210

flag
=--------
ntab=1

nrow
=4

frre
=-1

fsbo
=0x1a

fseo
=0x102f

avsp
=0x1015

tosp
=0x1015

0xe
<img src="images/smilies/13.gif" border="0" alt="吐舌">ti[0] nrow=4 offs=0

0x12
<img src="images/smilies/13.gif" border="0" alt="吐舌">ri[0] offs=0x1be1

0x14
<img src="images/smilies/13.gif" border="0" alt="吐舌">ri[1] offs=0x17df

0x16
<img src="images/smilies/13.gif" border="0" alt="吐舌">ri[2] offs=0x13dc

0x18
<img src="images/smilies/13.gif" border="0" alt="吐舌">ri[3] offs=0x102f

block_row_dump
:
tab 0, row 0, @0x1be1

tl
: 927 fb: --H-F--- lb: 0x0 cc: 255

nrid
: 0x01000014.1

col 0
: [ 2] c1 02

col 1
: [ 2] c1 02

col 2
: [ 2] c1 03

col 3
: [ 2] c1 04

col 4
: [ 2] c1 05

col 5
: [ 2] c1 06

col 6
: [ 2] c1 07

col 7
: [ 2] c1 08

col 8
: [ 2] c1 09

col 9
: [ 2] c1 0a

col 10
: [ 2] c1 0b

col 11
: [ 2] c1 0c

col 12
: [ 2] c1 0d

col 13
: [ 2] c1 0e

col 14
: [ 2] c1 0f

col 15
: [ 2] c1 10

col 16
: [ 2] c1 11

col 17
: [ 2] c1 12

col 18
: [ 2] c1 13

col 19
: [ 2] c1 14

col 20
: [ 2] c1 15

col 21
: [ 2] c1 16

col 22
: [ 2] c1 17

col 23
: [ 2] c1 18

col 24
: [ 2] c1 19

col 25
: [ 2] c1 1a

col 26
: [ 2] c1 1b

col 27
: [ 2] c1 1c

col 28
: [ 2] c1 1d

col 29
: [ 2] c1 1e

col 30
: [ 2] c1 1f

col 31
: [ 2] c1 20

col 32
: [ 2] c1 21

col 33
: [ 2] c1 22

col 34
: [ 2] c1 23

col 35
: [ 2] c1 24

col 36
: [ 2] c1 25

col 37
: [ 2] c1 26

col 38
: [ 2] c1 27

col 39
: [ 2] c1 28

col 40
: [ 2] c1 29

col 41
: [ 2] c1 2a

col 42
: [ 2] c1 2b

col 43
: [ 2] c1 2c

col 44
: [ 2] c1 2d

col 45
: [ 2] c1 2e

col 46
: [ 2] c1 2f

col 47
: [ 2] c1 30

col 48
: [ 2] c1 31

col 49
: [ 2] c1 32

col 50
: [ 2] c1 33

col 51
: [ 2] c1 34

col 52
: [ 2] c1 35

col 53
: [ 2] c1 36

col 54
: [ 2] c1 37

col 55
: [ 2] c1 38

col 56
: [ 2] c1 39

col 57
: [ 2] c1 3a

col 58
: [ 2] c1 3b

col 59
: [ 2] c1 3c

col 60
: [ 2] c1 3d

col 61
: [ 2] c1 3e

col 62
: [ 2] c1 3f

col 63
: [ 2] c1 40

col 64
: [ 2] c1 41

col 65
: [ 2] c1 42

col 66
: [ 2] c1 43

col 67
: [ 2] c1 44

col 68
: [ 2] c1 45

col 69
: [ 2] c1 46

col 70
: [ 2] c1 47

col 71
: [ 2] c1 48

col 72
: [ 2] c1 49

col 73
: [ 2] c1 4a

col 74
: [ 2] c1 4b

col 75
: [ 2] c1 4c

col 76
: [ 2] c1 4d

col 77
: [ 2] c1 4e

col 78
: [ 2] c1 4f

col 79
: [ 2] c1 50

col 80
: [ 2] c1 51

col 81
: [ 2] c1 52

col 82
: [ 2] c1 53

col 83
: [ 2] c1 54

col 84
: [ 2] c1 55

col 85
: [ 2] c1 56

col 86
: [ 2] c1 57

col 87
: [ 2] c1 58

col 88
: [ 2] c1 59

col 89
: [ 2] c1 5a

col 90
: [ 2] c1 5b

col 91
: [ 2] c1 5c

col 92
: [ 2] c1 5d

col 93
: [ 2] c1 5e

col 94
: [ 2] c1 5f

col 95
: [ 2] c1 60

col 96
: [ 2] c1 61

col 97
: [ 2] c1 62

col 98
: [ 2] c1 63

col 99
: [ 2] c1 64

col 100
: [ 2] c2 02

col 101
: [ 3] c2 02 02

col 102
: [ 3] c2 02 03

col 103
: [ 3] c2 02 04

col 104
: [ 3] c2 02 05

col 105
: [ 3] c2 02 06

col 106
: [ 3] c2 02 07

col 107
: [ 3] c2 02 08

col 108
: [ 3] c2 02 09

col 109
: [ 3] c2 02 0a

col 110
: [ 3] c2 02 0b

col 111
: [ 3] c2 02 0c

col 112
: [ 3] c2 02 0d

col 113
: [ 3] c2 02 0e

col 114
: [ 3] c2 02 0f

col 115
: [ 3] c2 02 10

col 116
: [ 3] c2 02 11

col 117
: [ 3] c2 02 12

col 118
: [ 3] c2 02 13

col 119
: [ 3] c2 02 14

col 120
: [ 3] c2 02 15

col 121
: [ 3] c2 02 16

col 122
: [ 3] c2 02 17

col 123
: [ 3] c2 02 18

col 124
: [ 3] c2 02 19

col 125
: [ 3] c2 02 1a

col 126
: [ 3] c2 02 1b

col 127
: [ 3] c2 02 1c

col 128
: [ 3] c2 02 1d

col 129
: [ 3] c2 02 1e

col 130
: [ 3] c2 02 1f

col 131
: [ 3] c2 02 20

col 132
: [ 3] c2 02 21

col 133
: [ 3] c2 02 22

col 134
: [ 3] c2 02 23

col 135
: [ 3] c2 02 24

col 136
: [ 3] c2 02 25

col 137
: [ 3] c2 02 26

col 138
: [ 3] c2 02 27

col 139
: [ 3] c2 02 28

col 140
: [ 3] c2 02 29

col 141
: [ 3] c2 02 2a

col 142
: [ 3] c2 02 2b

col 143
: [ 3] c2 02 2c

col 144
: [ 3] c2 02 2d

col 145
: [ 3] c2 02 2e

col 146
: [ 3] c2 02 2f

col 147
: [ 3] c2 02 30

col 148
: [ 3] c2 02 31

col 149
: [ 3] c2 02 32

col 150
: [ 3] c2 02 33

col 151
: [ 3] c2 02 34

col 152
: [ 3] c2 02 35

col 153
: [ 3] c2 02 36

col 154
: [ 3] c2 02 37

col 155
: [ 3] c2 02 38

col 156
: [ 3] c2 02 39

col 157
: [ 3] c2 02 3a

col 158
: [ 3] c2 02 3b

col 159
: [ 3] c2 02 3c

col 160
: [ 3] c2 02 3d

col 161
: [ 3] c2 02 3e

col 162
: [ 3] c2 02 3f

col 163
: [ 3] c2 02 40

col 164
: [ 3] c2 02 41

col 165
: [ 3] c2 02 42

col 166
: [ 3] c2 02 43

col 167
: [ 3] c2 02 44

col 168
: [ 3] c2 02 45

col 169
: [ 3] c2 02 46

col 170
: [ 3] c2 02 47

col 171
: [ 3] c2 02 48

col 172
: [ 3] c2 02 49

col 173
: [ 3] c2 02 4a

col 174
: [ 3] c2 02 4b

col 175
: [ 3] c2 02 4c

col 176
: [ 3] c2 02 4d

col 177
: [ 3] c2 02 4e

col 178
: [ 3] c2 02 4f

col 179
: [ 3] c2 02 50

col 180
: [ 3] c2 02 51

col 181
: [ 3] c2 02 52

col 182
: [ 3] c2 02 53

col 183
: [ 3] c2 02 54

col 184
: [ 3] c2 02 55

col 185
: [ 3] c2 02 56

col 186
: [ 3] c2 02 57

col 187
: [ 3] c2 02 58

col 188
: [ 3] c2 02 59

col 189
: [ 3] c2 02 5a

col 190
: [ 3] c2 02 5b

col 191
: [ 3] c2 02 5c

col 192
: [ 3] c2 02 5d

col 193
: [ 3] c2 02 5e

col 194
: [ 3] c2 02 5f

col 195
: [ 3] c2 02 60

col 196
: [ 3] c2 02 61

col 197
: [ 3] c2 02 62

col 198
: [ 3] c2 02 63

col 199
: [ 3] c2 02 64

col 200
: [ 2] c2 03

col 201
: [ 3] c2 03 02

col 202
: [ 3] c2 03 03

col 203
: [ 3] c2 03 04

col 204
: [ 3] c2 03 05

col 205
: [ 3] c2 03 06

col 206
: [ 3] c2 03 07

col 207
: [ 3] c2 03 08

col 208
: [ 3] c2 03 09

col 209
: [ 3] c2 03 0a

col 210
: [ 3] c2 03 0b

col 211
: [ 3] c2 03 0c

col 212
: [ 3] c2 03 0d

col 213
: [ 3] c2 03 0e

col 214
: [ 3] c2 03 0f

col 215
: [ 3] c2 03 10

col 216
: [ 3] c2 03 11

col 217
: [ 3] c2 03 12

col 218
: [ 3] c2 03 13

col 219
: [ 3] c2 03 14

col 220
: [ 3] c2 03 15

col 221
: [ 3] c2 03 16

col 222
: [ 3] c2 03 17

col 223
: [ 3] c2 03 18

col 224
: [ 3] c2 03 19

col 225
: [ 3] c2 03 1a

col 226
: [ 3] c2 03 1b

col 227
: [ 3] c2 03 1c

col 228
: [ 3] c2 03 1d

col 229
: [ 3] c2 03 1e

col 230
: [ 3] c2 03 1f

col 231
: [ 3] c2 03 20

col 232
: [ 3] c2 03 21

col 233
: [ 3] c2 03 22

col 234
: [ 3] c2 03 23

col 235
: [ 3] c2 03 24

col 236
: [ 3] c2 03 25

col 237
: [ 3] c2 03 26

col 238
: [ 3] c2 03 27

col 239
: [ 3] c2 03 28

col 240
: [ 3] c2 03 29

col 241
: [ 3] c2 03 2a

col 242
: [ 3] c2 03 2b

col 243
: [ 3] c2 03 2c

col 244
: [ 3] c2 03 2d

col 245
: [ 3] c2 03 2e

col 246
: [ 3] c2 03 2f

col 247
: [ 3] c2 03 30

col 248
: [ 3] c2 03 31

col 249
: [ 3] c2 03 32

col 250
: [ 3] c2 03 33

col 251
: [ 3] c2 03 34

col 252
: [ 3] c2 03 35

col 253
: [ 3] c2 03 36

col 254
: [ 3] c2 03 37

tab 0
, row 1, @0x17df

tl
: 1026 fb: -------- lb: 0x0 cc: 255

nrid
: 0x01000014.2

col 0
: [ 3] c2 03 38

col 1
: [ 3] c2 03 39

col 2
: [ 3] c2 03 3a

col 3
: [ 3] c2 03 3b

col 4
: [ 3] c2 03 3c

col 5
: [ 3] c2 03 3d

col 6
: [ 3] c2 03 3e

col 7
: [ 3] c2 03 3f

col 8
: [ 3] c2 03 40

col 9
: [ 3] c2 03 41

col 10
: [ 3] c2 03 42

col 11
: [ 3] c2 03 43

col 12
: [ 3] c2 03 44

col 13
: [ 3] c2 03 45

col 14
: [ 3] c2 03 46

col 15
: [ 3] c2 03 47

col 16
: [ 3] c2 03 48

col 17
: [ 3] c2 03 49

col 18
: [ 3] c2 03 4a

col 19
: [ 3] c2 03 4b

col 20
: [ 3] c2 03 4c

col 21
: [ 3] c2 03 4d

col 22
: [ 3] c2 03 4e

col 23
: [ 3] c2 03 4f

col 24
: [ 3] c2 03 50

col 25
: [ 3] c2 03 51

col 26
: [ 3] c2 03 52

col 27
: [ 3] c2 03 53

col 28
: [ 3] c2 03 54

col 29
: [ 3] c2 03 55

col 30
: [ 3] c2 03 56

col 31
: [ 3] c2 03 57

col 32
: [ 3] c2 03 58

col 33
: [ 3] c2 03 59

col 34
: [ 3] c2 03 5a

col 35
: [ 3] c2 03 5b

col 36
: [ 3] c2 03 5c

col 37
: [ 3] c2 03 5d

col 38
: [ 3] c2 03 5e

col 39
: [ 3] c2 03 5f

col 40
: [ 3] c2 03 60

col 41
: [ 3] c2 03 61

col 42
: [ 3] c2 03 62

col 43
: [ 3] c2 03 63

col 44
: [ 3] c2 03 64

col 45
: [ 2] c2 04

col 46
: [ 3] c2 04 02

col 47
: [ 3] c2 04 03

col 48
: [ 3] c2 04 04

col 49
: [ 3] c2 04 05

col 50
: [ 3] c2 04 06

col 51
: [ 3] c2 04 07

col 52
: [ 3] c2 04 08

col 53
: [ 3] c2 04 09

col 54
: [ 3] c2 04 0a

col 55
: [ 3] c2 04 0b

col 56
: [ 3] c2 04 0c

col 57
: [ 3] c2 04 0d

col 58
: [ 3] c2 04 0e

col 59
: [ 3] c2 04 0f

col 60
: [ 3] c2 04 10

col 61
: [ 3] c2 04 11

col 62
: [ 3] c2 04 12

col 63
: [ 3] c2 04 13

col 64
: [ 3] c2 04 14

col 65
: [ 3] c2 04 15

col 66
: [ 3] c2 04 16
<

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/19602/viewspace-61737/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 个人简介
请登录后发表评论 登录
全部评论