When local partitions….aren’t

Let’s say I’ve got a partitioned table, and because New Year’s eve is coming around, I certainly don’t want to be called out at 12:01am because I forgot to add the required partition for the upcoming year Smile.

Since 11g, I can sleep easy at night by using the INTERVAL partition scheme. Here’s my table



SQL> create table t ( x date, y int )
  2  partition by range ( x )
  3  interval ( numtoyminterval(1,'MONTH'))
  4  (
  5    partition p201412 values less than ( date '2015-01-01' )
  6  );

Table created.

SQL> insert into t
  2  select date '2015-01-01'+rownum, rownum
  3  from dual
  4  connect by level <= 330;

330 rows created.

SQL> commit;

Commit complete.

SQL> select partition_name from user_tab_partitions where table_name = 'T';

PARTITION_NAME
----------------------------------------------------------------------------
P201412
SYS_P20234
SYS_P20235
SYS_P20236
SYS_P20237
SYS_P20238
SYS_P20239
SYS_P20240
SYS_P20241
SYS_P20242
SYS_P20243
SYS_P20244

12 rows selected.

The problem is … man, do I hate those automatic names Smile. I suppose I can fix them up later, but in the meantime, I’ll create my local index on the table, but I’ll get the names right whilst I do



SQL>
SQL> create index IX on T ( y  )
  2  local
  3  (
  4      partition P201412
  5      partition P201501,
  6      partition P201502,
  7      partition P201503,
  8      partition P201504,
  9      partition P201505,
 10      partition P201506,
 11      partition P201507,
 12      partition P201508,
 13      partition P201509,
 14      partition P201510,
 15      partition P201511
 16  )
 17  ;

Index created.

So far so good… I created 12 partitions in my table, and I explicitly listed 12 partitions in my index. But you need to be careful with such an approach, because if you’ve got that script in (say) your source control system, then even though you’ve specified a LOCAL index, you have also (perhaps unknowingly) set of limit of 12 partitions on the index should be re-run that script. So if I was to drop that index and recreate with the unchanged script (or for example, you’ve done a datapump extraction of DDL etc), then you might get yourself into a jam if the table data changes.



SQL>
SQL>
SQL> drop index ix;

Index dropped.

SQL>
SQL> insert into t
  2  values (sysdate,2000);

1 row created.

SQL>
SQL>
SQL> create index IX on T ( y  )
  2  local
  3  (
  4      partition P201412
  5      partition P201501,
  6      partition P201502,
  7      partition P201503,
  8      partition P201504,
  9      partition P201505,
 10      partition P201506,
 11      partition P201507,
 12      partition P201508,
 13      partition P201509,
 14      partition P201510,
 15      partition P201511
 16  )
 17  ;
create index IX on T ( y  )
                   *
ERROR at line 1:
ORA-14024: number of partitions of LOCAL index must equal that of the underlying table

Now my same DDL does not work, because there are 13 table partitions and I only listed 12 index partitions. The easy workaround is not to list the partitions at all.


SQL>
SQL> create index IX on T ( y  )
  2  local ;

Index created.

SQL>
SQL>

and perhaps have an renaming routine that can be applied after the fact.