Database Programming - Study Guide Section 12
Name ______Date ______
- Can a sequence be shared by many users?
Yes, it is a sharable object.
- Can a sequence descend in value?
Yes, placing a negative value in the INCREMENT BY.
- 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.
- What data dictionary table are sequences defined?
- Explain NEXTVAL Pseudocolumns?
Used to extract successive sequence numbers from a specified sequence.
- Explain CURRVAL Pseudocolumns?
Used to refer to a sequence number that the current user has just generated.
- What are the can do rules for NEXTVAL and CURRVAL?
- SELECT list of a SELECT statement that is not part of a subquery
- SELECT list of a subquery in an INSERT statement
- VALUES clause of an INSERT statement
- SET clause of an UPDATE statement
- What are the cannot do rules for NEXTVAL and CURRVAL?
- SELECT list of a view
- SELECT statement with the DISTINCT keyword
- SELECT statement with GROUP BY, HAVING, or ORDER BY clauses
- Subquery in a SELECT, DELETE, or UPDATE statement
- DEFAULT expression in a CREATE TABLE or ALTER TABLE statement
- What happens when NEXTVAL is called?
It sends the current value to CURRVAL and assigns a new sequence number to NEXTVAL
- When do gaps in sequences occur?
- a rollback occurs
- the system crashes
- a sequence is used in another table
- 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
- Can any sequence be modified by any user?
No, user must own the sequence or have the ALTER privilege.
- What is a pointer?
A variable that holds the address of a memory location.
- Can a user create an index?
Yes, but the index is used and maintained automatically by the Oracle Server.
- 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.
- 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.
- What data dictionary table are indexes defined?
- Why would a synonym be beneficial?
Ease of referring to a table owned by another user, shorten lengthy object names
- What are the guidelines to create a synonym?
- the object cannot be contained in a package
- a private synonym name must be distinct from all other objects owned by the same user
- How do you change an index?
It has to be removed and then recreate the index