As the name implies, the Oracle partitioning facility allows large Oracle tables and indexes to be divided into manageable pieces that all have the same logical attributes. These pieces are accessed and manipulated independently, and can be managed as if they were separate entities.
This is especially important in working with large scale data warehouses. We know scale is an important factor in the performance of a data warehouse. Partitioning is an especially useful feature for Oracle data warehouses, because the DBA is provided with a tool to manage these partitions independently. Oracle partitioning can be extremely complex. For purposes of this module, we will focus on partitioning issues surrounding Oracle performance. let us look at the types of partitions Oracle uses.
Scale and Data Warehouse Performance
We know that scale is an important factor in the performance of a data warehouse.
A large FACT table may contain hundreds of millions of rows, and a full-table scan against this table could take days to complete.
Question: What Does a Data Warehouse Need?
Since the earliest days of Decision Support Systems (DSS) of the 1960s, database professionals have recognized that internal processing for data warehouse applications is very different from Online Transaction Processing Systems (OLTP). Data warehouse applications tend to be very I/O intensive, as the database reads trillions of bytes of information. Data warehouse systems require specialized servers that can support the typical processing that we see in data warehouses.
Most data warehouses are bi-modal and run batch process when new data is loaded, indexed, and summarized. The server must have on-demand CPU and RAM resources, and the database management system must be able to dynamically reconfigure its resources to accommodate these shifts in processing. While RAM costs continue to fall ever year, the speed of RAM access is constrained by silicon technology and has not improved in the past three decades.
Evolution Of The Data Warehouse and Data Management Platforms
While the use of databases as a vehicle for complex data analysis is new, the need to perform complex data analysis has been with us for centuries. Answering “what-if” questions, simulating the introduction of a new product, or determining the most profitable products are all legitimate business needs, and the advent of the data warehouse did not herald the first time that computers had been used to solve these types of tasks. In fact, computers have been used to solve complex types of data analysis problems since their earliest commercial inception in the 1950s. Essentially, the nature of the questions that data warehouses solve has not changed in four decades. The summarization of numbers, the aggregation of associated data, and data variance analysis is nothing new.
Regardless of the sophistication of a database manager, it remains true that all databases are constructed from simple data structures such as linked lists, B-trees, and hashed files. In reviewing the building blocks of database systems, it is possible to gain a historical perspective on the evolution of databases, and to remember the past, so that we are not condemned to repeat it. The following pages are a historical review of database evolution, showing each of the enhancements that were introduced with each new architecture. It is also important to review the problems inherent to each database architecture. As you will see, there are striking similarities between object-oriented databases and earlier database architectures. By understanding the historical reason that object-oriented databases have evolved into their present form, we can gain insight into the future trend and directions of databases.
Today's Oracle professional must be an expert in database performance and tuning, database
administration, data warehousing, using OLAP and spatial data, and other areas.
These robust features of Oracle present unique challenges to anyone who must use Oracle technology to deliver solutions to complex data-oriented challenges.
Oracle, the world's leading database management system, provides a vast wealth of features and options.
The Oracle market is filled with new possibilities as Oracle introduces the management of objects, data warehouses, and Web-enabled applications; Oracle professionals everywhere are struggling to understand how to exploit these new features.
Types of Oracle Partitions
Oracle provides three types of partitioning for tables and indexes:
Partition Type
Data is partitioned
Range
In a table or index according to a range of values
Hash
According to a hash function
Composite
By range and further subdivides the data into sub-partitions using a hash function
Advantages of Partitioning
Oracle partitioning can improve Oracle performance in a variety of ways:
Oracle can restrict SQL requests to only those partitions that are affected by the query.
Oracle maintenance is improved because index and table partitions can be reorganized and independently maintained.
Disk load balancing can be improved because table and index partitions can be transported to other disks independently of the entire table or index.
Parallel queries run faster because the parallel query optimizer detects partitions and creates parallel processes to simultaneously read each partition.
Some Oracle SQL statements run faster because the Oracle SQL optimizer recognizes those partitions that are required for a query and only accesses those partitions.
Creating a Partition
To see how partitions are defined let us examine a sample sales table for an Oracle data warehouse by viewing the code below. As we see from this syntax, the table is defined so that the data for each year resides in a separate partition, and each partition resides in a separate tablespace.
create table
sales_data
(
store_nbr number,
city char(40)
year number(4),
month number(2),
units_sold number,
sales_dollar number
)
partition by range(year)
(
partition p1998 values less than(1998) tablespace data_1998,
partition p1998 values less than(1999) tablespace data_1999,
partition p2000 values less maxvalue tablespace data_2000,
Define Partitions in Oracle
1) The table appears to the SQL engine as a large whole table
2) But is treated as independent entities at the Oracle and tablespace levels.
Partitioning As the Foundation for Information Lifecycle Management
(ILM) Information Lifecycle Management is a set of processes and policies for managing data throughout its useful life. One important component of an ILM strategy is determining the most appropriate and cost-effective medium for storing data at any point during its life time:
newer data used in day-to-day operations is stored on the fastest, most highly-available storage tier,
older data which is accessed infrequently may be stored on a less-expensive and less-performant storage tier.
Older data may also be updated less frequently in which case it makes sense to compress and store the data as read-only. Oracle Database provides the ideal environment for implementing your ILM solution. Oracle supports multiple storage tiers, and since all of the data remains in the Oracle database, the use of multiple storage tiers is completely transparent to the application and the data continues to be completely secure. Partitioning provides the fundamental technology that enables data in tables to be stored in different partitions. Although multiple storage tiers and sophisticated ILM policies are most often found in enterprise-level systems, most companies and most databases need some degree of information lifecycle management. The most basic of ILM operations, archiving older data and purging or removing that data from the database, can be orders of magnitude faster when using partitioning.
Now let us illustrate how a partition improves performance on a query.
In the query below we select all sales in 1997 in Raleigh.
SELECT SUM(units_sold)
FROM sales_data
WHERE
year = 1997
AND
city = 'Raleigh';
This is a regular sql select statement.
With partitions, we can re-formulate our query to include only the 1997 partition. See below.
This is the sql, re-formulated to include only one partition
This query limits the scope of the query to only those partitions that need to be accessed, thereby improving performance.
The next lesson looks at how partitions work and the different DBA views Oracle provides for partitioned objects.
Create Partitioned Table - Exercise
Before you continue, click the exercise link below to test your knowledge of the creation of table and index partitions. Create Partitioned Table - Exercise
