This is my first post :). So pardon me in case of any mistake.
I have been reading the teradata storage architecture in detail. Recently found very interesting article explaining how a row is stored in terdata using master index, cylinder index and how block storage is handled. I found that blocks store data in sorted order. So my question is, in case of new row is insertion or row deletion, does block is resorted on row_id ?
Could you please shed some light on this ?
The rows in the data blocks are not sorted, they are just placed at the end of a heap of storage. The block contains an index list though which is ordered by the rowid. Likewise the blocks are not physically ordered in the cylinders, just the cylinder index entries are ordered by rowid. And the cylinders are not only not sorted but they are not necessarily physically adjacent - the master index entries are sorted. So three very small binary searches to find a row.
Thanks a lot for the reply Todd. It was really helpful.
Just to revise, say on block level, data will be just apended to blocks whereas the index will be in sorted order for performing binary search. Index will also hold pointer to actual rows, which will be used to locate the row. Right ?
Correct. The row index list is just a set of offsets from the beginning of the block rather than a pointer but the effect is the same.