sql11 - 13 13 Other Database Objects 13-2 Objectives After...

Info iconThis preview shows pages 1–9. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 13 13 Other Database Objects 13-2 Objectives After completing this lesson, you should After completing this lesson, you should be able to do the following: be able to do the following: • Describe some database objects and their uses • Create, maintain, and use sequences • Create and maintain indexes • Create private and public synonyms 13-3 Database Objects Description Basic unit of storage; composed of rows and columns Logically represents subsets of data from one or more tables Generates primary key values Improves the performance of some queries Alternative name for an object Object Table View Sequence Index Synonym 13-4 What Is a Sequence? • Automatically generates unique numbers • Is a sharable object • Is typically used to create a primary key value • Replaces application code • Speeds up the efficiency of accessing sequence values when cached in memory 13-5 The CREATE SEQUENCE Statement Define a sequence to generate sequential Define a sequence to generate sequential numbers automatically numbers automatically CREATE SEQUENCE sequence [INCREMENT BY n ] [START WITH n ] [{MAXVALUE n | NOMAXVALUE }] [{MINVALUE n | NOMINVALUE }] [{CYCLE | NOCYCLE }] [{CACHE n | NOCACHE}]; 13-6 Creating a Sequence • Create a sequence named DEPT_DEPTNO to be used for the primary key of the DEPT table. • Do not use the CYCLE option. SQL> CREATE SEQUENCE dept_deptno 2 INCREMENT BY 1 3 START WITH 91 4 MAXVALUE 100 5 NOCACHE 6 NOCYCLE; Sequence created. Sequence created. 13-7 Confirming Sequences • Verify your sequence values in the USER_SEQUENCES data dictionary table. • The LAST_NUMBER column displays the next available sequence number. SQL> SELECT sequence_name, min_value, max_value, 2 increment_by, last_number 3 FROM user_sequences; 13-8 NEXTVAL and CURRVAL Pseudocolumns • NEXTVAL returns the next available sequence value....
View Full Document

This note was uploaded on 07/15/2011 for the course ECO 2023 taught by Professor Mr.raza during the Summer '10 term at FAU.

Page1 / 28

sql11 - 13 13 Other Database Objects 13-2 Objectives After...

This preview shows document pages 1 - 9. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online