1

I have 3 columns which I would like to partition by, let's call them

some_date DATE
some_type VARCHAR2
some_product VARCHAR2

I would like to partition by range using some_date, then subpartition by list using some_type, then subpartition that further by some_product. Creating a subpartition for a subpartition is not possible in Oracle. I'm on Oracle 11gR2. Is there an eloquent way to mimic what I want to do? A possible solution I have come up with is to range partition by two columns, some_date and some_type although it seems a bit messy since some_type is a VARCHAR2 and I have to care to add partitions in alphabetic order.

Jacek Trociński
  • 882
  • 1
  • 8
  • 23
  • What is the reason to subpartition a subpartition? Is your table that huge? Perhaps a bitmap index provides the same benefits in terms of performance. – Wernfried Domscheit Aug 22 '17 at 09:30
  • 1
    Maybe you can define subpartition on virtual column: `type_product VARCHAR2(100) GENERATED ALWAYS AS ( 't'||some_type||'p'||some_product ) VIRTUAL` - but I am not sure whether this is possible. – Wernfried Domscheit Aug 22 '17 at 09:32
  • Yes, the table is huge. A bitmap index is out of the question because I need to truncate data before each which would pertain to a subpartition subpartion and a DELETE would simply be too slow. Creating a virtual column does not give me the benefit of performance when querying the table by some_type and some_product. – Jacek Trociński Aug 22 '17 at 09:52
  • Why do you need to delete any data for creating an index? And of course a virtual column would gain performance by partition pruning. – Wernfried Domscheit Aug 22 '17 at 10:00
  • I sort of assumed you meant create a bitmap index instead of a subpartition but the point of the subpartition is that we would like to truncate it rather than DELETE. I guess the virtual column wouldn't be such a bad idea, I'll try to go this route and see what problems I encounter. – Jacek Trociński Aug 22 '17 at 11:23
  • How many values does `some_type` have? Is a composite partition of `range(some_date), list(some_type)` a possibility? – APC Aug 23 '17 at 07:21
  • @APC there would be a few hundred million records if partitioned by range(some_date) and list(some_type) in the list(some_type) subpartition, that's why we need to subpartition down further. Concatenating two VARCHAR columns is not that great of an idea because OBIEE will not build a query to use the virtual column with concatenation and partition pruning will not occur. I guess the initial design I mentioned in my post will have to stay. – Jacek Trociński Aug 24 '17 at 16:41

0 Answers0