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 Smile 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.

6 Comments on “Attribute clustering (part 3)

  1. 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

    • Hi connor
      Could be a work around to replace an order by clause for query on large tables?
      For perf and save temp purpose?

  2. 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.

  3. 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>

  4. Pingback: Attribute clustering….super cool – Learning is not a spectator sport

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: