Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Object partitioning in Oracle

Oracle Tips by Burleson Consulting
Nov 8, 2000

The ability to partition tables and indexes has had a dramatic impact on the use of Oracle for data warehouses. Object partitioning means that Oracle can handle petabytes of data, which has helped Oracle become one of the foremost data warehouse tools in the marketplace today.

Also see my related notes on partitioning:


This article will discuss the use of Oracle partitioning and explain how it allows databases to grow to such phenomenal sizes.


Table partitioning with Oracle

Table partitioning can greatly improve the manageability of very large data warehouse tables. Oracle provides a method to automatically partition a table in horizontal fashion at table creation time.

While at first glance this might seem to be a simple method of chunking tables, Oracle has also included the ability for each partition to have its own values for the internal Oracle control structures of an object. These include PCTFREE, PCTUSED, INITTRANS, and MAXTRANS.

This feature can be especially useful when a table is partitioned so that only the most recent partition is updated. In this case, all of the earlier partitions would have PCTUSED set to 100 and PCTFREE set to 0.

By allowing control over the partitions as if they were separate tables, the DBA can "pack" static data into the Oracle blocks and save space. The current partition would have PCTFREE set to a higher number to allow for row expansion as the rows are updated.


Increased availability with partitioning

Because table and index partitions exist as separate physical entities within the Oracle database, you can maintain them independently without affecting the availability of the other partitions.

This partitioning feature can be extremely useful for an Oracle data warehouse in a number of situations. For instance, the entire data warehouse may be far too large for a complete, periodic reorganization. Also, there are certain conditions that require the DBA to rebuild tables and indexes. These conditions include:

  • Disk failures. When a disk crashes, the object partition can be taken offline, restored, and rolled forward without affecting the availability of the other partitions.
     
  • Backup of objects. With object partitioning, portions of tables and indexes can be backed up without affecting the availability of the other partitions.
     
  • Static tables with PCTFREE set too high. Because data warehouses are data sensitive, it is common to see a "rolling" effect, in which the most current partition of a table gets heavy updates. As this partition becomes older, it is no longer updated, and the data warehouse DBA can export the partition and re-create it with small PCTFREE values, thereby packing the rows onto the data blocks and saving significant disk space. The DBA may also want to migrate these partitions onto read-only tablespaces on an optical jukebox, while the current partition remains in an updatable tablespace on disk.
     
  • Tables with too many migrated/chained rows. Sometimes a data warehouse table may initially be loaded with NULL VARCHAR columns that are later updated to insert expanded values. When this occurs, the rows can fragment into other data blocks, requiring additional I/O to access the row.
     
  • Tables that are approaching maximum extents. In tables with a finite number of extents, the insertion of new rows will cause the table to extend. While table extending is not a problem, if the table approaches its maximum values for extents, new rows will not be allowed into the table until it has been exported and imported into a single extent.
     
  • Indexes that have too many deleted leaf blocks. Oracle data warehouse indexes can become unbalanced if there are too many deleted leaf blocks within the index. When this happens, the Oracle warehouse DBA will want to drop and re-create the index.
     
  • Indexes with more than four levels. With heavy update activity, it is not uncommon to see a data warehouse index spawn to deeper levels in certain spots. When this happens, the data warehouse DBA will want to drop and re-create the index, thereby rebalancing the levels.
     
  • Base-table indexes that are not clustered with the base table. In Oracle, it is possible to have one index that is physically sequenced in the same order as the table. As rows are added onto the end of the table, the index will become less clustered.

In cases where the rows are out of sequence with the primary key index, the Oracle DBA will extract and sort the table, replace the table in row order, and rebuild the clustered index. When any of these conditions occur, the DBA will be able to take the offending partition offline, rebuild the object, and reintroduce it into the data warehouse with minimal service interruption.

Note: With table partitioning, some automatic clustering of the data with the index will take place automatically, since new table rows will be directed to the partition that contains similar partition values. However, clustering within the partition may get out of sync when too many rows are added to the end of the partition.

Increased performance with partitioning

The Oracle engine can take advantage of the physical segregation of table and index partitions in several ways:

  • Disk load balancing—Table and index partitioning allows the Oracle data warehouse DBA to segregate portions of very large tables and indexes onto separate disk devices, thereby improving disk I/O throughput and ensuring maximum performance.
     
  • Improved query speed—The Oracle optimizer can detect the values within each partition and access only those partitions that are necessary to service the query. Since each partition can be defined with its own storage parameters, the Oracle SQL optimizer may choose a different optimization plan for each partition.
     
  • Faster parallel query—The partitioning of objects also greatly improves the performance of parallel query. When Oracle detects that a query is going to span several partitions, such as a full-table scan, it can fire off parallel processes. Each of processes will independently retrieve data from each partition. This feature is especially important for indexes, since parallel queries don't need to share a single index when servicing a parallel query.

Now that we've reviewed the compelling reasons for using partitioning with Oracle tables and indexes, let's take an in-depth look at how partitioning is implemented within the Oracle architecture.

Creating an Oracle partitioned table

The process of creating a partitioned table with Oracle is very straightforward.  The only real difference from previous versions is that the STORAGE clause has been changed to specify the partitions.

For example, if we had a table called all_facts, we would first need to choose a partition key for the operation.  A partition key is generally a date value, and it specifies the range of rows that will participate in each partition.  For some data warehouse applications, the partition key could be a nondate value, such as where an employee table is partitioned according to the value of each employee's department column. Listing 1 shows some sample SQL code for creating an Oracle table partition:


Listing 1CREATE TABLE all_facts
(
order_date date,
order_year number(2),
order_quarter char(2),
order_month, number(2),
order_nbr number(5),
salerperson_name varchar(20),
customer_name varchar(20),
customer_city varchar(20),
customer_state varchar(2),
customer_region char(1),
item_nbr number(5),
quantity_sold number(4)
)
PARTITION BY RANGE
(order_date)
(
PARTITION
year_1998
VALUES LESS THAN '01-JAN-1999'
TABLESPACE year_1998
STORAGE (INITIAL 500M, NEXT 5M, PCTUSED 99, PCTFREE 1),
PARTITION
year_1999
VALUES LESS THAN '01-JAN-2000'
TABLESPACE year_1999
STORAGE (INITIAL 500M, NEXT 5M, PCTUSED 99, PCTFREE 1),
PARTITION
year_2000
VALUES LESS THAN (MAXVALUE)
TABLESPACE year_2000
STORAGE (INITIAL 500M, NEXT 50M, PCTUSED 60, PCTFREE 40),
)

Let's review this syntax. Each table partition is defined as having the order_date column as the partition key. We also see that the VALUES LESS THAN parameters determine which rows are partitioned into which tablespace. Note that the last partition (year_2000) specifies VALUES LESS THAN (MAXVALUE), which means that all other rows that do not meet the selection criteria will be placed into this partition. Also, note that even though the selection parameters for the year_1998 partition read VALUES LESS THAN '01-JAN-1999', the 1997 rows will not be stored into the year_1998 partition because the value check is preceded by the filter for the year_1998 partition.

As you can see, each partition has been created with different tablespace storage parameters. In this example, it appears that only the last partition will be updated, as evidenced by the value of the PCTFREE parameter in the year_2000 tablespace. At SQL INSERT TIME, the DDL is consulted in the Oracle data dictionary, and the value specified in order_date will determine which partition Oracle uses to store the row within the table.

This partitioning of tables also allows each partition to be referenced as a unique entity, saving resources within the database. For example, we can still query the entire table as a whole:


SELECT SUM( quantity_sold)
FROM all_facts
WHERE
order_year = 2000
AND
customer_city = 'Kittrell';


However, it would be simpler and less resource intensive to rework this query to specify the target partition in the query.


SELECT SUM( quantity_sold)
FROM all_facts PARTITION (year_2000)
WHERE
order_year = 2000
AND
customer_city = 'Kittrell';


In the same fashion, partitions can be used to limit update statements to a single partition. For instance, if we have a huge employee table partitioned by department, we could give a 10 percent raise to the MIS department:

UPDATE
   all_employee
PARTITION
   ('MIS')

SET
   salary = salary*1.1;


Migration into partitioned table structures

Migration into partitioned tables is very simple using Oracle. If we take our sample table definition from the listing above, we can easily migrate our data from an old Oracle fact table into the new partitioned structure:

INSERT INTO all_fact PARTITION (year_1995)
(
SELECT * FROM old_fact
WHERE
order_year = 05
);
INSERT INTO all_fact PARTITION (year_1996)
(
SELECT * FROM old_fact
WHERE
order_year = 06
);
INSERT INTO all_fact PARTITION (year_1997)
(
SELECT * FROM old_fact
WHERE
order_year = 07

);

Note: In the above example, the WHERE statement clause is redundant. The partition definition will automatically filter out the rows that do not match the selection criteria for each partition.

Now let's take a look at how you can partition Oracle indexes. In many ways, the ability to partition Oracle indexes has more performance potential than table partitioning, since indexes are a common source of contention with Oracle data warehouses.

Index partitioning with Oracle

The first partitioned index method is called a LOCAL partition. A local partitioned index creates a one-for-one match between the indexes and the partitions in the table. Of course, the key value for the table partition and the value for the local index must be identical. The second method is called GLOBAL and allows the index to have any number of partitions.

The partitioning of the indexes is transparent to all SQL queries. The great benefit is that the Oracle query engine will scan only the index partition that is required to service the query, thus speeding up the query significantly. In addition, the Oracle parallel query engine will sense that the index is partitioned and will fire simultaneous queries to scan the indexes.


Local partitioned indexes

Local partitioned indexes allow the DBA to take individual partitions of a table and indexes offline for maintenance (or reorganization) without affecting the other partitions and indexes in the table.

In a local partitioned index, the key values and number of index partitions will match the number of partitions in the base table.


CREATE INDEX year_idx
on all_fact (order_date)
LOCAL
(PARTITION name_idx1,
PARTITION name_idx2,
PARTITION name_idx3);


Oracle will automatically use equal partitioning of the index based upon the number of partitions in the indexed table. For example, in the above definition, if we created four indexes on all_fact, the CREATE INDEX would fail since the partitions do not match. This equal partition also makes index maintenance easier, since a single partition can be taken offline and the index rebuilt without affecting the other partitions in the table.

Global partitioned indexes

A global partitioned index is used for all other indexes except for the one that is used as the table partition key. Global indexes partition OLTP (online transaction processing) applications where fewer index probes are required than with local partitioned indexes. In the global index partition scheme, the index is harder to maintain since the index may span partitions in the base table.

For example, when a table partition is dropped as part of a reorganization, the entire global index will be affected. When defining a global partitioned index, the DBA has complete freedom to specify as many partitions for the index as desired.

Now that we understand the concept, let's examine the Oracle CREATE INDEX syntax for a globally partitioned index:

CREATE INDEX item_idx
on all_fact (item_nbr)
GLOBAL
(PARTITION city_idx1 VALUES LESS THAN (100),
PARTITION city_idx1 VALUES LESS THAN (200),
PARTITION city_idx1 VALUES LESS THAN (300),
PARTITION city_idx1 VALUES LESS THAN (400),
PARTITION city_idx1 VALUES LESS THAN (500));


Here, we see that the item index has been defined with five partitions, each containing a subset of the index range values. Note that it is irrelevant that the base table is in three partitions. In fact, it is acceptable to create a global partitioned index on a table that does not have any partitioning.

Summary on table partitioning

These new data warehouse features for Oracle continue to underscore Oracle Corporation's commitment to supporting very large data warehouse architectures. It will be exciting to see how these features continue to improve in Oracle9i, when some of the object-oriented features will be available for the data warehouse. Even more exciting will be the support for class hierarchies and inheritance, since these features will greatly improve the ability of the Oracle data warehouse designer to implement ad-hoc classifications of data attributes.

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational