Attribute clustering (part 3)

So in part1 and part2, we looked at creating tables with clustered data.  If you’re ready to climb aboard the attribute clustering heading toward Fastville you might want to take an existing table and cluster it.  In part 2 we saw how we had to be extra careful with syntax.  The same rule applies with altering a table to cluster it.  Lets start with our SOURCE_DATA table which was not clustered.

SQL> create table source_data  as
  2      select d.*
  3      from dba_objects d
  4      where object_id is not null
  5      order by dbms_random.random;

Table created.

SQL>
SQL> select object_id, owner from source_data where rownum < 50;

 OBJECT_ID OWNER
---------- ------------------------------
     37627 PUBLIC
     79081 ORDSYS
     26953 SYS
     74744 PUBLIC
     47975 PUBLIC
     94003 APEX_050000
     59014 SYS
     66409 PUBLIC
     36977 PUBLIC
     74886 PUBLIC
       565 SYS
       ...
       ...

As you see, the data has no particular ordering.  Let’s now run an ALTER command to cluster this table

SQL> alter table source_data clustering by linear order(object_id);

Table altered.

SQL> select object_id, owner from source_data where rownum < 50;

 OBJECT_ID OWNER
---------- ------------------------------
     37627 PUBLIC
     79081 ORDSYS
     26953 SYS
     74744 PUBLIC
     47975 PUBLIC
     94003 APEX_050000
     59014 SYS
     66409 PUBLIC
     36977 PUBLIC
     74886 PUBLIC
       565 SYS
     35456 SYS
     74656 SYS
     ...
     ...

As you can see, nothing seems to have happened. Now that is to be expected, because we have only set an attribute on the table, similar to (say) setting the compression attribute.

Let us now MOVE the data to “reload” the table data.

SQL> alter table source_data move;

Table altered.

SQL>
SQL> select object_id, owner from source_data where rownum < 50;

 OBJECT_ID OWNER
---------- ------------------------------
     37627 PUBLIC
     79081 ORDSYS
     26953 SYS
     74744 PUBLIC
     47975 PUBLIC
     94003 APEX_050000
     59014 SYS
     66409 PUBLIC
     36977 PUBLIC
     74886 PUBLIC
       565 SYS
     35456 SYS
     74656 SYS
     ...
     ...

And …. Bzzzzt! Still nothing has happened. The issue is here is not the MOVE command. We made a tiny error in our initial ALTER statement. Let’s try it again

SQL> alter table source_data ADD clustering by linear order(object_id);

Table altered.

SQL>
SQL> alter table source_data move;

Table altered.

SQL>
SQL> select object_id, owner from source_data where rownum < 50;

 OBJECT_ID OWNER
---------- ------------------------------
         2 SYS
         3 SYS
         4 SYS
         5 SYS
         6 SYS
         7 SYS
         8 SYS
         9 SYS
        10 SYS
        11 SYS
        12 SYS
        13 SYS
        14 SYS
        15 SYS
        16 SYS
     ...
     ...

As you can see, the keyword ADD makes all the difference.  As I said in the previous posts, always validate that your clustering is happening as you expect.