Skip to Main Content
  • Questions

  • Attribute Clustering/Zone Maps with Hash partitioning

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Manish.

Asked: December 07, 2015 - 5:14 pm UTC

Last updated: October 26, 2016 - 2:24 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

How do Attribute Clustering/Zone work with hash partitioned tables?

and Connor said...

There's a nice demo here

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/12c_aczm/12c_aczm.html

which also covers an example on (range) partitions. But there's some key comments in there:

"Zone maps keep partition-level information as well as zone-level information. This makes it possible to partition elimination on columns that are not included in partition keys or even on dimension attribute values. The likelihood of partition elimination is dependent on the level of correlation between zone map column values and the values in the partition key column (or columns)"

So if you had say 16 partitions, and there is not a good correlation, you could reasonably expect to be probing all the partitions.

Hope this helps.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Z-Order Curve Fitting

Rajeshwaran, Jeyabal, October 14, 2016 - 7:43 am UTC

Team,

Could you explain in more detail about "Z-Order" Curve Fitting? How does it works? and how it helps to promote the co-location of data?

http://docs.oracle.com/database/121/DWHSG/attcluster.htm#CCHFHJDB
Connor McDonald
October 17, 2016 - 1:04 am UTC

(Caveat: I'm no mathematician!)

Z-order takes data of the form (n1,n2,n3,...), and outputs a single value, which we could then use to define where to locate that data on storage.

http://docs.oracle.com/database/121/DWHSG/img/dwhsg136.png is probably the best diagram for this one. You can see that similar data pairs (in this example) get assigned the same Z-value, which in turn would define where that data will reside.

https://en.wikipedia.org/wiki/Z-order_curve has some (reasonably digestable) content on this as well.

Why no data movement ?

Rajeshwaran Jeyabal, October 25, 2016 - 2:13 pm UTC

Team,

could you help me to understand, why no clustering happened during data movement? (is that i am playing with small data sets?)

also why no entries in USER_CLUSTERING_TABLES and USER_CLUSTERING_KEYS ?


demo@ORA12C> create table t as
  2  select *
  3  from all_objects
  4  order by dbms_random.random;

Table created.

demo@ORA12C> create index t_idx on t(object_id);

Index created.

demo@ORA12C>
demo@ORA12C> select i.index_name, t.num_rows, i.clustering_factor, t.blocks
  2  from user_indexes i ,
  3       user_tables t
  4  where i.table_name = t.table_name
  5  and t.table_name ='T';

INDEX_NAME        NUM_ROWS CLUSTERING_FACTOR     BLOCKS
--------------- ---------- ----------------- ----------
T_IDX                81985             81917       1418

1 row selected.

demo@ORA12C> alter table t clustering by
  2  linear order(object_id)
  3  yes on data movement;

Table altered.

demo@ORA12C> select i.index_name, t.num_rows, i.clustering_factor, t.blocks
  2  from user_indexes i ,
  3       user_tables t
  4  where i.table_name = t.table_name
  5  and t.table_name ='T';

INDEX_NAME        NUM_ROWS CLUSTERING_FACTOR     BLOCKS
--------------- ---------- ----------------- ----------
T_IDX                81985             81917       1418

1 row selected.

demo@ORA12C>
demo@ORA12C> alter table t move;

Table altered.

demo@ORA12C>
demo@ORA12C> select index_name, status from user_indexes
  2  where table_name ='T';

INDEX_NAME      STATUS
--------------- --------
T_IDX           UNUSABLE

1 row selected.

demo@ORA12C>
demo@ORA12C> alter index t_idx rebuild nologging;

Index altered.

demo@ORA12C>
demo@ORA12C> select index_name, status from user_indexes
  2  where table_name ='T';

INDEX_NAME      STATUS
--------------- --------
T_IDX           VALID

1 row selected.

demo@ORA12C>
demo@ORA12C> select i.index_name, t.num_rows, i.clustering_factor, t.blocks
  2  from user_indexes i ,
  3       user_tables t
  4  where i.table_name = t.table_name
  5  and t.table_name ='T';

INDEX_NAME        NUM_ROWS CLUSTERING_FACTOR     BLOCKS
--------------- ---------- ----------------- ----------
T_IDX                81985             81917       1418

1 row selected.

demo@ORA12C> select * from user_clustering_tables where table_name ='T';

no rows selected

demo@ORA12C> select * from user_clustering_keys where table_name ='T';

no rows selected

demo@ORA12C>

AC on Exadata storage

Rajeshwaran Jeyabal, October 25, 2016 - 2:34 pm UTC

Team - Tried to run the above test case on Exadata box (12.1.0.2) got this error message.

demo@ORA12C-EXA> select d.predicate_evaluation, d.compress_for
  2  from user_users u,
  3      dba_tablespaces d
  4  where u.default_tablespace = d.tablespace_name;

PREDICA COMPRESS_FOR
------- ------------------------------
STORAGE BASIC

1 row selected.

demo@ORA12C-EXA> create table t
  2  tablespace USERS as
  3  select *
  4  from all_objects
  5  order by dbms_random.random;

Table created.

demo@ORA12C-EXA>
demo@ORA12C-EXA> create index t_idx on t(object_id);

Index created.

demo@ORA12C-EXA> alter table t CLUSTERING by linear order(object_id)
  2  yes on data movement;
alter table t CLUSTERING by linear order(object_id)
              *
ERROR at line 1:
ORA-65403: invalid usage of CLUSTERING clause


demo@ORA12C-EXA>


is that attribute clustering is not supported on exadata storage?
Connor McDonald
October 26, 2016 - 1:20 am UTC

Interesting results with create vs alter

SQL> create table t
  2  as
  3  select *
  4  from all_objects
  5  order by dbms_random.random;

Table created.

SQL>
SQL> alter table t clustering
  2   by linear order (object_id)
  3     yes on load  yes on data movement;
alter table t clustering
              *
ERROR at line 1:
ORA-65403: invalid usage of CLUSTERING clause


SQL>
SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t
  2  clustering by linear order (object_id) yes on load  yes on data movement
  3  as
  4  select *
  5  from all_objects
  6  order by dbms_random.random;

Table created.


I'll hunt around and see what I can find out.

Why no data movement ?

Rajeshwaran Jeyabal, October 26, 2016 - 2:15 am UTC

Could you please respond to the above followup ( Review Title : Why no data movement ? )
Connor McDonald
October 26, 2016 - 2:24 am UTC

Try changing

alter table t clustering by

to

alter table t ADD clustering by


(and this solves your other problem as well)

Thanks!

manishn, October 26, 2016 - 2:04 pm UTC

Very succinctly put. Thanks
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.