Large Database Features In Oracle

In this chapter, we will be covering topics and features available in Oracle Database 10g with which you will need to be familiar when working with large databases. These features are among the more advanced that you will encounter, but they’re necessary, as databases are growing larger and larger. When you start working with Oracle, you will find yourself facing the trials and tribulations associated with large databases sooner rather than later. The quicker you understand the features and know where and when to use them, the more effective you will be.

CRITICAL SKILL 9.1

What Is a Large Database?

Let’s start by describing what we mean by a large database. “Large” is a relative term that changes over time. What was large five or ten years ago is small by today’s standards, and what is large today will be peanuts a few years from now. Each release of Oracle has included new features and enhancements to address the need to store more and more data. For example, Oracle8i was released in 1999 and could handle databases with terabytes (1024 gigabytes) of data. In 2001, Oracle9i was released and could deal with up to 500 petabytes (1024 terabytes). Oracle Database 10g now offers support for exabyte (1024 petabytes) databases. You won’t come across too many databases with exabytes of data right now, but in the future at least we know Oracle will support them.

The most obvious examples of large database implementations are data warehouses and decision support systems. These environments usually have tables with millions or billions of rows, or wide tables with large numbers of columns and many rows. There are also many OLTP systems that are very large and can benefit from the features we are about to cover. Since we’ve got many topics to get through, let’s jump right in and start with data partitioning.

---

NOTE

Many of the topics discussed in this chapter could, each on their own, take an entire book to cover completely. Since this is an introductory book, specifics for some topics have been omitted. Real-world experiences and additional reading will build on this material.

---

CRITICAL SKILL 9.2

Why and How to Use Data Partitioning

As our user communities require more and more detailed information in order to remain competitive, it has fallen to us as database designers and administrators to help ensure that the information is managed efficiently and can be retrieved for analysis effectively. In this section, we will discuss partitioning data, and why it is so important when working with large databases. Afterward, we’ll follow the steps required to make it all work.

Why Use Data Partitioning

Let’s start by defining what we mean by data partitioning. In its simplest form, it is a way of breaking up or subsetting data into smaller units that can be managed and accessed separately. It has been around for a long time both as a design technique and as a technology. Let’s look at some of the issues that gave rise to the need for partitioning and the solutions to these issues.

Tables containing very large numbers of rows have always posed problems and challenges for DBAs, application developers, and end-users alike. For the DBA, the problems centered on the maintenance and manageability of the underlying data files that contain the data for these tables. For the application developers and end users, the issues were query performance and data availability.

To mitigate these issues, the standard database design technique was to create physically separate tables, identical in structure (for example, columns), but with each containing a subset of the total data (we will refer to this design technique as nonpartitioned). These tables could be referred to directly or through a series of views. This technique solved some of the problems, but still meant maintenance for the DBA to create new tables and/or views as new subsets of data were acquired. In addition, if access to the entire dataset was required, a view was needed to join all subsets together.

Figure 9-1 illustrates this design technique. In this sample, separate tables with identical structures have been created to hold monthly sales information for 2005. Views have also been defined to group the monthly information into quarters using a union query. The quarterly views themselves are then grouped together into a view that represents the entire year. The same structures would be created for each year of data. In order to obtain data for a particular month or quarter, an end user would have to know which table or view to use.

Similar to the technique illustrated in Figure 9-1, the partitioning technology offered by Oracle Database 10g is a method of breaking up large amounts of data into smaller, more manageable chunks. But, unlike the nonpartitioned technique, it is transparent to

• The size of each underlying data file is generally smaller for partitioned tables than nonpartitioned tables. This allows for easier and quicker backups.
  
• Historical partitions can be made read-only and will not need to be backed up more than once. This also means faster backups.
  
• The structure of a partitioned table needs to be defined only once. As new subsets of data are acquired, they will be assigned to the correct partition, based on the partition option chosen.
  
• Individual tablespaces and/or their data files can be taken offline for maintenance or archiving without affecting access to other subsets of data. For example, assuming data for a table is partitioned by month (later in this chapter, we will discuss the different types of partitioning) and only 13 months of data is to be kept online at any one time, when a new month is acquired, the earliest month is archived and dropped from the table. This is accomplished using the command alter table abc drop partition xyz and does not affect access to the remaining 12 months of data.

•  Other commands that would normally apply at the table level can also be applied to a particular partition of the table. These include, but are not limited to: delete, insert, select, truncate, and update.

Performance

One of the main reasons for partitioning a table is to improve I/O response time when selecting data from the table. Having a table’s data partitioned into subsets can yield much faster query results when you are looking for data that is limited to one subset of the total. Let’s look at an example to illustrate.

Assume the SALES table contains 100 million records representing daily sales revenue for the three years 2005 to 2007 inclusive. We want to know what the total revenue is for February 2006. The query might look something like this:

select sum(amount_sold)
from sales
where time_id between to_date('2006-02-01', 'YYYY-MM-DD') and
to_date('2006-02-28', 'YYYY-MM-DD');

Using a nonpartitioned table design, all 100 million rows would need to be scanned to determine if they belong to the date criteria. Using a partitioned table design based on monthly partitions with about 2.8 million rows for each month, only those rows in the February 2006 partition (and therefore only about 2.8 million rows) would be scanned. The process of eliminating data not belonging to the subset defined by the query criteria is referred to as partition pruning.

With the basic concepts of partitioning and why we use it under our belts, we can now discuss the finer details of how to implement partitioning.