Oracle Essentials Oracle Database 11g (19 page)

User-defined data

Oracle8 and later versions allow users to define their own complex datatypes, which are created as combinations of the basic Oracle datatypes previously discussed. These versions of Oracle also allow users to create objects composed of both basic datatypes and user-defined datatypes. For more information about

objects within Oracle, see Chapter 14.

Datatypes

|

87

AnyType, AnyData, AnyDataSet

Oracle9
i
and newer releases include three datatypes that can be used to explicitly define data structures that exist outside the realm of existing datatypes. Each of these datatypes must be defined with program units that let Oracle know how to process any specific implementation of these types.

Type Conversion

Oracle automatically converts some datatypes to other datatypes, depending on the SQL syntax in which the value occurs.

When you assign a character value to a numeric datatype, Oracle performs an implicit conversion of the ASCII value represented by the character string into a number. For instance, assigning a character value such as 10 to a NUMBER column results in an automatic data conversion.

If you attempt to assign an alphabetic value to a numeric datatype, you will end up with an unexpected (and invalid) numeric value, so you should make sure that you’re assigning values appropriately.

Youcan also perform explicit conversions on data, using a variety of conversion functions available with Oracle. Explicit data conversions are better to use if a conversion is anticipated, because they document the conversion and avoid the possibility of going unnoticed, as implicit conversions sometimes do.

Concatenation and Comparisons

The concatenation operator for Oracle SQL on most platforms is two vertical lines (||). Concatenation is performed with two character values. Oracle’s automatic type conversion allows you to seemingly concatenate two numeric values. If NUM1 is a numeric column with a value of 1, NUM2 is a numeric column with a value of 2, and NUM3 is a numeric column with a value of 3, the following expressions are TRUE: NUM1 || NUM2 || NUM3 = "123"

NUM1 || NUM2 + NUM3 = "15" (12 + 3)

NUM1 + NUM2 || NUM3 = "33" (1+ 2 || 3)

The result for each of these expressions is a character string, but that character string can be automatically converted back to a numeric column for further calculations.

Comparisons between values of the same datatype work as you would expect. For example, a date that occurs later in time is larger than an earlier date, and 0 or any positive number is larger than any negative number. You can use relational operators to compare numeric values or date values. For character values, comparisons of single characters are based on the underlying code pages for the characters. For mul-ticharacter strings, comparisons are made until the first character that differs between the two strings appears.

88

|

Chapter 4: Oracle Data Structures

If two character strings of different lengths are compared, Oracle uses two different types of comparison semantics:
blank-padded comparisons
and
nonpadded comparisons
. For a blank-padded comparison, the shorter string is padded with blanks and the comparison operates as previously described. For nonpadded comparisons, if both strings are identical for the length of the shorter string, the shorter string is identified as smaller. For example, in a blank-padded comparison the string “A ” (a capital A followed by a blank) and the string “A” (a capital A by itself) would be seen as equal, because the second value would be padded with a blank. In a nonpadded comparison, the second string would be identified as smaller because it is shorter than the first string. Nonpadded comparisons are used for comparisons in which one or both of the values are VARCHAR2 or NVARCHAR2 datatypes, while blank-padded comparisons are used when neither of the values is one of these datatypes.

Oracle Database 10
g
and later releases include a feature called the Expression Filter, which allows youto store a complex comparison expression as part of a row. You can use the EVALUATE function to limit queries based on the evaluation of the expression. The Expression Filter uses regular expressions, which are described later in this chapter.

NULLs

The NULL value is one of the key features of the relational database. The NULL, in fact, doesn’t represent any value at all—it represents the lack of a value. When you create a column for a table that must have a value, you specify it as NOT NULL, meaning that it cannot contain a NULL value. If you try to write a row to a database table that doesn’t assign a value to a NOT NULL column, Oracle will return an error.

You can assign NULL as a value for any datatype. The NULL value introduces what is called
three-state logic
to your SQL operators. A normal comparison has only two states: TRUE or FALSE. If you’re making a comparison that involves a NULL value, there are three logical states: TRUE, FALSE, and neither.

None of the following conditions are true for Column A if the column contains a NULL value:

A > 0

A < 0

A = 0

A != 0

The existence of three-state logic can be confusing for end users, but your data may frequently require you to allow for NULL values for columns or variables.

Datatypes

|

89

Youhave to test for the presence of a NULL value with the relational operator IS

NULL, since a NULL value is not equal to 0 or any other value. Even the expression: NULL = NULL

will always evaluate to FALSE, since a NULL value doesn’t equal any other value.

Should You Use NULLs?

The idea of three-state logic may seem somewhat confusing, especially when you imagine your poor end users executing ad hoc queries and trying to account for a value that’s neither TRUE nor FALSE. This prospect may concern you, so you may decide not to use NULL values at all.

We believe that NULLs have an appropriate use. The NULL value covers a very specific situation: a time when a column has not had a value assigned. The alternative to using a NULL is using a value with another meaning—such as 0 for numbers—and then trying to somehow determine whether that value has actually been assigned or simply exists as a replacement for NULL.

If you choose not to use NULL values, you’re forcing a value to be assigned to a column for every row. Youare, in effect, eliminating the possibility of having a column that doesn’t require a value, as well as potentially assigning misleading values for certain columns. This situation can be misleading for end users and can lead to inaccurate results for summary actions such as AVG (average).

Avoiding NULL values simply replaces one problem—educating users or providing them with an interface that implicitly understands NULL values—with another set of problems, which can lead to a loss of data integrity.

Basic Data Structures

This section describes the three basic Oracle data structures: tables, views, and indexes. This section also discusses partitioning, which affects the way that data in tables and indexes is stored.

Tables

The
table
is the basic data structure used in a relational database. A table is a collection of rows. Each
row
in a table contains one or more
columns
. If you’re unfamiliar with relational databases, youcan map a table to the concept of a file or database in a nonrelational database, just as you can map a row to the concept of a record in a nonrelational database.

As of Oracle9
i
, youcan define
external tables
. As the name implies, the data for an external table is stored outside the database, typically in a flat file. The external table
90

|

Chapter 4: Oracle Data Structures

is read-only; youcannot update the data it contains. The external table is good for loading and unloading data to files from a database, among other purposes.

Oracle Database 11
g
introduces the ability to create virtual columns for a table.

These columns are defined by an expression and, although the results of the expression are not stored, the columns can be accessed by applications at runtime.

Views

A
view
is an Oracle data structure defined through a SQL statement. The SQL statement is stored in the database. When you use a view in a query, the stored query is executed and the base table data is returned to the user. Views do not contain data, but represent ways to look at the base table data in the way the query specifies.

You can use a view for several purposes:

• To simplify access to data stored in multiple tables.

• To implement specific security for the data in a table (e.g., by creating a view that includes a WHERE clause that limits the data you can access through the view).

Starting with Oracle9
i
, youcan use
fine-grained access control
to accomplish the same purpose. Fine-grained access control gives you the ability to automatically limit data access based on the value of data in a row.

• To isolate an application from the specific structure of the underlying tables.

A view is built on a collection of
base tables
, which can be either actual tables in an Oracle database or other views. If youmodify any of the base tables for a view so that they no longer can be used for a view, that view itself can no longer be used.

In general, you can write to the columns of only one underlying base table of a view in a single SQL statement. There are additional restrictions for INSERT, UPDATE, and DELETE operations, and there are certain SQL clauses that prevent you from updating any of the data in a view.

You can write to a nonupdateable view by using an INSTEAD OF trigger, which is described later in this chapter.

Oracle8
i
introduced
materialized views
. These are not really views as defined in this section, but are physical tables that hold presummarized data providing significant performance improvements in a data warehouse. Materialized views are described in

more detail in Chapter 10.

Indexes

An
index
is a data structure that speeds up access to particular rows in a database.

An index is associated with a particular table and contains the data from one or more columns in the table.

Basic Data Structures

|

91

The basic SQL syntax for creating an index is shown in this example: CREATE INDEX emp_idx1 ON emp (ename, job);

in which emp_idx1 is the name of the index, emp is the table on which the index is created, and ename and job are the column values that make up the index.

The Oracle database server automatically modifies the values in the index when the values in the corresponding columns are modified. Because the index contains less data than the complete row in the table and because indexes are stored in a special structure that makes them faster to read, it takes fewer I/O operations to retrieve the data in them. Selecting rows based on an index value can be faster than selecting rows based on values in the table rows. In addition, most indexes are stored in sorted order (either ascending or descending, depending on the declaration made when you created the index). Because of this storage scheme, selecting rows based on a range of values or returning rows in sorted order is much faster when the range or sort order is contained in the presorted indexes.

In addition to the data for an index, an index entry stores the ROWID for its associated row. The ROWID is the fastest way to retrieve any row in a database, so the subsequent retrieval of a database row is performed in the most optimal way.

An index can be either unique (which means that no two rows in the table or view can have the same index value) or nonunique. If the column or columns on which an index is based contain NULL values, the row isn’t included in an index.

An index in Oracle refers to the physical structure used within the database. A
key
is a term for a logical entity, typically the value stored within the index. In most places in the Oracle documentation, the two terms are used interchangeably, with the nota-ble exception of the foreign key constraint, which is discussed later in this chapter.

Four different types of index structures, which are described in the following sections, are used in Oracle: standard B*-tree indexes; reverse key indexes; bitmap indexes; and function-based indexes, which were introduced in Oracle8
i
. Oracle Database 11
g
delivers the ability to use invisible indexes, which are described below.

Oracle also gives youthe ability to cluster the data in the tables, which can improve

performance. This is described later, in the section “Clusters.”

B*-tree indexes

The
B*-tree index
is the default index used in Oracle. It gets its name from its resem-

blance to an inverted tree, as shown in Figure 4-1.

The B*-tree index is composed of one or more levels of branch blocks and a single level of leaf blocks. The branch blocks contain information about the range of values contained in the next level of branch blocks. The number of branch levels between the root and leaf blocks is called the
depth
of the index. The leaf blocks contain the actual index values and the ROWID for the associated row.

92

|

Chapter 4: Oracle Data Structures

Miller

>Miller

Branch

blocks

Smith

Davis

Turner

Jones

Turner>

Adams

Deal

Jules

Moss

Sykes

Vera