You Asked

We have a list-range(number, timestamp) partitioned table called STREAMING_RESULTS. The table stores very high volume streaming data from a telemetry device. Depending on the channel resolution, some partitions have ~ 3000 subpartitions. I can exchange partitions without any errors for most partititions however, when I try to exchange a partition that has more than 1000 subpartitions, it fails with an ORA-01795 error like below.

ALTER TABLE STREAMING_RESULTS EXCHANGE PARTITION P_102 WITH TABLE INTERIM;

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01795: maximum number of expressions in a list is 1000

I can't find any reason for getting this error besides what queries Oracle might be issuing internally for the exchange partition command. Am I missing anything or is there such a limitation on this command? I couldn't replicate the issue in livesql due to size but I'm including an abridged version below.

Thank you,

CREATE TABLE STREAMING_RESULTS 
    ( 
     CHANNEL_ID NUMBER (6)  NOT NULL , 
     TIMESTAMP  TIMESTAMP (3)  NOT NULL , 
     VALUE      BINARY_DOUBLE , 
     FILTER     BINARY_FLOAT 
    ) 
    PARTITION BY LIST ( CHANNEL_ID ) 
    SUBPARTITION BY RANGE ( TIMESTAMP ) 
    ( 
    PARTITION P_DEF VALUES ( 0 ) 
    ( 
        SUBPARTITION P_DEF_SUB VALUES LESS THAN ( TO_TIMESTAMP('2016-01-01 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.FF3') ) 
        NOCOMPRESS 
    ) 
    ) 
;

CREATE TABLE INTERIM (
    channel_id NUMBER(6) not null, 
    timestamp timestamp(3) not null, 
    value binary_double, 
    filter binary_float,
    constraint TEL_PK PRIMARY KEY (channel_id, timestamp)
)
PARTITION BY RANGE(TIMESTAMP)
(
  PARTITION P_102_03_22_12_30 VALUES LESS THAN (TIMESTAMP' 2017-03-22 13:00:00'),
  PARTITION P_102_03_22_13_00 VALUES LESS THAN (TIMESTAMP' 2017-03-22 13:30:00'),
  PARTITION P_102_03_22_13_30 VALUES LESS THAN (TIMESTAMP' 2017-03-22 14:00:00'),
  PARTITION P_102_03_22_14_00 VALUES LESS THAN (TIMESTAMP' 2017-03-22 14:30:00'),
  PARTITION P_102_03_22_14_30 VALUES LESS THAN (TIMESTAMP' 2017-03-22 15:00:00'),
  PARTITION P_102_03_22_15_00 VALUES LESS THAN (TIMESTAMP' 2017-03-22 15:30:00'),
  PARTITION P_102_03_22_15_30 VALUES LESS THAN (TIMESTAMP' 2017-03-22 16:00:00'),
......
  PARTITION P_102_06_01_14_00 VALUES LESS THAN (TIMESTAMP' 2017-06-01 14:30:00'),
  PARTITION P_102_06_01_14_30 VALUES LESS THAN (TIMESTAMP' 2017-06-01 15:00:00'),
  PARTITION P_102_06_01_15_00 VALUES LESS THAN (TIMESTAMP' 2017-06-01 15:30:00'),
  PARTITION P_102_06_01_15_30 VALUES LESS THAN (TIMESTAMP' 2017-06-01 16:00:00'),
  PARTITION P_102_06_01_16_00 VALUES LESS THAN (TIMESTAMP' 2017-06-01 16:30:00'),
  PARTITION P_102_06_01_16_30 VALUES LESS THAN (TIMESTAMP' 2017-06-01 17:00:00'),
  PARTITION P_102_06_01_17_00 VALUES LESS THAN (TIMESTAMP' 2017-06-01 17:30:00'),
  PARTITION P_102_06_01_17_30 VALUES LESS THAN (TIMESTAMP' 2017-06-01 18:00:00'),
  PARTITION P_102_06_01_18_00 VALUES LESS THAN (TIMESTAMP' 2017-06-01 18:30:00'),
  PARTITION P_102_06_01_18_30 VALUES LESS THAN (TIMESTAMP' 2017-06-01 19:00:00'),
  PARTITION P_102_06_01_19_00 VALUES LESS THAN (TIMESTAMP' 2017-06-01 19:30:00'),
  PARTITION P_102_06_01_19_30 VALUES LESS THAN (TIMESTAMP' 2017-06-01 20:00:00')
) NOLOGGING 
;

ALTER TABLE STREAMING_RESULTS ADD PARTITION P_102 VALUES (102)(
 SUBPARTITION P_102_03_22_12_30 VALUES LESS THAN (TIMESTAMP' 2017-03-22 13:00:00'),
 SUBPARTITION P_102_03_22_13_00 VALUES LESS THAN (TIMESTAMP' 2017-03-22 13:30:00'),
 SUBPARTITION P_102_03_22_13_30 VALUES LESS THAN (TIMESTAMP' 2017-03-22 14:00:00'),
 SUBPARTITION P_102_03_22_14_00 VALUES LESS THAN (TIMESTAMP' 2017-03-22 14:30:00'),
 SUBPARTITION P_102_03_22_14_30 VALUES LESS THAN (TIMESTAMP' 2017-03-22 15:00:00'),
 SUBPARTITION P_102_03_22_15_00 VALUES LESS THAN (TIMESTAMP' 2017-03-22 15:30:00'),
 SUBPARTITION P_102_03_22_15_30 VALUES LESS THAN (TIMESTAMP' 2017-03-22 16:00:00'),
...
 SUBPARTITION P_102_06_01_14_30 VALUES LESS THAN (TIMESTAMP' 2017-06-01 15:00:00'),
 SUBPARTITION P_102_06_01_15_00 VALUES LESS THAN (TIMESTAMP' 2017-06-01 15:30:00'),
 SUBPARTITION P_102_06_01_15_30 VALUES LESS THAN (TIMESTAMP' 2017-06-01 16:00:00'),
 SUBPARTITION P_102_06_01_16_00 VALUES LESS THAN (TIMESTAMP' 2017-06-01 16:30:00'),
 SUBPARTITION P_102_06_01_16_30 VALUES LESS THAN (TIMESTAMP' 2017-06-01 17:00:00'),
 SUBPARTITION P_102_06_01_17_00 VALUES LESS THAN (TIMESTAMP' 2017-06-01 17:30:00'),
 SUBPARTITION P_102_06_01_17_30 VALUES LESS THAN (TIMESTAMP' 2017-06-01 18:00:00'),
 SUBPARTITION P_102_06_01_18_00 VALUES LESS THAN (TIMESTAMP' 2017-06-01 18:30:00'),
 SUBPARTITION P_102_06_01_18_30 VALUES LESS THAN (TIMESTAMP' 2017-06-01 19:00:00'),
 SUBPARTITION P_102_06_01_19_00 VALUES LESS THAN (TIMESTAMP' 2017-06-01 19:30:00'),
 SUBPARTITION P_102_06_01_19_30 VALUES LESS THAN (TIMESTAMP' 2017-06-01 20:00:00')
);

ALTER TABLE STREAMING_RESULTS EXCHANGE PARTITION P_102 WITH TABLE INTERIM;

and Chris said...

Sorry, I'm not able to reproduce this. Here's an example cranked up to 3,000 (sub)partitions:

CREATE TABLE STREAMING_RESULTS  ( 
   CHANNEL_ID NUMBER (6)  NOT NULL , 
   TIMESTAMP  TIMESTAMP  NOT NULL , 
   VALUE      BINARY_DOUBLE , 
   FILTER     BINARY_FLOAT 
) PARTITION BY LIST ( CHANNEL_ID ) 
  SUBPARTITION BY RANGE ( TIMESTAMP ) 
   (  PARTITION P_DEF VALUES ( 0 ) 
   ( 
      SUBPARTITION P_DEF_SUB VALUES LESS THAN ( timestamp'2017-01-01 00:00:00' ) 
   ) 
) ;
    
declare
  ct_stmt  clob;
  alt_stmt clob;
  ts       varchar2(30);
begin
  
  ct_stmt := 'CREATE TABLE INTERIM (
  channel_id NUMBER(6) not null, 
  timestamp TIMESTAMP not null, 
  value binary_double, 
  filter binary_float
)
PARTITION BY RANGE(TIMESTAMP)
(
  PARTITION P_102_0 VALUES LESS THAN (timestamp''2017-01-01 00:00:00'')';
  
  alt_stmt := 'ALTER TABLE STREAMING_RESULTS ADD PARTITION P_102 VALUES (102) (
 SUBPARTITION P_102_0 VALUES LESS THAN (timestamp''2017-01-01 00:00:00'')';
  
  for i in 1 .. 3000 loop
    ts := to_char((timestamp'2017-01-01 00:00:00' + numtodsinterval(i, 'second')), 'yyyy-mm-dd hh24:mi:ss');
    dbms_lob.append(ct_stmt, ', PARTITION P_102_' || i || ' VALUES LESS THAN (timestamp''' || ts || ''')');
    dbms_lob.append(alt_stmt, ', SUBPARTITION P_102_' || i || ' VALUES LESS THAN (timestamp''' || ts || ''')');
  end loop;
  
  ct_stmt := ct_stmt || ')';
  alt_stmt := alt_stmt || ')';
  
  execute immediate ct_stmt;
  execute immediate alt_stmt;
end;
/

insert into STREAMING_RESULTS
  select 102, timestamp'2017-01-01 00:00:00' + numtodsinterval(level-1, 'second'), 1, 1
  from   dual
  connect by level <= 3000;
  
commit;

exec dbms_stats.gather_table_stats(user, 'STREAMING_RESULTS');
exec dbms_stats.gather_table_stats(user, 'INTERIM');

select table_name , count(*), sum(num_rows) from user_tab_partitions
where  table_name in ('STREAMING_RESULTS', 'INTERIM')
group by table_name ;

TABLE_NAME         COUNT(*)  SUM(NUM_ROWS)  
STREAMING_RESULTS  2         3,000          
INTERIM            3,001     0   

select table_name , partition_name, count(*), sum(num_rows) from user_tab_subpartitions 
where  table_name in ('STREAMING_RESULTS', 'INTERIM')
group by table_name, partition_name ;

TABLE_NAME         PARTITION_NAME  COUNT(*)  SUM(NUM_ROWS)  
STREAMING_RESULTS  P_DEF           1         0              
STREAMING_RESULTS  P_102           3,001     3,000    

ALTER TABLE STREAMING_RESULTS EXCHANGE PARTITION P_102 WITH TABLE INTERIM;

exec dbms_stats.gather_table_stats(user, 'STREAMING_RESULTS');
exec dbms_stats.gather_table_stats(user, 'INTERIM');

select table_name , count(*), sum(num_rows) from user_tab_partitions
where  table_name in ('STREAMING_RESULTS', 'INTERIM')
group by table_name ;

TABLE_NAME         COUNT(*)  SUM(NUM_ROWS)  
STREAMING_RESULTS  2         0              
INTERIM            3,001     3,000   

select table_name , partition_name, count(*), sum(num_rows) from user_tab_subpartitions 
where  table_name in ('STREAMING_RESULTS', 'INTERIM')
group by table_name, partition_name ;

TABLE_NAME         PARTITION_NAME  COUNT(*)  SUM(NUM_ROWS)  
STREAMING_RESULTS  P_DEF           1         0              
STREAMING_RESULTS  P_102           3,001     0

Could you share a test case that shows your issue please? Use dynamic SQL to create the partitions as I've done to keep the size of the code down!

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Mustafa Nural, May 03, 2017 - 4:10 pm UTC

Hi Chris,

Thanks for the dynamic SQL tip. Following your example, I have tested this in both livesql.oracle.com and in a test schema in our database and I was not able to reproduce the error either.

To investigate further, I have tried to exchange a partition with a much smaller subpartition count(< 1000) and it still failed with the same error.

We have the STREAMING_RESULTS table in two different schemas. They are identical in definition but in different tablespaces and have different partitions. Turns out that the EXCHANGE PARTITION fails only for the table in one schema but works fine for the table in the other schema regardless of the subpartition count.

Any ideas for further troubleshooting?

May 04, 2017 - 2:22 am UTC

Try

dbms_support.session_trace_enable(binds=>true)

in the session before you run the exchange. That should reveal the internal statement experiencing the error.

Then log a call with Support with that trace file.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

VLDB

VLDB

Alter Table Exchange Partition fails with > 1000 subpartitions

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

VLDB

VLDB