Feb
12

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 »

Jan
18

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 »

Jan
15

Constraints and query performance

dt-improved-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 »