π Lecture 21 β Introduction to Sequence and Synonyms with implementation in Oracle 11g
π Overview: This lecture introduces sequences as a database feature for generating unique numeric values automatically, primarily for primary key columns. It covers the syntax for creating sequences, their implementation in Oracle 11g, interaction with the data dictionary, and their use with Data Manipulation Language (DML) commands. Understanding sequences is essential for maintaining data integrity in multi-user database environments.
ποΈ Topics Covered
The lecture covers the motivation for using sequences to guarantee unique primary key values, the full syntax of the CREATE SEQUENCE command with all parameters (MINVALUE, MAXVALUE, START WITH, INCREMENT BY, CACHE, CYCLE/NO CYCLE), implementation through a practical example creating the "customers_seq" sequence, retrieving sequence values using the dual table and Data Dictionary views, and applying sequences with DML commands like INSERT and DROP.
π Lecture Summary
A. Motivation for sequence
A sequence is a feature supported by some database systems to produce unique auto-number values on demand. Through sequences, unique numbers can be generated automatically to be used in tables. A sequence is a database object from which multiple users can generate unique integers. Using a sequence generator to provide the value for a primary key in a table is an easy way to guarantee that the key value is unique. Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables.
NNNNNNNN. Syntax of Sequence
The syntax of sequence is as follows:
CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;
CYCLE / No CYCLE;
Where:
- MINVALUE: Specify the minimum value of the sequence.
- MAXVALUE: Specify the maximum value the sequence can generate.
- START WITH: Specify the first sequence number to be generated.
- INCREMENT BY: Specify the interval between sequence numbers.
- CACHE: Specify how many values of the sequence the database pre-allocates and keeps in memory for faster access.
- CYCLE: Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.
- NO CYCLE: Specify NOCYCLE to indicate that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.
π Definition β Sequence: A database object that generates unique integers automatically, independent of tables, allowing multiple users to generate unique values for primary keys.
OOOOOOOO. Implementing Sequence
The following statement creates the sequence customers_seq in the sample schema. This sequence could be used to provide customer ID numbers when rows are added to the customers table.
CREATE SEQUENCE customers_seq
START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;
π Formula: Sequence creation pattern β START WITH [initial_value] INCREMENT BY [step] [CACHE/NOCACHE] [CYCLE/NOCYCLE]
π Example: The customers_seq sequence starts at 1000, increments by 1, does not pre-cache values, and does not cycle. The first reference to customers_seq.nextval returns 1000. The second returns 1001. Each subsequent reference will return a value 1 greater than the previous reference.
PPPPPPPP. Sequence & Data Dictionary
The Data Dictionary stores information about sequences. To view sequence details, you can query the user_sequences view:
SELECT * FROM user_sequences
WHERE sequence_name = 'customers_seq';
This query retrieves all metadata about the "customers_seq" sequence from the Oracle Data Dictionary.
QQQQQQQQ. Sequence & DML
Data Manipulation Language (DML) commands can be used with sequences for inserting, deleting, or updating data in the sequence. Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables.
π Definition β Dual table: A special dummy table in Oracle that is used when you need to select a value without referencing an actual table.
π Example: Consider an employee database in which a sequence is created with the name mySeq to auto-generate employee numbers:
- To retrieve the current and next values of a sequence without using Data Dictionary:
SELECT customers_seq.nextval, customers_seq.currval FROM dual; - To insert a row into the sequence:
INSERT INTO emp (empno) VALUES (mySeq.nextVal); - To drop (delete) the sequence:
DROP SEQUENCE mySeq;
π‘ Why this matters: Using NEXTVAL and CURRVAL is the primary way to access sequence values in queries and DML operations. The dual table provides a simple way to test sequence behavior without affecting actual table data.
β Key Takeaways
A sequence is a vital database object for automatically generating unique primary key values, ensuring data integrity in multi-user environments where many users may be inserting records simultaneously. The CREATE SEQUENCE command requires specifying START WITH and INCREMENT BY, with optional CACHE for performance and CYCLE/NO CYCLE for reuse behavior. After creating a sequence, you retrieve its next value using NEXTVAL and the current value using CURRVAL, often through the dual table. Sequences are independent of tables, so the same sequence can serve multiple tables, and DML commands like INSERT and DROP work directly with sequences. Always check the Data Dictionary view user_sequences to verify sequence properties.
π§ Quick Revision Questions
- What is a sequence in Oracle and why is it useful for primary keys?
- In the CREATE SEQUENCE syntax, what is the difference between CYCLE and NOCYCLE?
- How do you retrieve the next value from a sequence named "emp_seq" without inserting into a table?
- Can the same sequence be used to generate primary keys for multiple different tables? Explain.
- What SQL command is used to remove a sequence from the database, and what is its syntax?
π Lecture 22 β Indexes in Databases
π Overview: This lecture introduces indexes as database objects that improve the speed of data retrieval. It explains how indexes work by sorting values and storing row addresses, enabling the database to locate records without scanning every row. The lecture covers index creation, structure, and how to view index information in the data dictionary, which is critical for optimizing query performance.
ποΈ Topics Covered
The lecture begins with an explanation of the basic concept of indexes as sorted data structures that speed up record lookup, illustrated with a table and index table example. It then details how indexes work by creating a separate structure with key values and row IDs, sorted alphabetically. The syntax for creating indexes in Oracle using the CREATE INDEX statement is provided with examples. Finally, the lecture discusses indexes in the data dictionary, explaining how to query USER_INDEXES and USER_IND_COLUMNS to view index metadata.
π Lecture Summary
A. Index Basics
An index is a data structure that the database uses to find records within a table more quickly. Indexes are built on one or more columns of a table; each index maintains a list of values within that field that are sorted in ascending or descending order. Rather than sorting records on the field or fields during query execution, the system can simply access the rows in order of the index. In simple terms, an index is a database object that helps in efficient searching of the data.
Every time a database table is accessed, all the rows in the table are searched to find the required data, which limits database performance. Indexes are used to quickly locate data without having to search every row in a database table every time it is accessed. For example, to search the salary of "Aqeel," all rows in the table would be scanned to locate that record. In large tables, this can tremendously increase the run time of a query.
π‘ Why this matters: Without indexes, even simple lookups require full table scans, which become prohibitively slow as tables grow.
B. Searching Using Index
The index is created on one or more columns and makes it easier to locate records in a table. The index creates another table or index structure, which is not a part of the actual table. This index structure acts as a supplement to the table and contains just two columns: the key values and a row ID/address to the actual row in the table.
The index table/structure sorts the data of the column in alphabetical order, with the address or row ID in another column. As shown in the lecture example, after the index table is created, the data has been sorted alphabetically and the row ID for each record is stored. Now to search any record, the index table can provide the row ID for each record, allowing direct access to the required row without scanning the entire table.
C. Creating Index in Oracle
Creating an index involves the CREATE INDEX statement, which allows you to name the index, specify the table, and specify which column or columns to index. The basic syntax is as follows:
CREATE INDEX index_name
ON table_name (column_name);
Examples:
CREATE INDEX test_emp
ON emp (ename);
CREATE INDEX emp_ind
ON emp (job, sal);
π Definition β Index: A database object that stores sorted key values along with row addresses, enabling efficient data retrieval without full table scans.
π Formula (Syntax): CREATE INDEX index_name ON table_name (column_name);
β This creates a sorted index on the specified column(s) of the given table, which the database will use to speed up queries that search or sort by those columns.
D. Indexes and Data Dictionary
An index is a database object, and the database maintains a Data Dictionary for it. To view information about indexes, the following commands can be used:
-
SELECT * FROM user_indexes;β This displays all the indexes created by the currently logged-in user schema. -
SELECT * FROM user_IND_COLUMNS;β This displays all the columns of indexes created by the currently logged-in user schema.
These data dictionary views allow database developers and administrators to inspect which indexes exist, on which tables, and on which columns, helping them understand and manage the indexing structure of the database.
β Key Takeaways
An index is a crucial database object that stores sorted key values with row IDs, enabling the database to find records without scanning every row. Every time a table is accessed, without an index, all rows must be searched, which severely limits performance on large tables. The CREATE INDEX statement specifies the index name, table, and column(s) to index, as shown with examples on the emp table. Index metadata is maintained in the data dictionary, viewable via user_indexes (for index names and details) and user_ind_columns (for the specific columns indexed). For the exam, remember that an index is a separate structureβnot part of the actual tableβthat acts as a supplement for fast lookups.
π§ Quick Revision Questions
- What is an index, and how does it improve query performance?
- What two pieces of information are stored in an index structure?
- Write the SQL command to create an index named
emp_idxon thesalarycolumn of theemployeetable. - Which data dictionary view displays all indexes created by the currently logged-in user, and which view shows the columns in those indexes?
- Why would searching for a record like "Aqeel" be slow without an index, and how does the index speed up that search?