Page 21
The char data type is a fixed length field. This means that the field uses the maximum defined number of bytes of storage even if the field is not fully filled with characters. White space, similar to spaces caused by a space bar, fills the positions in the field that do not contain characters. The varchar2 data type is variable length. This means the field only uses the number of bytes equal to the actual characters contained in the field. The positions in the field that do not contain characters contain null values. Null means blank or unknown. The char data type is often used for fields that always contain the same number of characters. Examples include payroll ids, model numbers, or ZIP Codes. Varchar2 fields are used for addresses or text.
Indexes are comparable to indexes in a book. The index at the back of this book contains key words used throughout the book. Each reference has an associated page number used to locate the passage that discusses the key word. Indexes help the reader quickly identify the location of information. Database indexes perform the same function. They are mini-tables that contain a subset of the table's data and are used to rapidly locate records.
Database indexes increase the speed of record retrieval because they avoid tablespace scans. When the database manager tries to retrieve records from a table that does not contain an index, it must read each record in the table. This scanning of the table from the first byte to the last is called a tablespace scan. Developers try to avoid them as much as possible because reading records on the hard disk is a slow process. Indexes are mini-tables that contain the values from one or more of the table's columns and the location of the associated record. They eliminate the need to perform tablespace scans by allowing the database manager to read them rather than the table to obtain the record location.
Figure 2.5 illustrates the differences in size. If the Customer Information Table is unindexed and you try to find a record for an account number, Oracle7 must perform a tablespace scan. The database manager must read 80,000,000 bytes to obtain the records. When an index is created on the account number field, the database manager uses the index to locate the record. The index is only 2,000,000 bytes. This is 40 times less data through which the database manager must search.
FIG. 2.5
Comparison of the sizes
of the Customer
Information Table and
an index on the
Customer Account Number Field.
Indexes have other features as well that aid the retrieval of records. The first feature is that the records are sorted. This means the database manager does not have to continue reading the index after finding the desired record because it knows a record with the same value cannot exist somewhere else in the index. This sorting property also allows the database manager to perform special searches, eliminating the need to scan the entire index. The manager can read
Page 22
the middle record first. The record is likely to be lesser or greater than the value contained in this index record. After determining this, the manager has eliminated half of the records in the index. The next record read is at the end of the first quarter or third quarter of the index. The manager then determines whether the value in the record is higher or lower than the desired value. By repeating this process, the database manager can obtain the location of the desired records after reading a small number of index records.
Good database practice requires each record in a table to have a field(s) that makes that record unique to any other record in the table. Indexes provide the database manager with the mechanism to maintain this uniqueness. Oracle7 enables the developer to define unique indexes. This type of index allows only one occurrence of a particular value in a column. Before a record is added to the table, the value in the indexed field(s) are checked against the index. If the value matches an existing record, the database manager stops the transaction. If the value does not match, the transaction completes.
TIP |
You will find that indexes are one of the most important objects in a database for the enhancement of database performance. Whenever you are having performance trouble with a query, form, or report, the first place to look is the existing indexes. |
A database view is similar to a window. It enables the user to see or access a portion of the database. A view is, in reality, a predefined query that produces a virtual or temporary set of records or table when executed. Because a view defines how data is to be assembled, views do not have any impact on the database unless executed.
Views do have several advantages. Views enable the developer to hide portions of the table from others. To do this, you exclude these fields from the view definition. Views also enable you to hide data complexity from the user. In relational databases, information is seldom held in one table; it is contained in several related tables. The developer defines the complex logic of assembling data into the view. You can then use the view as a tablename in a query. You do not need to include the complex logic in the query. The user never knows whether he is using a view or a table (see Figure 2.6).
FIG. 2.6
It's a view.
Page 23
Views enable the developer to define custom names for the columns. This creates an element of user-friendliness in the database because the columns can conform to user desires rather than corporate naming standards.
A special advantage of views is that you can use them as a tablename in an Oracle Form. You will understand this more after reading about Oracle Forms 4.5 in Part IV of this book.
A synonym is another name for an Oracle table or view. Synonyms enable a developer to create simpler names for these objects.
NOTE |
At the company that I work for, our data administrators like to give tables descriptive names. One of the tables that I use contains electrical equipment called CTPS. This name is an acronym, so the data administrator decided to use the real equipment name for the table. The table was named T_and_D_Cable_Terminal_Poles (see Figure 2.7). Every time I use this name in a query, form, or report, I need to type 28 characters. I generally make a mistake typing that many characters, so I asked the DBA to create a synonym called CTPS for the table. This greatly increased the usability of the table. |
FIG. 2.7
What's that table's name?
Oracle Forms 4.5 is the product
used to develop and execute
GUI forms. The forms
are used by an operator
to manage and view the data.
Synonyms are also useful when the table is in a remote database. Remote database tables require some extra characters at the end of the tablename. The characters tell Oracle where the table is located. You can use a synonym to mask these characters. Users can then use the table as if it is located in their own user id.
Before Oracle allows you to establish a session, create a database object, or perform any database function, you must be granted a privilege. Privileges enable the developer to perform a large array of functions. You can find definitions of these privileges in Chapter 6, "Defining the Database Objects."
When a lot of privileges are granted to many users, it may be easier to create a role and grant the privileges to the role. The user is then given the role. When new privileges are needed, the role is granted the privileges. By default, each user that is assigned the role gets the new privilege. This decreases the DBA overhead in managing the database.