Infrastructure at your Service

Partitioning Archives - Blog dbi services

Joël Cattin

Oracle 12cR2 : Partitioning improvements – online conversion of a non-partitioned table to a partitioned table

By | Database Administration & Monitoring | No Comments

It’s time to a new blog about partitioning improvement in 12cR2. After auto-list partitioning and multi-column list partitioning & read-only partitions, I’ll demonstrate how we can easily convert a non-partitioned table to a partitioned table…online ! As a reminder, the way to convert a table to a partitioned table were to use the DBMS_REDEFINITION package (since 9i to 12cR1) which require few steps : Verify that the table is a candidate for redefinition : DBMS_REDEFINITION.CAN_REDEF_TABLE…

 
Read More
Joël Cattin

Oracle 12cR2 : Partitioning improvements – multi-column list partitioning & read-only partitions

By | Database Administration & Monitoring, Development & Performance | No Comments

In my last blog post I presented auto-list partitioning, a new partitioning functionality coming with 12cR2. In this one I will introduce two others : multi-column list partitioning and read-only partitions. Multi-column list partitioning Image : “Oracle Partitioning in Oracle Database 12c Release 2″ – Hermann Bär With the first release of 12c it wasn’t possible to create list partitioned tables based on multi-column partition key : ORA-14304: List partitioning method expects a single partitioning…

 
Read More
Joël Cattin

Oracle 12cR2 : Partitioning improvements – auto-list partitioning

By | Database Administration & Monitoring, Development & Performance | No Comments

The second release of Oracle Database 12c (12cR2) comes with new improvements regarding partitioning. In this blog post I’ll show one of them : auto-list partitioning. Auto-list partitioning is an extension of list partitioning. It enable the automatic creation of partitions for new values inserted into the partitioned table. Image : “Oracle Partitioning in Oracle Database 12c Release 2″ – Hermann Bär Let’s have a look at the syntax. As you can see below, you…

 
Read More
David Barbarin

Partitioning – When data movement is not performed as expected

By | Database Administration & Monitoring | No Comments

This blog is about an interesting partitioning story and curious data movements during merge operation. I was at my one of my customer uses intensively partitioning for various reasons including archiving and manageability. A couple of days ago, we decided to test the new fresh developed script, which will carry out the automatic archiving stuff against the concerned database in quality environment. Let’s describe a little bit the context. We used a range-based data distribution…

 
Read More
David Barbarin

SQL Server 2016: TRUNCATE PARTITIONS with sliding Windows scenarios

By | Database Administration & Monitoring, Development & Performance | 6 Comments

Some time ago, I had to deal with a new partitioning scenario that included sliding windows stuff for mainly archiving purpose. Regarding the customer context, I used some management scripts that include this time dropping oldest partition. We didn’t care about data oldest than 2 years. Usually in this case, I use a method that consists in dropping data by switching first the oldest partition to a staging table and then truncate it. Finally we may…

 
Read More
David Barbarin

SQL Server: switch partition and metadata inconsistency issue

By | Database Administration & Monitoring | No Comments

In this blog post, I would like to share with you a weird issue I faced when I implemented a sliding Windows partition scenario on SQL Server 2014 SP1 at one of my customer. The idea was to keep SQL Server audit records from several SQL Server instances into a global archiving table during two years in order to meet the customer audit specifications. Data older than year – 2 should be deleted. Based on…

 
Read More
David Barbarin

Changing an existing partition configuration … Well, not so easy!

By | Development & Performance | No Comments

This time let’s talk about an interesting customer scenario where table partitioning was implemented on a table with 100 GB of data on SQL Server 2014. Partitioning in this context aimed to save disk space  (archive data were compressed) , help to reduce maintenance time and consumed resources as well (by using index and statistic maintenance operations on the active partition).  Finally, it will help to improve the queries performance on the concerned table that mainly focused…

 
Read More
Oracle Team

Oracle 12c: Partitioning enhancements Part II – Other improvements

By | Database management | No Comments

Oracle Database 12c offers several enhancements for partitioning. In a previous blog posting, I talked about reference partitioning enhancements. This post will present other enhancements in relation to general partitioning. Asynchronous index maintenance With Oracle database, a global index becomes UNUSABLE when dropping or truncating the partition on which this index points, until the clause UPDATE GLOBAL INDEXES is used. With Oracle 11g, droping or truncating a partition involves the index maintenance, consisting on the…

 
Read More
Oracle Team

Oracle Database 12c: Partitioning enhancements Part I – Reference partitioning

By | Database management | No Comments

Oracle Database 12c offers several enhancements for partitioning. This blog posting will present some enhancements in relation to reference partitioning. The differences between the Oracle 11g R2 and Oracle 12c releases will be shown using practical examples. TRUNCATE and EXCHANGE with the CASCADE option With Oracle 12c, it is now possible to use the CASCADE option to cascade operations to a child-referenced table when truncating or exchanging a partition. As an example, I created the…

 
Read More
Oracle Team

Processor licensing for Oracle database on VMware ESX

By | Hardware & Storage | 2 Comments

In this post, I would like to share with you what I have learned about Oracle licensing particularities on a virtualization infrastructure (software partitioning) based on VMware ESX. Recently, I faced an Oracle licensing problem on a VMware ESX cluster on which I had to find a workaround in order to make the hardware comply with the customer’s licensing model. Without entering in details, the customer had a VMware cluster with three nodes and had…

 
Read More