Chapter Ten Sequences

1
Chapter TenSequences

• Objectives
• objects
• Create and use Sequences
• Create and maintain Indexes
• Create Synonym

2
Objects

• Object Types
• Table
• View
• Sequence
• Index
• Synonym

3
SEQUENCE

• Automatically generates sequence number
• Is sharable
• Typically used to create a primary key value
• Stored and generated independently of tables

4
CREATE SEQUENCE

• CREATE SEQUENCE sequence
• INCREMENT BY n
• START With n
• MAXVALUE n NOMAXVALUE
• MINVALUE n NOMINVALUE
• CYCLE NOCYCLE
• CASHE n NOCACHE

5
CREATE SEQUENCE

• CREATE SEQUENCE ID
• INCREMENT BY 4
• START WITH 1000
• MAXVALUE 99999
• NOCACHE
• NOCYCLE

6
CONFIRMING SEQUENCE

• SELECT sequence_name,
• min_value,
• max_value,
• increment_by,
• last_number
• FROM user_sequences //SEQ

7
CONFIRMING SEQUENCE

• NEXTVAL
• CURRVAL
• NEXTVAL CURRVAL can not be used in
• SELECT list of a view
• SELECT with distinct clause
• SELECT with group by
• SELECT having
• SELECT order by
• Subquery in SELECT, INSERT, or UPDATE

8
USING A SEQUENCE

• INSERT INTO student (id, name)
• VALUES (id.NEXTVAL, 'name')
• SELECT id.CURRVAL
• FROM dual

9
Gap in sequence values

• A rollback
• The system crashes
• A sequence is used in another table

10
Modifying a sequence

• ALTER SEQUENCE id
• INCREMENT BY 10
• START WITH 1000
• MAXVALUE 99999
• NOCACHE
• NOCYCLE

11
Rules for Modifying a Sequence

• You must be the owner or have the ALTER privilege
• Only future sequence numbers are affected
• To restart a sequence sequence must be dropped
  and recreated.

12
Dropping a Sequence

• DROP SEQUENCE id

13
INDEX

• Is an object used to speed up the retrieval of
  rows
• Reduce the disk I/O
• Is independent of the table indexes
• Used and maintained by the system automatically

14
INDEX CREATION

• When using primary key or unique, index is
  created automatically
• Can be created by user on non-unique columns

15
CREATE INDEX

• CREATE INDEX index
• ON table (col, col, __ )
• CREATE INDEX stud_index
• ON student (name)

16
When to use an index

• Columns that are used frequently in WHERE
• Columns containing a wide range of values
• Columns containing a large number of NULLS
• In large tables, where most retrieves are less
  than 5 of the rows

17
When not to use an index

• Small tables
• Tables that are updated frequently
• Retrieve is more than 5 of rows

18
INDEXES

• SELECT
• FROM USER_INDEXES //IND
• SELECT
• FROM USER_IND_COLUMNS

19
Confirming indexes

• SELECT ic.index_name, ic.column_name,
• ic.column_position, ix.uniqueness
• FROM user_indexes ix,
• user_ind_columns ic
• WHERE ic.index_name ix.index_name
• AND ic.table_name 'student'

20
Removing index

• DROP INDEX stud_index

21
SYNONYMS

• Access an object by creating synonyms
• Refer to a table owned by another user
• Shorter length object names
• CREATE PUBLIC SYNONYM synonym
• FOR object
• CREATE SYNONYM ss
• FOR student_view
• DROP SYNONYM ss

22
SYNONYM

• SELECT
• FROM USER_SYNONYM //SYN