Developing and Implementing Applications, continued - Sizing Tables That Contain Large Objects (LOBs) (Page 7 of 8 )
LOB data (in BLOB or CLOB datatypes) is usually stored apart from the main table. You can use the lob clause of the create table command to specify the storage for the LOB data. In the main table, Oracle stores a lob locator value that points to the LOB data. When the LOB data is stored out of line, between 36 and 86 bytes of control data (the lob locator) remain inline in the row piece.
Oracle does not always store the LOB data apart from the main table. In general, the LOB data is not stored apart from the main table until the LOB data and the lob locator value total more than 4000 bytes. Therefore, if you will be storing short LOB values, you need to consider their impact on the storage of your main table. If your LOB values are less than 4000 characters, you may be able to use VARCHAR2 datatypes instead of LOB datatypes for the data storage.
Sizing Partitions
You can create multiple partitions of a table. In a partitioned table, multiple separate physical partitions constitute the table. For example, a SALES table may have four partitions: SALES_ NORTH, SALES_SOUTH, SALES_EAST, and SALES_WEST. You should size each of those partitions using the table-sizing methods described earlier in this chapter. You should size the partition indexes using the index-sizing methods shown earlier in this chapter.
Using Temporary Tables
You can create temporary tables to hold temporary data during your application processing. The table’s data can be specific to a transaction or maintained throughout a user’s session. When the transaction or session completes, the data is truncated from the table.
To create a temporary table, use the create global temporary table option of the create table command. To delete the rows at the end of the transaction, specify on commit delete rows, as shown here:
create global temporary table MY_TEMP_TABLE (Name VARCHAR2(25),
Street VARCHAR2(25),
City VARCHAR2(25))
on commit delete rows;
You can then insert rows into MY_TEMP_TABLE during your application processing. When you commit, Oracle will truncate MY_TEMP_TABLE. To keep the rows for the duration of your session, specify on commit preserve rows instead.
From the DBA perspective, you need to know if your application developers are using this feature. If they are, you need to account for the space required by their temporary tables during their processing. Temporary tables are commonly used to improve processing speeds of complex transactions, so you may need to balance the performance benefit against the space costs. You can create indexes on temporary tables to further improve processing performance, again at the cost of increased space usage.
NOTE
Temporary tables and their indexes do not acquire any space until the first insert into them occurs. When they are no longer in use, their space is deallocated.
Supporting Tables Based on Abstract Datatypes
User-defined datatypes, also known as abstract datatypes, are a critical part of object-relational database applications. Every abstract datatype has related constructor methods users execute to manipulate data in tables. Abstract datatypes define the structure of data—for example, an ADDRESS_ TY datatype may contain attributes for address data, along with methods for manipulating that data. When you create the ADDRESS_TY datatype, Oracle will automatically create a constructor method called ADDRESS_TY. The ADDRESS_TY constructor method contains parameters that match the datatype’s attributes, facilitating inserts of new values into the datatype’s format. In the following sections, you will see how to create tables that use abstract datatypes, along with information on the sizing and security issues associated with that implementation.
You can create tables that use abstract datatypes for their column definitions. For example, you could create an abstract datatype for addresses, as shown here:
create type ADDRESS_TY as object
(Street VARCHAR2(50),
City VARCHAR2(25),
State CHAR(2),
Zip NUMBER);
/
Once the ADDRESS_TY datatype has been created, you can use it as a datatype when creating your tables, as shown in the following listing:
create table CUSTOMER
(Name VARCHAR2(25),
Address ADDRESS_TY);
When you create an abstract datatype, Oracle creates a constructor method for use during inserts. The constructor method has the same name as the datatype, and its parameters are the attributes of the datatype. When you insert records into the CUSTOMER table, you need to use the ADDRESS_TY datatype’s constructor method to insert Address values, as shown here:
insert into CUSTOMER values ('Joe',ADDRESS_TY('My Street', 'Some City', 'ST', 10001));
In this example, the insert command calls the ADDRESS_TY constructor method in order to insert values into the attributes of the ADDRESS_TY datatype.
The use of abstract datatypes increases the space requirements of your tables by 8 bytes for each datatype used. If a datatype contains another datatype, you should add 8 bytes for each of the datatypes.
Next: Using Object Views >>
More Oracle Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter five of the book Oracle Database 10g DBA Handbook, written by Kevin Loney and Bob Bryla (McGraw-Hill/Osborne, 2005; ISBN: 0072231459). Check it out today at your favorite bookstore. Buy this book now.
|
|
