Learn Something New About Alter Table Move

Imagine that you have a large table, let’s say 500GB in size and 500 million rows in it. You need to get rid of 95% of the data in the table. How would you do it? There are a few ways to do it, definitely a longer way and a shorter way.

The longer way would consist in actually going through deleting the rows in the table, and committing every X number of rows. This method would most likely take hours to complete, and in the end your table would still take up a few hundred gigs in size. So you’d need to reorg the table and rebuild the indexes.
The shorter way would be to save the 5% rows you want to keep into a staging table, the truncate the original table, and finally insert the rows from the staging table back into the original table. This method is much better than the previous one.

Let me tell you a little secret I came across the other day. Well, maybe it is not a secret, but knowledge I was not aware of before, even thou this feature is available since 12.2. The feature I am talking about, is the alter table move including rows statement.

This is genial! Basically it is a shortcut to the method #2 above. Here are some examples of how you could use it:

alter table move including rows where id > 10000;
alter table move including rows where name like 'DR%';
alter table move including rows 
where start_date between sysdate-100 and sysdate-50;

The options in the WHERE clause are infinite! Can you see how this statement can save tons of time and effort on the DBA side? Now, in one command you can get rid of the rows you don’t want to keep and reorg the table either in the same tablespace or a different tablespace.

To better understand how this work, let’s look at an example below.

First, I will create a table called test, with only two columns, to keep things simple. Then I will insert some rows, more precisely 5 rows.

SQL> create table dr.test (id number, name varchar2(30));
Table created.

SQL> insert into dr.test values (1, 'Diana1');
1 row created.

SQL> insert into dr.test values (2, 'Diana.2');
1 row created.

SQL> insert into dr.test values (3, 'Diana.3');
1 row created.

SQL> insert into dr.test values (4, 'Diana.4');
1 row created.

SQL> insert into dr.test values (5, 'Diana.5');
1 row created.

SQL> commit;
Commit complete.

SQL> select count(*) from hr.test;
  COUNT(*)
----------
         5

If I move the table, with the original options, all the rows will be moved.

SQL> alter table hr.test move online tablespace users;

Table altered.

SQL> select count(*) from hr.test;

  COUNT(*)
----------
         5

Now, let me move the table with the including rows option. You will notice that only the rows that comply with the WHERE clause will be moved.

SQL> alter table hr.test move online including rows 
     where name like 'Diana.%' tablespace users;
Table altered.

SQL> select count(*) from hr.test;
  COUNT(*)
----------
         4

SQL> select * from hr.test;
        ID NAME
---------- ------------------------------
         2 Diana.1
         3 Diana.2
         4 Diana.3
         5 Diana.5

You might be wondering what happened with the rest of the rows. Those rows got discarded. Oracle only moved the rows we mentioned in the including clause. Since this is a DDL statement there is no rollback from it.

Were you aware if this little gem? Have you used it before? Can you see it’s potential?

Let me know in the comments section! I read every comment!

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!

If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get the best price, only $18.99 CAD!