Can you please help me with knowing about space consumption by Primary Index of a Table. I want to know how much PERM does it consumes.
Primary Index don't cost you space unlike the secondary indexes which maintain the sub tables
In context to the above Question, just want to know how can we estimate the Size of the Secondary Index Subtable and also
do we need to take in to account of Secondary Index sub table during Capacity planning or DB size estimation for more accuracy ?
The beautiful thing is _why_ primary index doesn't cost you space. This is because Primary Index is effectively a part of the data storage mechanism that addresses Data blocks on the AMPs: Master Index -> Cylinder Index -> Data block. The details are available in Teradata documentation. As a result, you get two things at once - ability to address certain data blocks, and ability to locate data block(s) that correspond to certain Primary Index value (of a certain table). Therefore, you don't have to pay extra perm space to get the index access for the Primary Index.
you can use dbc.tablesize view to see the size of table plus its secondary indices. Create a table without secondary index. Note the size. Then create your new secondary index. Note the new size. The difference is the size of the index.
There are other methods to do that, but this one is the most straightforward one.
Yes, you should take Secondary Indices into account during Capacity planning.
Thanks Vlad for your response..
I belive that its size wont be fixed , the size of the Subtable is going to grow based on the data in main table.
for the tables which are currently in development database and going to production system,
then in order to project how much space the table will take at prod, do we need to calculate the size of the secondary index subtable based on the no. of records in dev db.. for eg say for 1000 records the size comes out to be 0.5 GB ...
so we can use the same proportional increase in size at prod..
like 1000 records --> 0.5 GB
10000 records --> 5 GB..
Is my understanding correct here?
Please correct or add some thing here..
yes, proportional increase in size is a logical way to go. You can check it yourself by creating a table in DEV with 1000 records only, then 10000 records. Check the size of the index in each case.