14.4 Different Types of Objects

---

The size and usage of tables affects the way that physical structure should be organized.

• Transactional Data.   Tables with high DML activity can have a profound effect on physical requirements. Updates on large datatypes making column values larger should have more free space available for future updates. High deletion activity dictates that blocks will become available for insertions again when a threshold is reached. Less space is wasted when blocks are more likely to be reused. Tables with high insertion activity and little update or deletion activity do not have to reserve free space.

• Read-Only and Static Data.   Read-only or static tables such as data warehouse or lookup tables do not require any reserved space for later changes. In these cases all block space can be used when initially filling the tables.

• Large Tables.   Regardless of volatility, large and small tables should be treated differently. Very large tables should use as much space as possible in each block in order to minimize disk space used. The more physical space that is read when data is retrieved the more I/O and CPU time used, and thus the worse the performance.

• Small Tables.   Very small tables with high concurrency rates can present problems all of their own. Small static tables, which are never changed, are generally not a problem as long as any foreign keys are indexed. Some small semi-static tables can present serious concurrency issues when they are subject to high amounts of DML activity. The Accounts schema has a number of tables of this nature, particularly the Customer, Supplier, and COA tables. Each of these tables has summarized values. Originally I used triggers in my DBMS_JOBS initiated highly active database code. I had serious problems with deadlocks updating the summary columns. I disabled the triggers and updated these columns in batch mode when my activity code was no longer running. High concurrency changes can be made to small tables at the block level to resolve this problem. Years ago when working in the Ingres relational database the only method of resolving this type of problem was by filling each row with an empty column, padding each row up to the block size. Thus each row occupied a single block. Of course in those days Ingres block-locked rather than row-locked; Oracle Database locks rows. This is an extreme solution but always a possibility, even with Oracle Database.

---