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, well 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 isnt 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 dont 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 dont forget to consider partitioned indexes).
Taking the list of tables and doing a look-see of whats 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 youre dealing with a 100 million plus rows, whats 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. Its (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.