0635-0637

Previous Table of Contents Next

Page 635

LONG	Used to store up to 4 gigabytes of alphanumeric data. As with VARCHAR2, values are stored at their actual lengths. LONG values cannot be indexed, and the normal character functions such as SUBSTR cannot be applied to LONG values.
RAW	Used to store binary data with no character set conversion. RAW data cannot exceed 255 bytes. RAW data can be indexed, but no functions can be performed on RAW values.
LONG RAW	Used to store large binary objects such as whole documents, video, and graphics, or entire compiled programs. LONG RAW can store up to 4 gigabytes of information, but cannot be indexed.
ROWID	Used to represent a row's physical address. Every table has a ROWID pseudo-column, which is not evident when describing the table or issuing SELECT * FROM table_name. This address will remain unchanged in Oracle8 and changes in Oracle7 only when the table is exported and imported, or otherwise physically moved on disk. In practice, this value is rarely used.

You should consider additional factors besides the nature of the data and its length when selecting a datatype for a column. For example, one might assume that it is always better to use VARCHAR2 instead of CHAR, so that only the actual number of bytes present in the data will be stored. There are differences, however, in how comparisons are handled with these datatypes. Two VARCHAR2 values must be the same length to be considered equal, where as two CHAR values are compared without consideration of trailing spaces. As a result, if the values `WORD' and `WORD` are being compared, they will compare as equal if they are CHAR values, but will not be considered equal if they are VARCHAR2 values because of the trailing spaces in the second value.

When using the NUMBER datatype, the declared precision and scale greatly affect how the data is stored. If not fully understood , these values might not behave as intended. For example, assume that a column has been declared NUMBER(10,4). One might conclude this means that up to 10 digits can be stored in this column, as many as 4 of which can be to the right of the decimal. This is not necessarily the case, however. An attempt to insert a value of 1234567 into this column will fail, with an error code of ORA01438 (value larger than specified precision allows for this column). The declaration of NUMBER(10,4) does allow up to 10 digits of precision, but only 6 of these can be to the left of the decimal.

The default DATE format can be changed for the database instance, or for a particular session. If you want to store times in a column declared as DATE, you must alter the default date format to include the time, or all values inserted will have the default time of 12:00 A.M. (with the exception of the SYSDATE system variable). To insert date values from an application using the default date format, a statement such as

 INSERT INTO table_name (column_name) VALUES ('01-JAN-95') 

Page 636

must be issued. Because there is no way to specify time using the default data format, Oracle will store the default value of 12:00 A.M. If this statement is changed to

 INSERT INTO table_name (column_name) VALUES(SYSDATE) 

the time is inserted accurately because SYSDATE shares the same internal representation. The date format can be altered for a session by issuing a statement such as

 ALTER SESSION SET NLS_DATE_FORMAT = `MM-DD-YYYY HH:MI A.M.' 

After this statement has been issued, times can be inserted accurately using the new format:

 INSERT INTO table_name (column_name) VALUES('12-31-1994 11:59 P.M.') 

Note that this format is valid only for the session. In any subsequent sessions, the statement

 SELECT column_name FROM table_name 

will return 31-DEC-94 for the previously inserted value until the session is altered again.

TIP

The NLS_DATE_FORMAT can be set for the database instance by including it in the initialization file.

Using the contact manager application example, the column attributes for the Addresses table might be defined as in Table 25.7.

Table 25.7. Definition of the column attributes of the Addresses relation in the contact manager sample application.

Column	Attribute
ID	NUMBER(10)
Entity Type	NUMBER(10)
Entity ID	NUMBER(10)
Address Line 1	VARCHAR2(40)
Address Line 2	VARCHAR2(40)
Address Line 3	VARCHAR2(40)
City	VARCHAR2(30)
Zip Code	NUMBER(5)
Address Type	NUMBER(10)
Last Update User ID	VARCHAR2(20)
Last Update Date/Time	DATE

Page 637

Defining the column attributes is an important step in capacity planning. From this information, the maximum record size for each table can be determined. This, combined with an estimate of the total number of rows, helps determine the amount of storage required to house the data.

The next step is to begin creating DDL scripts that will be used to create the tables. This might seem like a step toward implementation, but DDL can be used as a tool for capacity planning and the design of tablespaces and data file layout. The DDL for creating tables consists of defining column attributes and constraints, storage specification, table constraints, and rules. Constraints and rules are discussed in detail in the chapter on enforcing integrity; therefore, for now, the description of DDL focuses on column attributes and storage specification.

Referring to the sample application, assume that it is estimated that 5,000 address records will be stored initially and that the number of records is expected to double in the next several years . From the definition of the column attributes for the address table, it is apparent that the maximum size of any single record is 254 bytes. (Remember that DATE columns require 49 bytes always.) Assume further that the primary and foreign key IDs will be sequenced starting with 1, and that Address Line 2 and Address Line 3 are rarely populated . Based on this additional information, a conservative estimate would be that the average record size will be 200 bytes. The total size of the table then can be estimated at 1MB initially. The script in Listing 25.1 can then be written with an appropriate storage specification.

Listing 25.1. DDL script that illustrates the use of the STORAGE clause.

 CREATE TABLE Addresses (          Address_ID                 NUMBER(10)    PRIMARY KEY         ,Address_Type_ID        NUMBER(10)    NOT NULL         ,Entity_ID              NUMBER(10)    NOT NULL         ,Entity_Type_ID         NUMBER(10)    NOT NULL         ,Address_Line1          VARCHAR2(40)  NOT NULL         ,Address_Line2          VARCHAR2(40)         ,Address_Line3          VARCHAR2(40)         ,City                   VARCHAR2(30)  NOT NULL         ,Zip_Code               NUMBER(5)     NOT NULL         ,Last_Updt_User         VARCHAR2(20)     NOT NULL         ,Last_Updt_Timestamp    DATE          NOT NULL  )  TABLESPACE Contact_Main  STORAGE (    INITIAL             1M               NEXT              100K               MINEXTENTS           1               MAXEXTENTS         100 

PCTINCREASE 10 );

Note that, although the columns have been rearranged and renamed , the physical table still reflects the logical relation, with a few exceptions. The foreign key constraints have been omitted. There are a number of ways to enforce the foreign key constraints, through column constraints, table constraints, or other means. The issue of enforcing integrity through table and

Previous Table of Contents Next