Managing Indexes, Synonyms and Sequences: Complete Guide

An index is a separate structure the database maintains alongside a table so it can find rows without scanning the whole thing. The mental model: it is the index at the back of a book. Without one, you read every page. With one, you go straight to the page you need.

CREATE INDEX idx_customer_name ON customers (customer_name);

After that index exists, a query like SELECT * FROM customers WHERE customer_name = 'Alice Corp' can look up the value in the index and jump to the matching row. Without it, the database reads every row and checks the name column one by one.

Indexes make reads faster and writes slower. Every INSERT, UPDATE, or DELETE has to also update every index on the table. On a table with a heavy write load, that overhead matters — three indexes on a table means each INSERT does four writes.

The trade-off is worth it most of the time. Every foreign key column, every frequently filtered column, every column used in a JOIN condition, is a candidate for an index. Every column that is rarely queried is not.

CREATE INDEX does the job. You give the index a name, name the table, and list the columns to index.

CREATE INDEX idx_customer_name
ON     customers (customer_name);

Queries that filter on customer_name now have a shortcut. The database reads the sorted index instead of scanning the table.

For multiple columns, list them in the order you filter on them most:

CREATE INDEX idx_region_limit
ON     customers (region, credit_limit);

That index helps queries filtering on region alone or on both region and credit_limit. It does not help queries filtering on credit_limit alone, because the index is sorted by region first. Column order in a composite index is a decision, not a detail.

Every index you add costs storage and slows down writes. That is why indexes are added deliberately, based on which queries actually run, not preemptively for every column that might be filtered.

DROP INDEX removes an index entirely. The table and its data are untouched — only the index structure is removed.

DROP INDEX idx_customer_name;

Dropping frees the storage the index occupied and removes the maintenance overhead from INSERT, UPDATE, and DELETE. It also removes the speed-up for any query that was relying on the index. Depending on your situation, one of those two effects will matter more than the other.

The usual reasons to drop an index: it is never used (which the database's usage statistics can tell you), it is made redundant by a composite index that already covers the same queries, or the write overhead has become a problem and the read benefit no longer justifies it.

The one caution: dropping an index is not free in the other direction either. Once dropped, any query that was using it now runs without that shortcut. If the index was doing real work, you will notice the next time the query runs.

A unique index does everything a regular index does, plus it prevents duplicate values. That makes it the standard way to enforce business rules like "no two customers can share an id" or "no two users can share an email address."

CREATE UNIQUE INDEX idx_customer_name_unique
ON     customers (customer_name);

After that, any attempt to insert or update a row with a duplicate customer name fails immediately with an error. The database refuses the change — it does not silently allow the duplicate or overwrite an existing one.

Behind the scenes, the database is using the index's sorted structure to detect duplicates efficiently. When a new value is inserted, the database checks whether the value is already in the index. If it is, the operation fails. That check is essentially free because the index is already sorted.

Unique indexes are how primary keys and unique constraints are enforced. When you declare a column as PRIMARY KEY, most databases create a unique index behind the scenes without you asking. When you add a UNIQUE constraint, the same thing happens. The index is what does the work.

A composite index covers more than one column. It exists to speed up queries that filter or sort on the columns together.

CREATE INDEX idx_region_limit
ON     customers (region, credit_limit);

That index is sorted by region first, then by credit_limit within each region. The database can walk it in order to answer:

SELECT * FROM customers
WHERE  region = 'North'
  AND  credit_limit > 50000;

It can also answer queries that filter on region alone. Both of those cases are covered because the leading column of the index matches the filter.

The pattern that trips people up: a composite index cannot be used efficiently for queries that filter only on the second column. WHERE credit_limit > 50000 without a region filter would have to scan the whole index, because the index is sorted by region first. That is why the order of columns matters — the leftmost column is the one the index is primarily built around.

The decision rule: put the most selective filter first, or the column that appears in the most queries first. On our data, region has three values and credit_limit has seven, so putting credit_limit first would give a more selective index — but only if queries actually filter on it more often.

Managing indexes is about keeping only the ones that earn their keep. Too few indexes means slow reads. Too many means slow writes and wasted storage. The balance point moves as a system grows.

The lifecycle has five stages:

• Create indexes for columns that appear in frequent WHERE, JOIN, and ORDER BY clauses.
• Monitor index usage. Every major database can tell you which indexes are being hit and how often.
• Test with EXPLAIN — check that the index is actually being used by the query that motivated it.
• Rebuild or reorganize indexes that have fragmented due to heavy inserts, updates, or deletes.
• Drop indexes that are never used or that duplicate another index's coverage.

The single biggest source of index bloat: creating indexes for "just in case" filters that never end up running. Every such index still gets updated on every write. Cleaning those out periodically is often a bigger win than adding new ones.

Indexes speed up queries by letting the database find rows without scanning the table. Where they help the most:

-- WHERE filter on an indexed column
CREATE INDEX idx_region ON customers (region);
SELECT * FROM customers WHERE region = 'North';

-- JOIN condition on an indexed foreign key
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
SELECT c.customer_name, o.order_id
FROM   customers c
JOIN   orders o ON c.customer_id = o.customer_id;

-- ORDER BY on an indexed column
CREATE INDEX idx_order_amount ON orders (order_amount);
SELECT * FROM orders ORDER BY order_amount DESC;

Each of those queries was previously scanning a table to answer. After the index, the database reads the index instead.

Where indexes do not help: any query that filters on a function of a column (WHERE UPPER(name) = 'ALICE' cannot use a plain index on name), and queries that return most of the table anyway (if you are reading 90% of the rows, a full scan is faster than jumping between index pages and table pages).

The habit worth building: for every slow query, look at the WHERE clause, the JOIN conditions, and the ORDER BY. Those columns are the candidates for an index.

EXPLAIN tells you what the database is going to do before it does it. Running it in front of a query shows the plan — which tables are read, which indexes are used, and how many rows each step is estimated to process.

EXPLAIN
SELECT * FROM customers WHERE region = 'North';

The output on a small table might say "Seq Scan on customers" — the database is reading every row. That is what an index is supposed to prevent, but the database may decide that for small tables a scan is cheaper.

After creating an index:

CREATE INDEX idx_region ON customers (region);

EXPLAIN
SELECT * FROM customers WHERE region = 'North';

The plan should now say something like "Index Scan using idx_region on customers" — the database is using the index.

On large tables, the difference between "Seq Scan" and "Index Scan" is measurable in seconds. Making it a habit to run EXPLAIN when you suspect a slow query has a missing index is how you find them.

CREATE SEQUENCE defines a database object that produces a series of unique numbers on demand. Sequences are the standard way to generate primary key values without manual intervention or concurrency conflicts.

CREATE SEQUENCE order_seq
START WITH     1001
INCREMENT BY   1
NOCACHE;

That sequence starts at 1001, increases by 1 each time, and does not cache values. The alternative to NOCACHE is CACHE, which pre-fetches values into memory for speed — at the cost of losing some values if the database crashes.

Each call to order_seq.NEXTVAL returns the next number:

SELECT order_seq.NEXTVAL FROM dual;   -- 1001
SELECT order_seq.NEXTVAL FROM dual;   -- 1002

And in an INSERT:

INSERT INTO orders (order_id, customer_id, order_amount, order_status)
VALUES (order_seq.NEXTVAL, 'C001', 12000, 'Delivered');

The sequence generates the id. The application does not need to know what the previous id was. Concurrent sessions can call NEXTVAL at the same time without getting the same number, because the sequence internally serialises the calls.

Not every database uses CREATE SEQUENCE. MySQL uses AUTO_INCREMENT on the column itself. SQL Server uses IDENTITY columns. PostgreSQL and Oracle support sequences natively. The concept is the same — the syntax differs.

NEXTVAL advances the sequence and returns the new value. CURRVAL returns the last value NEXTVAL produced in the current session, without advancing anything.

-- Advance and read the new value
SELECT order_seq.NEXTVAL FROM dual;   -- 1001

-- Read what was just generated
SELECT order_seq.CURRVAL FROM dual;   -- 1001 (same value)

The rule that catches people out: CURRVAL only works after NEXTVAL has been called in the same session. Call it cold and the database throws an error — it has no "last value" to return.

This matters in a workflow like "insert an order, then insert its line items referencing the same order id":

INSERT INTO orders (order_id, customer_id, order_amount, order_status)
VALUES (order_seq.NEXTVAL, 'C001', 12000, 'Delivered');

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (order_seq.CURRVAL, 'P001', 2);

The second INSERT uses CURRVAL to get the same order id that was just inserted, without querying the orders table. Same session, same sequence, same value.

One important detail: sequence values are non-transactional. Once NEXTVAL has been called, the number is consumed even if the transaction rolls back. That is why a sequence can produce 100 numbers while your table only has 95 rows — five transactions rolled back after their insert failed. Uniqueness is guaranteed. Continuity is not.

Sequences have a small set of properties you can change. Beyond that, you drop and recreate.

-- Change the increment
ALTER SEQUENCE order_seq INCREMENT BY 10;

-- Change the cache
ALTER SEQUENCE order_seq CACHE 500;

-- Restart from a new value
ALTER SEQUENCE order_seq RESTART START WITH 2001;

-- Drop the sequence entirely
DROP SEQUENCE order_seq;

ALTER SEQUENCE INCREMENT BY changes the step size without resetting the current value. The next NEXTVAL call returns the current value plus the new increment.

ALTER SEQUENCE CACHE changes how many values are pre-fetched into memory. Higher cache means faster NEXTVAL calls but larger gaps if the database crashes.

ALTER SEQUENCE RESTART resets the sequence to a new starting point. This is used when you reload data from scratch and want ids to line up with the reload.

DROP SEQUENCE removes the sequence entirely. Any INSERT that calls NEXTVAL on the dropped sequence will now fail. Before dropping, confirm that nothing in the codebase still references it.

A synonym is an alias for a database object. It lets you refer to a table, view, sequence, or stored procedure by a short name that you choose, instead of the full name that includes the schema and sometimes the database.

CREATE SYNONYM customers
FOR    hr.customers;

SELECT * FROM customers;   -- refers to hr.customers

Why this is useful:

• Shorter queries. SELECT * FROM customers beats SELECT * FROM hr.customers every time.
• Schema independence. If the object moves from hr to sales, the synonym is updated and every query that referenced it keeps working.
• Access abstraction. A user can be granted access to a synonym without being granted access to the underlying schema. That keeps the schema's other objects private.
• Cross-database access. Some databases let a synonym point at a table in a completely different database. Distributed queries then look local.

Synonyms are not containers — they do not hold data. They are pointers, and when the object they point at is dropped or renamed, the synonym stops working until it is updated. That is why schema changes have to be paired with synonym updates in the same deployment.

CREATE SYNONYM defines an alias. You give the synonym a name and point it at the target object.

CREATE SYNONYM customers
FOR    hr.customers;

CREATE SYNONYM customer_summary
FOR    hr.customer_summary_view;

The first creates a synonym for a table. The second creates one for a view. Once they exist, queries can use the short names:

SELECT * FROM customers;
SELECT * FROM customer_summary;

That works because the database resolves the synonym to the underlying object before executing the query. To the caller, they are equivalent to writing the full name.

The difference between private and public synonyms is one of scope. A private synonym exists only for the user who created it — other schemas do not see it. A public synonym is visible to every user in the database:

CREATE PUBLIC SYNONYM customers FOR hr.customers;

Public synonyms are convenient but can collide. Only one public synonym can be named customers at a time, so if two schemas both want to expose a table by that name, one of them has to pick a different synonym.

DROP SYNONYM removes the alias. The underlying object is not affected — the table or view is still there, still owned by its schema.

DROP SYNONYM customers;
DROP PUBLIC SYNONYM customers;

The first drops a private synonym; the second drops a public one. They are different objects, so the syntax has to match the scope of what you created.

After the drop, any query that referenced the synonym fails with "object not found" or "table or view does not exist." Anything that used to write SELECT * FROM customers now needs to write the full name hr.customers — or the synonym needs to be recreated.

The reason to drop a synonym: the underlying object was moved or renamed and the synonym is out of date. Or the synonym was created for an application that no longer runs. Either way, leaving a broken synonym around is confusing — someone will eventually use it, get an error, and spend time tracing back to the fact that the target object no longer exists.

You create a synonym and then reference the underlying object by the synonym name. The database handles the resolution — no client-side change is needed.

-- Create the synonym
CREATE SYNONYM emp FOR hr.employees;

-- Use it
SELECT * FROM emp WHERE department_id = 10;

-- It is equivalent to
SELECT * FROM hr.employees WHERE department_id = 10;

The synonym is only a name, so anything you can do with the original object works with the synonym: SELECT, INSERT, UPDATE, DELETE, GRANT, and even CREATE INDEX. From the database's perspective the object is the same; only the way you refer to it has changed.

The strongest use case is cross-schema access. Suppose your finance schema needs to read the HR schema's employees table. Without a synonym, every query would need to write hr.employees, and the finance schema would need direct access to the HR schema. With a synonym, the query writes emp, and the only grant needed is on the synonym.

Synonyms also help during schema migrations. When the HR schema is renamed to people, the synonym is updated to point at the new name, and every downstream query keeps working with no change.

Every time a synonym's target changes — renamed, moved to a new schema, or converted from a table to a view — the synonym needs to be dropped and recreated. The syntax is the same in all three cases.

-- Target was renamed
DROP SYNONYM customers;
CREATE SYNONYM customers FOR hr.customers_v2;

-- Target was moved to another schema
DROP SYNONYM customers;
CREATE SYNONYM customers FOR sales.customers;

-- Target was converted to a view
DROP SYNONYM customers;
CREATE SYNONYM customers FOR hr.customer_summary_view;

The two-step nature is important. You cannot point an existing synonym at a new object — you have to drop it and create a new one. That is the same pattern for private and public synonyms, with the PUBLIC keyword added for the public version.

The rule that keeps this working: schema changes and synonym updates have to be deployed together. A missing synonym update is a silent failure that only shows up the next time someone runs a query against the synonym. On production systems, syncing them is part of the same migration script or deployment.

Where this matters most: any environment that uses synonyms to abstract schema names. A well-run deployment plan has a step that drops and recreates all affected synonyms whenever a schema migration happens.

Ordinary indexes cannot help when a query filters on a function of a column, like WHERE UPPER(customer_name) = 'ALICE CORP'. The database cannot use an index on customer_name because the values in the index are the raw names, not the uppercased versions.

Function-based indexes (also called expression indexes) solve this. You index the result of the function itself:

CREATE INDEX idx_customer_name_upper
ON     customers (UPPER(customer_name));

After that, the following query can use the index:

SELECT * FROM customers
WHERE  UPPER(customer_name) = 'ALICE CORP';

The index stores the uppercased values, so lookups go through the index the same way a normal index works. The benefit is real — a query that was scanning the whole table now finds its rows in milliseconds.

Support varies. Oracle, PostgreSQL, and SQL Server all support function-based indexes. MySQL 8.0 added support for indexing expressions. The exact syntax for creating them is slightly different on each database, but the concept is the same: index the computed value, not the raw column.

The cost is the same as any index — extra storage and slower writes. And the index is only used when the query uses the exact same expression. Writing WHERE UPPER(customer_name) = 'ALICE CORP' uses the index; WHERE LOWER(customer_name) = 'alice corp' does not, because it is a different expression.

Every database has system views that report which indexes exist, what they cover, and how they are being used. The syntax differs but the information is consistent.

-- Oracle: what indexes exist on a table
SELECT index_name, table_name, uniqueness
FROM   user_indexes
WHERE  table_name = 'CUSTOMERS';

-- Oracle: which columns each index covers
SELECT index_name, column_name
FROM   user_ind_columns
WHERE  table_name = 'CUSTOMERS';

-- MySQL
SHOW INDEX FROM customers;

-- PostgreSQL
SELECT indexname, indexdef
FROM   pg_indexes
WHERE  tablename = 'customers';

Those queries answer "what indexes exist" and "which columns they cover." Some databases also expose usage statistics — how many times each index has been used since the statistics were last reset. That is the query that tells you which indexes are dead weight.

The information is worth checking periodically. Indexes created years ago by people who no longer work on the system often go unused but still slow down every write. Dropping them is usually an easy performance win.

A sequence that is called a thousand times a second can become a bottleneck if every call goes to disk. The fix is caching, and the trade-off is gaps.

ALTER SEQUENCE order_seq CACHE 1000;

A cache of 1000 pre-fetches 1000 values into memory in one go. The next 1000 NEXTVAL calls are served from memory without touching disk. That is orders of magnitude faster than NOCACHE.

The cost of caching is gaps. If the database instance crashes, whatever was cached and not yet consumed is lost. The next call resumes from where the disk-stored value left off, which can skip up to 1000 numbers. On a high-throughput system, gaps of a few hundred or a few thousand are normal and not a problem.

A second lever is the increment size. Widening the increment gives each caller its own range and reduces contention:

ALTER SEQUENCE order_seq INCREMENT BY 10;

With an increment of 10, two sessions are not competing for the same numbers — each call consumes a different slot. This is useful when a sequence is shared across multiple instances of an application and you want to avoid serialising the calls.

The combination — a large cache plus a wide increment — is how high-transaction systems get sequence throughput where naive sequences would choke. The gaps that result are cosmetic. The ids are still unique, which is what actually matters.

An index can be built in ascending or descending order on each column. The choice matters when queries consistently sort in a specific direction.

CREATE INDEX idx_sales_date_amount
ON     sales (sale_date DESC, amount ASC);

That index is sorted by sale_date in descending order, then by amount in ascending order within each date. A query that sorts the same way can use the index to read rows in order and skip the sort step entirely:

SELECT sale_date, amount
FROM   sales
ORDER BY sale_date DESC, amount ASC;

Without the matching index, the database has to load the rows, sort them in memory (or spill to disk on large results), and then return them. With the index, it reads them in the sorted order directly.

The default for each column is ASC. If your queries mostly sort descending — for example, showing the newest records first — indexing in DESC order lets the database read the index in the direction the query needs, without a separate sort.

In practice, most indexes are created with the default ascending order and that is fine. The mixed ASC/DESC pattern is worth using when a specific query is running slowly and you have identified the sort direction as the bottleneck.

Large databases live and die by index strategy. Every query that runs a million times a day needs to be fast, and the difference between a well-indexed and a poorly-indexed system is often measured in hours of batch time.

Five techniques cover most of the ground:

• Index the columns that queries filter, join, and sort on. Start with WHERE clauses and JOIN conditions — those are where the biggest wins are.
• Use composite indexes when queries filter on multiple columns together. A single composite index can replace two single-column indexes and be more selective.
• Monitor usage and drop unused indexes. Every unused index still slows every write. Removing them is free performance.
• Consider covering indexes. An index that includes all the columns a query needs lets the database answer the query from the index alone, without touching the table.
• Partition large tables and partition the indexes with them. A partitioned table with a partitioned index keeps lookups and maintenance scoped to the relevant partition.

The order in which to try these: start with (1), then (2), then (3). Covering indexes and partitioning are refinements that matter most on very large systems where the first three are already in place.

As a table changes — inserts, updates, deletes — its indexes drift away from the compact structure they had when they were built. Reads become slower because the database has to visit more pages to find what it needs. This is fragmentation, and it needs attention.

First step: measure. Every database has a way to report the fragmentation level of each index.

-- SQL Server
SELECT *
FROM   sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'DETAILED');

Then choose a remedy. Two options:

-- Rebuild: reconstructs the index from scratch. Heavyweight.
ALTER INDEX idx_customer_name ON customers REBUILD;

-- Reorganize: reorders pages. Lighter weight.
ALTER INDEX idx_customer_name ON customers REORGANIZE;

The usual rule: fragmentation under 5% is fine and needs no action. Between 5% and 30% gets a reorganize. Above 30% gets a rebuild. Both operations use significant resources, so they run during maintenance windows.

To slow down future fragmentation, use a fill factor that leaves room for growth:

CREATE INDEX idx_customer_name
ON     customers (customer_name)
WITH   (FILLFACTOR = 80);

A fill factor of 80 means each index page is only 80% full when the index is built. New inserts fill the free space before pages have to split. Splits are what cause fragmentation in the first place.

The final habit: schedule index maintenance. Every production system should have a regular job that checks fragmentation and rebuilds or reorganizes as needed. Doing it manually means it happens once and then is forgotten until the next crisis.

An index hint tells the optimizer to use a specific index. It is a way to override the optimizer's choice when you know better than it does — or think you do.

-- Oracle
SELECT /*+ INDEX(customers idx_region) */ customer_name
FROM   customers
WHERE  region = 'North';

-- MySQL
SELECT customer_name
FROM   customers USE INDEX (idx_region)
WHERE  region = 'North';

-- SQL Server
SELECT customer_name
FROM   customers WITH (INDEX(idx_region))
WHERE  region = 'North';

The /*+ INDEX(table_name index_name) */ in Oracle is a comment that happens to be meaningful to the optimizer. The MySQL and SQL Server forms use distinct keywords. PostgreSQL does not support hints at all — the design philosophy is that the optimizer should be trusted, and bad plans should be fixed by improving statistics or rewriting the query, not by locking in a specific index.

The reason to reach for a hint: a query that used to be fast is suddenly slow because the optimizer picked a different index after the data grew, and you have confirmed by testing that the other index is faster.

The reason to avoid hints: they freeze a decision that may become wrong later. The data distribution changes, the index you hinted at stops being optimal, and the hint keeps forcing it anyway. Hints that helped in January can silently hurt in December. Use them as a last resort, and review them whenever the underlying data changes significantly.

The pattern is simple: call NEXTVAL in the VALUES clause where the primary key value belongs.

INSERT INTO orders (order_id, customer_id, order_amount, order_status)
VALUES (order_seq.NEXTVAL, 'C001', 12000, 'Delivered');

That inserts a new order with whatever order id the sequence produces next. The application does not need to know the previous id. Two sessions inserting at the same time both call NEXTVAL and receive different numbers — no collision, no lock, no coordination.

The same pattern works for any table that uses sequences as a source of unique values:

INSERT INTO customers (customer_id, customer_name, region)
VALUES ('C' || LPAD(customer_seq.NEXTVAL, 3, '0'), 'Henry Co', 'East');

That combines the sequence with LPAD to produce ids like C008, C009, C010 — the same format as the existing customers in the sample data.

Support varies by database. Oracle and PostgreSQL use the sequence's NEXTVAL directly. MySQL uses AUTO_INCREMENT on the column, which is effectively a built-in sequence tied to the table. SQL Server uses IDENTITY columns, same idea. The concept is the same — each database just packages it differently.

One practical note: sequences guarantee uniqueness, not continuity. If a transaction rolls back after generating an id, that id is consumed and skipped. Nobody will get that number again. On systems with frequent rollbacks, the id column can have gaps, and that is normal. Do not design reports that assume sequential ids.

Some indexes are created automatically by the database, without the DBA asking. They exist to support constraints — the database needs an efficient way to enforce a uniqueness rule, and an index is that way.

Primary key indexes

CREATE TABLE customers (
    customer_id VARCHAR2(10) PRIMARY KEY,
    customer_name VARCHAR2(100)
);

When the primary key constraint is declared, the database creates a unique index on customer_id automatically. The index is what enforces the uniqueness — every INSERT checks the index for a duplicate.

Unique constraint indexes

CREATE TABLE customers (
    customer_id VARCHAR2(10) PRIMARY KEY,
    customer_name VARCHAR2(100) UNIQUE
);

The UNIQUE keyword on customer_name also creates an automatic unique index. Same reasoning — the index is what makes uniqueness efficient to enforce.

Foreign key indexes — usually not automatic

Foreign key constraints do not automatically create indexes. The database can enforce the constraint without one, but every operation that checks the constraint has to scan the referencing table. On large tables, that is very slow.

CREATE TABLE orders (
    order_id VARCHAR2(10) PRIMARY KEY,
    customer_id VARCHAR2(10) REFERENCES customers(customer_id)
);

-- Manually create the index for performance
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

The rule: index every foreign key column. The constraint works without the index, but the performance is dramatically better with one.

Where the automatic indexing behaviour differs: Oracle and SQL Server create unique indexes for primary keys and unique constraints automatically. MySQL does the same. PostgreSQL does too. For foreign keys, none of them create the index automatically — that is on you.