0

I have a table which holds monthly data differentiated by a column year month (YYYYMM, eg 201911).

We have been planning to partition the table by month however we are unable to decide between Range Partitioning & List Partitioning for this type of situation.

Oracle clearly recommends using range partitions for columns with date/timestamp. Does it have any adverse effect in performance if the column is number?

Also most of the queries on this table has year_month = ?? filter!!

pOrinG
  • 896
  • 3
  • 13
  • 27
  • 1
    Any 'clear recommendation' is probably due to the nature of the data in whatever example was being described. There is no performance penalty for list partitions. Perhaps range partitioning may suit range queries better, but for monthly partitions I can't see it being a problem. – William Robertson Dec 01 '19 at 18:42
  • @WilliamRobertson aah okayy, so basically it doesn't matter if we use range or list partitioning. Its going to be the same performance in my case. – pOrinG Dec 01 '19 at 21:10
  • 1
    The benefit of range partition (or better interval partition) is, they are created automatically and you don't have to create new partitions manually every months. – Wernfried Domscheit Dec 02 '19 at 05:54
  • @WernfriedDomscheit Oh I get it. Thanks. – pOrinG Dec 02 '19 at 08:21
  • 1
    Please keep in mind, that you can use Auto-List Partitioning with Oracle 12.2. So you don't need to create new Partitions manually in List-Partitioning strategies any more. – bernhard.weingartner Dec 02 '19 at 15:29
  • 1
    With automatic partitioning in place it would be better to use the list partitioning, as the "interval" (= automatic range) partitioning would allocate plenty of "dummy" partitions between month 13 and month 99 (... however weird that sounds). – peter.hrasko.sk Dec 04 '19 at 08:32
  • @nop77svk Yeah,, I actually plan to go with List Partitioning and implement this weekend. – pOrinG Dec 06 '19 at 14:57

0 Answers0