Table Block Size


Table Block Size

one of the new features available from 12 is having a different blocksize at table level. From what i read and understand having a bigger blocksize should help increase the perforamnce of Selects and Inserts as well? I have a 1.3 TB table compressed that is denoramlized and has an AJI on top of it. The table load has been optimized nd wanted to further optimize by changing the blocksize. I did increase the blocksize to 128 KB and comapred with the baseline and yielded no perforamnce gain

I am surprised that i did not see any gain and it was taking the same time. Neither the AJI rebuild was faster.. I drop and rebuild the AJI. Any pointers on what could i be doing wrong? Or the block size will have a barring only on select but not inserts? it doesnt appear that way as the AJI build took the same time as well. 

appreciate any inputs on this?


Re: Table Block Size

My viewpoint:

Teradata 14.10 , you can increase upto 1 MB. Increasing the block size can reduce I/O contention. Metadata exhauting  read is minzimized. But having fewer blocks, may result into losing parallelism. Fine tuning  blocksize with rowsize is important.

When most queries to the table access a single row of data, the table DBs should be smaller to enhance the efficiency of retrieving the row.

When queries accessing data across the entire table, the DBs should be larger to reduce the number of I/Os required to read the table.

Just for information,you can check with UDA, where interaction of hadoop, aster DB and TD DB. Hadoop is set at 64 MB but you can change it on the fly too.