Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 9, 2009

To Partition or Not to Partition in E-Business Suite

By Steve Callan

As a database administrator, there are two kinds of databases you are likely to own: those you design and those you inherit. In this article, we’ll look at what takes place during the design phase, and then apply the lessons there to a real world implementation, courtesy of Oracle E-Business Suite.

As many database design books tend to illustrate, there are two structures to keep in mind when designing a database, and those are logical and physical. The logical structure is usually what comes to mind when visualizing a layout, where the visual image is materialized into an entity relation diagram (ERD) courtesy of a tool. The logical layout helps to diagram how the database (or more specifically, the application) will operate. Details filled in here include how tables are organized with respect to attributes. As an example, if you have a table dealing with people, how are those people further classified? You could have one table “managing” everything about people, regardless of where they exist (i.e., internal or external to your organization).

In this super table, using employees and customers as a simple classification, not all columns or attributes apply to a person (would an employee have a shipping address versus would a customer have a hire date?). If you prefer to segregate people, then two tables could be used. This approach leads to more tables (it can be applied to more than just people), but there are benefits related to performance since there are smaller sets of data to examine (which leads to a hint where this is going).

Continuing on, once all the tables are designed, what happens next is table creation. The big mistake or hobbling event here is that the physical table is created as a direct mapping of the logical table. In other words, you could look at many databases and see a direct mapping of the ERD/logical table in the physical structure of the database. The pitfall here is that the physical design often fails to take performance-enhancing features into account. The functional layout may be quite valid, but the physical layout has immediately introduced obstacles to good or acceptable performance.

A classic example of this involves partitioning, or the lack thereof. This is less likely to happen during the design of a data warehouse because by now, the inclusion of partitioning in a warehouse is a common, well-known practice. However, this is not always the case in non-warehouse databases, where partitioning can certainly help improve performance and make administration easier to perform. Most tables are heap tables, and the term heap connotes a pile, and that is what most databases are: a pile of tables.

Other types of tables to consider when going from the logical to the physical include index organized tables. An ERD-diagrammed picture of a table isn’t necessarily going to tell you “make this table IOT when you create it.” Other table options are available, such as external, temporary, and object (why would you ever use that?), so keep them in mind before blindly laying down a logical table into a physical one without any other considerations.

That consideration seems like a fairly common sense approach, but sometimes common sense loses out to cents, as in paying much more for a feature that would make an application run better in the first place. This brings us to the Oracle Applications/E-Business Suite tie-in where the logical is the physical, unless you pay more to make the physical perform better. How and why do we know this?

Oracle Support has published a 53-page document regarding the use of partitioning within EBS (Using Database Partitioning with Oracle E-Business Suite, An Oracle White Paper, March 2009). This white paper updates/replaces the first version published in February, 2008 (with the slightly different title of Database Partitioning for Oracle E-Business Suite). The majority of the white paper explains what partitioning is and how to use it, which is basically a repeat of what is already contained in “real world” Oracle documentation (as opposed to “Oracle Apps world” documentation). If you are already familiar with partitioning, the relevant part of the white paper is the table titled “Examples of Partition Keys for Oracle Application Tables.”

The suggestion for partitioning encompass ten tables, along with the type of partitioning and the partitioning key (one table is listed twice). So, does Oracle Apps have any partitioning or is what is listed in the white paper a drill in implementing this feature from scratch? The answer to both is yes. In version 11.5.10, and excluding SYSTEM, 110 tables are partitioned. These tables install that way by default, and do not support user modification. By not supported, what is meant is this:

Modifying existing base product indexes and tables that have already been partitioned is not recommended or supported as it can cause application errors.

So, supported means you can add custom partitioning, but don’t mess with existing partitioned objects. The other tables, the list of which being a starting point and not meant to be all inclusive, are left for you to transform from normal tables to partitioned tables (and don’t forget to consider partitioned indexes).

Taking the list of tables and doing a look-see of what’s in an existing implementation (an 11.5.10 database), the results show some moderately sized tables. Note: not all tables may appear as this depends on additional modules you have licensed.

SQL> select table_name, num_rows
  2  from DBA_tables
  3  where table_name in (
 14  );

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
AP_AE_LINES_ALL                  20292520
GL_BALANCES                      50047670
GL_IMPORT_REFERENCES            148672310
OE_ORDER_LINES_ALL                1286010

8 rows selected.

A couple of things to note are in order. First, the exact number of rows may be more or less, since num_rows is derived from statistics, which may or may not be a 100% sampling. But, when you’re dealing with a 100 million plus rows, what’s a few million here and there? The second is that the query uses DBA/ALL_TABLES, because remember, these tables are not partitioned yet, otherwise, DBA/ALL_TAB_PARTITIONS would be applicable.

The point of the white paper is interesting in several aspects. First, EBS out of the box comes with some tables already partitioned, but only for what Oracle designed in terms of running the application. Just because it comes with partitioned tables does not mean you, in turn, can use partitioning without meeting licensing requirements.

Second, if you are going to use partitioning in EBS, do it early on as opposed to later, budget considerations permitting. The base install of EBS, with little to none of your own data to begin with, is the ideal time to convert normal tables into partitioned tables. It’s (almost) funny in that Oracle Corp. says, “Here, use our flagship ERP product, and by the way, if you pay even more, it works even better.” For what EBS costs to begin with, it would be nice if partitioning were already included.

Third, if you do implement partitioning later on, consider yourself to be in a state mentioned at the beginning of the article: stuck with a physical layout created based on the logical layout. In other words, the out of the box implementation was not designed for overall performance. You are then left with the design/coding/testing/implementation of adding custom partitioning to an already hugely complex application.

The white paper does offer a useful plan for how to go about the business of creating partitioned tables and indexes, so if you are at a loss of how or where to begin, the paper provides a good running start. The paper also suggests the use of table compression, kind of as an aside.

In Closing

The main point of all of this is to design well and design early on. Even if you inherit a less than optimal design, if changes need to be made, implement them earlier rather than later if at all possible. Many of the EBS tables will grow without bound, so when it comes time to purge (which is its own separate ordeal at times), being able to do that quickly via what partitioning offers makes this process that much easier to perform. Obviously, this approach is not just limited to EBS, as many applications can stand to benefit from partitioning and purging. Also not quite so obvious is that partitioning is not always the answer to managing large tables, but if used correctly, it can help immensely.

» See All Articles by Columnist Steve Callan

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM