Ronnie Bahlsten

Author's details

Name: Ronnie Bahlsten
Date registered: December 14, 2008

Latest posts

  1. Fastest way of deleting all but 1 row in a table? — February 12, 2011
  2. Min/Max Optimization — January 18, 2011
  3. Constraints and query performance — January 15, 2011

Author's posts listings


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 »