Wednesday, September 5, 2007

Oracle 10g indexes, views, and sequences

Oracle 10g indexes, views, and sequences

Learning objective

After completing this topic, you should be able to recognize how to create indexes and views and use sequences in an Oracle 10g database.

1. Creating an index

In Oracle, you can associate indexes with tables. Oracle indexes are structures that provide a direct access path to table data. They improve data retrieval and enforce column uniqueness.

Oracle stores indexes in index segments attached to the user's default tablespace. On creating an index, you can attach the index segment to another tablespace to improve performance.

You can create indexes on one or more columns of a table. After you create an index, the Oracle server automatically maintains and uses it.

This means Oracle automatically propagates table updates - for example, adding a new row - to all relevant indexes, with complete transparency to the user making the change.

Question

Which of these are characteristics of indexes?

Options:

  1. They are updated automatically by the Oracle server
  2. They are optional structures associated with tables
  3. They can only be created on one column per table
  4. They improve data retrieval

Answer

Indexes are optional structures, associated with tables, which can improve data retrieval. They are maintained automatically by the Oracle server.

Option 1 is correct. Oracle automatically propagates table updates to all relevant indexes with complete transparency to the user making the change.

Option 2 is correct. Indexes are stored in index segments attached to the user's default tablespace.

Option 3 is incorrect. You can create indexes on one or more columns of a table.

Option 4 is correct. Oracle indexes provide a direct access path to table data.

Suppose you want to create an index on the MANAGER_ID column of the EMPLOYEES table in the Human Resources (HR) schema.

You click the Indexes link under the Schema heading of the Administration tabbed page.

In the Indexes page, you can view index attributes. In this case, you want to attach an index to a table.

You click Create.

This brings you to the Create Index page.

You enter EMPLOYEE_IDX in the Name field.

You enter HR in the Schema field.

And you accept the default tablespace and Standard - B-Tree as the index type.

Under the Indexed Table Object heading, you want to enter the table's name.

You type HR.EMPLOYEES in the Table Name field, and then click Populate Columns.

Under the Table Columns heading, you scroll to the column MANAGER_ID.

You want to sort the MANAGER_ID column in descending order and ensure that it is processed first.

You select DESC from the Sorting Order drop-down list, type 1 in the Order field, and click OK.

This returns you to the Indexes page, which displays a list of indexes for the HR schema.

Question

You want to create an index for another column on the EMPLOYEES table in the HR schema. You have navigated to Create Index page and named the index, DEPARTMENT_IDX. You want to accept the default tablespace and index type.

This question requires you to create an index on the DEPARTMENT_ID column of the EMPLOYEES table in the HR schema, and sort it in descending order with a value of 1. Which option enables you to perform this task?

Options:

  1. In the Table Name field, you type HR.EMPLOYEES. Under the Table Columns heading, you select DESC from the Sorting Order drop-down list of the DEPARTMENT_ID column. You then type 1 in the column's Order field, and click OK.
  2. In the Table Name field, you type HR.EMPLOYEES, and then click Populate Columns. Under the Table Columns heading, you select DESC from the Sorting Order drop-down list of the DEPARTMENT_ID column. You then type 1 in the column's Order field, and click OK.

Answer

In the Table Name field, you type HR.EMPLOYEES, and then click Populate Columns. Under the Table Columns heading, you select DESC from the Sorting Order drop-down list of the DEPARTMENT_ID column. You then type 1 in the column's Order field, and click OK.

2. Creating a view

Views are tailored presentations of the data in one or more tables, or in other views. A view takes the output of a query and treats it as a table.

Views do not contain actual data, but instead derive data from the tables they are based on. These tables are called the base tables of the view.

Views are like tables in that you can query, update, add to, and delete from them.

However, views restrict access to preset rows and columns in a table. This is because actions performed on a view affect the base tables of the view.

Views also hide data complexity and store complex queries.

Question

Which of these are characteristics of views?

Options:

  1. They are customized presentations of the data in one or more tables
  2. They can be queried, updated, added to, and deleted from
  3. They contain base data
  4. They restrict access to preset rows and columns of a table

Answer

Views are customized presentations of the data held in one or more tables. You can query, update, add to, and delete from them. Views restrict access to a predetermined set of rows and columns of a table.

Option 1 is correct. Views also display customized presentations of the data in other views.

Option 2 is correct. Views can also store complex queries and hide complexity.

Option 3 is incorrect. Views derive data from the tables upon which they are based.

Option 4 is correct. This is because actions performed on a view affect the base tables of the view.

Suppose you want to create a view named NEW in the HR schema.

This view will display the employee's ID followed by the employee's last name.

You navigate to the Schema region of the Administration tabbed page.

You click the Views link.

In the Views page, you click Create.

This brings you to the Create View page.

You enter NEW in the Name field.

You enter HR in the Schema field, and decide not to enter any aliases.

In the Query Text field, you enter the following SQL statement:

SELECT employee_id, last_name from employees

You confirm the creation of the view by clicking OK.

An Update Message informs you that the NEW view has been created.

Question

Suppose you want to create a view called EMP_DETAILS_VIEW that contains employee details for the HR schema. You've already opened the Administration tabbed page.

This question requires you to navigate to the Create View page, specify the view name and schema, and then click in the Query Text field. Which option enables you to perform this task?

Options:

  1. In the Administration tabbed page, you click the Views link. In the Views page, you click Create. In the Name field of the Create View page, you type EMP_DETAILS_VIEW. You then type HR in the Schema field, and click in the Query Text field.
  2. In the Administration tabbed page, you click the Views link. In the Views page, you click Create. In the Name field of the Create View page, you type EMP_DETAILS_VIEW. You then type OE in the Schema field, and click in the Query Text field.

Answer

In the Administration tabbed page, you click the Views link. In the Views page, you click Create. In the Name field of the Create View page, you type EMP_DETAILS_VIEW. You then type HR in the Schema field, and click in the Query Text field.

3. Using a sequence

A sequence is a database object from which multiple users can create unique integers.

You typically use sequences to create primary key values.

The parameters you can enter for a sequence include

  • Name
  • Schema
  • Type
  • Maximum Value
  • Minimum Value
Name
In the Name field, you create the sequence name by using Oracle's standard naming rules.
Schema
The Schema field enables you to name the owner of the sequence.
Type
In the Type field, you can specify whether a sequence is either ascending or descending.
Maximum Value
The Maximum Value option and field enable you to specify the maximum value the sequence can generate. This integer value can have 28 or fewer digits, but must be greater than the Minimum Value and Initial parameters. The Unlimited option provides a maximum value of 10 to the power of 27 for an ascending sequence or -1 for a descending sequence. The default is Unlimited.
Minimum Value
The Minimum Value option and field enable you to specify the minimum value of the sequence. This integer value can have 28 or fewer digits, but must be less than or equal to the Initial parameter and less than Maximum Value. The Unlimited option provides a minimum value of 1 for an ascending sequence or -10 to the power of 26 for a descending sequence. The default is Unlimited.

Other sequence parameters include

  • Interval
  • Initial
  • Cycle Values
  • Order Values
  • Cache Options
Interval
The Interval field enables you to specify the interval between sequence numbers. This integer value can be any positive or negative integer, except zero. This value can have 28 or fewer digits. The default is 1.
Initial
The Initial field enables you to specify the first sequence number to be generated. You use this parameter to start an ascending sequence at a value greater than its minimum or to start a descending sequence at a value less than its maximum.
Cycle Values
The Cycle Values option ensures that Oracle generates a minimum value after an ascending sequence reaches its maximum value. After a descending sequence reaches its minimum, it generates its maximum value. Without this option, Oracle returns an error if you try to retrieve a value after the sequence has been exhausted.
Order Values
The Order Values option ensures that Oracle generates sequence numbers in order of request. You can use this option if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys. You can use this option to guarantee ordered generation if you are using Oracle with Real Application Clusters.
Cache Options
The Cache Options section specifies how many values of the sequence Oracle preallocates and keeps in memory for faster access. This integer value can have 28 or fewer digits. The minimum value for this parameter is 2.

Pseudocolumns behave like table columns but are not actually stored in tables.

You use the following pseudocolumns to refer to sequence values in SQL statements:

  • CURRVAL, which retrieves the current value of a sequence
  • NEXTVAL, which increments the sequence and returns the next value

You must qualify CURRVAL and NEXTVAL with the name of the sequence as shown.

sequence.CURRVAL
sequence.NEXTVAL

Question

Which are parameters of a sequence?

Options:

  1. A parameter that specifies the maximum value the sequence can generate
  2. A parameter that specifies the interval between sequence numbers
  3. A parameter that specifies the first sequence number to be generated
  4. A parameter that specifies the user that can run the sequence

Answer

Sequence parameters include Maximum Value, Interval, and Initial.

Option 1 is correct. The Maximum Value field enables you to specify the maximum value the sequence can generate.

Option 2 is correct. The Interval field enables you to specify the interval between sequence numbers.

Option 3 is correct. The Initial field enables you to specify the first sequence number to be generated.

Option 4 is incorrect. Sequences can be used by many users.

Summary

Indexes are optional structures associated with tables that can improve data retrieval. You can create indexes on one or more columns of a table. The Oracle server updates indexes automatically when the table changes.

Views are customized presentations of the data in one or more tables, or other views. They derive data from base tables. You can query, update, add to, or delete from views, but access is restricted to preset rows and columns.

Sequences are database objects from which multiple users can create unique integers. You generally use sequences to create primary key values.

No comments: