Tables

The basic unit of information storage within an Oracle database is a table. A table contains rows of data which represent instances of a given entity (for example, addresses for employees). The table also has columns which represent attributes associated with the rows (for example, the city and state for a given address). Oracle has expanded its column capabilities over the years to progress from simple number and character data storage to more complex data types such as video, audio, or image data.

You might be interested in knowing how Oracle chooses which bytes on the disk to use to store the file and how it delimits the various columns and other such detailed storage data, but it’s not necessary. When you insert a row into a table, Oracle finds the correct spot within the extents that you have allocated for that table and stores the data in a manner that you can retrieve it. Oracle keeps track of the exact location of the bits and bytes. The only internal storage feature that you might use is the row ID. This unique number specifies a given row in a given table. That way, if you have multiple rows in a table that have the exact same values and you want to get rid of one of them, you can use the row ID to do it.

Here is a sample command used to create a table called golf_scores:

create table golf_scores

(course       char(20),

date_played   date,

partner       char(20),

front_nine    number(3),

back_nine     number(3),

total         number(3))

initrans 1

maxtrans 100

tablespace user_data

storage (

initial 10K

next 10K

pctincrease 0

minextents 1

maxextents 100);

The most straightforward approach to analyzing this command (and tables in general) is to break down the command into its components:

•  create table golf_scores tells Oracle what to do (create table). Every table needs a name, which is any valid combination of characters and underlines (spaces are not allowed) that is 30 characters or fewer. I like table names that tell me what is in the table versus the old mainframe code and number systems. By the way, Oracle has certain reserved words, such as order, which you cannot use as the table name (although order could be part of a larger name).

•  The next six lines (contained within parentheses) list the names of the columns for this table. Associated with each column is a datatype (for example, char for character) and size. (The various types of data and sizing are discussed later in this section.)

•  initrans and maxtrans are similar to the initial and next extent parameters, except they allocate space to record transactions that are in progress to the table. This space is allocated in the header of the table and is in chunks of 23 bytes each. This space is not recovered when the transaction is completed. The maximum value for these is no more than 255 and may be less on some operating systems. If you run out of transaction space for a table, new transactions have to wait until transaction space is freed up by existing transactions completing. If you do not specify values for these parameters, default values that apply to your instance are used.

•  tablespace user_data is where you tell Oracle to put your table—in this example, a tablespace called user_data. If you do not specify this parameter, the default tablespace for your user ID will be used.

•  storage() contains five parameters within the parentheses that are the storage parameters for this table. If you do not specify any of these values, the default value for that parameter in this tablespace is used.

•  initial and next are the initial extent and next extent parameters described under tablespaces.

•  pctincrease is the percentage of increase in size applied to each of the next extents. Take an example where the initial extent size was 1KB, the next is 2KB, and pctincrease is 50%. The third extent would be 3K (1.5 times 2KB). The fourth extent would be 4.5KB and so on.

•  minextents and maxextents are the minimum and maximum number of extents that you have for this table.

Each column in a table has a column name, a datatype, and some sizing parameters. The rules for column names are the same as the rules for table names, and it’s a good idea to use column names that are meaningful. Character data has a size to it that specifies the maximum length of the character string. Numbers have precision and scale. Precision refers to the total number of digits, not including the exponent when this number is expressed in exponential notation. The scale specifies the number of digits to the right of the decimal place. A number with a precision of 5 and a scale of 2 will range between –999.99 and +999.99 and is considered to be datatype number(5,2). Table 12.1 lists datatypes supported by Oracle (older versions may not support the long raw or varchar2 datatypes):