CHAPTER 5

DDL and Schema Objects

Exam Objectives

In this chapter, you will learn to

• 061.10.1    Categorize the Main Database Objects

• 061.10.2    Explain the Table Structure

• 061.10.3    Describe the Data Types that Are Available for Columns

• 061.10.4    Create a Simple Table

• 061.10.5    Explain How Constraints Are Created at the Time of Table Creation

• 061.10.6    Describe How Schema Objects Work

This chapter contains several sections that are not directly tested by the exam but are considered prerequisite knowledge for every student. Two tools used extensively for exercises are SQL*Plus and SQL Developer, which are covered in this chapter. Oracle specialists use these every day in their work. The exercises and many of the examples are based on two demonstration sets of data, known as the HR and OE schemas, supplied by Oracle. There are instructions on how to launch the tools and create the demonstration schemas.

The primitive data types and the standard heap-organized table structure are the first topics in this chapter that are directly measured in the exam. The chapter moves on to defining the object types that are dependent on tables (indexes, constraints, and views) and then sequences and synonyms. All of these object types will be used throughout the remainder of this book.

Use the Client Tools

Numerous tools can be used to connect to an Oracle database. Two of the most basic are SQL*Plus and SQL Developer. These are provided by Oracle Corporation and are adequate for much of the work that a developer or a database administrator needs to do. The choice between them is a matter of personal preference, partly to do with the environment and partly to do with functionality. SQL Developer undoubtedly offers far more functionality than SQL*Plus, but it is more demanding in that it needs a graphical terminal, whereas SQL*Plus can be used on character mode devices.

The tool that has lasted longest is SQL*Plus, and even though Oracle Corporation is promoting SQL Developer strongly as a replacement, technical people working in the Oracle environment will be well advised to become familiar with SQL*Plus.

SQL*Plus

SQL*Plus is a client-server tool for connecting to a database and issuing ad hoc SQL commands. It can also be used for creating PL/SQL code and has facilities for formatting results. It is available on all platforms to which the database has been ported; the sections that follow give some detail on using SQL*Plus on Linux and Windows. There are no significant differences with using SQL*Plus on any other platform.

In terms of architecture, SQL*Plus is a user process written in C. It establishes a session against an instance and a database over the Oracle Net protocol. The platforms for the client and the server can be different. For example, there is no reason not to use SQL*Plus on a Windows PC to connect to a database running on a Unix server (or the other way round), provided that Oracle Net has been configured to make the connection.

SQL*Plus on Linux

The SQL*Plus executable file on a Linux installation is sqlplus. The location of this file will be installation specific, but will typically be something like this:

Your Linux account should be set up appropriately to run SQL*Plus, but you will need to set some environment variables. These are as follows:

ORACLE_HOME
PATH
LD_LIBRARY_PATH

The ORACLE_HOME variable points to the Oracle Home. An Oracle Home is the Oracle software installation, in other words, the set of files and directories containing the executable code and some of the configuration files. The PATH must include the bin directory contained within the Oracle Home. The LD_LIBRARY_PATH should include the lib directory, also contained within the Oracle Home, but in practice you may get away without setting this. Figure 5-1 shows a Linux terminal window and some tests to see whether the environment is correct.

Figure 5-1    Checking the Linux session setup

In Figure 5-1, first the echo command checks whether the three variables have been set up correctly. There is an ORACLE_HOME, and the bin and lib directories in it have been set as the last element of the PATH and the first element of the LD_LIBRARY_PATH variables, respectively. Then, the which command confirms that the SQL*Plus executable file really is available in the PATH. Finally, SQL*Plus is launched with a username, a password, and a connect identifier passed to it on the command line. If the tests do not return acceptable results and SQL*Plus fails to launch, you should discuss this with your system administrator and your database administrator. Some common errors with the logon itself are described in the section “Creating and Testing a Database Connection” later in this chapter.

The format of the logon string is the database username followed by a forward slash character as a delimiter, then a password followed by an @ symbol as a delimiter, and finally an Oracle Net connect identifier. In this example, the username is system, whose password is admin123, and the database is identified by coda.

Following the logon, the next lines of text display the version of SQL*Plus being used, which is 12.1.0.0.2; the version of the database to which the connection has been made (which happens to be the same as the version of the SQL*Plus tool); and which options have been installed within the database. The last line is the prompt to the user, SQL>, at which point the user can enter any SQL*Plus or SQL command. If the logon does not succeed with whatever username (probably not system) you have been allocated, you should discuss this with your database administrator.

SQL*Plus on Windows

Historically, there were always two versions of SQL*Plus for Microsoft Windows: the character version and the graphical version. The character version was (and still is) the executable file sqlplus.exe, and the graphical version was sqlplusw.exe. With the current release and 11g, the graphical version no longer exists, but many developers will prefer to use it, and the versions shipped with earlier releases are perfectly good tools for working with a 12c database. There are no problems with mixing versions; a 12c SQL*Plus client can connect to a 10g database, and a 10g SQL*Plus client can connect to a 12c database. Following a default installation of either an Oracle database or just an Oracle client on Windows, SQL*Plus will be available as a shortcut on the Windows Start menu. The location of the executable file launched by the shortcut will typically be something like the following:

However, the exact path will be installation specific. Figure 5-2 shows a logon to a database with SQL*Plus, launched from the shortcut. The first line of text shows the version of SQL*Plus, which is the 12.1.0.0.2 release, and the time the program was launched. The third line of text is a logon prompt.

Figure 5-2    A database logon with SQL*Plus for Windows

This is followed by the logon string entered manually, which is as follows:

A change some people like to make to the shortcut that launches SQL*Plus is to prevent it from immediately presenting a logon prompt. To do this, add the nolog switch to the end of the command.

There is no reason not to launch SQL*Plus from an operating system prompt rather than from the Start menu shortcut; simply open a command window and run it. The program will immediately prompt for a logon, unless you invoke it with the nolog switch described earlier.

Creating and Testing a Database Connection

SQL*Plus does not have any way of storing database connection details. Each time a user wants to connect to a database, the user must tell SQL*Plus who they are and where the database is. There are variations depending on site-specific security facilities, but the most common means of identifying yourself to the database is by presenting a username and a case-sensitive password. There are two typically used forms of connect identifier for identifying the database: either by providing an alias that is resolved into the full connect details or by entering the full details.

From an operating system prompt, these commands will launch SQL*Plus and connect as database user SCOTT whose password is tiger using each technique:

The first example uses an alias, orcl, to identify the database. This must be resolved into the full connect details. The usual techniques for this name resolution are to use a locally stored text file called the tnsnames.ora file (typically contained within the network/admin subdirectory of the ORACLE_HOME) or to contact an LDAP directory such as Microsoft’s Active Directory or Oracle’s Oracle Internet Directory (OID).

The second example provides all the connect details inline. The connect details needed are the hostname of the computer on which the database instance is running, the Transmission Control Protocol (TCP) port on which the Oracle Net database listener can be contacted, and the database service to which the user wants the database listener to connect him. The first technique, where the user needs to enter only an alias, requires the database administrator to configure a name resolution mechanism; the second technique can work only if the user knows the connect details.

There are a number of circumstances that will cause a SQL*Plus connection attempt to fail. Figure 5-3 illustrates some of the more common problems.

Figure 5-3    Some common logon problems

First, the user launches SQL*Plus from a Windows operating system prompt, using the NOLOG switch to prevent the immediate logon prompt. No problem so far.

Second, from the SQL> prompt, the user issues a connection request, which fails with a well-known error:

This error is because the connect identifier given, wrongalias, cannot be resolved into database connection details by the Transparent Network Substrate (TNS) layer of Oracle Net. The name resolution method to be used and its configuration are matters for the database administrator. In this case, the error is obvious: The user entered the wrong connect identifier.

The second connect attempt gives the correct identifier, orcl. This fails with the following:

This indicates that the connect identifier has resolved correctly into the address of a database listener but that the listener is not actually running. Note that another possibility would be that the address resolution is faulty and is sending SQL*Plus to the wrong address. Following this error, the user should contact the database administrator and ask him or her to start the listener. Then try again.

The third connect request fails with the following:

This error is generated by the database listener. SQL*Plus has found the listener with no problems, but the listener cannot make the onward connection to the database service. The most likely reason for this is that the database instance has not been started, so the user should ask the database administrator to start it and then try again.

The fourth connect request fails with the following:

To receive this message, the user must have contacted the database. The user has gotten through all the possible network problems, the database instance is running, and the database itself has been opened by the instance. The user just has the password or username wrong. Note that the message does not state whether it is the password or the username that is wrong; if it were to do so, it would be giving out information to the effect that the other one was right.

Finally, the fifth connect attempt succeeds.

SQL Developer

SQL Developer is a tool for connecting to an Oracle database (or, in fact, some non-Oracle databases too) and issuing ad hoc SQL commands. It can also manage PL/SQL objects. Unlike SQL*Plus, it is a graphical tool with wizards for commonly needed actions. SQL Developer is written in Java and requires a Java Runtime Environment (JRE) to run.

Being written in Java, SQL Developer is available on all platforms that support the appropriate version of the JRE. There are no significant differences between platforms.

Installing and Launching SQL Developer

SQL Developer is not installed with the Oracle Universal Installer, which is used to install all other Oracle products. It does not exist in an Oracle Home, but is a completely self-contained product. The latest version can be downloaded from Oracle Corporation’s web site.

To install SQL Developer, unzip the ZIP file. That’s all. It does require a JDK, the Java Runtime Environment, to be available; this comes from Oracle. But if an appropriate JDK is not already available on the machine being used, there are downloadable versions of SQL Developer for Windows that include it. For platforms other than Windows, the JDK must be preinstalled. Download it from Oracle’s web site and install according to the platform-specific directions. To check that the JDK is available with the correct version, from an operating system prompt, run the following command:

This should return something like the following:

If it does not, using the which java command may help identify the problem; the search path could be locating an incorrect version.

Once SQL Developer has been unzipped, change your current directory to the directory in which SQL Developer was unzipped and launch it. On Windows, the executable file is sqldeveloper.exe. On Linux, it is the sqldeveloper.sh shell script. Remember to check that the DISPLAY environment variable has been set to a suitable value (such as 127.0.0.1:0.0, if SQL Developer is being run on the system console) before running the shell script.

Any problems with installing the JRE and launching SQL Developer should be referred to your system administrator.

The SQL Developer User Interface

Figure 5-4 shows the SQL Developer user interface after connecting to a database.

Figure 5-4    The SQL Developer user interface

The general layout of the SQL Developer window is a left pane for navigation around objects and a right pane to display and enter information.

In the figure, the left pane shows that a connection has been made to a database. The connection is called orcl_sys. This name is just a label chosen when the connection was defined, but most developers will use some sort of naming convention—in this case, the name chosen is the database identifier, which is orcl, and the name of the user the connection was made as, which was sys. The branches beneath list all the possible object types that can be managed. Expanding the branches would list the objects themselves. The right pane has an upper part prompting the user to enter a SQL statement and a lower part that will display the result of the statement. SQL Developer can be a useful tool because it is highly customizable. Experiment with it, read the Help, and set up the user interface the way that works best for you.

Creating a Database Connection

Database connections can be created and saved for reuse. The window where connections can be defined can be reached by choosing the + symbol visible on the Connections tab shown previously in Figure 5-4.

The name for the connection is arbitrary. The username and password must both be supplied, but only the username will be saved unless the Save Password check box is selected. Saving a password means that future connections can be made without any password prompt. This is convenient but highly dangerous if there is any possibility that the computer you are working on is not secure. In effect, you are delegating the authentication to your local operating system; if you can log on to that, you can log on to the database.

Assuming that you are using SQL Developer to connect to an Oracle database rather than to a third-party database, select the Oracle tab.

The Role drop-down box gives you the option to connect as sysdba. A sysdba connection is required before certain particularly serious operations (such as database startup and shutdown) can be carried out. It will never be needed for the exercises covered in this book.

The Connection Type radio buttons let you choose from five options:

•  Basic    This prompts for the machine name of the database server, the port on which the database listener will accept connection requests, and the instance (the session identifier [SID]) or the service to which the connection will be made.

•  TNS    If a name resolution method has been configured, then an alias for the database can be selected (from the local tnsnames.ora file) or entered, rather than the full details needed by the Basic option.

•  LDAP    Database service definitions and their connection details that are stored in a Lightweight Directory Access Protocol (LDAP) directory service may be queried by specifying the LDAP server details.

•  Advanced    This allows entry of a full Java Database Connectivity (JDBC) connect string. This is completely Oracle independent and could be used to connect to any database that conforms to the JDBC standard.

•  Local/Bequeath    If the database is running on the same machine as your SQL Developer client, this option allows you to connect directly to a server process, bypassing the network listener.

Selecting Basic requires the user to know how to connect to the database; selecting TNS requires some configuration to have been done on the client machine by the database administrator so that the alias can be resolved into the full connection details.

Categorize the Main Database Objects

Various object types can exist within a database, many more with the current release than with earlier versions. All objects have names, and all objects are owned by someone. The “someone” is a database user, such as HR. The objects the user owns are their schema. An object’s name must conform to certain rules.

Object Types

This query lists the count by object type for the objects that happen to exist in this particular database:

This query addresses the view DBA_OBJECTS, which has one row for every object in the database. The numbers are low because the database is a small one used only for teaching. A database used for a business application generally has many more objects. You may not be able to see the view DBA_OBJECTS because it depends on which permissions have been granted to your account. Alternative views are USER_OBJECTS, which will show all the objects owned by you, and ALL_OBJECTS, which will show all the objects to which you have been granted access (including your own). All users have access to these views. In a 12c database, an additional layer of views prefixed with CDB_ has been added to facilitate the new multitenant database feature that logically separates a traditional database into one container and optionally multiple pluggable databases.

The objects of greatest interest to a SQL programmer are those that contain, or give access to, data. These include tables, views, synonyms, indexes, and sequences.

Tables basically store data in rows segmented by columns. A view is a stored SELECT statement that can be referenced as though it were a table. It is nothing more than a named query. Instead of running the statement, the user issues a SELECT statement against the view. In effect, the user is selecting from the result of another selection. A synonym is an alias for a table (or a view). Users can execute SQL statements against the synonym, and the database will map them into statements against the object to which the synonym points. Indexes are a means of improving access times to rows in tables. If a query requires only one row, then rather than scanning the entire table to find the row, an index can provide a pointer to the row’s exact location. Of course, the index itself must be searched, but this is often faster than scanning the table. A sequence is a construct that generates numbers. Sequences issue numbers in order on demand.

The remaining object types are less commonly relevant to a SQL programmer. Their use falls more within the realm of PL/SQL programmers and database administrators.

Users and Schemas

Many people use the terms user and schema interchangeably. In the Oracle environment, you can get away with this (though not necessarily with other database management systems). A user is a person who can connect to the database. The user will have a username and a password. A schema is a container for the objects owned by a user. When a user is created, their schema is created too. A schema is the objects owned by a user; initially, it will be empty.

Some schemas will always be empty; users will never create any objects because they do not need to and (if set up correctly) will not have the necessary privileges anyway. Users such as this will have been granted permissions, either through direct privileges or through roles, to use code and access data in other schemas owned by other users. Other users may be the reverse of this; they will own many objects but will never actually log on to the database. They might not even have been granted the CREATE SESSION privilege, so the account is effectively disabled (or indeed it can be locked); these schemas are used as repositories for code and data accessed by others.

Schema objects are objects with an owner. The unique identifier for an object of a particular type is not its name; it is its name prefixed with the name of the schema to which it belongs. Thus, the table HR.REGIONS is a table called REGIONS, which is owned by user HR. There could be another table named SYSTEM.REGIONS, which would be a completely different table (perhaps different in both structure and contents) owned by user SYSTEM and residing in their schema.

A number of users (and their associated schemas) are created automatically at database creation time. Principal among these are SYS and SYSTEM. User SYS owns the data dictionary, which is a set of tables (in the SYS schema) defining the database and its contents. SYS also owns several hundred PL/SQL packages, which consist of code that is provided for the use of database administrators and developers. Objects in the SYS schema should never be modified with data manipulation language (DML) commands. If you were to execute DML against the data dictionary tables, you would run the risk of corrupting the data dictionary, with disastrous results. You update the data dictionary by running DDL commands (such as CREATE TABLE), which provide a layer of abstraction between you and the data dictionary. The SYSTEM schema stores various additional objects used for administration and monitoring.

Depending on the options selected during database creation, there may be more users created. These users store code and data required by various database options. For example, the user MDSYS stores the objects used by Spatial, an option that extends the capabilities of an Oracle database to manage geographical information.

Naming Schema Objects

A schema object is owned by a user and must conform to certain rules:

•  The name may be between 1 and 30 characters long (with the exception of database link names that may be up to 128 characters long).

•  Reserved words (such as SELECT) cannot be used as object names.

•  All names must begin with a letter of the alphabet.

•  Object names can only include letters, numbers, the underscore (_), the dollar sign ($), or the hash symbol (#).

•  Lowercase letters will be automatically converted to uppercase.

By enclosing the name within double quotes, all these rules (with the exception of the length) can be broken, but to get to the object subsequently, it must always be specified with double quotes, as in the examples in Figure 5-5. Note that the same restrictions also apply to column names.

Figure 5-5    Using double quotes to use nonstandard names

Although tools such as SQL*Plus and SQL Developer will automatically convert lowercase letters to uppercase unless the name is enclosed within double quotes, remember that object names are always case sensitive. In this example, the two tables are completely different:

Object Namespaces

Generally speaking, the unique identifier for an object is its name, which is dot prefixed with the schema name. However, for a full understanding of object naming, it is necessary to introduce the concept of a namespace. A namespace defines a group of object types, within which all names must be uniquely identified by schema and name. Objects in different namespaces can share the same name.

These object types all share the same namespace:

•  Tables

•  Views

•  Sequences

•  Private synonyms

•  Stand-alone procedures

•  Stand-alone stored functions

•  Packages

•  Materialized views

•  User-defined types and operators

Thus, it is impossible to create a view with the same name as a table if they are in the same schema. Once created, SQL statements can address a view as though it were a table. The fact that tables, views, and private synonyms share the same namespace means that you can set up several layers of abstraction between what the users see and the actual tables, which can be invaluable for both security and for simplifying application development.

These object types each have their own namespace:

•  Constraints

•  Clusters

•  Database triggers

•  Private database links

•  Dimensions

Thus, it is possible (though perhaps not a very good idea) for an index to have the same name as a table, even within the same schema.

Exercise 5-1: Determine What Objects Are Accessible to Your Session    In this exercise, query various data dictionary views as user HR to determine what objects belong to the HR schema and what objects in other schemas are accessible to HR. Please follow the instructions outlined in Chapter 7 to create the HR schema if it is absent from your database.

1.  Connect to the database with SQL*Plus or SQL Developer as user HR.

2.  Determine how many objects of each type are in the HR schema.

The USER_OBJECTS view lists all objects owned by the schema to which the current session is connected, in this case HR.

3.  Determine how many objects in total HR has permissions on.

The ALL_OBJECTS view lists all objects to which the user has some sort of access.

4.  Determine who owns the objects HR can see.

List the Data Types that Are Available for Columns

When creating tables, each column must be assigned a data type, which determines the nature of the values that can be inserted into the column. These data types are also used to specify the nature of the arguments for PL/SQL procedures and functions. When selecting a data type, you must consider the data you need to store and the operations you will want to perform upon it. Space is also a consideration. Some data types are fixed length, taking up the same number of bytes no matter what data is actually in it; others are variable. If a column is not populated, then Oracle will not give it any space at all. If you later update the row to populate the column, then the row will get bigger, no matter whether the data type is fixed length or variable. In a 12c database, a new system parameter, MAX_STRING_SIZE, allows string data types to be much larger than in previous versions when it is changed from its default value of STANDARD to EXTENDED.

The following are the data types for alphanumeric data:

•  VARCHAR2    Variable-length character data, from 1 byte to 4,000 bytes if MAX_STRING_SIZE=STANDARD and up to 32,767 bytes if MAX_STRING_SIZE=EXTENDED. The data is stored in the database character set.

•  NVARCHAR2    Like VARCHAR2, but the data is stored in the alternative national language character set, one of the permitted Unicode character sets.

•  CHAR    Fixed-length character data, from 1 byte to 2,000 bytes, in the database character set. If the data is not the length of the column, then it will be padded with spaces.

The following is the data type for binary data:

•  RAW    Variable-length binary data, from 1 byte to 4,000 bytes if MAX_STRING_SIZE=STANDARD and up to 32,767 bytes if MAX_STRING_SIZE=EXTENDED. Unlike the CHAR and VARCHAR2 data types, RAW data is not converted by Oracle Net from the database’s character set to the user process’s character set on SELECT or the other way on INSERT.

The following are the data types for numeric data, all variable length:

•  NUMBER    Numeric data, for which you can specify precision and scale. The precision can range from 1 to 38, and the scale can range from −84 to 127.

•  FLOAT    An ANSI data type, floating-point number with a precision of 126 binary (or 38 decimal). Oracle also provides BINARY_FLOAT and BINARY_DOUBLE as alternatives.

•  INTEGER    Equivalent to NUMBER, with scale zero.

The following are the data types for date and time data, all fixed length:

•  DATE    This is either length zero, if the column is empty, or 7 bytes. All DATE data includes century, year, month, day, hour, minute, and second. The valid range is from January 1, 4712 BC, to December 31, 9999 AD.

•  TIMESTAMP    This is length zero if the column is empty, or up to 11 bytes, depending on the precision specified. This is similar to DATE, but with a precision of up to nine decimal places for the seconds and six places by default.

•  TIMESTAMP WITH TIMEZONE    This is like TIMESTAMP, but the data is stored with a record kept of the time zone to which it refers. The length may be up to 13 bytes, depending on precision. This data type lets Oracle determine the difference between two times by normalizing them to Coordinated Universal Time (UTC), even if the times are for different time zones.

•  TIMESTAMP WITH LOCAL TIMEZONE    This is like TIMESTAMP, but the data is normalized to the database time zone on saving. When retrieved, it is normalized to the time zone of the user process selecting it.

•  INTERVAL YEAR TO MONTH    This is used for recording a period in years and months between two DATEs or TIMESTAMPs.

•  INTERVAL DAY TO SECOND    This is used for recording a period in days and seconds between two DATEs or TIMESTAMPs.

The following are the large object data types:

•  CLOB    Character data stored in the database character set. The size is effectively unlimited: 4GB minus 1 multiplied by the database block size.

•  NCLOB    Like CLOB, but data is stored in the alternative national language character set, one of the permitted Unicode character sets.

•  BLOB    Like CLOB, but binary data that will not undergo character set conversion by Oracle Net.

•  BFILE    A locator pointing to a file stored on the operating system of the database server. The size of the files is limited to 2 to the power of 64 minus 1, though your operating system may have other ideas.

•  LONG    Character data in the database character set, up to 2GB minus 1. All the functionality of LONG (and more) is provided by CLOB; LONGs should not be used in a modern database, and if your database has any columns of this type, they should be converted to CLOB. There can be only one LONG column in a table.

•  LONG RAW    Like LONG, but binary data that will not be converted by Oracle Net. Any LONG RAW columns should be converted to BLOBs.

The following is the ROWID data type:

•  ROWID    This is a value coded in base 64 that is the pointer to the location of a row in a table. It is encrypted. Within it is the exact physical address. ROWID is an Oracle proprietary data type that is not visible unless specifically selected.

The VARCHAR2 data type must be qualified with a number indicating the maximum length of the column. If a value is inserted into the column that is less than this, it is not a problem. The value will take up only as much space as it needs. If the value is longer than this maximum, the INSERT will fail with an error. If the value is updated to a longer or shorter value, the length of the column (and therefore the row itself) will change accordingly. If it is not entered at all or is updated to NULL, then it will take up no space at all.

The NUMBER data type may optionally be qualified with a precision and a scale. The precision sets the maximum number of significant decimal digits, where the most significant digit is the left-most nonzero digit and the least significant digit is the right-most known digit in the number. The scale is the number of digits from the decimal point to the least significant digit. A positive scale is the number of significant digits to the right of the decimal point to (and including) the least significant digit. A negative scale is the number of significant digits to the left of the decimal point to (but not including) the least significant digit.

The DATE data type always includes century, year, month, day, hour, minute, and second—even if all these elements are not specified at insert time. Year, month, and day must be specified; if the hours, minutes, and seconds are omitted, they will default to midnight. Using the TRUNC function on a date also has the effect of setting the hours, minutes, and seconds to midnight.

Exercise 5-2: Investigate the Data Types in the HR Schema    In this exercise, find out what data types are used in the tables in the HR schema, using two techniques.

1.  Connect to the database as user HR with SQL*Plus or SQL Developer.

2.  Use the DESCRIBE command to show the data types in some tables.

3.  Use a query against a data dictionary view to show what columns make up the EMPLOYEES table, like the DESCRIBE command would.

The view USER_TAB_COLUMNS shows the detail of every column in every table in the current user’s schema.

Create a Simple Table

Tables can be stored in the database in several ways. The simplest is the heap table. A heap consists of variable-length rows in random order. There may be some correlation between the order in which rows are entered and the order in which they are stored, but this is a matter of luck. More advanced table structures, such as the following, may impose ordering and grouping on the rows or force a random distribution:

•  Index organized tables    These store rows in the order of an index key.

•  Index clusters    These can denormalize tables in parent-child relationships so that related rows from different tables are stored together.

•  Hash clusters    These force a random distribution of rows, which will break down any ordering based on the entry sequence.

•  Partitioned tables    These store rows in separate physical structures, the partitions, and allocate rows according to the value of a column.

Using the more advanced table structures has no effect whatsoever on SQL. Every SQL statement executed against tables defined with these options will return the same results as though the tables were standard heap tables, so using these features will not affect code. But while their use is transparent to programmers, they do give enormous benefits in performance.

Creating Tables with Column Specifications

To create a standard heap table, use this syntax:

At a minimum, specify the table name (it will be created in your own schema if you don’t specify someone else’s) and at least one column with a data type. There are few developers who ever specify ORGANIZATION HEAP because this is the default and is industry-standard SQL. The DEFAULT keyword in a column definition lets you provide an expression that will generate a value for the column when a row is inserted if a value is not provided by the INSERT statement.

Consider this statement:

This will create a table called EMP in the SCOTT schema. Either user SCOTT himself has to issue the statement (in which case nominating the schema would not actually be necessary), or another user could issue it if he has been granted permission to create tables in another user’s schema. Taking the columns one by one, here is a breakdown:

•  EMPNO can be four digits long, with no decimal places. If any decimals are included in an INSERT statement, they will be rounded (up or down) to the nearest integer.

•  ENAME can store any characters at all, up to ten of them.

•  HIREDATE will accept any date, optionally with the time, but if a value is not provided, today’s date will be entered as midnight.

•  SAL, intended for the employee’s salary, will accept numeric values with up to seven digits. A maximum of five digits may be to the left of the decimal point. If any digits over two are to the right of the decimal point, they will be rounded.

•  COMM (for commission percentage) has a default value of 0.03, which will be entered if the INSERT statement does not include a value for this column.

Following creation of the table, these statements insert a row and select the result:

Note that values for the columns not mentioned in the INSERT statement have been generated by the DEFAULT clauses. Had those clauses not been defined in the table definition, the columns would have been NULL. Also note the rounding of the value provided for SAL.

Creating Tables from Subqueries

Rather than creating a table from nothing and then inserting rows into it (as in the previous section), tables can be created from other tables by using a subquery. This technique lets you create the table definition and populate the table with rows with just one statement. Any query at all can be used as the source of both the table structure and the rows. The syntax is as follows:

CREATE TABLE [schema.]table AS subquery;

All queries return a two-dimensional set of rows; this result is stored as the new table. Here is a simple example of creating a table with a subquery:

This statement will create a table EMPLOYEES_COPY, which is an exact copy of the EMPLOYEES table, identical in both definition and the rows it contains. Any not null and check constraints on the columns will also be applied to the new table, but any primary key, unique, or foreign key constraints will not be. (Constraints are discussed later.) This is because these three types of constraints require indexes that might not be available or desired.

The following is a more complex example:

The rows in the new table will be the result of joining the two source tables, with two of the selected columns having their names changed. The new SERVICE column will be populated with the result of the arithmetic that computes the number of days since the employee was hired. The rows will be inserted in the order specified. This ordering will not be maintained by subsequent DML, but assuming the standard HR schema data, the new table will look like this:

The subquery can, of course, include a WHERE clause to restrict the rows inserted into the new table. To create a table with no rows, use a WHERE clause that will exclude all rows:

The WHERE clause 1=2 can never return TRUE, so the table structure will be created, ready for use, but no rows will be inserted at creation time.

Altering Table Definitions After Creation

There are many alterations that can be made to a table after creation. Those that affect the physical storage fall into the domain of the database administrator, but many changes are purely logical and will be carried out by the SQL developers. The following are examples (for the most part self-explanatory):

•  Adding columns:

•  Modifying columns:

•  Dropping columns:

•  Marking columns as unused:

•  Renaming columns:

•  Marking the table as read-only:

All of these changes are data definition language (DDL) commands with the built-in COMMIT. They are therefore nonreversible and will fail if there is an active transaction against the table. They are also virtually instantaneous, with the exception of dropping a column. Dropping a column can be a time-consuming exercise because as each column is dropped, every row must be restructured to remove the column’s data. The SET UNUSED command, which makes columns nonexistent as far as SQL is concerned, is often a better alternative, followed, when convenient, by the following lines, which will drop all the unused columns in one pass through the table:

Marking a table as read-only will cause errors for any attempted DML commands. But the table can still be dropped. This can be disconcerting, but is perfectly logical when you think it through. A DROP command doesn’t actually affect the table; it affects the tables in the data dictionary that define the table, and these are not read-only.

Dropping and Truncating Tables

The TRUNCATE TABLE command has the effect of removing every row from a table, while leaving the table definition intact. DROP TABLE is more drastic in that the table definition is removed as well. The syntax is as follows:

DROP TABLE [schema.]tablename ;

If schema is not specified, then the table called tablename in your currently logged-on schema will be dropped.

As with a TRUNCATE, SQL will not produce a warning before the table is dropped, and furthermore, as with any DDL command, it includes a COMMIT. But there are some restrictions; if any session (even your own) has a transaction in progress that includes a row in the table, then the DROP will fail, and it is also impossible to drop a table that is referred to in a foreign key constraint defined for another table. This table (or the constraint) must be dropped first.

Exercise 5-3: Create Tables    In this exercise, use SQL Developer to create a heap table, insert some rows with a subquery, and modify the table. Do some more modifications with SQL*Plus and then drop the table.

1.  Connect to the database as user HR with SQL Developer.

2.  Right-click the Tables branch of the navigation tree and click New Table.

3.  Name the new table EMPS and use the Add Column button to set it up, as in the following illustration:

4.  Click the DDL tab to see the statement has been constructed. It should look like this:

Return to the Table tab (as in the preceding illustration) and click OK to create the table.

5.  Run this statement:

and commit the insert:

6.  Right-click the EMPS table in the SQL Developer navigator and click Column and Add.

7.  Define a new column HIRED, of type DATE, as in the following illustration; click Apply to create the column.

8.  Connect to the database as HR with SQL*Plus.

9.  Define a default for the HIRED column in the EMPS table:

10.  Insert a row without specifying a value for HIRED and check that the new row does have a HIRED date but that the other rows do not:

11.  Tidy up by dropping the new table:

Create and Use Temporary Tables

A temporary table has a definition that is visible to all sessions, but the rows within it are private to the session that inserted them. Programmers can use them as a private storage area for manipulating large amounts of data. The syntax is as follows:

The column definition is the same as a regular table and can indeed be supplied from a subquery. The optional clause at the end determines the lifetime of any rows inserted. The default is to remove the rows the moment the transaction that inserted them completes, but this behavior can be changed to preserve them until the session that inserted them ends. Whichever option is chosen, the data will be private to each session; different users can insert their own rows into their own copy of the table, and they will never see each other’s rows.

In many ways, a temporary table is similar to a permanent table. You can execute any DML or SELECT command against it. It can have indexes, constraints, and triggers defined. It can be referenced in views and synonyms or joined to other tables. The difference is that the data is transient and private to the session and that all SQL commands against it will be far faster than commands against permanent tables.

The first reason for the speed is that temporary tables are not segments in permanent tablespaces. The table gets written out to a temporary segment in the user’s temporary tablespace. I/O on temporary tablespaces is much faster than I/O on permanent tablespaces because it does not go via the database buffer cache; it is all performed directly on disk by the session’s server process.

A second reason for speed is that DML against temporary tables does not generate redo. Since the data persists only for the duration of a session (perhaps only for the duration of a transaction), there is no purpose in generating a redo. This gives the dual benefit of fast DML for the session working on the table and taking the strain off the redo generation system, which can be a bad point of contention on busy multiuser databases.

Figure 5-6 shows the creation and use of a temporary table with SQL*Plus. The Database Control Table Creation Wizard can also create temporary tables.

Figure 5-6    Creation and use of a temporary table

Exercise 5-4: Create and Use Temporary Tables    In this exercise, create a temporary table to be used for reporting on current employees. Demonstrate, by using two SQL*Plus sessions, that the data is private to each session.

1.  Connect to your database with SQL*Plus as user HR.

2.  Create a temporary table as follows:

3.  Insert some rows and commit them.

4.  Start a second SQL*Plus session as HR.

5.  In the second session, confirm that the first insert is not visible, even though it was committed in the first session, and insert some different rows.

6.  In the first session, truncate the table.

7.  In the second session, confirm that there are still rows in that session’s copy of the table.

8.  In the second session, demonstrate that terminating the session does clear the rows. This will require disconnecting and connecting again.

9.  Tidy up the environment by dropping the tables in both sessions.

Indexes

Indexes have two functions: to enforce primary key and unique constraints and to improve performance. An application’s indexing strategy is critical for performance. There is no clear demarcation of whose domain index management lies within. When the business analysts specify business rules that will be implemented as constraints, they are in effect specifying indexes. The database administrators will be monitoring the execution of code running in the database and will make recommendations for indexes. The developer, who should have the best idea of what is going on in the code and the nature of the data, will also be involved in developing the indexing strategy.

Why Indexes Are Needed

Indexes are part of the constraint mechanism. If a column (or a group of columns) is marked as a table’s primary key, then every time a row is inserted into the table, Oracle must check that a row with the same value in the primary key does not already exist. If the table has no index on the columns, the only way to do this would be to scan right through the table, checking every row. Although this might be acceptable for a table of only a few rows, for a table with thousands or millions (or billions) of rows, this is not feasible. An index gives (near) immediate access to key values, so the check for existence can be made virtually instantaneously. When a primary key constraint is defined, Oracle will automatically create an index on the primary key columns, if one does not exist already.

A unique constraint also requires an index. It differs from a primary key constraint in that the columns of the unique constraint can be left null. This does not affect the creation and use of the index. Foreign key constraints are enforced by indexes, but the index must exist on the parent table, not necessarily on the table for which the constraint is defined. A foreign key constraint relates a column in the child table to the primary key or to a unique key in the parent table. When a row is inserted in the child table, Oracle will do a lookup on the index on the parent table to confirm that there is a matching row before permitting the insert. However, you should always create indexes on the foreign key columns within the child table for performance reasons; a DELETE on the parent table will be much faster if Oracle can use an index to determine whether there are any rows in the child table referencing the row that is being deleted.

Indexes are critical for performance. When executing any SQL statement that includes a WHERE clause, Oracle has to identify which rows of the table are to be selected or modified. If there is no index on the columns referenced in the WHERE clause, the only way to do this is with a full table scan. A full table scan reads every row of the table in order to find the relevant rows. If the table has many rows, this can take a long time. If there is an index on the relevant columns, Oracle can search the index instead. An index is a sorted list of key values, structured in a manner that makes the search very efficient. With each key value is a pointer to the row in the table. Locating relevant rows via an index lookup is far faster than using a full table scan, if the table is over a certain size and the proportion of the rows to be retrieved is below a certain value. For small tables or for a WHERE clause that will retrieve a large fraction of the table’s rows, a full table scan will be quicker. You can (usually) trust Oracle to make the correct decision regarding whether to use an index based on statistical information the database gathers about the tables and the rows within them.

A second circumstance where indexes can be used is for sorting. A SELECT statement that includes the ORDER BY, GROUP BY, or UNION keyword (and a few others) must sort the rows into order—unless there is an index, which can return the rows in the correct order without needing to sort them first.

A third circumstance when indexes can improve performance is when tables are joined, but again Oracle has a choice. Depending on the size of the tables and the memory resources available, it may be quicker to scan tables into memory and join them there, rather than use indexes. The nested loop join technique passes through one table using an index on the other table to locate the matching rows; this is usually a disk-intensive operation. A hash join technique reads the entire table into memory, converts it into a hash table, and uses a hashing algorithm to locate matching rows; this is more memory and CPU intensive. A sort merge join sorts the tables on the join column and then merges them together; this is often a compromise among disk, memory, and CPU resources. If there are no indexes, then Oracle is severely limited in the join techniques available.

Types of Indexes

Oracle supports several types of indexes, each with several variations. The two index types of concern here are the B*Tree index, which is the default index type, and the bitmap index. As a general rule, indexes will improve performance for data retrieval but reduce performance for DML operations. This is because indexes must be maintained. Every time a row is inserted into a table, a new key must be inserted into every index on the table, which places an additional strain on the database. For this reason, on transaction processing systems it is customary to keep the number of indexes as low as possible (perhaps no more than those needed for the constraints) and on query-intensive systems such as a data warehouse to create as many as might be helpful.

B*Tree Indexes

A B*Tree index (the B stands for “balanced”) is a tree structure. The root node of the tree points to nodes at the second level, which can point to nodes at the third level, and so on. The necessary depth of the tree will be largely determined by the number of rows in the table and the length of the index key values.

The leaf nodes of the index tree store the rows’ keys, in order, each with a pointer that identifies the physical location of the row. So to retrieve a row with an index lookup, if the WHERE clause is using an equality predicate on the indexed column, Oracle navigates down the tree to the leaf node containing the desired key value and then uses the pointer to find the row location. If the WHERE clause is using a nonequality predicate (such as LIKE, BETWEEN, >, or <), then Oracle can navigate down the tree to find the first matching key value and then navigate across the leaf nodes of the index to find all the other matching values. As it does so, it will retrieve the rows from the table in order.

The pointer to the row is the rowid. The rowid is an Oracle-proprietary pseudocolumn, which every row in every table has. Encrypted within it is the physical address of the row. Because rowids are not part of the SQL standard, they are never visible to a normal SQL statement, but you can see them and use them if you want. This is demonstrated in Figure 5-7.

Figure 5-7    Displaying and using rowids

The rowid for each row is globally unique. Every row in every table in the entire database will have a different rowid. The rowid encryption provides the physical address of the row; Oracle can use this to calculate which operating system file, and where in the file, the row is and go straight to it.

B*Tree indexes are an efficient way of retrieving rows if the number of rows needed is low in proportion to the total number of rows in the table and if the table is large. Consider this statement:

This WHERE clause is sufficiently broad in that it will include every row in the table. It would be much slower to search the index to find the rowids and then use the rowids to find the rows than to scan the whole table. After all, it is the whole table that is needed. Another example is that if the table is small enough that one disk read can scan it in its entirety, there is no point in reading an index first.

It is often said that if the query is going to retrieve more than 2 to 4 percent of the rows, then a full table scan will be quicker. A special case is if the value specified in the WHERE clause is NULL. NULLs do not go into B*Tree indexes, so a query such as the following will always result in a full table scan:

There is little value in creating a B*Tree index on a column with few unique values because it will not be sufficiently selective; the proportion of the table that will be retrieved for each distinct key value will be too high. In general, B*Tree indexes should be used if the following items are true:

•  The cardinality (the number of distinct values) in the column is high.

•  The number of rows in the table is high.

•  The column is used in WHERE clauses or JOIN conditions.

Bitmap Indexes

In many business applications, the nature of the data and the queries is such that B*Tree indexes are not of much use. Consider a table of sales for a chain of supermarkets that stores one year of historical data, which can be analyzed in several dimensions. Figure 5-8 shows a simple entity-relationship diagram, with just four of the dimensions.

Figure 5-8    A fact table with four dimensions

The cardinality of each dimension could be quite low. Make these assumptions:

Assuming an even distribution of data, only two of the dimensions (PRODUCT and DATE) have a selectivity that is better than the commonly used criterion of 2 percent to 4 percent, which makes an index worthwhile. But if queries use range predicates (such as counting sales in a month or of a class of ten or more products), then not even these will qualify. This is a simple fact: B*Tree indexes are often useless in a data warehouse environment. A typical query might want to compare sales between two shops by walk-in customers of a certain class of product in a month. There could well be B*Tree indexes on the relevant columns, but Oracle would ignore them as being insufficiently selective. This is what bitmap indexes are designed for.

A bitmap index stores the rowids associated with each key value as a bitmap. The bitmaps for the CHANNEL index might look like this:

This indicates that the first two rows were sales to walk-in customers, the third sale was a delivery, the fourth sale was a walk-in, and so on.

The bitmaps for the SHOP index might be as follows:

This indicates that the first two sales were in the London shop, the third was in Oxford, the fourth in Reading, and so on. Now if this query is received:

Oracle can retrieve the two relevant bitmaps and add them together with a Boolean AND operation.

The result of the bitwise AND operation shows that only the seventh and sixteenth rows qualify for selection. This merging of bitmaps is fast and can be used to implement complex Boolean operations with many conditions on many columns using any combination of AND, OR, and NOT operators. A particular advantage that bitmap indexes have over B*Tree indexes is that they include NULLs. As far as the bitmap index is concerned, NULL is just another distinct value, which will have its own bitmap.

In general, bitmap indexes should be used if the following are true:

•  The cardinality (the number of distinct values) in the column is low.

•  The number of rows in the table is high.

•  The column is used in Boolean algebra operations.

Index Type Options

There are six commonly used options that can be applied when creating indexes:

•  Unique or nonunique

•  Reverse key

•  Compressed

•  Composite

•  Function based

•  Ascending or descending

All six variations apply to B*Tree indexes, but only the last three can be applied to bitmap indexes.

A unique index will not permit duplicate values. Nonunique is the default. The unique attribute of the index operates independently of a unique or primary key constraint; the presence of a unique index will not permit insertion of a duplicate value even if there is no such constraint defined. A unique or primary key constraint can use a nonunique index; it will just happen to have no duplicate values. This is in fact a requirement for a constraint that is deferrable because there may be a period (before transactions are committed) when duplicate values do exist. Constraints are discussed in the next section.

A reverse key index is built on a version of the key column with its bytes reversed. Rather than indexing John, it will index nhoJ. When a SELECT is done, Oracle will automatically reverse the value of the search string. This is a powerful technique for avoiding contention in multiuser systems. For instance, if many users are concurrently inserting rows with primary keys based on a sequentially increasing number, all their index inserts will concentrate on the high end of the index. By reversing the keys, the consecutive index key inserts will tend to be spread over the whole range of the index. Even though John and Jules are close together, nhoJ and seluJ will be quite widely separated.

A compressed index stores repeated key values only once. The default is not to compress, meaning that if a key value is not unique, it will be stored once for each occurrence, each having a single rowid pointer. A compressed index will store the key once, followed by a string of all the matching rowids.

A composite index is built on the concatenation of two or more columns. There are no restrictions on mixing data types. If a search string does not include all the columns, the index can still be used, but if it does not include the leftmost column, Oracle will have to use a skip-scanning method that is much less efficient than if the left-most column is included.

A function-based index is built on the result of a function applied to one or more columns, such as UPPER(last_name) or TO_CHAR(startdate, ‘ccyy-mm-dd’). A query will have to apply the same function to the search string, or Oracle may not be able to use the index.

By default, an index is ascending, meaning that the keys are sorted in order of lowest value to highest. A descending index reverses this. In fact, the difference is often not important. The entries in an index are stored as a doubly linked list, so it is possible to navigate up or down with equal celerity, but this will affect the order in which rows are returned if they are retrieved with an index full scan.

Creating and Using Indexes

Indexes are created implicitly when primary key and unique constraints are defined if an index on the relevant columns does not already exist. The basic syntax for creating an index explicitly is as follows:

The default type of index is a nonunique, noncompressed, non-reverse-key B*Tree index. It is not possible to create a unique bitmap index (and you wouldn’t want to if you could—think about the cardinality issue). Indexes are schema objects, and it is possible to create an index in one schema on a table in another, but most people would find this somewhat confusing. A composite index is an index on several columns. Composite indexes can be on columns of different data types, and the columns do not have to be adjacent in the table.

Consider this example of creating tables and indexes and then defining constraints:

The first two indexes created are flagged as UNIQUE, meaning that it will not be possible to insert duplicate values. This is not defined as a constraint at this point but is true nonetheless. The third index is not defined as UNIQUE and will therefore accept duplicate values; this is a composite index on two columns. The fourth index is defined as a bitmap index because the cardinality of the column is likely to be low in proportion to the number of rows in the table.

When the two primary key constraints are defined, Oracle will detect the preexisting indexes and use them to enforce the constraints. Note that the index on DEPT.DEPTNO has no purpose for performance because the table will in all likelihood be so small that the index will never be used to retrieve rows (a scan will be quicker), but it is still essential to have an index to enforce the primary key constraint.

Once created, indexes are used completely transparently and automatically. Before executing a SQL statement, the Oracle server will evaluate all the possible ways of executing it. Some of these ways may involve using whatever indexes are available; others may not. Oracle will make use of the information it gathers on the tables and the environment to make an intelligent decision about which (if any) indexes to use.

Modifying and Dropping Indexes

The ALTER INDEX command cannot be used to change any of the characteristics described in this chapter, including the type (B*Tree or bitmap) of the index, the columns, and whether it is unique or nonunique. The ALTER INDEX command lies in the database administration domain and is typically used to adjust the physical properties of the index, not the logical properties that are of interest to developers. If it is necessary to change any of these properties, the index must be dropped and re-created. Continuing the example in the preceding section, to change the index EMP_I2 to include the employees’ birthdays, you use this:

This composite index now includes columns with different data types. The columns happen to be listed in the same order that they are defined in the table, but this is by no means necessary.

When a table is dropped, all the indexes and constraints defined for the table are dropped as well. If an index was created implicitly by creating a constraint, then dropping the constraint will also drop the index. If the index had been created explicitly and the constraint created later, then if the constraint were dropped, the index would survive.

Exercise 5-5: Create Indexes    In this exercise, create, populate, and add some indexes to the CUSTOMERS table.

1.  Connect to the HR schema and create the CUSTOMERS, ORDERS, ORDER_ITEMS, and PRODUCTS tables as follows:

2.  Create a compound B*Tree index on the customer names and status.

3.  Create bitmap indexes on a low-cardinality column.

4.  Determine the name and some other characteristics of the indexes just created by running this query:

Constraints

Table constraints are a means by which the database can enforce business rules and guarantee that the data conforms to the entity-relationship model determined by the systems analysis that defines the application data structures. For example, the business analysts of your organization may have decided that every customer and every order must be uniquely identifiable by number, that no orders can be issued to a customer before that customer has been created, and that every order must have a valid date and a value greater than zero. These would be implemented by creating primary key constraints on the CUSTOMER_ID column of the CUSTOMERS table and the ORDER_ID column of the ORDERS table, a foreign key constraint on the ORDERS table referencing the CUSTOMERS table, a not-null constraint on the DATE column of the ORDERS table (the DATE data type will itself ensure that any dates are valid automatically—it will not accept invalid dates), and a check constraint on the ORDER_AMOUNT column on the ORDERS table.

If any DML executed against a table with constraints defined violates a constraint, then the whole statement will be rolled back automatically. Remember that a DML statement that affects many rows might partially succeed before it hits a constraint problem with a particular row. If the statement is part of a multistatement transaction, then the statements that have already succeeded will remain intact but uncommitted.

The Types of Constraints

The constraint types supported by Oracle Database are as follows:

•  UNIQUE

•  NOT NULL

•  PRIMARY KEY

•  FOREIGN KEY

•  CHECK

•  REF

Constraints have names. It is good practice to specify the names with a standard naming convention, but if they are not explicitly named, Oracle will generate names. A discussion of REF constraints that are typically used in object-relational interactions is beyond the scope of this guide.

Unique Constraints

A unique constraint nominates a column (or combination of columns) for which the value must be different for every row in the table. If the constraint is based on a single column, this is known as the key column. If the constraint is composed of more than one column (known as a composite key unique constraint), the columns do not have to be the same data type or be adjacent in the table definition.

An oddity of unique constraints is that it is possible to enter a NULL value into the key columns; it is indeed possible to have any number of rows with NULL values in their key columns. This is because NULL is not equal to anything, not even to another NULL. So, selecting rows on a key column will guarantee that only one row is returned—unless you search for NULL, in which case all the rows where the key columns are NULL will be returned.

Unique constraints are enforced by an index. When a unique constraint is defined, Oracle will look for an index on the key columns, and if one does not exist, it will be created. Then whenever a row is inserted, Oracle will search the index to see whether the values of the key columns are already present; if they are, it will reject the insert. The structure of these indexes (known as B*Tree indexes) does not include NULL values, which is why many rows with NULL are permitted; they simply do not exist in the index. While the first purpose of the index is to enforce the constraint, it has a secondary effect: improving performance if the key columns are used in the WHERE clauses of SQL statements. However, selecting WHERE key_column IS NULL cannot use the index (because it doesn’t include the NULLs) and will therefore always result in a scan of the entire table.

Not-Null Constraints

The not-null constraint forces values to be entered into the key column. Not-null constraints are defined per column and are sometimes called mandatory columns; if the business requirement is that a group of columns should all have values, you cannot define one not-null constraint for the whole group but must define a not-null constraint for each column.

Any attempt to insert a row without specifying values for the not-null-constrained columns results in an error. It is possible to bypass the need to specify a value by including a DEFAULT clause on the column when creating the table, as discussed in the earlier section “Creating Tables with Column Specifications.”

Primary Key Constraints

The primary key is the means of locating a single row in a table. The relational database paradigm includes a requirement that every table should have a primary key, namely, a column (or combination of columns) that can be used to distinguish every row. Oracle Database deviates from the paradigm (as do some other RDBMS implementations) by permitting tables without primary keys.

The implementation of a primary key constraint is in effect the union of a unique constraint and a not-null constraint. The key columns must have unique values, and they may not be null. As with unique constraints, an index must exist on the constrained columns. If one does not exist already, an index will be created when the constraint is defined. A table can have only one primary key. Try to create a second, and you will get an error. A table can, however, have any number of unique constraints and not-null columns, so if there are several columns that the business analysts have decided must be unique and populated, one of these can be designated the primary key and the others made unique and not null. An example could be a table of employees, where e-mail address, Social Security number, and employee number are all required and unique.

Foreign Key Constraints

A foreign key constraint is defined on the child table in a parent-child relationship. The constraint nominates a column (or columns) in the child table that corresponds to the primary key columns in the parent table. The columns do not need to have the same names, but they must be of the same data type. Foreign key constraints define the relational structure of the database, that is, the many-to-one relationships that connect the table, in their third normal form.

If the parent table has unique constraints as well as (or instead of) a primary key constraint, these columns can be used as the basis of foreign key constraints, even if they are nullable.

Just as a unique constraint permits null values in the constrained column, so does a foreign key constraint. You can insert rows into the child table with null foreign key columns—even if there is not a row in the parent table with a null value. This creates orphan rows and can cause dreadful confusion. As a general rule, all the columns in a unique constraint and all the columns in a foreign key constraint are best defined with not-null constraints as well; this will often be a business requirement.

Attempting to insert a row in the child table for which there is no matching row in the parent table will give an error. Similarly, deleting a row in the parent table will give an error if there are already rows referring to it in the child table. There are two techniques for changing this behavior. First, the constraint may be created as ON DELETE CASCADE. This means that if a row in the parent table is deleted, Oracle will search the child table for all the matching rows and delete them too. This will happen automatically. A less drastic technique is to create the constraint as ON DELETE SET NULL. In this case, if a row in the parent table is deleted, Oracle will search the child table for all the matching rows and set the foreign key columns to null. This means that the child rows will be orphaned but will still exist. If the columns in the child table also have a not-null constraint, then the deletion from the parent table will fail.

It is not possible to drop or truncate the parent table in a foreign key relationship, even if there are no rows in the child table. This still applies if the ON DELETE SET NULL or ON DELETE CASCADE clause was used.

A variation on the foreign key constraint is the self-referencing foreign key constraint. This defines a condition where the parent and child rows exist in the same table. An example is a table of employees that includes a column for the employee’s manager. The manager is an employee and must exist in the table. So if the primary key is the EMPLOYEE_ID column and the manager is identified by a column MANAGER_ID, then the foreign key constraint will state that the value of the MANAGER_ID column must refer back to a valid EMPLOYEE_ID. If an employee is his own manager, then the row would refer to itself.

Check Constraints

A check constraint can be used to enforce simple rules, such as that the value entered in a column must be within a range of values. The rule must be an expression that will evaluate to TRUE or FALSE. The rules can refer to absolute values entered as literals or to other columns in the same row, and they may make use of some functions. As many check constraints as you want can be applied to one column, but it is not possible to use a subquery to evaluate whether a value is permissible or to use functions such as SYSDATE.

Defining Constraints

Constraints can be defined when creating a table or added to the table later. When defining constraints at table creation time, the constraint can be defined inline with the column to which it refers or at the end of the table definition. There is more flexibility to using the latter technique. For example, it is impossible to define a foreign key constraint that refers to two columns or a check constraint that refers to any column other than that being constrained if the constraint is defined inline, but both these are possible if the constraint is defined at the end of the table.

For the constraints that require an index (the unique and primary key constraints), the index will be created with the table if the constraint is defined at table creation time.

Consider these two table creation statements (to which line numbers have been added):

Taking these statements line by line, here is the process:

1.  The first table created is DEPT, intended to have one row for each department.

2.  DEPTNO is numeric, two digits, no decimals. This is the table’s primary key. The constraint is named DEPT_DEPTNO_PK.

3.  A second constraint applied to DEPTNO is a check limiting it to numbers in the range 10 to 90. The constraint is named DEPT_DEPTNO_CK.

4.  The DNAME column is variable-length characters, with a constraint DEPT_DNAME_NN making it not nullable.

5.  The second table created is EMP, intended to have one row for every employee.

6.  EMPNO is numeric, up to four digits with no decimals. Constraint EMP_EMPNO_PK marks this as the table’s primary key.

7.  ENAME consists of variable-length characters, with a constraint EMP_ENAME_NN making it not nullable.

8.  MGR is the employee’s manager, who must himself be an employee. The column is defined in the same way as the table’s primary key column of EMPNO. The constraint EMP_MGR_FK defines this column as a self-referencing foreign key, so any value entered must refer to an already existing row in EMP (though it is not constrained to be not null, so it can be left blank).

9.  DOB, the employee’s birthday, is a date and not constrained.

10.  HIREDATE is the date the employee was hired and is not constrained. At least, not yet.

11.  DEPTNO is the department with which the employee is associated. The column is defined in the same way as the DEPT table’s primary key column of DEPTNO, and the constraint EMP_DEPTNO_FK enforces a foreign key relationship; it is not possible to assign an employee to a department that does not exist. This is nullable, however.

12.  The EMP_DEPTO_FK constraint is further defined as ON DELETE SET NULL, so if the parent row in DEPT is deleted, all matching child rows in EMPNO will have DEPTNO set to NULL.

13.  EMAIL is variable-length character data and must be unique if entered (though it can be left empty).

14.  This defines an additional table-level constraint EMP_HIREDATE_CK. The constraint checks for use of child labor by rejecting any rows where the date of hiring is not at least 16 years later than the birthday. This constraint could not be defined inline with HIREDATE because the syntax does not allow references to other columns at that point.

15.  An additional constraint EMP_EMAIL_CK is added to the EMAIL column, which makes two checks on the e-mail address. The INSTR functions search for the at (@) sign and dot (.) characters (which will always be present in a valid e-mail address); if it can’t find both of them, the check condition will return FALSE, and the row will be rejected.

The preceding examples show several possibilities for defining constraints at table creation time. Further possibilities not covered include the following:

•  Controlling the index creation for the unique and primary key constraints

•  Defining whether the constraint should be checked at insert time (which it is by default) or later, when the transaction is committed

•  Stating whether the constraint is in fact being enforced at all (which is the default) or is disabled

It is possible to create tables with no constraints and then to add them later with an ALTER TABLE command. The end result will be the same, but this technique does make the code less self-documenting because the complete table definition will then be spread over several statements rather than being in one.

Constraint State

At any time, every constraint is either enabled or disabled and validated or not validated. Any combination of these is syntactically possible:

•  ENABLE VALIDATE    It is not possible to enter rows that will violate the constraint, and all rows in the table conform to the constraint.

•  DISABLE NOVALIDATE    Any data (conforming or not) can be entered, and there may already be nonconforming data in the table.

•  ENABLE NOVALIDATE    There may already be nonconforming data in the table, but all data entered now must conform.

•  DISABLE VALIDATE    All data in the table conforms to the constraint, but new rows need not. The index is also dropped on the constraint.

The ideal situation (and the default when a constraint is defined) is ENABLE VALIDATE. This will guarantee that all the data is valid, and no invalid data can be entered. The other extreme, DISABLE NOVALIDATE, can be useful when uploading large amounts of data into a table. It may well be that the data being uploaded does not conform to the business rules, but rather than have a large upload fail because of a few bad rows, putting the constraint in this state will allow the upload to succeed. Immediately following the upload, transition the constraint into the ENABLE NOVALIDATE state. This will prevent the situation from deteriorating further while the data is checked for conformance before transitioning the constraint to the ideal state.

As an example, consider this script, which reads data from a source table of live data into a table of archive data. The assumption is that there is a NOT NULL constraint on a column of the target table that may not have been enforced on the source table.

Constraint Checking

Constraints can be checked as a statement is executed (an IMMEDIATE constraint) or when a transaction is committed (a DEFERRED constraint). By default, all constraints are IMMEDIATE and not deferrable. An alternative approach to the previous example would have been possible had the constraint been created as deferrable.

For the constraint to be deferrable, it must have been created with the appropriate syntax.

It is not possible to make a constraint deferrable later if it was not created that way. The constraint SA_NN1 will, by default, be enforced when a row is inserted (or updated), but the check can be postponed until the transaction commits. A common use for deferrable constraints is with foreign keys. If a process inserts or updates rows in both the parent and child tables, then if the foreign key constraint is not deferred, the process may fail if rows are not processed in the correct order.

Changing the status of a constraint between ENABLED/DISABLED and VALIDATE/NOVALIDATE is an operation that will affect all sessions. The status change is a data dictionary update. Switching a deferrable constraint between IMMEDIATE and DEFERRED is session specific, though the initial state will apply to all sessions.

Exercise 5-6: Manage Constraints    In this exercise, define and adjust some constraints on the table created in Exercise 5-5.

1.  In SQL Developer, navigate to the HR schema and click the CUSTOMERS table.

2.  Go to the Constraints tab to view the four NOT NULL constraints that were created with the table. Note that their names are not helpful—this will be fixed in step 8.

3.  Click the Actions button and choose Constraints: Add Primary Key.

4.  In the Add Primary Key window, name the constraint PK_CUSTOMER_ID, choose the CUSTOMER_ID column, and click Apply.

5.  Choose the Show SQL tab to see the constraint creation statement and then click the Apply button to run the statement.

6.  Connect to your database as user HR with SQL*Plus.

7.  Run this query to find the names of the constraints:

8.  Rename the constraints to something more meaningful using the original constraint names retrieved in step 7, with ALTER TABLE commands.

9.  Add the following constraints to the HR schema:

Views

To the user, a view looks like a table, in other words, a two-dimensional structure of rows of columns against which the user can run SELECT and DML statements. The programmer knows the truth: A view is just a named SELECT statement. Any SELECT statement returns a two-dimensional set of rows. If the SELECT statement is saved as a view, then whenever the users query or update rows in the view (under the impression that it is a table), the statement runs, and the result is presented to users as though it were a table. The SELECT statement on which a view is based can be anything. It can join tables, perform aggregations, or do sorts; absolutely any legal SELECT command can be used as the basis for a view.

Why Use Views at All?

Possible reasons include providing security, simplifying user SQL statements, preventing errors, improving performance, and making data comprehensible. Table and column names are often long and pretty meaningless. The view and its columns can be much more obvious.

Views to Enforce Security

It may be that users should see only certain rows or columns of a table. There are several ways of enforcing this, but a view is often the simplest. Consider the HR.EMPLOYEES table. This includes personal details that should not be visible to staff outside the personnel department. But finance staff will need to be able to see the costing information. This view will depersonalize the data.

Note the use of schema qualifiers for the table as the source of the data (often referred to as either the base or the detail table) and the view; views are schema objects and can draw their data from tables in the same schema or in other schemas. If the schema is not specified, it will, of course, be in the current schema.

Finance staff can then be given permission to see the view but not the table and can issue statements such as this:

They will see only the five columns that make up the view, not the remaining columns of EMPLOYEES with the personal information. The view can be joined to other tables or aggregated as though it were a table.

A well-constructed set of views can implement a whole security structure within the database, giving users access to data they need to see while concealing data they do not need to see.

Views to Simplify User SQL

It will be much easier for users to query data if the hard work (such as joins or aggregations) is done for them by the code that defines the view. In the previous example, the user had to write code that joined the EMP_FIN view to the DEPARTMENTS table and summed the salaries per department. This could all be done in a view.

Then the users can select from DEPT_SAL without needing to know anything about joins or even how to sort the results.

In particular, they do not need to know how to make sure that all departments are listed, even those with no employees. The example in the preceding section would have missed these.

Views to Prevent Errors

It is impossible to prevent users from making errors, but well-constructed views can prevent some errors arising from a lack of understanding of how data should be interpreted. The preceding section already introduced this concept by constructing a view that will list all departments, whether or not they currently have staff assigned to them.

A view can help to present data in a way that is unambiguous. For example, many applications never actually delete rows. Consider this table:

The column ACTIVE is a flag indicating that the employee is currently employed and will default to Y when a row is inserted. When a user, through the user interface, “deletes” an employee, the underlying SQL statement will be an update that sets ACTIVE to N. If users who are not aware of this query the table, they may severely misinterpret the results. It will often be better to give them access to a view.

Queries addressed to this view cannot possibly see “deleted” staff members.

Views to Make Data Comprehensible

The data structures in a database will be normalized tables. It is not reasonable to expect users to understand normalized structures. To take an example from the Oracle E-Business Suite, a “customer” in the Accounts Receivable module is, in fact, an entity consisting of information distributed across the tables HZ_PARTIES, HZ_PARTY_SITES, HZ_CUST_ACCTS_ALL, and many more. All these tables are linked by primary key–to–foreign key relationships, but these are not defined on any identifiers visible to users (such as a customer number). They are based on columns the users never see that have values generated internally from sequences. The forms and reports used to retrieve customer information never address these tables directly; they all work through views.

As well as presenting data to users in a comprehensible form, the use of views to provide a layer of abstraction between the objects seen by users and the objects stored within the database can be invaluable for maintenance work. It becomes possible to redesign the data structures without having to recode the application. If tables are changed, then adjusting the view definitions may make any changes to the SQL and PL/SQL code unnecessary. This can be a powerful technique for making applications portable across different databases.

Views for Performance

The SELECT statement behind a view can be optimized by programmers so that users don’t need to worry about tuning code. There may be many possibilities for getting the same result, but some techniques can be much slower than others. For example, when joining two tables, there is usually a choice between the nested loop join and the hash join. A nested loop join uses an index to get to individual rows; a hash join reads the whole table into memory. The choice between the two will be dependent on the state of the data and the hardware resources available.

Theoretically, you can always rely on the Oracle optimizer to work out the best way to run a SQL statement, but there are cases where the optimizer gets it wrong. If the programmers know which technique is best, they can instruct the optimizer. This example forces use of the hash technique:

Whenever users query the DEPT_EMP view, the join will be performed by scanning the detail tables into memory. The users do not need to know the syntax for forcing use of this join method. You do not need to know it, either; this is beyond the scope of the Oracle Certified Professional (OCP) examination, but the concept of tuning with view design should be known.

Simple and Complex Views

For practical purposes, classification of a view as simple or complex is related to whether DML statements can be executed against it. Simple views can (usually) accept DML statements; complex views cannot. The strict definitions are as follows:

•  A simple view draws data from one detail table, uses no functions, and does no aggregation.

•  A complex view can join detail tables, use functions, and perform aggregations.

Applying these definitions shows that of the four views used as examples in the preceding section, the first and third are simple, and the second and fourth are complex.

It is not possible to execute INSERT, UPDATE, or DELETE commands against a complex view. The mapping of the rows in the view back to the rows in the detail tables cannot always be established on a one-to-one basis, which is necessary for DML operations. It is usually possible to execute DML against a simple view, but not always. For example, if the view does not include a column that has a NOT NULL constraint, then an INSERT through the view cannot succeed (unless the column has a default value). This can produce a disconcerting effect because the error message will refer to a table and a column that are not mentioned in the statement, as demonstrated in the first example in Figure 5-9.

Figure 5-9    DML against simple and complex views

The first view in the figure, RNAME_V, does conform to the definition of a simple view, but an INSERT cannot be performed through it because it is missing a mandatory column. The second view, RUPPERNAME_V, is a complex view because it includes a function. This makes an INSERT impossible because there is no way the database can work out what should actually be inserted. It can’t reverse-engineer the effect of the UPPER function in a deterministic fashion. But the DELETE succeeds because that is not dependent on the function.

CREATE VIEW, ALTER VIEW, and DROP VIEW

The syntax to create a view is as follows:

Note that views are schema objects. There is no reason not to have a view owned by one user referencing detail tables owned by another user. By default, the view will be created in the current schema. The optional keywords, none of which have been used in the examples so far, are as follows:

•  OR REPLACE    If the view already exists, it will be dropped before being created.

•  FORCE or NOFORCE    The FORCE keyword will create the view even if the detail tables in the subquery do not exist. NOFORCE is the default and will cause an error if the detail table does not exist.

•  WITH CHECK OPTION    This has to do with DML. If the subquery includes a WHERE clause, then this option will prevent insertion of rows that wouldn’t be seen in the view or updates that would cause a row to disappear from the view. By default, this option is not enabled, which can give disconcerting results.

•  WITH READ ONLY    This prevents any DML through the view.

•  CONSTRAINT constraintname    This can be used to name the WITH CHECK OPTION and WITH READ ONLY restrictions so that error messages when the restrictions cause statements to fail will be more comprehensible.

In addition, a set of alias names can be provided for the names of the view’s columns. If not provided, the columns will be named after the table’s columns or with aliases specified in the subquery.

The main use of the ALTER VIEW command is to compile the view. A view must be compiled successfully before it can be used. When a view is created, Oracle will check that the detail tables and the necessary columns on which the view is based do exist. If they do not, the compilation fails, and the view will not be created—unless you use the FORCE option. In that case, the view will be created but will be unusable until the tables or columns to which it refers are created and the view is successfully compiled. When an invalid view is queried, Oracle will attempt to compile it automatically. If the compilation succeeds because the problem has been fixed, users won’t know there was ever a problem—except that their query may take a little longer than usual. Generally speaking, you should manually compile views to make sure they do compile successfully, rather than having users discover errors.

It is not possible to adjust a view’s column definitions after creation in the way that a table’s columns can be changed. The view must be dropped and re-created. The DROP command is as follows:

By using the OR REPLACE keywords with the CREATE VIEW command, the view will be automatically dropped (if it exists at all) before being created.

Exercise 5-7: Create Views    In this exercise, you will create some simple and complex views using data in the HR schema. Either SQL*Plus or SQL Developer can be used.

1.  Connect to your database as user HR.

2.  Create views on the EMPLOYEES and DEPARTMENT tables that remove all personal information.

3.  Create a complex view that will join and aggregate the two simple views. Note that there is no reason you cannot have views of views.

4.  Confirm that the view works by querying it.

Synonyms

A synonym is an alternative name for an object. If synonyms exist for objects, then any SQL statement can address the object either by its actual name or by its synonym. This may seem trivial. It isn’t. Use of synonyms means that an application can function for any user, irrespective of which schema owns the views and tables or even in which database the tables reside. Consider this statement:

The user issuing the statement must know that the employees table is owned by the HR schema in the database identified by the database link PROD (do not worry about database links—they are a means of accessing objects in a database other than that onto which you are logged). If a public synonym has been created with this statement:

then all the user (any user!) needs to enter is the following:

This gives both data independence and location transparency. The user must have privileges to access the underlying object in order for the synonym-based reference to succeed. Tables and views can be renamed or relocated without ever having to change code; only the synonyms need to be adjusted.

As well as SELECT statements, DML statements can address synonyms as though they were the object to which they refer.

Private synonyms are schema objects. Either they must be in your own schema or they must be qualified with the schema name. Public synonyms exist independently of a schema. A public synonym can be referred to by any user to whom permission has been granted to see it without the need to qualify it with a schema name. Private synonyms must have unique names within their schema. Public synonyms can have the same name as schema objects. When executing statements that address objects without a schema qualifier, Oracle will first look for the object in the local schema, and only if it cannot be found will it look for a public synonym. Thus, in the preceding example, if the user happened to own a table called EMP, it would be this that would be seen, not the table pointed to by the public synonym.

The syntax to create a synonym is as follows:

A user will need to have been granted permission to create private synonyms and further permission to create public synonyms. Usually, only the database administrator can create (or drop) public synonyms. This is because their presence (or absence) will affect every user.

To drop a synonym, do this:

If the object to which a synonym refers (the table or view) is dropped, the synonym continues to exist. Any attempt to use it will return an error. In this respect, synonyms behave in the same way as views. If the object is re-created, the synonym must be recompiled before use. As with views, this will happen automatically the next time the synonym is addressed, or it can be done explicitly with this:

Exercise 5-8: Create and Use Synonyms    In this exercise, you will create and use private synonyms using objects in the HR schema. You can use either SQL*Plus or SQL Developer.

1.  Connect to your database as user HR.

2.  Create synonyms for the three views created in Exercise 5-7.

3.  Confirm that the synonyms are identical to the underlying objects.

4.  Confirm that the synonyms work (even to the extent of producing the same errors) by running the statements in Exercise 5-7 against the synonyms instead of the views.

5.  Drop two of the views.

6.  Query the complex view that is based on the dropped views.

Note that the query fails.

7.  Attempt to recompile the broken view.

This fails as well.

8.  Drop the DEP_SUM_V view.

9.  Query the synonym for a dropped view.

This fails.

10.  Recompile the broken synonym.

Note that this does not give an error, but rerun the query from step 9. It is definitely still broken.

11.  Tidy up by dropping the synonyms.

Sequences

A sequence is a structure for generating unique integer values. Only one session can read the next value and thus force it to increment. This is a point of serialization, so each value generated will be unique.

Sequences are an invaluable tool for generating primary keys. Many applications will need automatically generated primary key values. Examples in everyday business data processing are customer numbers and order numbers. The business analysts will have stated that every order must have a unique number, which should continually increment. Other applications may not have such a requirement in business terms, but it will be needed to enforce relational integrity. Consider a telephone billing system. In business terms, the unique identifier of a telephone is the telephone number (which is a string), and the unique identifier of a call will be the source telephone number and the time the call began (which is a timestamp). These data types are unnecessarily complex to use as primary keys for the high volumes that go through a telephone switching system. While this information will be recorded, it will be much faster to use simple numeric columns to define the primary and foreign keys. The values in these columns can be sequence based.

The sequence mechanism is independent of tables, the row locking mechanism, and commit or rollback processing. This means that a sequence can issue thousands of unique values a minute—far faster than any method involving selecting a column from a table, updating it, and committing the change.

Figure 5-10 shows two sessions selecting values from a sequence called SEQ1.

Figure 5-10    Use of a sequence by two sessions concurrently

Note that in the figure each selection of SEQ1.NEXTVAL generates a unique number. The numbers are issued consecutively in order of the time the selection was made, and the number increments globally, not just within one session.

Creating Sequences

The full syntax for creating a sequence is as follows:

You can see that creating a sequence can be simple. For example, the sequence used in Figure 5-6 was created with this:

The options are shown in the following table:

Appropriate settings for INCREMENT BY, START WITH, and MAXVALUE or MINVALUE will come from your business analysts.

It is rare for CYCLE to be used because it lets the sequence issue duplicate values. If the sequence is being used to generate primary key values, CYCLE makes sense only if there is a routine in the database that will delete old rows faster than the sequence will reissue numbers.

Caching sequence values is vital for performance. Selecting from a sequence is a point of serialization in the application code; only one session can do this at once. The mechanism is efficient. It is much faster than locking a row, updating the row, and then unlocking it with a COMMIT. But even so, selecting from a sequence can be a cause of contention between sessions. The CACHE keyword instructs Oracle to pregenerate sequence numbers in batches. This means that they can be issued faster than if they had to be generated on demand.

Using Sequences

To use a sequence, a session can select either the next value with the NEXTVAL pseudocolumn, which forces the sequence to increment, or the last (or “current”) value issued to that session with the CURRVAL pseudocolumn. The NEXTVAL will be globally unique. Each session that selects it will get a different, incremented value for each SELECT. The CURRVAL will be constant for one session until it selects NEXTVAL again. There is no way to find out what the last value issued by a sequence was. You can always obtain the next value by incrementing it with NEXTVAL, and you can always recall the last value issued to your session with CURRVAL, but you cannot find the last value issued.

A typical use of sequences is for primary key values. This example uses a sequence CUST_SEQ to generate unique customer numbers, ORDER_SEQ to generate unique order numbers, and LINE_SEQ to generate unique line numbers for the line items of the order. First create the sequences, which is a once-off operation.

Then insert the orders with their lines as a single transaction.

The first INSERT statement raises an order with a unique order number drawn from the sequence ORDER_SEQ for customer number 1000. The second and third statements insert the two lines of the order, using the previously issued order number from ORDER_SEQ as the foreign key to connect the line items to the order, and the next values from LINE_SEQ to generate a unique identifier for each line. Finally, the transaction is committed.

A sequence is not tied to any one table. In the preceding example, there would be no technical reason not to use one sequence to generate values for the primary keys of the order and of the lines.

A COMMIT is not necessary to make the increment of a sequence permanent. It is permanent and made visible to the rest of the world the moment it happens. It can’t be rolled back, either. Sequence updates occur independently of the transaction management system. For this reason, there will always be gaps in the series. The gaps will be larger if the database has been restarted abnormally and the CACHE clause was used. All numbers that have been generated and cached but not yet issued will be lost when the database is aborted. At the next restart, the current value of the sequence will be the last number generated, not the last issued. So, with the default CACHE of 20, every shutdown abort or instance failure will lose up to 20 numbers.

If the business analysts have stated that there must be no gaps in a sequence, then another means of generating unique numbers must be used. For the preceding example of raising orders, the current order number could be stored in this table and initialized to 10:

Then the code to create an order would have to become the following:

This will certainly work as a means of generating unique order numbers, and because the increment of the order number is within the transaction that inserts the order, it can be rolled back with the insert if necessary. There will be no gaps in order numbers, unless an order is deliberately deleted. But it is far less efficient than using a sequence, and code like this is famous for causing dreadful contention problems. If many sessions try to lock and increment the one row containing the current number, the whole application will hang as the sessions queue up to take their turn.

After creating and using a sequence, you can modify it. The syntax is as follows:

This ALTER command is the same as the CREATE command, with one exception: There is no way to set the starting value. If you want to restart the sequence, the only way is to drop it and re-create it. To adjust the cache value from the default to improve the performance of the preceding order entry example, do the following:

However, if you want to reset the sequence to its starting value, the only way is to drop it and create it again.

Exercise 5-9: Create and Use Sequences    In this exercise, you will create and use some sequences. You will need two concurrent sessions, either SQL Developer or SQL*Plus.

1.  Log on to your database twice as HR in separate sessions. Consider one to be your A session and the other to be your B session.

2.  In your A session, create a sequence as follows:

The use of NOCACHE is deleterious to performance. If MAXVALUE is specified, then CYCLE will be necessary to prevent errors when MAXVALUE is reached.

3.  Execute the following commands in the appropriate session in the correct order to observe the use of NEXTVAL and CURRVAL and the cycling of the sequence:

4.  Create a table with a primary key.

5.  Create a sequence to generate primary key values.

6.  In your A session, insert a row into the new table and commit.

7.  In your B session, insert a row into the new table and do not commit it.

8.  In your A session, insert a third row and commit.

9.  In your B session, roll back the second insertion.

10.  In your B session, see the contents of the table.

This demonstrates that sequences are incremented and the next value is published immediately, outside the transaction control mechanism.

11.  Tidy up.

12.  Connect to the HR schema with either SQL Developer or SQL*Plus and create three sequences that will be used in later exercises.

Two-Minute Drill

Categorize the Main Database Objects

•  Some objects contain data, principally tables and indexes.

•  Programmatic objects such as stored procedures and functions are executable code.

•  Views and synonyms are objects that give access to other objects.

•  Tables are two-dimensional structures, storing rows defined with columns.

•  Tables exist within a schema. The schema name together with the table name makes a unique identifier.

List the Data Types that Are Available for Columns

•  The most common character data types are VARCHAR2, NUMBER, and DATE.

•  There are many other data types.

Create a Simple Table

•  Tables can be created from nothing or with a subquery.

•  After creation, column definitions can be added, dropped, or modified.

•  The table definition can include default values for columns.

Create and Use Temporary Tables

•  Rows in a temporary table are visible only to the session that inserted them.

•  DML on temporary tables does not generate redo.

•  Temporary tables exist only in sessions’ Program Global Areas (PGAs) or in temporary segments.

•  A temporary table can keep rows for the duration of a session or of a transaction, depending on how it was created.

Indexes

•  Indexes are required for enforcing unique and primary key constraints.

•  NULLs are not included in B*Tree indexes but are included in bitmap indexes.

•  B*Tree indexes can be unique or nonunique, which determines whether they can accept duplicate key values.

•  B*Tree indexes are suitable for high-cardinality columns; bitmap indexes are for low-cardinality columns.

•  Bitmap indexes can be compound, function based, or descending; B*Tree indexes can also be unique, compressed, and reverse key.

Constraints

•  Constraints can be defined at table creation time or added later.

•  A constraint can be defined inline with its column or at the table level after the columns.

•  Table-level constraints can be more complex than those defined inline.

•  A table may have only one primary key but can have many unique keys.

•  A primary key is functionally equivalent to unique plus not null.

•  A unique constraint does not stop insertion of many null values.

•  Foreign key constraints define the relationships between tables.

Views

•  A simple view has one detail (or base) table and uses neither functions nor aggregation.

•  A complex view can be based on any SELECT statement, no matter how complicated.

•  Views are schema objects. To use a view in another schema, the view name must be qualified with the schema name.

•  A view can be queried exactly as though it were a table.

•  Views can be joined to other views or to tables, they can be aggregated, and in some cases they can accept DML statements.

•  Views exist only as data dictionary constructs. Whenever you query a view, the underlying SELECT statement must be run.

Synonyms

•  A synonym is an alternative name for a view or a table.

•  Private synonyms are schema objects; public synonyms exist outside user schemas and can be used without specifying a schema name as a qualifier.

•  Synonyms share the same namespace as views and tables and can therefore be used interchangeably with them.

Sequences

•  A sequence generates unique values—unless either MAXVALUE or MINVALUE and CYCLE have been specified.

•  Incrementing a sequence need not be committed and cannot be rolled back.

•  Any session can increment the sequence by reading its next value. It is possible to obtain the last value issued to your session but not the last value issued.

Self Test

1.  If a table is created without specifying a schema, in which schema will it be? (Choose the best answer.)

A.  It will be an orphaned table, without a schema.

B.  The creation will fail.

C.  It will be in the SYS schema.

D.  It will be in the schema of the user creating it.

E.  It will be in the PUBLIC schema.

2.  Several object types share the same namespace and therefore cannot have the same name in the same schema. Which of the following object types is not in the same namespace as the others? (Choose the best answer.)

A.  Index

B.  PL/SQL stored procedure

C.  Synonym

D.  Table

E.  View

3.  Which of these statements will fail because the table name is not legal? (Choose two answers.)

A.  create table "SELECT" (col1 date);

B.  create table "lowercase" (col1 date);

C.  create table number1 (col1 date);

D.  create table 1number(col1 date);

E.  create table update(col1 date);

4.  What are distinguishing characteristics of heap tables? (Choose two answers.)

A.  A heap table can store variable-length rows.

B.  More than one table can store rows in a single heap.

C.  Rows in a heap are in random order.

D.  Heap tables cannot be indexed.

E.  Tables in a heap do not have a primary key.

5.  Which of the following data types are variable length? (Choose all that apply.)

A.  BLOB

B.  CHAR

C.  LONG

D.  NUMBER

E.  RAW

F.  VARCHAR2

6.  Study these statements:

Will the insert succeed? (Choose the best answer.)

A.  The insert will fail because the 1.1 is too long.

B.  The insert will fail because the '31-01-07' is a string, not a date.

C.  The insert will fail for both reasons A and B.

D.  The insert will succeed.

7.  Which of the following is not supported by Oracle as an internal data type? (Choose the best answer.)

A.  CHAR

B.  FLOAT

C.  INTEGER

D.  STRING

8.  Consider this statement:

What will be the result? (Choose the best answer.)

A.  There will be an error because of the impossible condition.

B.  No table will be created because the condition returns FALSE.

C.  The table T1 will be created, but no rows will be inserted because the condition returns FALSE.

D.  The table T1 will be created, and every row in REGIONS will be inserted because the condition returns a NULL as a row filter.

9.  When a table is created with a statement such as the following:

will there be any constraints on the new table? (Choose the best answer.)

A.  The new table will have no constraints because constraints are not copied when creating tables with a subquery.

B.  All the constraints on TAB will be copied to NEWTAB.

C.  Primary key and unique constraints, but not check and not-null constraints, will be copied.

D.  Check and not-null constraints, but not unique or primary keys, will be copied.

E.  All constraints will be copied, except foreign key constraints.

10.  Which types of constraints require an index? (Choose all that apply.)

A.  CHECK

B.  NOT NULL

C.  PRIMARY KEY

D.  UNIQUE

11.  A transaction consists of two statements. The first succeeds, but the second (which updates several rows) fails partway through because of a constraint violation. What will happen? (Choose the best answer.)

A.  The whole transaction will be rolled back.

B.  The second statement will be rolled back completely, and the first will be committed.

C.  The second statement will be rolled back completely, and the first will remain uncommitted.

D.  Only the one update that caused the violation will be rolled back; everything else will be committed.

E.  Only the one update that caused the violation will be rolled back; everything else will remain uncommitted.

12.  Which of the following statements is correct about indexes? (Choose the best answer.)

A.  An index can be based on multiple columns of a table, but the columns must be of the same data type.

B.  An index can be based on multiple columns of a table, but the columns must be adjacent and specified in the order that they are defined in the table.

C.  An index cannot have the same name as a table, unless the index and the table are in separate schemas.

D.  None of these statements is correct.

13.  Which of the following options can be applied to B*Tree indexes but not to bitmap indexes? (Choose all correct answers.)

A.  Compression

B.  Descending order

C.  Function-based key expressions

D.  Reverse key indexing

E.  Uniqueness

F.  Use of compound keys

14.  Data in temporary tables has restricted visibility. If a user logs on as HR and inserts rows into a temporary table, to whom will the rows be visible?

A.  To no session other than the one that did the insert

B.  To all sessions connected as HR

C.  To all sessions, until the session that inserted them terminates

D.  To all sessions, until the session that inserted them commits the transaction

15.  Where does the data in a temporary table get written to disk? (Choose the best answer.)

A.  It is never written to disk.

B.  To the user’s temporary tablespace.

C.  To the temporary tablespace of the user in whose schema the table resides.

D.  To a disk local to the session’s user process.

16.  Which of these is a defining characteristic of a complex view, rather than a simple view? (Choose all correct answers.)

A.  Restricting the projection by selecting only some of the table’s columns

B.  Naming the view’s columns with column aliases

C.  Restricting the selection of rows with a WHERE clause

D.  Performing an aggregation

E.  Joining two tables

17.  Consider these three statements:

Why will the first query be quicker than the second? (Choose the best answer.)

A.  The view has already done the work of joining the tables.

B.  The view uses ISO standard join syntax, which is faster than the Oracle join syntax used in the second query.

C.  The view is precompiled, so the first query requires less dynamic compilation than the second query.

D.  There is no reason for the first query to be quicker.

18.  Study this view creation statement:

What might make the following statement fail? (Choose the best answer.)

A.  Unless specified otherwise, views will be created as WITH READ ONLY.

B.  The view is too complex to allow DML operations.

C.  The WITH CHECK OPTION will reject any statement that changes the DEPARTMENT_ID.

D.  The statement will succeed.

19.  There is a simple view SCOTT.DEPT_VIEW on the table SCOTT.DEPT. This insert fails with an error:

What might be the problem? (Choose the best answer.)

A.  The INSERT violates a constraint on the detail table.

B.  The INSERT violates a constraint on the view.

C.  The view was created as WITH READ ONLY.

D.  The view was created as WITH CHECK OPTION.

20.  What are the distinguishing characteristics of a public synonym rather than a private synonym? (Choose two correct answers.)

A.  Public synonyms are always visible to all users.

B.  Public synonyms can be accessed by name without a schema name qualifier.

C.  Public synonyms can be selected from without needing any permissions.

D.  Public synonyms can have the same names as tables or views.

21.  Consider these three statements:

Which of the following statements is correct? (Choose the best answer.)

A.  The second statement will fail because an object S1 already exists.

B.  The third statement will show the contents of EMPLOYEES.

C.  The third statement will show the contents of DEPARTMENTS.

D.  The third statement will show the contents of the table S1, if such a table exists in the current schema.

22.  A view and a synonym are created as follows:

Subsequently the table DEPT is dropped. What will happen if you query the synonym DEPT_S? (Choose the best answer.)

A.  There will not be an error because the synonym addresses the view, which still exists, but there will be no rows returned.

B.  There will not be an error if you first recompile the view with the command ALTER VIEW DEPT_V COMPILE FORCE;.

C.  There will be an error because the synonym will be invalid.

D.  There will be an error because the view will be invalid.

E.  There will be an error because the view will have been dropped implicitly when the table was dropped.

23.  A sequence is created as follows:

If the current value is already 50, when you attempt to select SEQ1.NEXTVAL, what will happen? (Choose the best answer.)

A.  The sequence will cycle and issue 0.

B.  The sequence will cycle and issue 1.

C.  The sequence will reissue 50.

D.  There will be an error.

24.  You create a sequence as follows:

After selecting from it a few times, you want to reinitialize it to reissue the numbers already generated. How can you do this? (Choose the best answer.)

A.  You must drop and re-create the sequence.

B.  You can’t. Under no circumstances can numbers from a sequence be reissued once they have been used.

C.  Use the command ALTER SEQUENCE SEQ1 START WITH 1; to reset the next value to 1.

D.  Use the command ALTER SEQUENCE SEQ1 CYCLE; to reset the sequence to its starting value.

Self Test Answers

1.      D. The schema will default to the current user.
    A, B, C, and E are incorrect. A is incorrect because all tables must be in a schema. B is incorrect because the creation will succeed. C is incorrect because the SYS schema is not a default schema. E is incorrect because while there is a notional user PUBLIC, he does not have a schema at all.

2.      A. Indexes have their own namespace.
    B, C, D, and E are incorrect. Stored procedures, synonyms, tables, and views exist in the same namespace.

3.      D and E. D violates the rule that a table name must begin with a letter, and E violates the rule that a table name cannot be a reserved word. Both rules can be bypassed by using double quotes.
    A, B, and C are incorrect. These are incorrect because all will succeed (though A and B are not exactly sensible).

4.      A and C. A heap is a table of variable-length rows in random order.
    B, D, and E are incorrect. B is incorrect because a heap table can be only one table. D and E are incorrect because a heap table can (and usually will) have indexes and a primary key.

5.      A, C, D, E, and F. All these are variable-length data types.
    B is incorrect. CHAR columns are fixed length.

6.      D. The number will be rounded to one digit, and the string will be cast as a date.
    A, B, and C are incorrect. Automatic rounding and typecasting will correct the “errors,” though ideally they would not occur.

7.      D. STRING is not an internal data type.
    A, B, and C are incorrect. CHAR, FLOAT, and INTEGER are all internal data types, though not as widely used as some others.

8.      C. The condition applies only to the rows selected for insert, not to the table creation.
    A, B, and D are incorrect. A is incorrect because the statement is syntactically correct. B is incorrect because the condition does not apply to the DDL, only to the DML. D is incorrect because the condition will exclude all rows from selection.

9.      D. Check and not-null constraints are not dependent on any structures other than the table to which they apply and so can safely be copied to a new table.
    A, B, C, and E are incorrect. A is incorrect because not-null and check constraints will be applied to the new table. B, C, and E are incorrect because these constraints need other objects (indexes or a parent table) and so are not copied.

10.      C and D. Unique and primary key constraints are enforced with indexes.
    A and B are incorrect. Check and not-null constraints do not rely on indexes.

11.      C. A constraint violation will force a rollback of the current statement but nothing else.
    A, B, D, and E are incorrect. A is incorrect because all statements that have succeeded remain intact. B and D are incorrect because there is no commit of anything until it is specifically requested. E is incorrect because the whole statement, not just the failed row, will be rolled back.

12.      D. All the statements are incorrect.
    A, B, and C are incorrect. A is incorrect because compound indexes need not be on columns of the same datatype. B is incorrect because the columns in a compound index need not be physically adjacent. C is incorrect because indexes and tables do not share the same namespace.

13.      A, D, and E. Compression, reverse key, and unique can be applied only to B*Tree indexes.
    B, C, and F are incorrect. Descending, function-based, and compound indexes can be either B*Tree or bitmap.

14.      A. Rows in a temporary table are visible only to the inserting session.
    B, C, and D are incorrect. All these incorrectly describe the scope of visibility of rows in a temporary table.

15.      B. If a temporary table cannot fit in a session’s PGA, it will be written to the session’s temporary tablespace.
    A, C, and D are incorrect. A is incorrect because temporary tables can be written out to temporary segments. C is incorrect because the location of the temporary segment is session specific, not table specific. D is incorrect because it is the session server process that writes the data, not the user process.

16.      D and E. Aggregations and joins make a view complex and make DML impossible.
    A, B, and C are incorrect. Selection and projection or renaming columns does not make the view complex.

17.      D. Sad but true. Views will not help performance, unless they include tuning hints.
    A, B, and C are incorrect. A is incorrect because a view is only a SELECT statement; it doesn’t prerun the query. B is incorrect because the Oracle optimizer will sort out any differences in syntax. C is incorrect because although views are precompiled, this doesn’t affect the speed of compiling a user’s statement.

18.      C. The WITH CHECK OPTION will prevent DML that would cause a row to disappear from the view.
    A, B, and D are incorrect. A is incorrect because views are, by default, created read-write. B is incorrect because the view is a simple view. D is incorrect because the statement cannot succeed because the CHECK option will reject it.

19.      A. There is a NOT NULL or PRIMARY KEY constraint on DEPT.DEPTNO.
    B, C, and D are incorrect. B is incorrect because constraints are enforced on detail tables, not on views. C and D are incorrect because the error message would be different.

20.      B and D. Public synonyms are not schema objects and so can be addressed directly only. They can have the same names as schema objects.
    A and C are incorrect. These are incorrect because users must be granted privileges on a public synonym before they can see it or select from it.

21.      B. The order of priority is to search the schema namespace before the public namespace, so it will be the private synonym (to EMPLOYEES) that will be found.
    A, C, and D are incorrect. A is incorrect because a synonym can exist in both the public namespace and the schema namespace. C is incorrect because the order of priority will find the private synonym first. D is incorrect because it would not be possible to have a table and a private synonym in the same schema with the same name.

22.      D. The synonym will be fine, but the view will be invalid. Oracle will attempt to recompile the view, but this will fail.
    A, B, C, and E are incorrect. A is incorrect because the view will be invalid. B is incorrect because the FORCE keyword can be applied only when creating a view (and it would still be invalid, even so). C is incorrect because the synonym will be fine. E is incorrect because views are not dropped implicitly (unlike indexes and constraints).

23.      D. The default is NOCYCLE, and the sequence cannot advance further.
    A, B, and C are incorrect. A and B are incorrect because CYCLE is disabled by default. If it were enabled, the next number issued would be 1 (not 0) because 1 is the default for START WITH. C is incorrect because under no circumstances will a sequence issue repeating values.

24.      A. It is not possible to change the next value of a sequence, so you must re-create it.
    B, C, and D are incorrect. B is incorrect because while a NOCYCLE sequence can never reissue numbers, there is no reason why a new sequence (with the same name) cannot do so. C is incorrect because START WITH can be specified only at creation time. D is incorrect because this will not force an instant cycle; it will affect what happens only when the sequence reaches its MAXVALUE or MINVALUE.