Monday, June 24, 2013

Oracle 10g - a time traveller's tale

Time travel sucks, especially going back in time. Nobody takes a bath, there are no anaesthetics and you can't get a decent wi-fi signal anywhere. As for killing your own grandfather, forget about it.

The same is true for going back in database versions. In 2009 I had gone straight from an Oracle 9i project to an Oracle 11g one. So when I eventually found myself on a 10g project it was rather disorientating. I would keep reaching for tools which weren't in the toolbox: LISTAGG(), preprocessor scripts for external tables, generalized invocation for objects.

I had missed out on 10g while it was shiny and new, and now it just seemed limited. Take Partitioning. Oracle 10g supported exactly the same composite partitioning methods as 9i: just Range-hash and Range-List, whereas 11g is full of wonders like Interval-Range, Hash-Hash and the one I needed, List-List.

Faking a List-List composite partitioning scheme in 10g

Consider this example of a table with a (slightly forced) need for composite List-List partitioning. It is part of a engineering stock control system, in which PRODUCTS are grouped in LINES (Ships, Cars, Planes) and COMPONENTS are grouped into CATEGORIES (Frame, interior fittings, software, etc). We need an intersection table which links components to products.

There are hundreds of thousands of components and tens of thousands of products. But we are almost always only interested in components for a single category within a single product line (or product) so composite partitiong on (product_line, component_category) is a good scheme. In 11g the List-List method works just fine:
SQL> create table product_components
  2      (product_line varchar2(10) not null
  3          , product_id number not null
  4          , component_category varchar2(10) not null
  5          , component_id number not null
  6          , constraint pc_pk primary key (product_id, component_id )
  7          , constraint pc_prd_fk foreign key (product_id )
  8             references products (product_id)
  9          , constraint pc_com_fk foreign key (component_id )
 10             references components (component_id)
 11      )
 12  partition by range(product_line) subpartition by list(component_category)
 13       subpartition template
 14           (subpartition sbody values ('BODY')
 15            , subpartition sint values ('INT')
 16            , subpartition selectr values ('ELECTR')
 17            , subpartition ssoft values ('SOFT')
 18           )
 19      (partition pship values ('SHIP')
 20       , partition pcar values  ('CAR')
 21       , partition pplane values ('PLANE')
 22       )
 23  /

Table created.

SQL> 

But in 10g the same statement hurls ORA-00922: missing or invalid option . The workaround is a bit of a nasty hack: replace the first List with a Range, producing a legitimate Range-List composite:
SQL> create table product_components
  2      (product_line varchar2(10) not null
  3          , product_id number not null
  4          , component_category varchar2(10) not null
  5          , component_id number not null
  6          , constraint pc_pk primary key (product_id, component_id )
  7          , constraint pc_prd_fk foreign key (product_id )
  8             references products (product_id)
  9          , constraint pc_com_fk foreign key (component_id )
 10             references components (component_id)
 11      )
 12  partition by range(product_line) subpartition by list(component_category)
 13       subpartition template
 14           (subpartition sbody values ('BODY')
 15            , subpartition sint values ('INT')
 16            , subpartition selectr values ('ELECTR')
 17            , subpartition ssoft values ('SOFT')
 18           )
 19      (partition pcar values less than ('CAS')
 20       , partition pplane values less than ('PLANF')
 21       , partition pship values less than ('SHIQ')
 22       )
 23  /

Table created.

SQL> 

Note the wacky spellings which ensure that 'CAR' ends up in the right partition. Also we have to re-order the partition clause so that the partition bounds don't raise an ORA-14037 exception. We are also left with the possibility that a rogue typo might slip records into the wrong partition, so we really ought to have a foreign key constraint on the product_line column:
alter table product_components add constraint pc_prdl_fk foreign key (product_line) 
           references product_lines (line_code)
/

I described this as a nasty hack. It is not really that nasty, in fact it actually works very well in daily processing. But managing the table is less intuitive. Say we want to manufacture another line, rockets. We cannot just add a new partition:
SQL> alter table product_components 
    add partition prock values less than ('ROCKEU')
/
  2    3      add partition prock values less than ('ROCKEU')
                  *
ERROR at line 2:
ORA-14074: partition bound must collate higher than that of the last partition


SQL> 

Instead we have to split the PSHIP partition in two:
SQL> alter table product_components split partition pship
  2     at ('ROCKEU')
  3     into (partition  prock, partition pship)
  4  /

Table altered.

SQL> 

The other snag is, that once we do get back to the future it's a bit of a chore to convert the table to a proper List-List scheme. Probably too much of a chore to be worth the effort. Even with a time machine there are only so many hours in the day.

1 comment:

William Robertson said...

We have exactly this situation on my current legacy 10g environment - a lot of tables range-partitioned on values like 'D20130531' which is defined as 'less than D20130601', although if you're not careful rows can end up accidentally in M20100101. And splitting the first 'M' partition to create a new 'D' partition does not invoke the subpartition template, leading to obscure bugs if subpartitions were added recently and not applied retrospectively to all historical partitions.