Have more than 1024 Ros containers

hoseiney

I create a simple table like this:

then I insert more than1024 different values and then
run :

SELECT DO_TM_TASK('moveout');
SELECT DO_TM_TASK('mergeout');

after that, I check number of partitions by this query:

and result is:

for check number of Ros containers I run this query:

and result:

So I have more than 1024 number of Ros containers. I can insert more data and not getting any error.

but for other situations I get "too many ros containers" for having more than 1024 Ros containers.

what is happened?

Jim_Knicely

Hi,

When you ran the TM Moveout command, you should have received an error...

Example:

dbadmin=> select export_tables('','public.test_ros_partition');
                                                    export_tables
------------------------------------------------------------------------------------------------------

CREATE TABLE public.test_ros_partition
(
    id int,
    p int NOT NULL
)
PARTITION BY (test_ros_partition.p);
(1 row)

dbadmin=> insert into test_ros_partition select row_number() over (), row_number() over () from all_tables cross join vs_columns limit 1033;
 OUTPUT
--------
   1033
(1 row)

dbadmin=> select storage_type, count(*) from storage_containers where projection_name = 'test_ros_partition_super' group by storage_type;
 storage_type | count
--------------+-------
 WOS          |     1
(1 row)

dbadmin=> SELECT DO_TM_TASK('moveout', 'test_ros_partition_super');
                                                                            DO_TM_TASK
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Task: moveout
(Table: public.test_ros_partition) (Projection: public.test_ros_partition_super)
On node v_sfdc_node0001:
  ERROR 5060:  Too many data partitions
(1 row)

You should also get an error if you try to insert directly into ROS:

dbadmin=> rollback;
ROLLBACK

dbadmin=> insert /*+ direct */ into test_ros_partition select row_number() over (), row_number() over () from all_tables cross join vs_columns limit 1033;
ERROR 5060:  Too many data partitions
HINT:  Verify that the table partitioning expression is correct

Can you check the values of the following config parameters?

• MaxPartitionCount
• ContainersPerProjectionLimit

Example:

dbadmin=> select parameter_name, current_value, default_value, description from configuration_parameters where parameter_name in ('MaxPartitionCount', 'ContainersPerProjectionLimit');
        parameter_name        | current_value | default_value |                                      description
------------------------------+---------------+---------------+----------------------------------------------------------------------------------------
 ContainersPerProjectionLimit | 1024          | 1024          | Number of ROS containers that are allowed before new ROSs are prevented (ROS pushback)
 MaxPartitionCount            | 1024          | 1024          | Max no of partitions per projection
(2 rows)

Ben_Vandiver

There's a different between blowing past the limit in a single operation and creeping up to it slowly. It's generally a bad thing to get data "stuck" in the WOS with moveout failing. I suspect if you load 900 partitions into the wos, moveout, then load 133 more you can see the behavior. The WOS isn't partitioned, so inserts there can sneak past some of the max partition count checks.

hoseiney

@Jim_Knicely this is very useful , thanks