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

Oracle starts a transaction implicitly when you execute DML. BEGIN/END delimit a PL/SQL block; they do not mean BEGIN TRANSACTION and do not disable a tool’s autocommit. You can roll back uncommitted DML in the same database session. Once it is committed, ROLLBACK cannot undo it. SQL Workshop behavior depends on its execution mode and instance setup, so verify it before running DML. Ordinary permanent-table DDL such as TRUNCATE TABLE introduces implicit commits. See Oracle transaction semantics.

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, asking for unneeded columns can increase data access and prevent an otherwise available index-only plan. It does not automatically force a full table scan: a selective predicate may use an index and table rowid access, and an index can sometimes cover every selected column. Check the actual execution plan and row counts rather than inferring the access path from SELECT * alone. See Oracle index access paths.

Second, SELECT * returns every selected column to the client. A table with 80 columns returns 80 columns per row even if you need only four. The extra bytes depend on the column types, values and client fetching behavior, but unnecessary projection can add significant network and memory overhead.

Third, SELECT * couples your result shape to the table definition. Adding or removing columns can break fixed-record assignments, inserts or client code that expects a particular column list. Naming the columns makes that interface explicit.

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 generates undo for a DELETE, so the changes can be rolled back before commit in the same transaction. If the client commits automatically, that opportunity may be gone before you notice a mistake. Do not assume SQL Workshop or any other client is in the mode you need; check first.

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 production DML, use an authorized connection and verify the target database. For the staged example below, use SQLcl with SET AUTOCOMMIT OFF, or a SQL Developer worksheet with autocommit disabled, and keep the same session. Run one step at a time, stopping before commit. In APEX SQL Commands, confirm that transactional mode is available and enabled for your instance; a PL/SQL wrapper is not a substitute. Run the matching SELECT COUNT and inspect representative rows first.

-- SQLcl only: run this first, in the same session
SET AUTOCOMMIT OFF

-- 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';

-- STOP and review affected-row count and business checks.
-- Then execute exactly one choice separately:
-- COMMIT;    -- accept the verified changes
-- ROLLBACK;  -- discard this session's uncommitted changes

Keep transaction control explicit: check the rows, run the DML, verify the affected-row count and business result, then commit or roll back in that same session. The count and delete are separate statements and may see different committed data under concurrent activity. Use the application’s locking/version checks or a coordinated maintenance window when that matters; a matching count alone is not a concurrency guarantee.

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. Use the same autocommit-off session, inspect the target rows, preserve the complete predicate, check the affected-row count and commit only after review. The example names are illustrative; use approved values and predicates for your application.

-- 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;

-- Stop and review, then run COMMIT or ROLLBACK separately.

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 % limits ordinary B-tree range scans

Oracle’s B-tree indexes store values in sorted order. A prefix predicate such as LIKE 'hassan%' can provide a useful index range, subject to the index, collation, selectivity and optimizer cost. An index being available does not guarantee that it will be chosen or that the query is fast.

With LIKE '%hassan%', the leading wildcard normally prevents narrowing a conventional leading-column B-tree range scan by that pattern. Oracle may still choose a full or fast-full index scan, or use a different selective predicate. A full table scan is possible, not inevitable. Measure the actual plan on representative data.

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:

-- A prefix may support a selective B-tree range scan
SELECT customer_id, full_name FROM customers WHERE last_name LIKE 'Hassan%';

-- A leading wildcard usually cannot narrow that range scan
SELECT customer_id, full_name FROM customers WHERE last_name LIKE '%Hassan%';

If your workload needs text search, evaluate Oracle Text with the appropriate index, lexer, query operators and synchronization policy. It is not a drop-in semantic replacement for every LIKE pattern, and it does not guarantee millisecond response times or eliminate all scans. Test both result equivalence and performance.

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

For an ordinary permanent table, TRUNCATE TABLE is DDL with implicit commit behavior, and its changes cannot be undone with ROLLBACK. A PL/SQL block does not make it rollbackable. Temporary-table rules have exceptions, so check the exact object type. See the Oracle TRUNCATE reference.

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 truncating a production table, coordinate a maintenance window, stop concurrent writers, and have a tested recovery procedure. The following CREATE TABLE AS SELECT example is only a data-copy demonstration. CTAS is itself DDL with commit effects and does not copy all constraints, indexes, grants or other metadata. Data committed after the copy can still be lost by the truncate.

-- Illustrative only: approved maintenance isolation and recovery required
-- Copy the rows; this is not a complete database backup
CREATE TABLE staging_orders_bak AS
SELECT * FROM staging_orders;

-- Destructive DDL: only after validating the copy and recovery plan
TRUNCATE TABLE staging_orders;

Validate the copy, metadata and restore procedure before the destructive step. A convenient backup table may help a controlled staging-table workflow; it is not a replacement for a tested backup and recovery strategy.

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 the scope match the approved change? For normal DELETE or UPDATE work, require a reviewed WHERE clause. Deliberate full-table maintenance is a legitimate exception only when explicitly intended, reviewed and recoverable.

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. Who owns the transaction? Confirm autocommit is off and that you can verify and commit or roll back in the same database session. BEGIN/END is a PL/SQL block, not a transaction switch.

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 records the errors and context you explicitly instrument; it is not a complete SQL audit trail.

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 only the columns you need and inspect the execution plan. Unintended full-table DELETE/UPDATE is dangerous, especially with autocommit. Leading wildcards can make ordinary index range scans ineffective, but do not dictate every optimizer choice. Permanent-table TRUNCATE cannot be rolled back; a copy made with CTAS is only one part of a recovery plan.

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. Inspect the rows, verify transaction control, and review the result before committing. 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