-
Column data in Oracle block
Consider a table having 3 columns,namely EMP(c1,c2,c3)
Using data block header,Oracle identifies that a block containing rows
for EMP table.My doubt in this is,
In the row data of a block,how oracle identifies that this piece of data belong to a particular column(i.e.,column data) of EMP table i.e,how it identifies this portion of data belongs to C1,C2 & C3? Is oracle following any offset mechanism for this classification?Please suggest your answers & views on this question.
-
I don't think Oracle explained in that level.
http://www.stanford.edu/dept/itss/do...43/logical.htm
Explaing about, how the Blocks are confiured.
-
Code:
data_block_dump,data header at 0xd603864
===============
tsiz: 0x1f98
hsiz: 0xa2
pbl: 0x0d603864
bdba: 0x01008398
76543210
flag=--------
ntab=1
nrow=72
frre=-1
fsbo=0xa2
fseo=0x1cc8
avsp=0x1c26
tosp=0x1c26
0xe:pti[0] nrow=72 offs=0
0x12:pri[0] offs=0x1f8e
0x14:pri[1] offs=0x1f84
0x16:pri[2] offs=0x1f7a
0x18:pri[3] offs=0x1f70
0x1a:pri[4] offs=0x1f66
0x1c:pri[5] offs=0x1f5c
0x1e:pri[6] offs=0x1f52
0x20:pri[7] offs=0x1f48
0x22:pri[8] offs=0x1f3e
0x24:pri[9] offs=0x1f34
0x26:pri[10] offs=0x1f2a
0x28:pri[11] offs=0x1f20
0x2a:pri[12] offs=0x1f16
0x2c:pri[13] offs=0x1f0c
0x2e:pri[14] offs=0x1f02
0x30:pri[15] offs=0x1ef8
0x32:pri[16] offs=0x1eee
0x34:pri[17] offs=0x1ee4
0x36:pri[18] offs=0x1eda
0x38:pri[19] offs=0x1ed0
0x3a:pri[20] offs=0x1ec6
0x3c:pri[21] offs=0x1ebc
0x3e:pri[22] offs=0x1eb2
0x40:pri[23] offs=0x1ea8
0x42:pri[24] offs=0x1e9e
0x44:pri[25] offs=0x1e94
0x46:pri[26] offs=0x1e8a
0x48:pri[27] offs=0x1e80
0x4a:pri[28] offs=0x1e76
0x4c:pri[29] offs=0x1e6c
0x4e:pri[30] offs=0x1e62
0x50:pri[31] offs=0x1e58
0x52:pri[32] offs=0x1e4e
0x54:pri[33] offs=0x1e44
0x56:pri[34] offs=0x1e3a
0x58:pri[35] offs=0x1e30
0x5a:pri[36] offs=0x1e26
0x5c:pri[37] offs=0x1e1c
0x5e:pri[38] offs=0x1e12
0x60:pri[39] offs=0x1e08
0x62:pri[40] offs=0x1dfe
0x64:pri[41] offs=0x1df4
0x66:pri[42] offs=0x1dea
0x68:pri[43] offs=0x1de0
0x6a:pri[44] offs=0x1dd6
0x6c:pri[45] offs=0x1dcc
0x6e:pri[46] offs=0x1dc2
0x70:pri[47] offs=0x1db8
0x72:pri[48] offs=0x1dae
0x74:pri[49] offs=0x1da4
0x76:pri[50] offs=0x1d9a
0x78:pri[51] offs=0x1d90
0x7a:pri[52] offs=0x1d86
0x7c:pri[53] offs=0x1d7c
0x7e:pri[54] offs=0x1d72
0x80:pri[55] offs=0x1d68
0x82:pri[56] offs=0x1d5e
0x84:pri[57] offs=0x1d54
0x86:pri[58] offs=0x1d4a
0x88:pri[59] offs=0x1d40
0x8a:pri[60] offs=0x1d36
0x8c:pri[61] offs=0x1d2c
0x8e:pri[62] offs=0x1d22
0x90:pri[63] offs=0x1d18
0x92:pri[64] offs=0x1d0e
0x94:pri[65] offs=0x1d04
0x96:pri[66] offs=0x1cfa
0x98:pri[67] offs=0x1cf0
0x9a:pri[68] offs=0x1ce6
0x9c:pri[69] offs=0x1cdc
0x9e:pri[70] offs=0x1cd2
0xa0:pri[71] offs=0x1cc8
block_row_dump:
tab 0, row 0, @0x1f8e
tl: 10 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 58 58 58
tab 0, row 1, @0x1f84
tl: 10 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 58 58 58
tab 0, row 2, @0x1f7a
tl: 10 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 58 58 58
tab 0, row 3, @0x1f70
tl: 10 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 59 59 59
tab 0, row 4, @0x1f66
tl: 10 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 59 59 59
tab 0, row 5, @0x1f5c
tl: 10 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 59 59 59
tab 0, row 6, @0x1f52
tl: 10 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 5a 5a 5a
tab 0, row 7, @0x1f48
tl: 10 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 5a 5a 5a
tab 0, row 8, @0x1f3e
tl: 10 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 5a 5a 5a
tab 0, row 9, @0x1f34
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 58 58 58
tab 0, row 10, @0x1f2a
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 58 58 58
tab 0, row 11, @0x1f20
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 58 58 58
tab 0, row 12, @0x1f16
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 59 59 59
tab 0, row 13, @0x1f0c
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 59 59 59
tab 0, row 14, @0x1f02
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 59 59 59
tab 0, row 15, @0x1ef8
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 5a 5a 5a
tab 0, row 16, @0x1eee
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 5a 5a 5a
tab 0, row 17, @0x1ee4
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 5a 5a 5a
tab 0, row 18, @0x1eda
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 58 58 58
tab 0, row 19, @0x1ed0
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 58 58 58
tab 0, row 20, @0x1ec6
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 58 58 58
tab 0, row 21, @0x1ebc
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 59 59 59
tab 0, row 22, @0x1eb2
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 59 59 59
tab 0, row 23, @0x1ea8
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 59 59 59
tab 0, row 24, @0x1e9e
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 5a 5a 5a
tab 0, row 25, @0x1e94
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 5a 5a 5a
tab 0, row 26, @0x1e8a
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 5a 5a 5a
tab 0, row 27, @0x1e80
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 58 58 58
tab 0, row 28, @0x1e76
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 58 58 58
tab 0, row 29, @0x1e6c
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 58 58 58
tab 0, row 30, @0x1e62
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 59 59 59
tab 0, row 31, @0x1e58
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 59 59 59
tab 0, row 32, @0x1e4e
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 59 59 59
tab 0, row 33, @0x1e44
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 5a 5a 5a
tab 0, row 34, @0x1e3a
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 5a 5a 5a
tab 0, row 35, @0x1e30
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 5a 5a 5a
tab 0, row 36, @0x1e26
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 58 58 58
tab 0, row 37, @0x1e1c
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 58 58 58
tab 0, row 38, @0x1e12
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 58 58 58
tab 0, row 39, @0x1e08
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 59 59 59
tab 0, row 40, @0x1dfe
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 59 59 59
tab 0, row 41, @0x1df4
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 59 59 59
tab 0, row 42, @0x1dea
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 5a 5a 5a
tab 0, row 43, @0x1de0
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 5a 5a 5a
tab 0, row 44, @0x1dd6
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 5a 5a 5a
tab 0, row 45, @0x1dcc
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 58 58 58
tab 0, row 46, @0x1dc2
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 58 58 58
tab 0, row 47, @0x1db8
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 58 58 58
tab 0, row 48, @0x1dae
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 59 59 59
tab 0, row 49, @0x1da4
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 59 59 59
tab 0, row 50, @0x1d9a
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 59 59 59
tab 0, row 51, @0x1d90
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 5a 5a 5a
tab 0, row 52, @0x1d86
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 5a 5a 5a
tab 0, row 53, @0x1d7c
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 5a 5a 5a
tab 0, row 54, @0x1d72
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 58 58 58
tab 0, row 55, @0x1d68
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 58 58 58
tab 0, row 56, @0x1d5e
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 58 58 58
tab 0, row 57, @0x1d54
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 59 59 59
tab 0, row 58, @0x1d4a
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 59 59 59
tab 0, row 59, @0x1d40
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 59 59 59
tab 0, row 60, @0x1d36
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 5a 5a 5a
tab 0, row 61, @0x1d2c
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 5a 5a 5a
tab 0, row 62, @0x1d22
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 5a 5a 5a
tab 0, row 63, @0x1d18
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 58 58 58
tab 0, row 64, @0x1d0e
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 58 58 58
tab 0, row 65, @0x1d04
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 58 58 58
tab 0, row 66, @0x1cfa
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 59 59 59
tab 0, row 67, @0x1cf0
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 59 59 59
tab 0, row 68, @0x1ce6
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 59 59 59
tab 0, row 69, @0x1cdc
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 5a 5a 5a
tab 0, row 70, @0x1cd2
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 5a 5a 5a
tab 0, row 71, @0x1cc8
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 5a 5a 5a
end_of_block_dump
The block dump shows ROW DIRECTORY (72 rows) and each column is separated by column number. Definitely Oracle is using certain internal algorithm to track this !!
-
I would assume there will be some number appended to the data in each column for each row or in other words some number (number corresponding to the col number) will be appened along with data while insertion.. so that it can decode while retrival.
- Abhay.
Last edited by abhaysk; 05-22-2006 at 07:37 AM.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
excuse my ignorance but knowing this little tid bit of information is going to help me in my daily DBA duties how?
Oracle it's not just a database it's a lifestyle!
--------------
BTW....You need to get a girlfriend who's last name isn't .jpg
-
not at all... this info may be for curiosity.. or may be for some research?
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Thanks Thomasps,
Using which tool,you've generated that,"data_block_dump".
-
logical block dump:
alter system dump datafile 10 block 104;
physical dump
dd if=yourdatafile bs=blocksize skip=104 count=1 (nuber of blocks to dump)
-
Originally Posted by dba127
Consider a table having 3 columns,namely EMP(c1,c2,c3)
Using data block header,Oracle identifies that a block containing rows
for EMP table.My doubt in this is,
In the row data of a block,how oracle identifies that this piece of data belong to a particular column(i.e.,column data) of EMP table i.e,how it identifies this portion of data belongs to C1,C2 & C3? Is oracle following any offset mechanism for this classification?Please suggest your answers & views on this question.
Yes it certainly is.
The block contains a table directory (offsets for clustered table ).. And Row directory an offset list for the starting address for row data that is all relative to the blocks header structures.
Each row has some flag fields as well as a count of the columns for the row. Combine that whith some basic info from sys.col$ to generate a mapping between the internal column and the external column position.
-
Originally Posted by abhaysk
I would assume there will be some number appended to the data in each column for each row or in other words some number (number corresponding to the col number) will be appened along with data while insertion.. so that it can decode while retrival.
- Abhay.
- Abhay,
There is no column number appended to each column during insertion.
Each column is in a physical position that is determined by the sys.col$ table.
Writing a "logical" column number into the bock would waste a lot of space. Oracle is very space efficient. For instance, the length of the column is stored along with the column data. If it < 255 bytes in lenght then a single byte is used to store the length. But if it longer then 3 bytes are used to store the length.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|