Posted by 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.
Learning is not a spectator sport 
Hi Connor,
could you then elaborate what the purpose of the ALTER TABLE command without the ADD keyword is? What is achieved by doing so?
Randolf
I have no idea 🙂 It’s quite possible a syntax error should be thrown, but I’ve not confirmed that internally
Hi connor
Could be a work around to replace an order by clause for query on large tables?
For perf and save temp purpose?
Well, you still always have to specify the ORDER BY, but if the data is clustered in a similar order, then the sorting might be more efficient. I’ll do a separate blog post to demonstrate that.
Connor,
Just an update – as of 12.2 this got fixed.
when we tried to add Attribute clustering without ADD keyword it fails like this.
demo@ORA12C> alter table t clustering by linear order(object_id );
alter table t clustering by linear order(object_id )
*
ERROR at line 1:
ORA-65403: invalid usage of CLUSTERING clause
demo@ORA12C> alter table t add clustering by linear order(object_id );
Table altered.
demo@ORA12C> select * from v$version;
BANNER
——————————————————————————–
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
PL/SQL Release 12.2.0.1.0 – Production
CORE 12.2.0.1.0 Production
TNS for 64-bit Windows: Version 12.2.0.1.0 – Production
NLSRTL Version 12.2.0.1.0 – Production
demo@ORA12C>