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
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
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
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
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 ( 4 'AR_INVOICES_ALL', 5 'RA_CUST_TRX_LINE_GL_DIST_ALL', 6 'GL_IMPORT_REFERENCES', 7 'AP_AE_LINES_ALL', 8 'PA_EXPENDITURES_ITEMS_ALL', 9 'GL_BALANCES', 10 'OE_ORDER_LINES_ALL', 11 'PA_COST_DISTRIBUTION_LINES_ALL', 12 'RA_CUSTOMER_TRX_LINES_ALL', 13 'WF_ITEM_ATTRIBUTE_VALUES' 14 ); TABLE_NAME NUM_ROWS ------------------------------ ---------- AP_AE_LINES_ALL 20292520 RA_CUSTOMER_TRX_LINES_ALL 23246400 RA_CUST_TRX_LINE_GL_DIST_ALL 115920970 GL_BALANCES 50047670 GL_IMPORT_REFERENCES 148672310 PA_COST_DISTRIBUTION_LINES_ALL 4231480 WF_ITEM_ATTRIBUTE_VALUES 25304330 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.
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.