## 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 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: - Create, use, and modify **sequences** (including `NEXTVAL` and `CURRVAL`). - Create private and public **synonyms** to simplify object access. - Create and maintain **indexes**, including function‑based ones. - Query dictionary views to inspect sequences, synonyms, and indexes. --- ## 1. Recap: Schema Objects in Play We now have a cast of schema objects: - **Tables** – store data. - **Views** – virtual tables based on queries. - **Sequences** – generate numbers, usually for keys. - **Indexes** – speed up data retrieval. - **Synonyms** – alternative names for other 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**: - Automatically generate numeric values. - Are **shared** objects; multiple sessions can use the same sequence. - Are commonly used to supply **primary key** values. - Replace ad‑hoc “find max(id)+1” logic (which is a race‑condition factory). - Can cache values in memory for speed (at the cost of potential gaps). ### 2.1 Creating a simple sequence Minimal syntax: ```sql CREATE SEQUENCE demo_seq; ``` This uses defaults: - `START WITH 1` - `INCREMENT BY 1` - `NOMINVALUE` - `NOMAXVALUE` - `CACHE 20` - `NOCYCLE` You can see details via `USER_SEQUENCES` later. ### 2.2 Using a sequence in INSERT statements Suppose you have: ```sql CREATE TABLE demo ( id NUMBER PRIMARY KEY, name VARCHAR2(25) ); CREATE SEQUENCE demo_seq; ``` Insert rows using `NEXTVAL`: ```sql INSERT INTO demo (id, name) VALUES (demo_seq.NEXTVAL, 'Mickey'); INSERT INTO demo (id, name) VALUES (demo_seq.NEXTVAL, 'Mary'); ``` Query: ```sql SELECT * FROM demo; ``` You’ll see `id` populated with 1, then 2, etc. The sequence guarantees **unique** values. ### 2.3 Pseudocolumns `NEXTVAL` and `CURRVAL` - `sequence_name.NEXTVAL` – returns the **next** number in the sequence and advances it. - `sequence_name.CURRVAL` – returns the **current** number, but only after `NEXTVAL` has been called in the session. Example: ```sql SELECT demo_seq.CURRVAL FROM dual; ``` - Fails if `NEXTVAL` hasn’t been used in this session. - Returns the last value generated by `NEXTVAL` otherwise. ### 2.4 Customizing sequences Full syntax allows many options: ```sql CREATE SEQUENCE dept_deptid_seq START WITH 280 INCREMENT BY 10 MINVALUE 1 MAXVALUE 9999 NOCYCLE NOCACHE; ``` Options cheat‑sheet: - `START WITH n` – first value. - `INCREMENT BY n` – step size (can be negative). - `MINVALUE n` / `NOMINVALUE`. - `MAXVALUE n` / `NOMAXVALUE`. - `CYCLE` / `NOCYCLE` – whether to wrap around when hitting max/min. - `CACHE n` / `NOCACHE` – how many values to buffer in memory. **Caching and gaps:** You may see **gaps** in sequence numbers when: - A transaction is rolled back after fetching `NEXTVAL`. - The instance crashes. - Cached values are lost when a session ends. 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: ```sql 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: ```sql ALTER SEQUENCE demo_seq INCREMENT BY 5 MAXVALUE 10000 CACHE 50; ``` Rules: - You must own the sequence or have `ALTER` privilege on it. - Changes affect **future** numbers only. - To restart from a different `START WITH`, you must `DROP` and `CREATE` the sequence anew. Drop a sequence: ```sql DROP SEQUENCE demo_seq; ``` Inspect sequences via: ```sql 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**: - Is a schema object that provides an **alternate name** for a table, view, sequence, procedure, etc. - Stores only a pointer to the real object in the dictionary—minimal storage. - Can hide the owning schema and object name from end users. ### 3.1 Private vs public synonyms - **Private synonym** – lives in a single schema; only that user (and those with privileges) sees it. - **Public synonym** – lives in the `PUBLIC` schema; visible to **all** users (subject to underlying object privileges). Example – private synonym: ```sql CREATE SYNONYM dept FOR hr.departments; SELECT * FROM dept; -- actually querying hr.departments ``` Example – public synonym (requires `CREATE PUBLIC SYNONYM` privilege): ```sql CREATE PUBLIC SYNONYM employees FOR hr.employees; ``` The classic example is `DUAL`: - Table `SYS.DUAL` exists in the SYS schema. - A public synonym `DUAL` is created, so you simply write `SELECT SYSDATE FROM dual;`. ### 3.2 Dropping synonyms ```sql DROP SYNONYM dept; DROP PUBLIC SYNONYM employees; ``` You must own the synonym or have appropriate system privileges. Inspect your synonyms with: ```sql 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: - Are based on one or more columns. - Can reduce full‑table scans by providing direct access paths. - Are maintained automatically by Oracle. - Speed up reads at the cost of extra work on writes (INSERT/UPDATE/DELETE). ### 4.1 Automatic indexes from constraints When you create: - A **PRIMARY KEY** constraint, or - A **UNIQUE** constraint, Oracle automatically creates a unique index (unless one already exists) to enforce uniqueness. ### 4.2 Manually creating indexes To create a non‑unique index: ```sql CREATE INDEX emp_last_name_idx ON employees (last_name); ``` - Useful when a column is frequently used in WHERE/ORDER BY and has a selective distribution. To create a unique index explicitly: ```sql 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: ```sql 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: - Type (e.g., B‑tree vs bitmap). - Partitioning. - Uniqueness properties. Example: ```sql 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: ```sql 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: - System‑named indexes like `SYS_C00NNNN` created for primary keys. - Your own named indexes. Drop an index: ```sql 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: - **Sequences**: - `USER_SEQUENCES` – sequences you own. - **Synonyms**: - `USER_SYNONYMS` – synonyms you own. - `ALL_SYNONYMS` – synonyms you can access. - **Indexes**: - `USER_INDEXES` – metadata about your indexes. - `USER_IND_COLUMNS` – columns used by each index. Example queries: ```sql -- 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: - Create and manage sequences, and use `NEXTVAL`/`CURRVAL` directly or via column defaults. - Recognize sequence caching behavior and why gaps are normal. - Create private and public synonyms so users don’t have to type `OTHER_USER.REALLY_LONG_NAME` all day. - Create, inspect, and drop indexes, including function‑based and invisible indexes, and understand when they help. - Use dictionary views (`USER_SEQUENCES`, `USER_SYNONYMS`, `USER_INDEXES`, `USER_IND_COLUMNS`) to see what exists. 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.