Database Programming - Study Guide Section 12

Database Programming - Study Guide Section 12

Database Programming - Study Guide Section 12

Name ______Date ______

  1. Can a sequence be shared by many users?

Yes, it is a sharable object.

  1. Can a sequence descend in value?

Yes, placing a negative value in the INCREMENT BY.

  1. Why should a sequence be named after its intended use, instead of the table that uses it?

A sequence is not tied to a table, it can be used anywhere.

  1. What data dictionary table are sequences defined?

USER_SEQUENCES

  1. Explain NEXTVAL Pseudocolumns?

Used to extract successive sequence numbers from a specified sequence.

  1. Explain CURRVAL Pseudocolumns?

Used to refer to a sequence number that the current user has just generated.

  1. What are the can do rules for NEXTVAL and CURRVAL?
  1. SELECT list of a SELECT statement that is not part of a subquery
  2. SELECT list of a subquery in an INSERT statement
  3. VALUES clause of an INSERT statement
  4. SET clause of an UPDATE statement
  1. What are the cannot do rules for NEXTVAL and CURRVAL?
  1. SELECT list of a view
  2. SELECT statement with the DISTINCT keyword
  3. SELECT statement with GROUP BY, HAVING, or ORDER BY clauses
  4. Subquery in a SELECT, DELETE, or UPDATE statement
  5. DEFAULT expression in a CREATE TABLE or ALTER TABLE statement
  1. What happens when NEXTVAL is called?

It sends the current value to CURRVAL and assigns a new sequence number to NEXTVAL

  1. When do gaps in sequences occur?
  1. a rollback occurs
  2. the system crashes
  3. a sequence is used in another table
  1. Why are sequence values cached and what happens to these values during a system crash?

To gain access to sequence values faster and cached values are lost after a system crash

  1. Can any sequence be modified by any user?

No, user must own the sequence or have the ALTER privilege.

  1. What is a pointer?

A variable that holds the address of a memory location.

  1. Can a user create an index?

Yes, but the index is used and maintained automatically by the Oracle Server.

  1. What types of indexes can be created?

A unique index automatically created when a column in a table is defined as primary key or a unique key, and a non-unique index which a user can create.

  1. Explain why more indexes on a table does not mean faster queries?

The more indexes associated with a table, the more effort the Oracle Server must make to update all the indexes after a DML operation.

  1. What data dictionary table are indexes defined?

USER_INDEXES

  1. Why would a synonym be beneficial?

Ease of referring to a table owned by another user, shorten lengthy object names

  1. What are the guidelines to create a synonym?
  1. the object cannot be contained in a package
  2. a private synonym name must be distinct from all other objects owned by the same user
  1. How do you change an index?

It has to be removed and then recreate the index

Oracle Academy11-May-2005

Top View