Column data in Oracle block

[ixion]

Well how would you explain the scenario where the table (very big containing more than 10 million blocks with around 90 columns) is added with "N" more columns & with default value "X".

Will it recreate the whole table by updating all the existing blocks to fit in these columns physically for all rows?


Rgds
Abhay.

The table would not be "recreated" The existing blocks would be modified acording to the documentation and the new columns would be appended to each row. Needless to say this would probably take quite a while to finish.


Regarding space efficiency. Adding a real column number of 2 bytes in length
for a 90 column table with say, 1 million rows. Would waste about 180 Meg in space. But its even worse than that. But you might argue disk is cheap. Well the real problem is in memory. Remember that those disk blocks need to be cached in memory.

To answer "ThomasPS" Your right about the formatting.
The row contains row header followed by N number of columns, where each column contains starts with a 1 or 3 byte length field Then the data for the column follows. Simple.. So there is no need to store any column number they are simply sequential so the column number is implied and associated with the sys.col$ table.

I have no documents. Other than the oracle documentation and my own knowledge.

[ixion]

Here is some block internals info.. but i still wonder why so and may be some more hidden info?

I wonder why column length is included and why not just col number which would reduce lots of headache..


Rgds
Abhay.

Column length is needed because columns are variable in nature. The varchar2 datatype is a perfect example.
consider table with the following columns:
create table t1 ( id number, txt varchar2(2000) );

If oracle used a fixed width representation then inserting one row would consume 2044 bytes (roughly). Now if the user put in something like
insert into t1 values(1, 'test') then the txt field only needs 5 bytes on disk.
1 for the length and 4 for the data. The id field needs 1 for the length and 2 for the number (number fields are not stored as strings). So the totall row would take 8 bytes of storage vs 2044 bytes, Now that is space effecient.

Regards

PS.. Some columns are not variable length, CHAR, DATE come to mind.

[abhaysk]

Column length is needed because columns are variable in nature. The varchar2 datatype is a perfect example.
consider table with the following columns:
create table t1 ( id number, txt varchar2(2000) );

If oracle used a fixed width representation then inserting one row would consume 2044 bytes (roughly). Now if the user put in something like
insert into t1 values(1, 'test') then the txt field only needs 5 bytes on disk.
1 for the length and 4 for the data. The id field needs 1 for the length and 2 for the number (number fields are not stored as strings). So the totall row would take 8 bytes of storage vs 2044 bytes, Now that is space effecient.

Regards

PS.. Some columns are not variable length, CHAR, DATE come to mind.

You totally lost the point.. i was curious to know why on earth oracle is wasting space to have a column length info (by prefixing this info before each column data which takes away 1 to 3 bytes) rather just have a delimiter or col number...

Rgds
Abhay.

[ixion]

You totally lost the point.. i was curious to know why on earth oracle is wasting space to have a column length info (by prefixing this info before each column data which takes away 1 to 3 bytes) rather just have a delimiter or col number...

Rgds
Abhay.

I did miss that indeed.
Ok.. Let me ask the question, what character would you pick as the delimiter?
The character must not ever occur in the users data.

The answer, there is no way to pick a delimeter character or even a string of delimiter characters that are guaranteed not to be in the data.

Thus, oracle must store the column length. Plus there is a real benefit, if it stored it as say a string, every time it returned back a varchar to a proc program it would have to call strlen. This way it intrisically knows and does not need to calculate the lengths of each dataype.

Regards

[slimdave]

Oh, for goodness sake. Google is your friend.

http://database.sarang.net/database/.../db_block.html

[ixion]

Slim dave...

Good post, but apparently this chap doesn't want to click to these links. Your referenced link was also posted quite a few posts above!

I think this one has been beaten into submission.

Regards,
Ken

[slimdave]

I think this one has been beaten into submission.

Ah, now there's something we can all agree on!

[abhaysk]

I did miss that indeed.
Ok.. Let me ask the question, what character would you pick as the delimiter?
The character must not ever occur in the users data.

The answer, there is no way to pick a delimeter character or even a string of delimiter characters that are guaranteed not to be in the data.

Thus, oracle must store the column length.

Well you have column length info before each column's actual data right?.. like wise you could have had just a column number which takes a single byte as against one or 3 bytes depending on length variations..

Which would save the space more efficiently?

Well but as you said below..

Plus there is a real benefit, if it stored it as say a string, every time it returned back a varchar to a proc program it would have to call strlen. This way it intrisically knows and does not need to calculate the lengths of each dataype.

Regards

I dont know about call of strlen.. but this would suerly be a benifit if column length info is present and overrules any space utilization efficiency.

Was that gesture for me.. not reading that link?

Slimdave,

I think i posted that very link in my earlier post..


Rgds
Abhay.