Home arrow Oracle arrow Large Database Features In Oracle

Large Database Features In Oracle

When you are working with large databases with Oracle Database 10g, there are certain topics with which you need to be familiar. These topics are covered in this article, the first of two parts. It is excerpted from chapter nine of the book Oracle Database 10g A Beginner's Guide, written by Ian Abramson et al (McGraw-Hill/Osborne, 2004; ISBN: 0072230789).

  1. Large Database Features In Oracle
  2. Implement Data Partitioning
  3. Select the Type of Partitioning
  4. Define the Indexing Strategy
  5. Project 9-1 Creating a Range-Partitioned Table and a Local Partitioned Index
  6. Data Compression
  7. Parallel Processing Configuration
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 26
December 08, 2005

print this article



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.


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.


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.


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

FIGURE 9-1.  Partitioning using separate physical tables

the end user, offering improved performance and reduced maintenance. Figure9-2 illustrates the same SALES table, but implemented using Oracle Database10gís partitioning option. From the end userís perspective, there is only one table called SALES and all that is required to access data from the correct partition is a date (or a month and year).

Oracle partitioning was first introduced in Oracle8 and is only available with the Enterprise Edition. Many improvements have been made since then and Oracle Database 10g contains all the latest features. The remainder of this section discusses these features in more detail.


When administering large databases, DBAs are required to determine the most efficient and effective way to configure the underlying data files that support the tables in the database. The decisions made will affect data accessibility and availability as well as back up and recovery.

Some of the benefits to database manageability when using partitioned tables include the following:                                 


          FIGURE 9-2. Partitioning using Oracle 
              Database 10g partitioning

  • 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.

Ask the Expert

 Q:  Can you use the analyze table command to gather statistics on partitioned tables?

 A:  No; at least not correctly. The supplied DBMS_STATS package should be used instead to gather statistics on partitioned tables. The analyze table command does not gather all required statistics for partitioned tables, in particular global statistics. In addition, the analyze command will eventually be phased out (for all types of table and indexes) and only those statistics gathered by the DBMS_STATS package will be used by the cost-based optimizer.

  •  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.


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.

>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: