Where will data be stored (or will it) for a Partitioned tab

Posted by randallkharp on 03-Aug-2014 16:11

I get the whole horizontal table partitioning concept, partition policies, etc., but I have a question (or two) regarding the nuts and bolts of what happens or should happen in the DB.

If I create a new Table through the Data Dictionary and check the Partitioned toggle, the Area drop-down list disappears. I assume that is because the storage area(s) of the table will/should be determined by Partition Policies. If no partition policies exist for the table and records are added to the new table, where will the data go?

I see that when a new Partitioned table is created through the Data Dictionary, its _File._ianum value is ? (unknown value) and its associated _storageobject._Area-number = 0. If an existing non-partitioned table with no data is later marked Partitioned via the Data Dictionary, its associated _storageobject._Area-number value remains unchanged from the original Storage Area designated for the table. Will the storage location for data in the latter case be different than the former?

Posted by Håvard Danielsen on 04-Aug-2014 11:42

>> Just to confirm, a partitioned table without allocated storage (provided through a policy) is essentially useless. Right?

You cannot add data to a partitioned table with no allocated partitions.  

All Replies

Posted by Håvard Danielsen on 04-Aug-2014 09:38

You will get an error if you try to add data to a partitioned table with no allocated partitions.

Creation of _file and _partition-policy  is currently done in separate transactions when added from ABL. (I believe you have to specify everything in one transaction in SQL). The reason the partitioned flag can be set when the table (_file)  is created is that it allows the table and local indexes to be created without areas.  

--

Existing data remain in their original partitions (and areas ) after the table is partitioned.  You mark the policy details as split targets and run the split utility to complete the migration to new partitions ( new _StorageObject records) allocated in the areas specified on the detail.  The ability to allow data to be migrated incrementally is assumed to be important for very large tables. The data can be accessed transparently with some or all partitions remaining in the original  partition also called composite partition, since it has multiple logical partitions. The data in partition that is marked as a split target is not avail until you run the utility.    

Posted by randallkharp on 04-Aug-2014 11:01

Just to confirm, a partitioned table without allocated storage (provided through a policy) is essentially useless. Right?

A table that was not partitioned originally has allocated storage from its original creation. Later marking it as partitioned allows the addition of local indices. Without partition policies for that table, data (including newly added records) will only be stored in the storage area originally allocated to the non-partitioned table. Is that correct?

Posted by Håvard Danielsen on 04-Aug-2014 11:42

>> Just to confirm, a partitioned table without allocated storage (provided through a policy) is essentially useless. Right?

You cannot add data to a partitioned table with no allocated partitions.  

Posted by Håvard Danielsen on 04-Aug-2014 11:54

>> A table that was not partitioned originally has allocated storage from its original creation. Later marking it as partitioned allows the addition of local indices. Without partition policies for that table, data (including newly added records) will only be stored in the storage area originally allocated to the non-partitioned table. Is that correct?

This is not a possible state for a table with data. A table with data cannot be marked as partitioned without creating the policy.

The policy and details that matches/covers all existing data and local indexes need to be created in the same transaction. You can add local indexes later, but I don't think you can make existing indexes local after the table has been partitioned. (Not 100% sure)  

This data remain in their original partitions and areas until they are split out.  

Posted by Jeff Ledbetter on 05-Aug-2014 13:23

It seems that the data dictionary UI has changed a bit as well from the prior build.

When adding a new table:

1. If I select the 'Partitioned' options for a table, I can no longer select an area. Is this the correct and expected behavior?

2. The Global / Local radio-set is enabled regardless if the table is 'Partitioned'. Should that always be enabled or just for 'Partitioned' tables?

3. For Local, no area selection is possible. For Global, I can select an area? Is this the correct and expected behavior?

Posted by gus on 05-Aug-2014 13:39

if the table is partitioned, then each partition goes into some area. the
table itself doesn't get an area because it is composed of the partitions,
each in an area.

on the other hand, a unpartitioned table does go into an area.

Posted by Håvard Danielsen on 06-Aug-2014 10:29

1. Yes. See gus post.

2. This is a bug. The radio-set in the create dialog sometimes keeps state from previous open. You wont be able to create a local index unless the table is partitioned though.  

3. Yes, Local indexes need an area for each partition and not per index.

------

You can add a policy to any table with type-2 areas without first marking it as partitioned or adding local indexes in Data Dictionary, but in the case of a new table you would end up with unused areas.

The main reason you can mark the table as partitioned and create new local indexes in Data Dictionary. is to allow you to create the table without specifying an area that wont be used.  

This thread is closed