Category Archive: Performance


Fastest way of deleting all but 1 row in a table?

I came across a situation at work today that required me to delete most of the rows in a table. The table wasn’t very large, about 200 000 rows with an average row size of 90-100. Plus indexes. Let’s say it looked something like this: CREATE TABLE order_lines( order_no NUMBER(11) NOT NULL ,order_line_no NUMBER(5) NOT …

Continue reading »


Min/Max Optimization

I recently rediscovered an interesting case when performance took a major hit after a tiny change (or so I thought). Oracle implements a special min/max optimization that is used to spead up queries where we just want the MIN or MAX value of an indexed column. This optimization is only available when querying the leading …

Continue reading »


Constraints and query performance


Today I want to show you an example of when constraints affect query performance. I don’t want to ruin the fun right at the beginning, so I will introduce you to the scenario first, and then describe what is happening. Have a look at the following code. CREATE TABLE t1 AS SELECT rownum AS id …

Continue reading »