SQL Guide Chapter

Lesson 13 – Creating Sequences, Synonyms, and Indexes (or: giving your database superpowers it will absolutely abuse)

And look, your tables are lovely, but on their own they’re a bit slow, slightly inconvenient, and prone to “who assigned this ID twice?” disasters. This is where sequences, synonyms, and indexes stroll in and say: “What...

oracle 19c mysql notes study chapter

And look, your tables are lovely, but on their own they’re a bit slow, slightly inconvenient, and prone to “who assigned this ID twice?” disasters. This is where sequences, synonyms, and indexes stroll in and say: “What if IDs were automatic, names were shorter, and lookups weren’t glacial?”

This lesson is about those supporting actors that make day‑to‑day SQL less painful.

You will learn to:

1. Recap: Schema Objects in Play

We now have a cast of schema objects:

This lesson focuses on sequences, synonyms, and indexes.

2. Sequences – Auto‑Number Generators

Human‑assigned IDs are a problem: they collide, they get reused, and people forget what number comes next. Sequences fix that.

Sequences:

2.1 Creating a simple sequence

Minimal syntax:

CREATE SEQUENCE demo_seq;

This uses defaults:

You can see details via USER_SEQUENCES later.

2.2 Using a sequence in INSERT statements

Suppose you have:

CREATE TABLE demo (
      id   NUMBER PRIMARY KEY,
      name VARCHAR2(25)
    );
    
    CREATE SEQUENCE demo_seq;

Insert rows using NEXTVAL:

INSERT INTO demo (id, name)
    VALUES (demo_seq.NEXTVAL, 'Mickey');
    
    INSERT INTO demo (id, name)
    VALUES (demo_seq.NEXTVAL, 'Mary');

Query:

SELECT *
    FROM   demo;

You’ll see id populated with 1, then 2, etc. The sequence guarantees unique values.

2.3 Pseudocolumns NEXTVAL and CURRVAL

Example:

SELECT demo_seq.CURRVAL
    FROM   dual;

2.4 Customizing sequences

Full syntax allows many options:

CREATE SEQUENCE dept_deptid_seq
      START WITH 280
      INCREMENT BY 10
      MINVALUE 1
      MAXVALUE 9999
      NOCYCLE
      NOCACHE;

Options cheat‑sheet:

Caching and gaps:

You may see gaps in sequence numbers when:

This is normal; sequences guarantee uniqueness, not continuity.

2.5 Using a sequence in a column default

You can bind a sequence directly as a column’s default:

CREATE SEQUENCE id_seq;
    
    CREATE TABLE emp (
      id   NUMBER DEFAULT id_seq.NEXTVAL NOT NULL,
      name VARCHAR2(25)
    );
    
    INSERT INTO emp (name) VALUES ('Smith');
    INSERT INTO emp (name) VALUES ('Jones');

The id column is populated automatically; no need to reference the sequence in each INSERT.

2.6 Modifying and dropping sequences

Use ALTER SEQUENCE to tweak behavior:

ALTER SEQUENCE demo_seq
      INCREMENT BY 5
      MAXVALUE 10000
      CACHE 50;

Rules:

Drop a sequence:

DROP SEQUENCE demo_seq;

Inspect sequences via:

SELECT sequence_name,
           min_value,
           max_value,
           increment_by,
           cache_size,
           last_number
    FROM   user_sequences;

3. Synonyms – Alternative Names for Objects

If you constantly type OTHER_USER.REALLY_LONG_TABLE_NAME, eventually you will revolt. Synonyms exist to prevent that.

A synonym:

3.1 Private vs public synonyms

Example – private synonym:

CREATE SYNONYM dept
    FOR   hr.departments;
    
    SELECT *
    FROM   dept;
    -- actually querying hr.departments

Example – public synonym (requires CREATE PUBLIC SYNONYM privilege):

CREATE PUBLIC SYNONYM employees
    FOR   hr.employees;

The classic example is DUAL:

3.2 Dropping synonyms

DROP SYNONYM dept;
    
    DROP PUBLIC SYNONYM employees;

You must own the synonym or have appropriate system privileges.

Inspect your synonyms with:

SELECT synonym_name,
           table_owner,
           table_name
    FROM   user_synonyms;

4. Indexes – Speeding Up Queries (and Occasionally Slowing Down Writes)

An index is a separate structure that helps Oracle find rows more quickly, like a book index for row addresses.

Indexes:

4.1 Automatic indexes from constraints

When you create:

Oracle automatically creates a unique index (unless one already exists) to enforce uniqueness.

4.2 Manually creating indexes

To create a non‑unique index:

CREATE INDEX emp_last_name_idx
    ON employees (last_name);

To create a unique index explicitly:

CREATE UNIQUE INDEX emp_email_uk
    ON employees (email);

This can be paired with a UNIQUE constraint or used standalone.

4.3 Function‑based indexes

If you frequently query a column through a function, Oracle can index the expression instead of the raw column.

Example – case‑insensitive search on department_name:

CREATE INDEX dept_name_upper_idx
    ON departments (UPPER(department_name));
    
    SELECT department_name
    FROM   departments
    WHERE  UPPER(department_name) = 'SALES';

The optimizer can now use the function‑based index to avoid scanning the entire table every time.

4.4 Multiple indexes on the same columns

Oracle allows multiple indexes on the same column set if they differ in:

Example:

CREATE INDEX emp_btree_idx
    ON employees (employee_id, first_name) INVISIBLE;
    
    CREATE BITMAP INDEX emp_bitmap_idx
    ON employees (employee_id, first_name);

Marking an index INVISIBLE lets you test system performance as if the index didn’t exist, without actually dropping it.

4.5 Inspecting and dropping indexes

Inspect indexes:

SELECT index_name,
           table_name,
           uniqueness,
           visibility
    FROM   user_indexes
    WHERE  table_name = 'EMPLOYEES';
    
    SELECT index_name,
           table_name,
           column_name,
           column_position
    FROM   user_ind_columns
    WHERE  table_name = 'EMPLOYEES';

You’ll see:

Drop an index:

DROP INDEX emp_last_name_idx;

You must own the index or have DROP ANY INDEX privilege.

5. Dictionary Views for Sequences, Synonyms, and Indexes

Key views to remember:

Example queries:

-- Sequences
    SELECT sequence_name,
           increment_by,
           cache_size,
           last_number
    FROM   user_sequences;
    
    -- Synonyms
    SELECT synonym_name,
           table_owner,
           table_name
    FROM   user_synonyms;
    
    -- Indexes
    SELECT index_name,
           table_name,
           uniqueness
    FROM   user_indexes;

6. What You Should Now Be Able to Do

By the end of this lesson, you should be able to:

You now have the tools to automate keys, hide ugly object names, and make lookups fast—all of which are great, as long as you remember that every index you add is another thing your poor database has to babysit on every row change.

← previous view source markdown next →
// sql-guide generated from markdown notes