Database partitioning

Hello everyone. My name is Willie Hardie. I am VicePresident of Database Product Marketing at Oracle Corporation, and today we aregoing to talk about database partitioning. Database partitioning gives us theability to take our very large tables and divide, or partition, them up intosmaller, more manageable pieces.

The reason we need to think about database partitioning isthat databases that support our transaction processing and our data warehousingsystems are tripling in size, on average, every two years. This means a 500gigabytes or half a terabyte database back in 2001 is going to one and a halfterabytes by 2003, four and a half terabytes by 2005, and will be over thirteenand a half terabytes by the year 2007. This gives us a big challenge in termsof managing our user's performance and availability expectations.

Let us say we have a large sales order table that is spreadacross four individual disks. This table typically contains millions of rows.The challenge we have with large tables like this is that all of our operationstake place at the table level. We do queries at the table level. We do indexbuilds at the table level. We do data loads at the table level. Which meansthat as we add more rows into this large table, performance and availabilitycan tail off. If we were to lose one of the disks that support this table, wewould lose the entire table. Our systems down while we try and repair thatfailure.

A better solution is to take our sales order table and, byissuing a simple alter table command, we can partition our large table. We canpartition it based on a range of values such as date or time. We can partitionit on a list of values such as country, region or product. Or we couldpartition it based on a hashing algorithm on a partition key.

In our example we are going to take our sales order tableand we are going to partition it up into four quarters. So any rows that werein January, for example, are going to appear in quarter one. Any rows thatappeared in say the month of April are going to appear in quarter two. We aregoing to similarly insert rows into quarters three and four. Now, what we areable to do by partitioning this large table is instead of having the largetable spread across those four disks, we've now got individual partitions thathave each got their own disks.

The advantage we have got in terms of query performance isthat if we need to retrieve rows from quarter one, we do not do this full tablescan or full index scan anymore. We go straight into the partition thatcontains the data we are after. Now, in terms of providing higher availability,if we were to lose one of the disks that supports one of our partitions, all weactually lose is access to that partition, in this case quarter two. We cancontinue to do inserts, updates and deletes to all our other partitions.

By issuing a simple alter table command to partition ourlarge transactional tables, we can improve the performance of our systems, wecan increase the availability of our systems, and we can ease the complexity ofmanaging very large databases.