Stop Running These 5 SQL Queries on Your Production Database

It was a Tuesday afternoon. One query. No WHERE clause. Fourteen thousand customer records, gone in under a second.

That story is not unique. It happens on Oracle databases every week, in companies you would recognize, to developers with years of experience. The queries that cause the most damage are not obscure. They are the ones you run every day in development without thinking twice.

Production is different. The data is real. The volumes are ten or a hundred times larger. And Oracle SQL Workshop has no undo button.

These are the five SQL queries production database developers run every week and regret. For each one, I will show you what actually breaks, why Oracle handles it differently from what you expect, and what to run instead.

What Makes SQL Queries on a Production Database So Dangerous?

The honest answer is scale. A query that runs in 0.3 seconds on your development table with 500 rows can run for 40 minutes on a production table with 50 million rows. The query is identical. The consequence is not.

The difference between dev data and production data volume

Development databases are small by design. You load a subset of production data, maybe a few thousand rows per table, and query it freely. Nothing breaks. Nothing locks. The optimizer picks a plan that looks fine.

Production is a different machine. Tables grow. Indexes fragment. Concurrent users hit the same rows at the same time. A query that bypasses an index on a 5,000-row table costs almost nothing. The same query on a 20-million-row table holds CPU, I/O, and undo segment resources for minutes. Other sessions queue behind it. The application slows down. Users notice before your monitoring does.

Why Oracle SQL Workshop has no undo button

When you run a DML statement in Oracle SQL Workshop and click Execute, Oracle runs it. Immediately. If you did not wrap the statement in an explicit transaction, there is no going back. Some DDL statements in Oracle, like TRUNCATE TABLE, auto-commit the moment they execute. No rollback, no warning, no second chance.

I wrote a piece on PL/SQL mistakes junior Oracle developers make that covers the transaction model in detail. The short version: understand what commits before you run anything on production.

1. SELECT * FROM table: The Lazy Query That Slows Everything Down

SELECT * is the most common query in any Oracle developer’s muscle memory. It works fine in development. On a production table with millions of rows and dozens of columns, it creates three separate problems at once.

What actually happens when you run SELECT * on a large table

First, Oracle reads every block in the table. It cannot use a covering index because you asked for all columns, and covering indexes only hold the columns you define. The optimizer falls back to a full table scan up to the table’s high-water mark, reading every allocated block whether it contains live rows or not.

Second, Oracle transfers all that data across the network. A table with 80 columns returns 80 columns per row. If you needed 4 columns, you just sent 20 times more data than necessary. On a table with millions of rows, that is a meaningful amount of I/O and network overhead that accumulates fast.

Third, and this is the one developers miss most often, SELECT * breaks applications silently when someone adds a column to the table. The query keeps running. It returns the new column. Code that maps results by position gets the wrong values in the wrong variables. The bug surfaces days later in a format that is very hard to trace back to the original query.

What to run instead

Name your columns. Always.

-- Instead of this:
SELECT * FROM orders WHERE customer_id = 1001;

-- Run this:
SELECT order_id, order_date, status, total_amount
FROM orders
WHERE customer_id = 1001;

If you are exploring a table structure in SQL Workshop, run DESCRIBE table_name first, then select only the columns you need. If you want a row count before doing anything else, run SELECT COUNT(1) FROM table_name rather than fetching rows you will never use.

2. DELETE FROM table Without a WHERE Clause

This one has its own genre of developer horror stories. You want to remove a handful of test records from production. You write the DELETE. You forget the WHERE. You press F5.

Every row in the table is gone.

What Oracle does when you DELETE without a WHERE clause

Oracle processes a DELETE without a WHERE clause row by row. It logs every deletion into the undo tablespace so the operation can be rolled back, as long as you are still inside the same transaction and have not committed. The problem is that most developers run statements in auto-commit mode, especially inside SQL Workshop. The commit fires before they realize what happened.

On a table with ten million rows, the delete also takes a long time. Long enough for other sessions to notice the table locks. Long enough for support calls to start coming in while the query is still running.

The habit that protects you

Before you run any DELETE on production, run the matching SELECT COUNT first and verify the number.

-- Step 1: See what you are about to delete
SELECT COUNT(1) FROM orders WHERE status = 'TEST';

-- Step 2: If the count looks right, delete with the same WHERE clause
DELETE FROM orders WHERE status = 'TEST';

-- Step 3: Verify the result before committing
SELECT COUNT(1) FROM orders WHERE status = 'TEST';

-- Step 4: Commit only when you are certain
COMMIT;

Wrap production deletes in explicit transactions. Run the count. Verify. Then commit. This takes 30 extra seconds. Recovering from an accidental full-table delete takes hours, assuming you have a backup recent enough to restore from.

3. UPDATE table SET column = value Without a WHERE Clause

This one is quieter than a bad DELETE. That is what makes it worse.

Why a missing WHERE on UPDATE is harder to catch than DELETE

When you accidentally delete all the rows in a table, the application breaks immediately. Login fails. Reports return empty. Support calls start within minutes.

When you accidentally update every row in a table, the application keeps running. It reads the wrong values silently. Users see incorrect data and assume it is a display bug or a caching issue. Finance runs end-of-day batch processing and discovers that every salary in the payroll table is 0. That gap between the bad query and the discovery of the damage can be six hours or more. The longer the gap, the harder the recovery.

The salary = 0 scenario that appears in more incident reports than you expect

A developer means to update the salary for one employee record. They write:

UPDATE employees SET salary = 75000 WHERE employee_id = 1042;

Then something interrupts them. They come back to SQL Workshop, forget they were mid-query, and run only the first line. Oracle updates every salary in the table to 75000. No error. No warning. Statement processed.

The same discipline applies here as with DELETE. Run the matching SELECT first. Verify the row count. Wrap in an explicit transaction. Commit last.

-- First: confirm the target row exists and current value
SELECT employee_id, salary FROM employees WHERE employee_id = 1042;

-- Then: update with the WHERE clause intact
UPDATE employees SET salary = 75000 WHERE employee_id = 1042;

-- Verify before committing
SELECT employee_id, salary FROM employees WHERE employee_id = 1042;

COMMIT;

4. LIKE ‘%value%’: The Wildcard That Kills Your Index

This query looks harmless. Developers use it constantly in development to search for a name, a product code, a reference number. On a production table with millions of rows, it can run for minutes and drag down the entire database.

Why a leading % forces a full table scan in Oracle

Oracle’s B-tree indexes store values in sorted order. When you search LIKE 'hassan%', Oracle walks the index from the first character and finds matching entries efficiently. The index is useful. The query is fast.

When you search LIKE '%hassan%', Oracle has no idea where in the sorted index to start. The matching value could begin with any character. Oracle abandons the index entirely and reads every row in the table from beginning to end, comparing each one against the pattern. This is a full table scan on every execution, regardless of how many indexes you have on that column.

On a table with 500 rows, this takes milliseconds. On a table with five million rows, it takes long enough to visibly slow your application and every other query running at the same time.

What to use instead

If you can restructure the search to use a trailing wildcard only, do it:

-- This uses the B-tree index
SELECT customer_id, full_name FROM customers WHERE last_name LIKE 'Hassan%';

-- This does not and scans the full table every time
SELECT customer_id, full_name FROM customers WHERE last_name LIKE '%Hassan%';

If you genuinely need mid-string search on a large column in production, Oracle Text is the right tool. It indexes column content for substring search and returns results in milliseconds even on tables with tens of millions of rows. It takes setup time upfront, but it removes the full table scan permanently.

5. TRUNCATE TABLE: The Fastest Way to Lose Everything in Oracle

Developers choose TRUNCATE over DELETE because it is faster. That part is true. What catches them off guard is what Oracle does that SQL Server and PostgreSQL do not.

Why TRUNCATE is irreversible in Oracle

TRUNCATE TABLE is a DDL statement in Oracle. DDL auto-commits immediately upon execution in Oracle. You cannot wrap a TRUNCATE in a transaction and roll it back. The moment it finishes, the data is gone and the high-water mark resets to zero.

In SQL Server and PostgreSQL, you can roll back a TRUNCATE inside an open transaction. That behavior leads developers who have worked across multiple database platforms to assume Oracle works the same way. It does not. This is one of the most common production accidents I hear about from Oracle developers who moved from a SQL Server background.

-- This does NOT protect you in Oracle
-- TRUNCATE auto-commits before the ROLLBACK can reach it
BEGIN
  -- This commits immediately, ignoring the outer block
  EXECUTE IMMEDIATE 'TRUNCATE TABLE staging_orders';
  ROLLBACK; -- too late
END;

When TRUNCATE is the right choice

TRUNCATE is the right tool for staging tables, ETL landing zones, and temporary working tables where you control the data lifecycle completely. It resets the high-water mark, which means subsequent full table scans run faster than they would after a DELETE that leaves empty blocks allocated above the HWM.

Before running TRUNCATE on anything in production, take a snapshot first:

-- Save a copy before truncating
CREATE TABLE staging_orders_bak AS
SELECT * FROM staging_orders;

-- Now truncate safely
TRUNCATE TABLE staging_orders;

That backup table costs one extra step. It saves you from a conversation with your DBA about restoring from last night’s backup while your ETL pipeline sits idle.

What Should You Do Before Running Any Query on Production?

Five questions. Ask them in order before pressing F5 on anything that modifies data.

1. Am I connected to the right database? Check the connection string in SQL Workshop before you run anything. Running a DELETE against production when you meant to hit development is one of the most common incident causes in Oracle shops.

2. Does this statement have a WHERE clause? If it is a DELETE or UPDATE, the answer must be yes. No exceptions.

3. Did I run the matching SELECT first? Count the rows your DML will affect before it affects them. If the count surprises you, stop and investigate.

4. Is this inside an explicit transaction? Wrap DML in explicit BEGIN and COMMIT blocks when working in SQL Workshop. Auto-commit mode is the enemy of safe production operations.

5. Do I have a recovery path? For destructive operations, know whether you have a recent backup, an active flashback query window, or a backup table to restore from before you run the statement.

These five questions add about two minutes to any production query session. Two minutes is a reasonable price for not opening a P1 incident at 3 PM on a Wednesday.

If you are building frameworks that catch what slips through anyway, the centralized error logging framework for Oracle APEX I built captures errors, call stacks, and full session context automatically using PRAGMA AUTONOMOUS_TRANSACTION. It does not prevent bad queries. It tells you exactly what ran and when something breaks.

Key Takeaways

None of these are obscure edge cases. They are the most common queries in Oracle development, run wrong in ways that are completely obvious in hindsight.

SELECT * kills performance at scale because Oracle cannot use covering indexes and transfers far more data than necessary. DELETE and UPDATE without WHERE wipe or corrupt data you cannot recover from auto-commit. Leading wildcards in LIKE force full table scans that slow the whole database, not just your query. TRUNCATE in Oracle does not roll back, and developers who come from SQL Server or PostgreSQL backgrounds assume it does until it does not.

The pattern behind all five is the same. Habits that work fine in development break hard on production because the data is real, the volumes are larger, and there is no ctrl+Z.

Slow down before F5. Run the SELECT first. Wrap in a transaction. Production will thank you for it.

If you want to go deeper on safe production automation, the DBMS_SCHEDULER Oracle tutorial covers scheduling production jobs so your automated queries run predictably and without surprises. And if you are still building the habits that keep Oracle databases healthy long-term, the article on why Oracle developers get stuck is worth reading next.

Drop a comment below if you have a production SQL story of your own. The Oracle community learns from the ones that went wrong just as much as the ones that went right.

Hassan Raza
An Oracle ACE Associate and Senior Oracle Application Developer at S&H Software Solution. I specialize in Oracle APEX, SQL, and PL/SQL and write about Oracle development at oraclewithhassan.com