Managing Indexes and Data Integrity
Lesson 1. Course Introduction
*Course 7: Managing Indexes and Data IntegrityAs a DBA, a big part of your daily job function is the creation and maintenance of database objects like tables and indexes.
This course covers what you need to know for creating indexes and integrity constraints. Oracle offers indexes as a method of speeding database performance when accessing tables with a lot of data. Oracle provides different types of indexes for different uses, and you'll learn about them here. You'll also learn where to look in the data dictionary for information about your indexes.
Integrity constraints are used to enforce business rules and prevent bad data from entering tables. You'll learn about the five types of integrity constraints and how to use, maintain, and manage them.
This course presents information relevant to the following Oracle 1Z1-031: Oracle9i DBA Fundamentals I exam objectives:
Managing Indexes
- List the different types of indexes and their uses
- Create various types of indexes
- Reorganize indexes
- Drop indexes
- Get index information from the data dictionary
- Monitor the usage of an index
- Implement data integrity constraints
- Maintain integrity constraints
- Obtain constraint information from the data dictionary
Lesson 2. Index Types
Tables can grow quite large, and when they do it becomes difficult for users to quickly find the data they need. For this reason, Oracle offers indexes as a method of speeding database performance when users access tables with a lot of data.After completing this lesson, you should be able to:
- Describe how Oracle indexes work
- Identify the different types of indexes and their uses
An index in Oracle can be compared to the card catalog in a library. When you want to find a book, you go to the card catalog (or computer) and look up the book under author, title, or subject.
When you find the card for that book, it lists the location of the book in the library according to a classification system. Looking for a book this way prevents you from looking for a book on fly-fishing in the autobiographies section.
Oracle indexes work the same way. You find row data that matches your search criteria in the index first, and then use the ROWID for the matching row from the index to get the entire row quickly from the table.
Several criteria determine what kind of index you're looking at. The first is how many columns the index has. Simple indexes contain only one column of data through which you can search, plus the ROWID of the corresponding row in the table.
Composite indexes store more than one column of data, plus the ROWID of the corresponding row in the table. You can put up to 32 columns in a composite index, but you may be prevented from including that many if the total size of all the columns you want in the index exceeds DB_BLOCK_SIZE / 3.
Other criteria for identifying indexes are whether the indexed column(s) contains all unique (composite) values, whether an index is partitioned or nonpartitioned, and whether the index is a traditional B-tree index, a bitmap index, or a reverse-key index.
When composite indexes are in place on a table, Oracle will use that index only if the leading column(s) of the composite index are referenced in the where clause of a query against the table.
Oracle maintains indexes whenever user processes make data changes to tables. For example, if you insert a new row in a table, an associated entry is made in the index for that row's indexed column. That entry isn't made to the last leaf block of the index. The appropriate leaf block is located according to index sort order, and the entry is made there.
The pctfree setting has no effect on the index except at the time of creation. When data is removed from the table, the corresponding index entry is marked for removal.
Later, when all other rows corresponding to all index entries in the leaf node are removed, then and only then is the entire block purged of index entries. Thus, the structure of the index is preserved.
An update statement that changes the value of a row's indexed column value is treated as a marked removal followed by an insert.
Finally, index entries can be added to a block even past the pctfree threshold.
*Nonpartitioned B-Tree Indexes
The B-tree index is the traditional indexing mechanism used in Oracle. It stores data in a treelike fashion. At the base of the index is the root node, which is the entry point for your search for data in the index. The root node contains pointers to other nodes at the next level in the index. Depending on the value you seek, you'll be pointed in one of many directions.
The next level in the index consists of branch nodes, which are similar to the root node in that they, too, contain pointers to the next level of nodes in the index. Again, depending on the value you seek, you'll be pointed in one of many directions.
Branch nodes point to the highest level of the index: the leaf nodes. In this highest level, index entries contain indexed column values and the corresponding ROWIDs of rows storing those column values. Each leaf node is linked to the leaf nodes on its left and on its right, so you can search up and down through a range of entries in the index.
Within a single index entry there are several elements. The first is the index entry header, containing the number of columns in the entry.
Following that, the entry stores the values for the column(s) in the index. Preceding each column value is a length byte that follows the same rules length bytes follow in row entries.
Finally, the index entry stores the ROWID. No length byte is needed for this value because all ROWIDs are the same length.
There are a few special cases of data stored in index entries:
- If the index is non-unique and several rows contain the same value for the column, then each row with that value will have its own index entry to store each unique ROWID.
- If a row has a NULL value for the column(s) being indexed, there will be no corresponding index entry for that row.
- For only nonpartitioned indexes, because the index stores data for only one table and because all tables can be stored in only one tablespace, the object ID number isn't required to locate the row from the index. Nonpartitioned B-tree indexes use restricted ROWIDs to point to row data.
SQL> create index idx_emp_01 on table emp (lastname);
Index created.
B-tree indexes are used most commonly to improve performance on select statements that search columns of unique or mostly distinct values. And it's relatively easy and quick for Oracle to maintain B-tree indexes when data is changed in an indexed column, making this type of index useful for online transaction-processing applications.
However, these indexes do a bad job of finding data quickly on select statements with where clauses that contain comparison operations joined with or. They're also less useful in situations where values in the indexed column aren't very distinct.
*Bitmap Indexes
Although all indexes in Oracle are stored with the root-branch-leaf structure, bitmap indexes are conceptualized differently.
Instead of storing entries for each row in the table, a bitmap index stores an entry containing each distinct value, the start and end ROWIDs to indicate the range of ROWIDs in this table, and a long binary string with as many bits as there are rows in the table.
For example, say you're looking at a representation of a bitmap index for the table shown here. The APPLE_TYPE column index has three distinct values. The bitmap index would have three entries.
The start and end restricted ROWIDs for the object are also shown, so you can see the potential ROWID range.
Finally, you see a binary string representing a bitmap. A position will be set to 1 for the entry if the column for that row contains the associated value. Otherwise the bit is set to 0.
If an entry contains a bit set to 1, the corresponding bit in every other entry will always be set to 0.
Actually, this binary entry is also compressed, which means you can't see the bitmap with 0 and 1, but this is how the information is represented internally.
Bitmap indexes improve performance in situations where you select data from a column whose values are repeated often, as is the case with employee status (for example, active, LOA, or retired).
They also improve performance on select statements with multiple where conditions joined by or.
Bitmap indexes improve performance where data in a column isn't distinct and is infrequently or never changed.
By the same token, it's an arduous process to change data in that column. Changing the value of a column stored in a bitmap index requires Oracle to lock the entire segment storing the bitmap index in order to make the change. Locking affects the entire bitmap index. In other words, when changes are made to the key column in the table, bitmaps must be modified. This results in the locking of relevant bitmap segments.
The code shown here creates a bitmap index.
SQL> create bitmap index idx_emp_bmp_01 on table emp (gender);
Index created.
*Reverse-Key Indexes
Finally, consider the use of reverse-key indexes. This type of index is the same as a regular B-tree index except for one thing — the data from the column being indexed is stored in reverse order. If the column value in a table of first names is JASON, the reverse-key index column will be NOSAJ.
Users see the most benefit from reverse-key indexes when their select statements contain where clauses that use equality comparisons, such as where X = 5, but not in situations where range comparisons are used, such as where X between 4 and 6.
The value or benefit of reverse-key indexes is to assist performance in Oracle Real Application Cluster environments.
The code below creates a reverse-key index.
SQL> create index idx_emp_rev_01 on table emp (lastname) reverse;
Index created.
Question 1
Question 2
Question 3
Question 4
In this lesson, you learned that Oracle indexes allow you to find row data that matches your search criteria in the index, and then use the ROWID for that row from the index to quickly get to the corresponding table row.
You also learned how Oracle maintains indexes whenever user processes make data changes to tables.
Finally, you learned about the different uses for B-tree, bitmap and reverse-key indexes.
Lesson 3. Function-Based and Descending Indexes
Function-based and descending indexes are two types of indexes in Oracle8i and higher that can improve query performance in certain specific instances.After completing this lesson, you should be able to:
- Understand the use of function-based indexes
- Understand the use of descending indexes
*Function-Based Indexes
The function-based index is designed to improve query performance by making it possible to define an index that works when your where clause contains operations on columns. Traditional B-tree indexes can't be used when your where clause contains columns that participate in functions or operations.
For example, suppose you have table EMP with four columns: EMPID, LASTNAME, FIRSTNAME, and SALARY. The SALARY column has a B-tree index. However, if you issue the statement select * from EMP where (SALARY*1.08) > 63000, the relational database management system (RDBMS) ignores the index, performing a full table scan instead.
Function-based indexes are designed to be used in situations like this, where your SQL statements contain such operations in their where clauses. The code block shows how you define a function-based index.
CREATE INDEX idx_emp_func_01
ON emp(SALARY*1.08);
By using function-based indexes, you can optimize the performance of queries containing function operations on columns in the where clause, such as the query described previously. As long as the function you specify is repeatable, you can create a function-based index around it. A repeatable function is one whose result will never change for the same set of input data.
For example, 2 + 2 will always equal 4, and will never change to equal 5. The addition operation is repeatable. To enable the use of function-based indexes, you must issue two alter session statements, as follows:
SQL> alter session set query_rewrite_enabled = true;
Session altered.
SQL> alter session set query_rewrite_integrity = trusted;
Session altered.
Bitmap indexes can also be function-based indexes.
Function-based indexes can also be partitioned.
*Descending Indexes
Another type of index instituted in Oracle8i and higher is the descending index. Recall that the order by clause is used in SQL statements to impose sort order on data returned from the database. It makes the data more readable.
Oracle doesn't typically store data in any particular order, a common practice in relational database systems. However, a B-tree index does employ a particular order.
In versions of Oracle before Oracle8i, the order used by B-tree indexes was ascending order, ordered from the lowest column value to the highest. In Oracle8i and higher, you can categorize data in a B-tree index in descending order as well.
This feature can be useful in applications where sorting operations are required in conflicting ways. For example, say you have the EMP table with four columns: EMPID, LASTNAME, SALARY, and DEPT. As part of a departmental performance comparison, you may have to query this table by department code in ascending order, but query salary in descending order, using the following query:
SQL> select dept, salary, empid, lastname
2 from emp
3 order by dept asc, salary desc;
If the EMP table is large, prior versions of Oracle may have required enormous amounts of sort space to obtain DEPT data in one sort order and SALARY data in another. Descending indexes can be used to change that. For example, you could define separate, simple indexes for DEPT and SALARY data, where the DEPT data used the traditional ascending method in its B-tree index, while the SALARY column used descending order in the index. To create the simple indexes, you could use the following code block:
-- Regular ascending index
CREATE INDEX emp_dept_idx_01
ON EMP(DEPT);
-- Descending index
CREATE INDEX emp_sal_idx_01
ON EMP(SALARY DESC);
Different sort orders can also be specified for columns in a composite index. Using the previous example, you could define a composite index containing two columns with different sort orders specified for each column, such as the index definition shown in the following code block:
CREATE INDEX emp_dep_sal_idx_01
ON EMP(dept ASC, salary DESC);
You can combine function-based indexes with descending-index features to create function-based descending indexes.
Descending indexes can also be partitioned.
Question 5
Question 6
In this lesson, you learned that a function-based index is designed to improve the performance of queries containing function operations on columns in the where clause.
You also learned that a descending index can be useful in situations where sorting operations are required in conflicting ways.
Lesson 4. Creating B-Tree and Bitmap Indexes
You use the create index statement to create all types of indexes.After completing this lesson, you should be able to:
- Create B-tree indexes
- Create bitmap indexes
- Understand index-creation issues
The create index statement creates all types of indexes. To define special types of indexes, you include keywords such as create unique index for indexes on columns that enforce uniqueness of every element of data or create bitmap index for creating bitmap indexes. The code block below shows the statement for creating a unique B-tree index. The statement also includes options for data storage and creation.
CREATE UNIQUE INDEX employee_lastname_indx_01
ON employee (lastname ASC)
TABLESPACE INDEXES
PCTFREE 12
INITRANS 2 MAXTRANS 255
LOGGING
NOSORT
STORAGE ( INITIAL 900K
NEXT 1800K
MINEXTENTS 1
MAXEXTENTS 200
PCTINCREASE 0 );
There are several items in the storage definition that should look familiar, such as tablespace, pctfree, logging, and the items in the storage clause. Other than pctfree, these options have the same use as in create table statements. Oracle uses pctfree during the creation of the index only to reserve space for index entries that may need to be inserted into the same index block.
CREATE UNIQUE INDEX employee_lastname_indx_01
ON employee (lastname ASC)
TABLESPACE INDEXES
PCTFREE 12
INITRANS 2 MAXTRANS 255
LOGGING
NOSORT
STORAGE ( INITIAL 900K
NEXT 1800K
MINEXTENTS 1
MAXEXTENTS 200
PCTINCREASE 0 );
Pctused isn't used in index definitions. Because all items in an index must be in the right order for the index to work, Oracle must put an index entry into a block no matter what. Thus, pctused is not used.
A few other items may look unfamiliar, such as unique, asc, and nosort.
Specify unique when you want the index to enforce uniqueness for values in the column.
The asc keyword indicates ascending order for this column in the index; desc (descending) can be substituted for this clause.
CREATE UNIQUE INDEX employee_lastname_indx_01
ON employee (lastname ASC)
TABLESPACE INDEXES
PCTFREE 12
INITRANS 2 MAXTRANS 255
LOGGING
NOSORT
STORAGE ( INITIAL 900K
NEXT 1800K
MINEXTENTS 1
MAXEXTENTS 200
PCTINCREASE 0 );
Use the nosort keyword when you've loaded your table data in the proper sort order on the column you're indexing. In this case, it would mean that you've loaded data into the EMPLOYEE table sorted in ascending order on the LASTNAME column. By specifying nosort, Oracle will skip the sort ordinarily used in creating the index, thereby increasing performance on your create index statement. You might use this option if your operating system offered a procedure for sorting that was more efficient than Oracle's.
CREATE UNIQUE INDEX employee_lastname_indx_01
ON employee (lastname ASC)
TABLESPACE INDEXES
PCTFREE 12
INITRANS 2 MAXTRANS 255
LOGGING
NOSORT
STORAGE ( INITIAL 900K
NEXT 1800K
MINEXTENTS 1
MAXEXTENTS 200
PCTINCREASE 0 );
You can create bitmap indexes with several storage specifications, but remember that this type of index is used to improve search performance for low-cardinality columns, so a bitmap index may not be unique. The following code block creates a bitmap index:
CREATE BITMAP INDEX employee_lastname_indx_01
ON employee (lastname)
TABLESPACE ORGDBIDX
PCTFREE 12
INITRANS 2 MAXTRANS 255
LOGGING
NOSORT
STORAGE ( INITIAL 900K
NEXT 1800K
MINEXTENTS 1
MAXEXTENTS 200
PCTINCREASE 0 );
*Bitmap Index Performance
The performance of commands that use bitmap indexes is heavily influenced by an area of memory specified by the initsid.ora CREATE_BITMAP_AREA_SIZE parameter.
This area determines how much memory will be used for storing bitmap segments. You need more space for this purpose if the column on which you're creating the bitmap index has high cardinality.
For a bitmap index, high cardinality might mean a dozen or so unique values out of 500,000 (as opposed to B-tree indexes, for which high cardinality might mean 490,000 unique values out of 500,000). In this situation, you might stick with the Oracle default setting of 8MB for your CREATE_BITMAP_AREA_SIZE initialization parameter.
An example of low cardinality for a column would be having two distinct values in the entire table, as is the case for a column indicating whether an employee is male or female. In this case, you might size your initialization parameter considerably lower than the Oracle default, perhaps around 750KB.
Searching a large table without the benefit of an index takes a long time because a full table scan must be performed. Indexes are designed to improve search performance.
Unlike full table scans, whose performance worsens as the table grows larger, the performance of table searches that use indexes gets exponentially better as the index (and associated table) gets larger and larger.
In fact, on a list containing 1 million elements, a binary search tree algorithm similar to the one used in a B-tree index finds any element in the list within 20 tries. And in reality, the B-tree algorithm is actually far more efficient.
There is a price for all this speed, which is paid for in the additional disk space required to store the index and the overhead required to maintain it when DML operations are performed on the table. To minimize the trade-off, you must weigh the storage cost of adding an index to the database against the performance gained by having the index available for searching the table.
The performance improvement achieved by using an index is exponential over the performance of a full table scan, but there's no value in the index if it's never used by the application. You should also consider the volatility of data in a table before creating an index. If data in the indexed column changes regularly, you might want to index a more static column.
Consider how you're sizing pctfree for your index. Oracle uses pctfree only to determine free space when the index is first created. After that, the space is fair game, because Oracle has to keep all the items in the index in order. So, after creation, Oracle will put index records in a block right down to the last bit of space available.
CREATE BITMAP INDEX employee_lastname_indx_01
ON employee (lastname)
TABLESPACE ORGDBIDX
PCTFREE 12
INITRANS 2 MAXTRANS 255
LOGGING
NOSORT
STORAGE ( INITIAL 900K
NEXT 1800K
MINEXTENTS 1
MAXEXTENTS 200
PCTINCREASE 0 );
To determine the best value for pctfree on your index, consider the following:
If the values in the column you're indexing increase sequentially, such as column values generated by sequences, you can size pctfree as low as 2 or 3. If not, you should calculate pctfree based on row-count forecasts for growth over a certain time period (12 months, for example) with the following formula:
((max_#_rows_in_period - initial_#_rows_in_period) / max_#_rows_in_period) x 100.
Question 7
Question 8
Question 9
Question 10
Topic 4.1 Exercises
* Exercise 1
Try creating indexes in Oracle 9i.
| Step | Action |
|---|---|
| 1 | Please Note: For the exercises in this course, you should set up an isolated Oracle9i system for practice. Do not use your production Oracle9i system for these practice exercises. Create a unique b-tree index with a descending column and allow logging. |
| 2 | Create a bitmap index in which you will load your table data in the proper sort order. |
In this lesson, you learned that you use the create unique index statement to create a B-tree index. Within this statement there are items for defining storage, ordering columns, and sort order.
You learned that you can create bitmap indexes with the create bitmap index statement. Because they're used to improve search performance for low-cardinality columns, bitmap indexes may not be unique.
Finally, you learned that the performance of table searches that use indexes gets exponentially better as the index gets larger and larger. But the price for this speed is additional disk space to store the index and the overhead to maintain it.
Lesson 5. Reorganizing Indexes
As time goes on and values in an index change, the index may become stale. You can rebuild or reorganize indexes to correct index staleness.After completing this lesson, you should be able to:
- Reorganize indexes
- Rebuild indexes
- Rebuild indexes online
Indexes are maintained by Oracle behind the scenes whenever you make a change to data on the indexed column in the table. As time goes on, the values in an indexed column may change so Oracle has to rearrange the contents of the index to make room for new values in the indexed column.
If enough column values are changed to skew the overall number of elements in the index from its initial configuration, the index may become stale. A stale index does a poor job of retrieving data quickly, which causes users to notice degraded performance when running their queries. To fix this situation, you can either reorganize or rebuild an index.
*Reorganizing Indexes
Reorganizing indexes is handled with the alter index statement. alter index is useful for redefining storage options such as next, pctincrease, maxextents, initrans, or maxtrans.
You can also use alter index to change the pctfree value for new blocks in new extents allocated by your index.
You can add extents manually to an index much as you do for tables, with the alter index allocate extent statement, specifying size and datafile optionally. You can also remove unused space below the index high-water mark with the alter index deallocate unused statement, optionally reserving a little extra space with the keep clause.
You can use the analyze index validate structure command as you would with tables to check for block corruption.
The INDEX_STATS dictionary view shows you the number of index entries in leaf nodes in the LF_ROWS column compared to the number of deleted entries in the DEL_LF_ROWS column.
Oracle recommends that if the number of deleted entries is over 30 percent, you should rebuild the index.
*Rebuilding Indexes
Rebuilding lets you create a new index using the data from the old one, resulting in fewer table reads while rebuilding, tidier space management in the index, and better overall performance.
This operation is accomplished with the alter index idxname rebuild tablespace tblspcname statement. The tablespace clause also moves the index to the tablespace named.
All the storage options you can specify in a create index statement can be applied to alter index rebuild as well.
Rebuild an index when you want to move the index to another tablespace or when many rows have been deleted from the table, causing index entries to be removed as well. Queries continue to use the existing index while the new index is being built.
If you have to build or rebuild an index for any reason, you usually have to plan when you would perform the maintenance operation around users who need to make changes to tables.
To rebuild an index, Oracle places a DML lock on the base table whose index is about to be rebuilt. During the time Oracle holds this DML lock, you can't make any changes to data in the base table.
If the table being indexed is large and has to be available to users, downtime at night or over the weekend is required, because Oracle has to prevent DML operations to data in the table while building or rebuilding the index. For e-business applications requiring 24x7 availability, downtime is simply not possible.
*Rebuilding Indexes Online
Oracle provides a method for building or rebuilding indexes using less-restrictive locking mechanisms than a simple rebuild. This less-restrictive locking method lets users make changes to data in the table while you build or rebuild the index. These changes are recorded in the new or rebuilt index. The steps Oracle takes to perform an online index rebuild are described on the pages that follow.
First, Oracle obtains locks on the table for a very short time to define the structure of the index and to update the data dictionary. During this time, the table and the index aren't available for data queries or changes.
Second, Oracle releases the lock and users can once again make data changes to the table. The index is made available only for queries, and Oracle starts rebuilding a copy of the index. Oracle simultaneously maintains a small copy of the index called a journal table. The journal table houses new information added to the table column.
Third, Oracle incorporates new data added to the journal table into the larger index copy. An even smaller copy of the index exists for capturing any new data added to the table during the time the journal table is added to the index being rebuilt. Once changes from the journal table are added to the rebuilt index, Oracle begins incorporating the new data found in the even smaller copy. Oracle then repeats the process, creating the smallest copy of the index for housing the few new records added while Oracle incorporated new data from the smaller copy.
This process can't continue forever, so after Oracle integrates the changes from the smaller copy, it locks a few rows of the table at a time so no users can change data. The smallest index copy is then incorporated into the index being rebuilt. When finished, Oracle discards the original index in favor of the rebuilt version, releases all locks, and users are once again given access to the table.
To build an index on a table while continuing to leave the table online and available for user changes in this fashion, you use the create index name on table(columns) online statement.
To rebuild an existing index in the same fashion, you use the alter index name rebuild online statement. The following code block shows the use of the online rebuild command in a SQL*Plus session:
SQL> alter index idx_emp_01 rebuild online;
Index altered.
Depending on the size of the table being indexed and how much data is changed by users while the index is being rebuilt, the online rebuild operation could take a long time to complete.
Unfortunately, there's little you can do to tune the process. It's just one of the trade-offs you have to make when you want 24x7 data availability. You can minimize impact on end users by scheduling the online rebuild during off-peak times.
You can't use this method for building or rebuilding any kinds of bitmap or cluster indexes. This method works for B-tree indexes and their variants, such as function-based, descending, and reverse-key indexes, and for partitioned indexes.
You also can't use this indexing method on secondary indexes in IOTs.
Question 11
Question 12
Question 13
Topic 5.1 Exercises
* Exercise 1
Try reorganizing and rebuilding indexes in Oracle 9i.
| Step | Action |
|---|---|
| 1 | Please Note: For the exercises in this course, you should set up an isolated Oracle9i system for practice. Do not use your production Oracle9i system for these practice exercises. Reorganize an index to change the pctfree value and to get rid of unused space below the index high-water mark. |
| 2 | Do a simple rebuild of an index. |
| 3 | Rebuild an index online. |
In this lesson, you learned that you can reorganize indexes with the alter index statement. You can also use the statement to manually add extents to an index and change the pctfree value for extents.
You also learned that you can rebuild an index using the alter index rebuild statement. This lets you build a new index using data from the old one, resulting in fewer table reads, tidier space management, and better overall performance.
Finally, you learned that with alter index name rebuild online, you can rebuild an index using less-restrictive locking mechanisms. This is helpful in cases where you need to let users make changes to data while an index is rebuilt.
Lesson 6. Dropping Indexes and Getting Index Information
Dropping an index will allow you to expand or re-create your index. The Oracle data dictionary can help to find information about your indexes.After completing this lesson, you should be able to:
- Drop indexes
- Retrieve index information from the data dictionary
What happens when you want to expand your index to include more columns or to get rid of columns? Can you use alter index for that? Unfortunately, the answer is no.
You must drop and re-create the index to modify column definitions or change column order from ascending to descending (or vice versa). Dropping an index is accomplished with the drop index idxname statement.
You may want to get rid of an index that's used only for specific purposes on an irregular basis, especially if a table has other indexes and volatile data.
You may also want to drop an index if you're about to perform a large load of table data, perhaps preceded by purging all data in the table. Dropping the index lets your data load run faster, and the index created later is fresh and well-organized.
You may have to re-create your index if it has a status of INVALID in the DBA_OBJECTS view, if you know the index is corrupt from running DBVERIFY on the tablespace housing the index, or if running the analyze command on the index itself shows corruption.
*Using the Data Dictionary
You may find yourself looking for information about your indexes, and the Oracle data dictionary can help. The DBA_INDEXES view offers a great deal of information about indexes, such as the index owner, the index name, which table the index is associated with, the current status (valid, invalid, and others) of the index, whether the index enforces uniqueness or not, and type of index (normal or bitmap).
The DBA_IND_COLUMNS view contains information about the columns stored in an index. The most valuable piece of information this view can give you (in addition to telling you which columns are indexed) is the order in which the columns of the index appear. This is a crucial factor in determining whether the index will improve performance in selecting data from a table.
If you were to issue select * from EMP where LASTNAME = 'SMITH' and a composite index existed in which LASTNAME was the first column in the index order, that index would improve performance. However, if the index listed FIRSTNAME as the first column, the index wouldn't help.
Finally, a note on finding information about reverse-key indexes. You might notice, if you have reverse-key indexes in your database, that there's no information in the DBA_INDEXES view telling you specifically that the index is reverse key. To see this information, you must execute a specialized query that uses a SYS-owned table called IND$, as well as the DBA_OBJECTS view. The following code block shows the query:
SELECT object_name FROM dba_objects
WHERE object_id IN (SELECT obj#
FROM ind$
WHERE BITAND(property,4) = 4);
Question 14
Question 15
Question 16
Topic 6.1 Exercises
* Exercise 1
Try viewing information about an index in Oracle 9i.
| Step | Action |
|---|---|
| 1 | Please Note: For the exercises in this course, you should set up an isolated Oracle9i system for practice. Do not use your production Oracle9i system for these practice exercises. View the status, index type, and which table is associated with an index. |
| 2 | View the columns stored in an index, and determine the order in which those columns appear. |
In this lesson, you learned that to expand an index to include more columns or to get rid of columns, you use the drop index idxname statement and then must re-create it. You can also use this statement to get rid of a rarely-used index or if you're about to perform a large load of table data and want to create a fresh index later.
You learned that the Oracle data dictionary can help you find information about your indexes. You can use the DBA_INDEXES view to get information such as the type of index, its status, and which table the index is associated with. The DBA_IND_COLUMNS view tells you which columns are stored in an index and the order in which the columns appear in the index.
Lesson 7. Monitoring Use of Indexes
Oracle provides a means of monitoring indexes to determine if they are being used or not.After completing this lesson, you should be able to:
- Start and stop index usage monitoring
- Understand the V$OBJECT_USAGE view
Indexes enhance users' ability to access data in a large table. However, an index isn't doing its job if no one uses it. Oracle provides a means of monitoring indexes to determine if they're being used or not.
Although you might be tempted to provide many indexes to improve performance in as many query situations as possible, each index you create takes up space that could have been used for other purposes.
Even though disk space isn't very expensive anymore, if you determine that an index isn't being used you can drop that index, eliminating unnecessary statement overhead.
Oracle's mechanism for monitoring disk usage is activated with the alter index name monitoring usage command, where name is the name of the index you intend to monitor. Issue this command, then let users access the Oracle database for a while.
During this time, Oracle gathers information about whether the index is used or not. For best results, try to ensure that usage of the Oracle database during this period realistically reflects typical use of the database.
For example, you wouldn't want to monitor index disk usage from late on a Friday evening to early Saturday morning if few users were working during that time and no batch processes were running.
Issue the alter index name nomonitoring usage statement to have Oracle stop monitoring index usage.
Once Oracle has gathered usage statistics, you can look in the V$OBJECT_USAGE dynamic performance view to figure out whether your index is being employed by users in a meaningful way. Records for every index monitored during the life of the instance will be kept in this view, listed by the name of the index and the table that index is associated with.
When you monitor an index for the first time, a corresponding record is added to V$OBJECT_USAGE. Subsequent attempts to monitor the index won't add new records to V$OBJECT_USAGE, but the MONITORING column (shown here as MON) will contain YES for this index whenever monitoring is turned on. Any previous monitoring information for that index in V$OBJECT_USAGE will be cleared and reset.
This view also contains a column called USED (shown here as USE). Values for this column are YES or NO, making its interpretation easy. If the index was used during the monitoring period, Oracle lists YES; otherwise Oracle lists NO.
The view also contains the start and stop times of the monitoring period.
You must be logged into Oracle as the user owning the index in order to see index statistics in V$OBJECT_USAGE.
For example, if the index being monitored is owned by SCOTT and you log in as SYSTEM to query V$OBJECT_USAGE, you won't see info for SCOTT's index.
The USED column in V$OBJECT_USAGE changes only when the index being monitored has been used, so it's worth noting when indexes are used and when they aren't. For this scenario, we'll employ the standard DEPT table owned by SCOTT.
SCOTT is concerned that the index on the DEPTNO column in the DEPT table, PK_DEPT, isn't being utilized effectively by other users in Oracle. He logs into the database and starts monitoring the PK_DEPT index. He also queries the V$OBJECT_USAGE view to make sure everything is in place for Oracle to start monitoring use of the index. These activities are shown in the following code:
SQL> connect scott/tiger
Connected.
SQL> alter index pk_dept monitoring usage;
Index altered.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MONI USED START_MONITORING END_MONITORING
-------------- -------------- ---- ---- ---------------- --------------
PK_DEPT DEPT YES NO 10/21/2001 13:21
At this point, SCOTT knows that index monitoring is turned on. The SYSTEM user then logs into Oracle9i and issues the following query on the DEPT table:
SQL> select * from dept;
DEPTNO DNAME LOC
----------- ------------ ---------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Later, SCOTT queries the V$OBJECT_USAGE view again to see how things have progressed and receives the following:
INDEX_NAME TABLE_NAME MONI USED START_MONITORING END_MONITORING
-------------- -------------- ---- ---- ---------------- --------------
PK_DEPT DEPT YES NO 10/21/2001 13:21
But how can that be? SYSTEM logged in and queried the DEPT table, yet Oracle tells us that the index on the DEPTNO column isn't being used.
That's exactly how it should be, because SYSTEM didn't query the DEPT table based on the contents of the DEPTNO column — SYSTEM simply asked for all the data, which resulted in Oracle executing a full table scan to retrieve all data from the DEPT table.
Had SYSTEM instead issued select * from dept where deptno = 10, SCOTT would have seen the following output, with a YES in the USED column:
INDEX_NAME TABLE_NAME MONI USED START_MONITORING END_MONITORING
-------------- -------------- ---- ---- ---------------- --------------
PK_DEPT DEPT YES YES 10/21/2001 13:21
Question 17
Question 18
Topic 7.1 Exercises
* Exercise 1
Try monitoring the use of an index in Oracle9i.
| Step | Action |
|---|---|
| 1 | Please Note: For the exercises in this course, you should set up an isolated Oracle9i system for practice. Do not use your production Oracle9i system for these practice exercises. Enable monitoring for an index. |
| 2 | Look at the V$OBJECT_USAGE view for the index. |
| 3 | End monitoring for the index. |
In this lesson, you learned that you can monitor indexes to determine if they're being used or not. You learned that the alter index name monitoring usage command activates index monitoring and the alter index name nomonitoring usage statement stops index monitoring.
You also learned that you can look in the V$OBJECT_USAGE view to determine whether your index is being employed by users. You learned that this view keeps records for every index that's been monitored during the life of the instance.
Lesson 8. Implementing Data Integrity Constraints
The goal of an integrity constraint is to enforce business rules.After completing this lesson, you should be able to:
- Distinguish each data integrity constraint
- Implement each data integrity constraint
The popularity of integrity constraints relates to the fact that they're easy to define and use, they execute quickly, and they're highly flexible.
For example, if an organization wants to be sure every employee has a last name, the most common way to accomplish this in an Oracle database is by using a declarative integrity constraint.
The LASTNAME column of the EMPLOYEE table can have a not NULL constraint that prevents any row of information from being added without that LASTNAME column populated.
There are five types of declarative integrity constraints in Oracle: primary keys, foreign keys, unique constraints, not NULL constraints, and check constraints.
*Primary Keys
The primary key of a database table is the unique identifier for that table. It distinguishes each row in the table from all other rows. Primary keys are the backbone of the table.
A primary key constraint consists of two data integrity rules for the column declared as the primary key.
First, every value in the primary key column must be unique in the table. Second, no value in the primary key column can be NULL.
You should choose the primary key for a table carefully. The column or columns defined to be the primary key should reflect the most important piece of information that's unique about each row of the table.
*Foreign Keys
The creation of a foreign key constraint from one table to another defines a special relationship between the two tables that's often referred to as a parent/child relationship.
The parent table is the one referred to by the foreign key, while the child table actually contains the foreign key. The DBA should ensure that foreign keys on one table refer only to primary keys on other tables.
Unlike primary key constraints, a foreign key constraint on a column doesn't prevent user processes from setting a value in the foreign key column of the child table to NULL. In cases where the column is NULL, there will be no referential integrity check between the child and the parent.
*Unique Constraints
Like the primary key, a unique key constraint ensures that values in the column on which the unique constraint is defined are not duplicated by other rows. In addition, the unique constraint is the only type of constraint, other than the primary key constraint, that has an associated index created with it when the constraint is named.
*Not NULL Constraints
NULL can't be specified as the value for a column on which the not NULL constraint is applied.
Often, a DBA will define this constraint in conjunction with another constraint.
For example, the not NULL constraint can be used with a foreign key constraint to force the validation of column data against a valid value table.
*Check Constraints
Check constraints let the DBA specify a set of valid values for a column. Oracle will check these values automatically when a row is inserted with a non-NULL value for that column. This constraint is limited to hard-coded valid values. In other words, a check constraint can't look up valid values, nor can it perform any type of SQL or PL/SQL operation as part of its definition.
Primary keys and unique key constraints are created with an associated unique index. This index preserves uniqueness in the column(s) and also facilitates high-performance searches on the table whenever the primary key is named in the where clause.
*Creating an Integrity Constraint
Constraint definitions are handled at the table-definition level, either in a create table or alter table statement. Whenever a constraint is created, it's automatically enabled unless a condition exists on the table that violates the constraint. If the constraint condition is violated, Oracle will create the constraint with disabled status. The rows that violated the constraint will be optionally written to a special location.
Alternatively, you can disable a constraint on creation with the disable clause, or force the constraint to be created and enabled by not validating the data with the novalidate clause.
Here are some general guidelines for creating constraints:
- Put indexes associated with constraints in a tablespace separate from table data.
- Disable constraints before loading tables with lots of row data, and then reenable the constraints afterward.
- Make constraints deferrable when using self-referencing foreign key constraints.
*Creating Integrity Constraints
The primary key is defined with the constraint clause. A name should be given to the primary key in order to name the associated index. The type of constraint is defined on the next line; it will either be a primary key, foreign key, unique constraint, or check constraint.
For indexes associated with primary keys and unique constraints, the tablespace used for storing the index is named in the using tablespace clause. You should specify a separate tablespace for indexes and the tables, for performance reasons. The code block below illustrates the creation of a table with constraints defined.
CREATE TABLE emp
( empid NUMBER NOT NULL,
empname VARCHAR2(30) NOT NULL,
salary NUMBER NOT NULL,
CONSTRAINT pk_emp_01
PRIMARY KEY (empid)
NOT DEFERRABLE
USING INDEX TABLESPACE indexes_01 DISABLE)
TABLESPACE data_01;
The create table statement defines constraints after the columns are named. This is called out-of-line constraint definition because the constraints are after the columns. You must do this if you plan to use two or more columns in your primary or unique key constraints.
CREATE TABLE emp
( empid NUMBER NOT NULL,
empname VARCHAR2(30) NOT NULL,
salary NUMBER NOT NULL,
CONSTRAINT pk_emp_01
PRIMARY KEY (empid)
NOT DEFERRABLE
USING INDEX TABLESPACE indexes_01 DISABLE)
TABLESPACE data_01;
A different way to use create table with inline constraint definition is shown here, but remember that if you use inline constraint definition, your constraint can apply only to the column it is inline with. Also, remember that not NULL constraints must always be defined inline.
CREATE TABLE emp
( empid NUMBER
CONSTRAINT pk_emp_01
PRIMARY KEY NOT DEFERRABLE
USING INDEX TABLESPACE indexes_01 ENABLE NOVALIDATE,
empname VARCHAR2(30) NOT NULL,
salary NUMBER NOT NULL )
TABLESPACE data_01;
*Creating Foreign Keys
A foreign key is also defined in the create table or alter table statement. The foreign key in one table refers to the primary key in another, which is sometimes called the parent key. Another clause, on delete cascade, is purely optional. It tells Oracle that if any deletion is performed on rows in a parent table containing referenced key values, all rows in child tables with dependent foreign key values will also be deleted.
CREATE TABLE bank_account
(bank_acct VARCHAR2(40) NOT NULL,
aba_rtng_no VARCHAR2(40) NOT NULL,
empid NUMBER NOT NULL,
CONSTRAINT pk_bank_account_01
PRIMARY KEY (bank_acct)
USING INDEX TABLESPACE indexes_01,
CONSTRAINT fk_bank_account_01
FOREIGN KEY (empid) REFERENCES (emp.empid)
ON DELETE CASCADE)
TABLESPACE data01;
Typically, this relationship is desirable when one table is the child of another. If the on delete cascade option isn't included, then deletion of a record won't be allowed from a parent table that has a corresponding child record in a child table. Additionally, in order to link two columns via a foreign key constraint, the names don't have to be the same, but the datatype for each column must be identical.
CREATE TABLE bank_account
(bank_acct VARCHAR2(40) NOT NULL,
aba_rtng_no VARCHAR2(40) NOT NULL,
empid NUMBER NOT NULL,
CONSTRAINT pk_bank_account_01
PRIMARY KEY (bank_acct)
USING INDEX TABLESPACE indexes_01,
CONSTRAINT fk_bank_account_01
FOREIGN KEY (empid) REFERENCES (emp.empid)
ON DELETE CASCADE)
TABLESPACE data01;
For a foreign key to reference a column in the parent table, the datatypes of both columns must be identical.
*Creating Unique Constraints
Defining a unique constraint is handled as follows: Suppose the DBA decides to track telephone numbers in addition to all the other data tracked in EMP. The alter table statement can be issued against the database to make the change. As with a primary key, an index is created for the purpose of verifying uniqueness on the column. That index is identified with the name given to the constraint.
ALTER TABLE emp
ADD (home_phone varchar2(10)
CONSTRAINT ux_emp_01 unique
USING INDEX TABLESPACE indexes_01);
*Creating Check Constraints
The final constraint is the check constraint. Suppose the company using the EMP and BANK_ACCOUNT tables places a salary cap on all employees of $110,000 per year. To mirror that policy, the DBA issues the alter table statement shown below, and the constraint takes effect as soon as the statement is issued. If a row exists in the table whose column value violates the check constraint, the constraint remains disabled.
ALTER TABLE emp
ADD CONSTRAINT ck_emp01
CHECK (salary < 110000);
Question 19
Question 20
Question 21
In this lesson, you learned about the five types of declarative integrity constraints in Oracle: primary keys, foreign keys, unique constraints, not NULL constraints, and check constraints.
You also learned how to implement each of the five constraints at the table-definition level using either a create table or alter table statement. You learned that a constraint is automatically enabled when it's created, unless a condition exists that violates the constraint.
Finally, you learned that you can force a constraint to be created and enabled by not validating the data, or you can specify that a constraint is to be disabled on creation.
Lesson 9. Using Deferred Constraints
Declarative integrity constraints are more flexible in Oracle8i and higher because you have the option of using either immediate or deferred constraints.After completing this lesson, you should be able to:
- Understand how to use immediate constraints
- Understand how to use deferred constraints
Oracle enhances declarative integrity constraints with new features for their use. The first change is the differentiation between deferred and immediate constraints.
Immediate integrity constraints are enforced immediately, as soon as a statement is executed. If a user attempts to enter data that violates the constraint, Oracle signals an error and the statement is rolled back.
Until Oracle8i, all declarative integrity constraints in the database were immediate constraints. Oracle8i and later releases also offer the option to defer database integrity checking.
Deferred integrity constraints aren't enforced until a user attempts to commit a transaction. If, at that time, the data entered by statements violates an integrity constraint, Oracle will signal an error and roll back the entire transaction.
You can defer any and all deferrable constraints for an entire session with the statement:
alter session set constraints=deferred
Alternatively, you can defer named or all constraints for a specific transaction with the statement:
set constraint name deferred or set constraint all deferred
This form of lazy evaluation temporarily enables data that violates integrity constraints to enter the database.
In Oracle7 there was no way to insert data into a child table for which there wasn't also data in the parent. In Oracle8i or higher, you can conduct the insert on the child table before inserting data into the parent simply by deferring the foreign key constraint.
You may also set constraints for immediate enforcement, using the set constraint name immediate or set constraint all immediate statements.
You can define constraints as either deferrable or not deferrable, and either initially deferred or initially immediate. These attributes can be different for each constraint. You specify the attributes with keywords in the constraint clause, as described next.
Deferrable or Not Deferrable
The definition of a constraint determines whether the constraint is deferrable by users. Two factors play into that determination. The first is the overall deferability of the constraint. If a constraint is created with the deferrable keyword, the constraint is deferrable by user processes until the time the transaction is committed.
In contrast, if the constraint is created with the not deferrable keyword, then user process statements will always be bound by the integrity constraint. The not deferrable status, as shown in the code below, is the default for the constraint. If a constraint has been created with the not deferrable status, then the alter session and set statements for deferring integrity constraints can't be used.
CREATE TABLE employees
(empid NUMBER(10) NOT NULL,
name VARCHAR2(40) NOT NULL,
salary NUMBER(10) NOT NULL,
CONSTRAINT pk_employees_01
PRIMARY KEY (empid) NOT DEFERRABLE);
Initially Deferred or Initially Immediate
The second factor that determines whether a constraint is deferrable by users is the default behavior of the constraint. You can defer the constraint, defined with the initially deferred keyword shown in the code below. This keyword and the not deferrable keyword are mutually exclusive. Or you can have the integrity constraint enforced unless explicitly deferred by the user process, which is specified by the initially immediate keyword.
CREATE TABLE employees
(empid NUMBER(10) NOT NULL,
name VARCHAR2(40) NOT NULL,
salary NUMBER(10) NOT NULL,
CONSTRAINT pk_employees_01
PRIMARY KEY (empid) INITIALLY DEFERRED);
If you don't indicate whether the constraint can be deferrable, Oracle will assume that the constraint can't be deferrable. Later attempts to issue alter table modify constraint name deferrable will return an error. To make the constraint deferrable, you'll have to drop and re-create the constraint.
Question 22
Question 23
In this lesson, you learned that you have the option of both deferred and immediate integrity constraints.
You learned that deferred integrity constraints aren't enforced until the user attempts to commit a transaction. When creating a constraint, you can use the deferrable or not deferrable keywords to set the deferability.
You also learned that immediate constraints are enforced as soon as a statement is executed. You can use the initially deferred or initially immediate keywords to set the default behavior of a constraint.
Lesson 10. Maintaining Integrity Constraints
The more recent versions of Oracle give you more flexibility for maintaining integrity constraints.After completing this lesson, you should be able to:
- Change the status of integrity constraints
- Use the EXCEPTIONS table
Historically, there have been two basic statuses for integrity constraints: enable and disable. In more recent versions of Oracle, the database offers variations on this theme. For example, the enable status has been modified to include an enable validate status, which checks the current contents of a constrained column for violations using this statement
alter table tblname enable validate constraint constraint_name
Another status for integrity constraints, enable novalidate, lets Oracle enforce a constraint on new data entering a table (enabling), but not on data that already exists in the table (no validating).
These statuses can be used by issuing the statement
alter table tblname enable novalidate constraint constraint_name
Oracle can support unique constraints being enforced with nonunique indexes. Columns indexed in the unique constraint should be the first columns in the nonunique index. As long as those columns are the leading columns of the index, they may appear in any order.
Other columns can also be present in the index to make it nonunique.
This feature speeds the process of enabling primary key or unique constraints on the table. The nonunique index supporting the unique or primary key constraint can't be dropped.
In Oracle8i and higher releases, there's a fourth status for integrity constraints called DISABLE VALIDATE. If a constraint is in this state, no modification of the constrained columns is allowed. In addition, the index on the constraint is dropped and the constraint is disabled.
That's useful for a unique constraint. The DISABLE VALIDATE state lets you efficiently load data from a nonpartitioned table into a partitioned table using the EXCHANGE PARTITION option of the alter table command.
Constraints perform their intended operation when enabled, but don't operate when they're disabled. The alter table tblname enable constraint command enables a constraint. You can use the optional validate or novalidate keywords to have Oracle validate or not validate data currently in the constrained column for compliance with the constraint, as shown below.
ALTER TABLE emp ENABLE CONSTRAINT pk_emp_01;
ALTER TABLE emp ENABLE NOVALIDATE CONSTRAINT pk_emp_01;
ALTER TABLE emp ENABLE VALIDATE CONSTRAINT pk_emp_01;
Using validate means Oracle will check data according to the rules of the constraint. If Oracle finds that the data doesn't meet the constraint's criteria, Oracle won't enable the constraint.
Using novalidate causes Oracle to enable the constraint automatically without checking data, but users may later have trouble committing changes if those changes contain data that violates the deferred constraint.
Disabling a constraint is much simpler — just use the alter table tblname disable constraint constraint_name command.
If you want to remove a constraint from a table, use the alter table tblname drop constraint constraint_name statement.
If you want to remove a table that's referenced by foreign keys in other tables, use the drop table tblname cascade constraints statement.
The code for these three options is shown below.
ALTER TABLE emp DISABLE CONSTRAINT pk_emp_01;
ALTER TABLE emp DROP CONSTRAINT ux_emp_01;
DROP TABLE emp CASCADE CONSTRAINTS;
When you employ novalidate to enable or deferrable to defer a primary or unique key, your associated index must be nonunique. This will store potential violator records while the transaction remains uncommitted.
*Using the EXCEPTIONS Table
The only foolproof way to create a constraint without experiencing violations on constraint creation is to create the constraint before any data is inserted. Otherwise you must know how to manage violations using the EXCEPTIONS table. You create this table by running a script called utlexcpt.sql. This file is usually found in the rdbms/admin subdirectory under the Oracle software home directory.
You can manage violations with a table you name yourself, as long as the columns are the same as those created by the utlexcpt.sql script for the EXCEPTIONS table.
The EXCEPTIONS table contains a column for the ROWID of a row that violated the constraint, and a column for the constraint it violated. For constraints that aren't explicitly named (such as not NULL), the constraint name listed is the one generated by Oracle when the constraint was created.
The exceptions into clause can be added to the alter table statement to help identify those rows that violate the constraint you're trying to enable.
This example demonstrates a constraint violation being caused and then resolved using the EXCEPTIONS table. The code block below shows a problem being created.
SQL> alter table emp disable constraint ux_emp_01;
Table altered.
SQL> desc emp
Name Null? Type
------------------------------- -------- ----
EMPID NOT NULL NUMBER
EMPNAME NOT NULL VARCHAR2(30)
SALARY NOT NULL NUMBER
HOME_PHONE VARCHAR2(10)
SQL> insert into emp (empid, empname, salary, home_phone)
2 values (3049394,'FERRIS',110000,'1234567890');
1 row created.
SQL> insert into emp (empid, empname, salary, home_phone)
2 values(40294932,'BLIBBER',50000,'1234567890');
1 row created.
SQL> commit;
Commit complete.
SQL> alter table emp enable validate constraint ux_emp_01
2 exceptions into exceptions;
alter table emp enable validate constraint ux_emp_01
*
ERROR at line 1:
ORA-02299: cannot enable (SYS.UX_EMP_01) - duplicate keys found
This code block shows how you employ the EXCEPTIONS table, using the alter table statement, to resolve the problem. EXCEPTIONS shows you every row that violates the constraint. You could easily have simply deleted the offending data, and then added it after enabling the constraint:
SQL> select rowid, home_phone from emp
2 where rowid in (select rowid from exceptions);
ROWID HOME_PHONE
------------------ ----------
AAAA89AAGAAACJKAAA 1234567890
AAAA89AAGAAACJKAAB 1234567890
SQL> update emp set home_phone = NULL where rowid =
2 chartorowid('AAAA89AAGAAACJKAAB');
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from emp;
EMPID EMPNAME SALARY HOME_PHONE
---------- ------------------------------ ---------- ----------
3049394 FERRIS 110000 1234567890
40294932 BLIBBER 50000
SQL> alter table emp enable validate constraint ux_emp_01;
Table altered.
Remember to clean up the EXCEPTIONS table before and after you use it to avoid being confused by rows violating constraints from different tables.
SQL> truncate table EXCEPTIONS;
Table truncated.
Question 24
Question 25
Question 26
In this lesson, you learned that in addition to the enable and disable statuses for an integrity constraint, you can use enable validate to check the current contents of the constrained column for violations and enable novalidate to enforce the constraint only on new data entering a table.
You also learned how to employ the EXCEPTIONS table to manage violations that occur when you create a constraint after data is inserted in a table.
Lesson 11. Obtaining Constraint Information
Data dictionary views are a good way to access information about constraints.After completing this lesson, you should be able to:
- Use the DBA_CONSTRAINTS view
- Use the DBA_CONS_COLUMNS view
Many data dictionary views give access to information about constraints. Although the views described here are DBA views, they're also available in the ALL_ or USER_ versions, with data limited in the following ways:
ALL_ views correspond to the data objects, privileges, and so on available to the user who executes the query.
USER_ views correspond to only those data objects, privileges, and so on created by the user.
*DBA_CONSTRAINTS
The DBA_CONSTRAINTS view lists detailed information about all constraints in the system. The constraint name and the owner of a constraint are listed, along with the type of constraint, status, and the referenced column name and owner for the parent key, if the constraint is a foreign key constraint.
One weakness of this view is revealed when you try to look up the name of the parent table for a foreign key constraint. You must try to find the table whose primary key is the same as the column specified for the referenced column name.
The DBA_CONSTRAINTS view includes the following columns:
| View columns | Descriptions |
|---|---|
| CONSTRAINT_TYPE | displays p for primary key, r for foreign key, c for check constraints (including checks to see if data is not NULL), and u for unique constraints |
| SEARCH_CONDITION | displays the check constraint criteria |
| R_OWNER | displays the owner of the referenced table, if the constraint is foreign key |
| R_CONSTRAINT_NAME | displays the name of the primary key in the referenced table if the constraint is foreign key |
| GENERATED | indicates whether the constraint name was defined by the user creating a table or generated by Oracle |
| BAD | indicates whether the check constraint contains a reference to two-digit years, a problem for millennium compliance |
*DBA_CONS_COLUMNS
The DBA_CONS_COLUMNS view lists detailed information about every column associated with a constraint. The view includes the name of the constraint and the associated table, as well as the name of the column in the constraint.
If the constraint is composed of multiple columns, as can be the case for primary key, unique, and foreign key constraints, the position or order of the columns is specified by a 1, 2, 3,...n value in the POSITION column of this view. Knowing the position of a column is especially useful in tuning SQL queries to use composite indexes when there's an index corresponding to the constraint.
Question 27
Question 28
In this lesson, you learned that the data dictionary views give you access to information about constraints.
You learned that the DBA_CONSTRAINTS view lists detailed information about all constraints in the system, including the constraint name and owner, the type of constraint, and the status.
You also learned that the DBA_CONS_COLUMNS view provides information about every column associated with a constraint.
Lesson 12. Course In Review
This lesson includes questions and material covering the entire course. You can use it to review and study for the exam.Topic 12.1 Review Notes
*Review Notes| 1. | Know what a unique index is and how it compares with a nonunique index. Know the difference between composite and simple indexes, and the difference between B-tree and bitmap index structure. |
| 2. | Know what the other types of indexes in Oracle are, including descending indexes, function-based indexes, and reverse-key indexes. |
| 3. | Describe the settings that can and can't be used in a storage clause for an index, and know the reason why this is the case. |
| 4. | Know that you can't create unique bitmap indexes — this is a contradiction in terms. Also, understand how Oracle uses the SORT_BITMAP_AREA_SIZE parameter with respect to creating bitmap indexes. |
| 5. | Know in which situations you'd want to rebuild an index, and the statement for doing that. Know what storage parameters can't be changed as part of the alter index command. |
| 6. | Know what the INDEX_STATS dictionary table is used for and how it relates to the analyze command. |
| 7. | Describe the process by which indexes are built or rebuilt by default. Understand why this operation requires downtime. Know how this operation can be performed online in Oracle. |
| 8. | Identify the syntax used to rebuild an index online. Know the performance implications and other restrictions of doing this. |
| 9. | Know why you might want to drop an index, and how to do so. |
| 10. | Identify dictionary views and tables that contain information about indexes. |
| 11. | Understand the significance of column position in a composite index and know where you can look in the data dictionary to find this information. |
| 12. | Know how to use the alter index monitoring usage and alter index nomonitoring usage commands, and understand that these commands let you detect whether an index is being used in the database. |
| 13. | Understand the contents of the V$OBJECT_USAGE view. |
| 14. | Understand when indexes will and will not be used in a database. An index will be used when an indexed column is referenced in a where clause. An index will not be used when a query has no where clause or when a query references columns that aren't indexed. |
| 15. | Understand what declarative data integrity means, and be sure you can name the five types of integrity constraints used in the Oracle database. Know how to use the alter table or create table command to generate constraints. |
| 16. | Remember that Oracle creates indexes in support of primary keys and unique constraints in the database. |
| 17. | Remember that when you define foreign key constraints between two columns in two different tables, those columns must be defined with the same datatype. The column names don't have to be the same. |
| 18. | Understand that whether Oracle enforces constraints on a table depends on whether those constraints are enabled. If a constraint is enabled, Oracle validates incoming data. If the constraint isn't enabled, Oracle doesn't validate incoming data. |
| 19. | Know that you can use the validate and novalidate keywords to determine whether Oracle will check the existing data in a table for violations. |
| 20. | Know where you'd look in the data dictionary to find out whether a constraint's status is enabled or disabled, and where you'd look to determine what columns have integrity constraints. |
Question 29
Question 30
Question 31
Question 32
Question 33
Question 34
Question 35
Question 36
Question 37
Topic 12.2 Exam Preparation
*Exam PreparationThis page contains a bulleted list of fast facts to review or crib notes for the days leading up to the exam.
- Indexes are used to improve performance on database objects in Oracle. The types of indexes in Oracle are bitmap, B-tree, descending, function-based, and reverse-key.
- Bitmap indexes are best used for improving performance on columns containing static values with low cardinality or few unique values in the column.
- B-tree indexes are best used for improving performance on columns containing values with high cardinality.
- Your decision to create an index should weigh the performance gain of using the index against the performance overhead produced when DML statements change index data.
- The pctused parameter isn't available for indexes, because every index block is always available for data changes since Oracle needs to keep index data in order.
- The DBA_INDEXES and DBA_IND_COLUMNS data dictionary views store information about indexes.
- Data integrity constraints are declared in the Oracle database as part of the table definition.
- The types of integrity constraints in Oracle are primary key, foreign key, unique constraint, not NULL constraint, and check constraint.
- A primary key identifies each row in the table as unique.
- A foreign key develops referential integrity between two tables.
- A unique key constraint forces each non-NULL value in the column to be unique.
- A not NULL constraint forces each value in the column to be not NULL.
- A check constraint validates each entry into the column against a set of valid value constants.
- There are different constraint states in Oracle8i and higher, including deferrable constraints or nondeferrable constraints.
- A constraint can be enabled on a table without validating existing data in the constrained column using the enable novalidate clause.
- Oracle uses unique indexes to enforce unique and primary key constraints when those constraints are not deferrable. If the constraints are deferrable, Oracle uses nonunique indexes for those constraints.
- When a constraint is created, every row in a table is validated against the constraint restriction.
- The EXCEPTIONS table stores rows that violate the integrity constraint created for a table.
- The EXCEPTIONS table can be created by running the utlexcpt.sql script.
- The DBA_CONSTRAINTS and DBA_CONS_COLUMNS data dictionary views display information about the constraints of a database.
- Constraints can be enabled or disabled. If enabled, constraints will be enforced. If disabled, constraints will not be enforced.
No comments:
Post a Comment